写点什么

留存率计算

用户头像
Flychen
关注
发布于: 2 小时前

留存率计算

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
复制代码


用户头像

Flychen

关注

日拱一卒! 2020.02.21 加入

还未添加个人简介

评论

发布
暂无评论
留存率计算