怎么进行用户留存分析？

猴子数据分析

发布于 2023-03-07 20:09:02

7880

发布于 2023-03-07 20:09:02

文章被收录于专栏：猴子数据分析

【题目】：

某电商平台面试题：

下面为用户登录表，分别计算用户的次日、3日留存数及留存率

【解题思路】

此题的核心是怎么计算用户的留存数/留存率？用户留存率是电商行业经常用到的指标，用户的留存数指“第一天登录，以后几天还继续登录的用户数”，"留存率=次日的留存数/当日总的用户数"。因此先要计算出每日的总的用户数、次日的留存数，然后再计算留存率。

一、计算每个用户登录天数间隔

利用表的自联结计算间隔天数，a表作为前面登录的表（计算当日的用户数），b表作为后登录的表(计算后面的留存数)，间隔日期为{“b表的登录日期”-“a表的登录日期”}，通过a、b两个表的“用户ID”关联可以计算在某一日用户在后面几日的留存数。

select a.登录序号
,a.用户ID
,a.登录日期 as 登录日期a
,b.登录日期 as 登录日期b
,datediff(b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期;

得到一个用户在各个日期与后面几日的间隔天数的笛卡尔积的表

二、计算留存数及留存率

1.计算留存数

把第一步的结果作为临时表dates，用case when聚合函数筛选出符合间隔的天数，“1”代表次日留存，“3”代表3日留存，以此类推，然后把满足条件的用户ID筛选取出来，因满足条件的用户ID有多个，所以需要用distinct去重，然后对满足条件的”用户ID” 进行计数就行了。SQL语句和结果如下：

select dates.登录日期a
,count(distinct dates.用户ID)  as 当日用户数
,count(distinct case when dates.间隔天数=1 then dates.用户ID else null end) as 次日留存数
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff (b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

2.计算留存率

select dates.登录日期a
,count(distinct dates.用户ID) as 当日用户数
,count(distinct case when dates.间隔天数=1 then dates.用户ID else null end) as 次日留存数
,concat(round(count(distinct case when dates.间隔天数=1 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 次日留存率
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff (b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

3、扩展一下，后续可以计算3日，7日，10日、30日的留存率，现增加3日留存，把时间间隔更改一下即可，

select dates.登录日期a
,count(distinct dates.用户ID) as 当日用户数
,concat(round(count(distinct case when dates.间隔天数=1 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 次日留存率
,concat(round(count(distinct case when dates.间隔天数=3 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 3日留存率
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff(b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;