主页 >> 程序猿的东西 >> 计算留存率的SQL(梯形视图)

计算留存率的SQL(梯形视图)

代码及说明

使用with..as..语句计算留存率,可以提高计算的复用率,A1,A2两个表完全可以相同,分开是因为可以设置不同的活跃和留存事件,否则可以进一步简化。还有用户的统计口径,这里用client_id,可以替换为user_id以及client_id+user_id。

A1,A2取出时间范围内每个用户的活跃天数,B1计算出A1中各个用户在哪几天产生留存(retention)事件。C1再根据日期和留存天数算出留存用户数。其实大部分场景下到这里就可以结束了,剩下的计算可以由后续的数据可视化工具来完成。但是因为我用的QuickBI没有这么灵活的操作,于是C2从C1提取0日数据做为日活,直接在模型中计算出留存率。

BI上用这个表结构,把dt放在列,把day_of_retention作为行,再把需要的指标放进去即可实现梯形视图下的留存率表格。

WITH
    A1 AS (
       -- 初始行为
        SELECT dt,
               client_id
        FROM ods_user_behavior_fixed
        WHERE dt >= '${report_date_ph.get(0)}'
        AND dt <= '${report_date_ph.get(1)}'
        AND client_id != ''
        AND event = 'display'
        GROUP BY dt, client_id
    ),
    A2 AS (
    -- 后续行为
        SELECT dt,
               client_id
        FROM ods_user_behavior_fixed
        WHERE dt >= '${report_date_ph.get(0)}'
        AND dt <= '${report_date_ph.get(1)}'
        AND client_id != ''
        AND event = 'display'
        GROUP BY dt, client_id
    ),
    B1 AS (
       -- 时间段内每个用户的留存天数
        SELECT A1.client_id,
               A1.dt,
               DATEDIFF(TO_DATE(A2.dt, 'yyyymmdd'), TO_DATE(A1.dt, 'yyyymmdd'), 'dd') AS day_of_retention
        FROM  A1 INNER JOIN  A2 ON A1.client_id = A2.client_id AND A1.dt <= A2.dt
    ),
    C1 AS (
       -- 每个初始日期,每个留存日的留存人数
        SELECT dt,
               day_of_retention,
               COUNT(distinct client_id) AS cnt
        FROM B1
        GROUP BY dt, day_of_retention
    ),
    C2 AS (
       -- 0日留存等于日活
        SELECT *
        FROM C1
        WHERE day_of_retention = 0
    )
SELECT C1.dt,
       C1.day_of_retention,
       C2.cnt AS dau,
       C1.cnt AS retentions,
       C1.cnt / C2.cnt AS retention_rate
FROM C1
LEFT JOIN C2
    ON C1.dt = C2.dt
ORDER BY C1.dt, C1.day_of_retention
LIMIT 10000;

TODO

关于梯形视图,梯形视图是因为这个表格通常用来看最近的n天数据,但是时间越靠近今天缺失的数据就越多。比如T-1日,他一定不会有次日留存,T-2日一定不会有三日留存,所以产生了梯形。可是如果这个时间再整体往前推n天,那么所有的日期都应该有n日留存,那就可以不是梯形了,但是这个例子还是会输出梯形,你可以通过修改A2的dt范围条件来实现这个更好的体验。

发表评论

滚动至顶部