留存和流失指标

原创

平常心

修改于 2021-11-25 20:55:20

1.1K0

修改于 2021-11-25 20:55:20

文章被收录于专栏：个人总结系列个人总结系列

一.背景和意义

关注产品对应用户的黏性，评判APP初期能否留下用户，以及活跃用户规模增长的情况，尤其在在AARRR模型，留存被单独说明为一个用户运营数据指标。

但是在APP中期和后期，更关心产品的用户稳定性，收益转化，所以流失指标更能反应产品的生命周期处于那个阶段。也有助于活跃用户生命周期分析，以及渠道质量变化情况。

二.数据和指标计算

1.数据来源

1.1 数据库表：用户表/登录表(用户注册时间，最近登录时间，登录地点)

1.2 用户埋点数据：考虑到有些产品，尤其是海外产品是非登录状态可用，所以采用埋点上报数据。

2.留存指标

2.1 留存用户，留存率

衍生指标：次日留存率(Day 1Retention Ratio)

定义：指定日期使用(登录)的用户在隔天(指定日期+1)后继续使用(登录) 用户的比例。

类似指标：3日留存率，7日留存率。

开发实现：

SELECT 
   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 0 THEN user_id  ELSE null END ) AS day_user_num,   --当日用户数
   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 1 THEN user_id  ELSE null END ) AS 1day_user_num,  --次日留存用户数
   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 1 THEN user_id  ELSE null END ) / COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 0 THEN user_id  ELSE null END ) as 1day_ratio, --次日留存率

   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 2 THEN user_id  ELSE null END ) AS 3day_user_num,  --3日留存用户数
   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 2 THEN user_id  ELSE null END ) / COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 0 THEN user_id  ELSE null END ) as 3day_ratio,  --3日留存率

   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 6 THEN user_id  ELSE null END ) AS 7day_user_num, --7日留存用户数
   COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 6 THEN user_id  ELSE null END ) / COUNT(DISTINCT CASE WHEN DATEDIFF($day, event_day) <= 0 THEN user_id  ELSE null END ) as 7day_ratio  --7日留存率
FROM
   user_event 
WHERE
    DATEDIFF($day, event_day) <= 6
AND DATEDIFF($day, event_day) >= 0



---- ok的sql

SELECT 
    t_day,
    count(distinct user_id) as day_user_num,
    count(distinct case when day_interval=1 then user_id else null end) as  1day_user_num,
    count(distinct case when day_interval=1 then user_id else null end)/ count(distinct user_id) as 1day_ratio,
    count(distinct case when day_interval=3 then user_id else null end) as  3day_user_num,
    count(distinct case when day_interval=3 then user_id else null end)/ count(distinct user_id) as 3day_ratio,
    count(distinct case when day_interval=7 then user_id else null end) as  7day_user_num,
    count(distinct case when day_interval=7 then user_id else null end)/ count(distinct user_id) as 7day_ratio
FROM 
(
    SELECT 
        t.day as t_day,
        t.user_id,
        t.n_day_user_id,
        DATEDIFF(TO_DATE(t.n_day,'yyyyMMdd'), TO_DATE(t.day,'yyyyMMdd')) as day_interval
    FROM
    (
        SELECT  a.did AS user_id
                ,b.did AS n_day_user_id
                ,a.day AS day
                ,b.day AS n_day
        FROM    dwd_user_event_di AS a
        LEFT JOIN dwd_user_event_di AS b
        ON      a.did = b.did
        WHERE   
            DATEDIFF(TO_DATE(a.day,'yyyyMMdd'), TO_DATE('20211001','yyyyMMdd'))<=6 
        AND 
            DATEDIFF(TO_DATE(a.day,'yyyyMMdd'), TO_DATE('20211001','yyyyMMdd'))>=0 
    ) t
)
GROUP BY t_day
;

也可以通过行业数据或者平均留存来进行对比，例如：