用户留存率分析

留存率：某一天新增用户在之后的第N天仍然登录的比例，称为第N日留存率。

比如2022年1月1日新增100个用户：

·2022年1月2日这些用户中登陆人数为60，次日留存率为60%；

·2022年1月3日这些用户中登陆人数为50，第2日留存率为50%；

·2022年1月4日这些用户中登陆人数为30，第7日留存率为30%.

使用到的数据表如下：

首先是用户信息表，前十列信息如下：

id	user_name	register_time
1	user1	2022-01-01 00:00:00
2	user2	2022-01-01 00:01:00
3	user3	2022-01-01 00:02:00
4	user4	2022-01-01 00:03:00
5	user5	2022-01-01 00:04:00
6	user6	2022-01-01 00:05:00
7	user7	2022-01-01 00:06:00
8	user8	2022-01-01 00:07:00
9	user9	2022-01-01 00:08:00
10	user10	2022-01-01 00:09:00

generated 2023-09-14 17:51:58 by HeidiSQL 12.5.0.6677

以及用户登录表，前十列信息：

id	uid	login_time
1	4566	2022-01-01 00:00:00
2	4495	2022-01-01 00:00:01
3	8777	2022-01-01 00:00:02
4	398	2022-01-01 00:00:03
5	5659	2022-01-01 00:00:04
6	7102	2022-01-01 00:00:05
7	8534	2022-01-01 00:00:06
8	1360	2022-01-01 00:00:07
9	1199	2022-01-01 00:00:08
10	1913	2022-01-01 00:00:09

generated 2023-09-14 17:53:33 by HeidiSQL 12.5.0.6677

SELECT * FROM t_user
WHERE  DATE_FORMAT( register_time,'%Y%m%d') ='20220101'

首先筛选出2022年1月1日的新增用户。

同一个用户一天可能会登录多次，需要用distinct 

SELECT COUNT(distinct tl.uid)/COUNT(DISTINCT tu.id)as sec
,COUNT(distinct tl1.uid)/COUNT(DISTINCT tu.id) AS two
,COUNT(distinct tl2.uid)/COUNT(DISTINCT tu.id) AS sev
 FROM t_user tu
LEFT JOIN t_user_login tl
ON (tu.id =tl.uid AND DATE( tl.login_time) =DATE(register_time) +INTERVAL '1' day)
LEFT JOIN t_user_login tl1
ON (tu.id =tl1.uid AND DATE( tl1.login_time) =DATE(register_time) +INTERVAL '2' day)
LEFT JOIN t_user_login tl2
ON (tu.id =tl2.uid AND DATE( tl2.login_time) =DATE(register_time) +INTERVAL '7' DAY)
#WHERE  DATE_FORMAT( register_time,'%Y%m%d') ='20220101'
WHERE tu.register_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59' 

得到一月一号的新用户不同时间后的留存率。

如果要分析每一天新用户不同时间后的留存率，把where筛选日期的条件取消即可。

SELECT DATE(tu.register_time),
COUNT(distinct tl.uid)/COUNT(DISTINCT tu.id)as sec
,COUNT(distinct tl1.uid)/COUNT(DISTINCT tu.id) AS two
,COUNT(distinct tl2.uid)/COUNT(DISTINCT tu.id) AS sev
 FROM t_user tu
LEFT JOIN t_user_login tl
ON (tu.id =tl.uid AND DATE( tl.login_time) =DATE(register_time) +INTERVAL '1' day)
LEFT JOIN t_user_login tl1
ON (tu.id =tl1.uid AND DATE( tl1.login_time) =DATE(register_time) +INTERVAL '2' day)
LEFT JOIN t_user_login tl2
ON (tu.id =tl2.uid AND DATE( tl2.login_time) =DATE(register_time) +INTERVAL '7' DAY)
#WHERE  DATE_FORMAT( register_time,'%Y%m%d') ='20220101'
#WHERE tu.register_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59' 
GROUP BY DATE(tu.register_time)

得到如下结果：

DATE(tu.register_time)	sec	two	sev
2022-01-01	0.5917	0.5257	0.3472
2022-01-02	0.5236	0.4583	0.3243
2022-01-03	0.4736	0.4111	0.3153
2022-01-04	0.4382	0.3847	0.2819
2022-01-05	0.3986	0.3778	0.2826
2022-01-06	0.3542	0.3368	0.2764
2022-01-07	0.3618	0.3140	0.2684

generated 2023-09-14 20:11:59 by HeidiSQL 12.5.0.6677

但是由于join次数太多导致代码的效率非常低，所以采用窗口函数对其进行优化。

WITH t1 AS (
SELECT u.id, u.user_name, date(u.register_time) reg_date, date(l.login_time) login_date,
       DENSE_RANK() OVER (PARTITION BY date(u.register_time) ORDER BY u.id) daily_reg,
       DENSE_RANK() OVER (PARTITION BY date(u.register_time), date(l.login_time) ORDER BY l.uid) daily_login
FROM t_user u
LEFT JOIN t_user_login l 
ON (l.uid = u.id AND date(l.login_time) BETWEEN date(u.register_time) + INTERVAL '1' DAY AND date(u.register_time) + INTERVAL '30' DAY)
),
t2 AS (
SELECT reg_date, max(daily_reg) daily_reg, login_date, max(daily_login) daily_login
FROM t1
GROUP BY reg_date, login_date)
SELECT reg_date, max(daily_reg),
       100*max(CASE WHEN login_date = reg_date + INTERVAL '1' DAY THEN daily_login END)/max(daily_reg) rr1,
       100*max(CASE WHEN login_date = reg_date + INTERVAL '3' DAY THEN daily_login END)/max(daily_reg) rr3,
       100*max(CASE WHEN login_date = reg_date + INTERVAL '7' DAY THEN daily_login END)/max(daily_reg) rr7,
       100*max(CASE WHEN login_date = reg_date + INTERVAL '30' DAY THEN daily_login END)/max(daily_reg) rr30
FROM t2 
GROUP BY reg_date;

其中t1中的窗口函数分别按照uid进行排序，序号最大的数也就是每天的新用户人数。

同理，第二个窗口函数，序号最大的数就是不同天的新用户人数不同天数之后的留存人数。

t2表格选出每天的新用户人数，以及留存人数。

最后进行汇总计算，将列转化成行。