1
留存率计算
发布于: 2021 年 04 月 20 日
留存率计算
1、数据现状
日活表 active 表（表中记录了每次用户打开 app 信息、日增量），假定目前表中有两个字段：
dt (日期字段：eg 2021-04-18)
uid（用户 Id）
2、演示数据
dt uid
2021-04-18 chen2021-04-18 zhang2021-04-18 wang2021-04-19 chen2021-04-19 liu2021-04-19 li2021-04-20 chen2021-04-20 wang2021-04-20 li2021-04-21 li2021-04-21 zhang2021-04-22 li2021-04-22 wang2021-04-23 li2021-04-23 chen2021-04-24 li2021-04-24 liu
复制代码
3、次日留存率计算
select
(select count(uid) from active where dt ='2021-04-19' and uid in (select uid from active where dt ='2021-04-18'))
/
(select count(uid) from active where dt ='2021-04-18') 次日留存率;
复制代码
4、多日留存率计算
以连续 7 日留存率为例
4.1、确定天数之间的天数间隔 gap
select uid as uid2, -- 计算活跃用户距离第0天的天数
datediff(dt,'2021-04-18') as gap
from active where dt BETWEEN '2021-04-18' and '2021-04-25';
复制代码
4.2、计算每天留存的用户数 retention_num
-- 统计第0天的新增用户在第1~7天活跃用户中的留存数量
SELECT
target_date 目标日期,gap 间隔日期,count(distinct uid2) as retention_num -- target_date,gap,count(*)FROM ( ( SELECT dt AS target_date, uid AS uid1 FROM active WHERE dt = '2021-04-18' ) t01 LEFT JOIN ( SELECT uid AS uid2, -- 计算活跃用户距离第0天的天数 datediff(dt, '2021-04-18') AS gap FROM active WHERE dt BETWEEN '2021-04-18' AND '2021-04-25' ) t02 ON t01.uid1 = t02.uid2
) group by target_date,gap
复制代码
4.3、计算 7 日内留存率
select t11.dt,t12.gap,t12.retention_num,t12.retention_num/t11.day0_uv as '留存率'
from (
(-- 统计第0天的新增用户数
SELECT dt,count(uid) as day0_uvfrom activewhere dt ='2021-04-18'group by dt
) t11
left JOIN
(-- 统计第0天的新增用户在第1~7天活跃用户中的留存数量
SELECT
target_date,gap ,count(distinct uid2) as retention_num -- target_date,gap,count(*)FROM ( ( SELECT dt AS target_date, uid AS uid1 FROM active WHERE dt = '2021-04-18' ) t01 LEFT JOIN ( SELECT uid AS uid2, -- 计算活跃用户距离第0天的天数 datediff(dt, '2021-04-18') AS gap FROM active WHERE dt BETWEEN '2021-04-18' AND '2021-04-25' ) t02 ON t01.uid1 = t02.uid2
) group by target_date,gap
) t12
on t11.dt = t12.target_date)
order by t11.dt,t12.gap
复制代码
4.4 结果
dt gap retention_num 留存率2021-04-18 0 3 1.00002021-04-18 1 1 0.33332021-04-18 2 2 0.66672021-04-18 3 1 0.33332021-04-18 4 1 0.33332021-04-18 5 1 0.33332021-04-18 7 1 0.3333
复制代码
划线
评论
复制
发布于: 2021 年 04 月 20 日阅读数: 15
Flychen
关注
日拱一卒，做好每件小事。 2020.02.21 加入
还未添加个人简介
评论