活跃用户数怎么计算_留存率计算

50e4531945a704657a6450e9c35ab4e3.png

数据源:来自猴子老师的留存率分析教程

链家面试题:如何分析留存率?​mp.weixin.qq.com

【面试题】

手机中的相机是深受大家喜爱的应用之一,下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。

12b4ae24559f7455ae924745b0611463.png

用户id:用户唯一标识;
应用名称:是手机中的某个应用,例如相机、微信、大众点评等。
启动时长:某一天中使用某应用多长时间(分钟)。启动次数:某一天中启动了某应用多少次。
登陆时间:使用手机的日期。例如2018-05-01。

现在该手机厂商想要分析手机中的应用(相机)的活跃情况,需统计如下数据:

某日活跃用户(用户id)在后续的一周内的留存情况(计算次日留存用户数,3日留存用户数,7日留存用户数)

指标定义:

某日活跃用户数,某日活跃的去重用户数。

N日活跃用户数,某日活跃的用户数在之后的第N日活跃用户数。

N日活跃留存率,N日留存用户数/某日活跃用户数

例:登陆时间(20180501日)去重用户数10000,这批用户在20180503日仍有7000人活跃,则3日活跃留存率为7000/10000=70%

求解过程:

第一步:通过数据自联结,然后找出数据a和数据b都是相机,且b的时间小于a的时间的数据(例如a中日期为2020-05-02,b中则为5月2日之后的数据)。

select a.用户id, a_t, b_t from
(select 用户id, 登陆时间 a_t from 留存率题目 
where 应用名称 = '相机') a left join 
(select 用户id, 登陆时间 b_t from 留存率题目 
where 应用名称 = '相机') b 
on a.用户id = b.用户id and a_t < b_t[1]

b5826db5425ba2a43069c3fe34c3585c.png

第二步:求出at 和 b_t 的时间间隔,使用函数datediff[2]

select *, datediff(b_t, a_t) as 时间间隔 from
(select a.用户id, a_t, b_t from
(select 用户id, 登陆时间 a_t from 留存率题目 
where 应用名称 = '相机') a left join 
(select 用户id, 登陆时间 b_t from 留存率题目 
where 应用名称 = '相机') b 
on a.用户id = b.用户id and a_t < b_t) c

6dfe97d711425a85c516886ee6efb141.png

第三步:使用case when分组计算

select a_t, count(distinct case when 时间间隔=1 then 用户id else null end) 次日留存数,
count(distinct case when 时间间隔=1 then 用户id else null end)/count(distinct 用户id) 次日留存率,
count(distinct case when 时间间隔=3 then 用户id else null end) 3日留存数,
count(distinct case when 时间间隔=3 then 用户id else null end)/count(distinct 用户id) 3日留存率,
count(distinct case when 时间间隔=7 then 用户id else null end) 7日留存数,
count(distinct case when 时间间隔=7 then 用户id else null end)/count(distinct 用户id) 7日留存率
from
(select *, datediff(b_t, a_t) as 时间间隔 from
(select a.用户id, a_t, b_t from
(select 用户id, 登陆时间 a_t  from 留存率题目 
where 应用名称 = '相机') a left join 
(select 用户id, 登陆时间 b_t from 留存率题目 
where 应用名称 = '相机') b 
on a.用户id = b.用户id and a_t < b_t) c) d
group by a_t

b13814e136f9a05c2342b7d029e3f83b.png

总结:1、涉及到时间间隔,要考虑自连表的使用;

2、时间间隔的计算,datediff和timestampdiff的用法区别;

3、分组计算case when的用法。

参考

  1. ^注意datediff的格式为datediff(a,b),输出的是a-b的结果;此处也可以使用timestampdiff(day,a,b),输出结果为b-a。此外,datediff只用来计算以天为单位的时间间隔,timestampdiff则可以计算month/day/hour/second等时间间隔
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页