2020-10-29

什么是用户留存率

在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。

这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。顾名思义，留存指的就是“有多少用户留下来了”。留存用户和留存率体现了应用的质量和保留用户的能力。

如何计算留存率

留存率=新增用户中登录用户数/新增用户数*100%（一般统计周期为天）

新增用户数：在某个时间段（一般为第一整天）新登录应用的用户数；

登录用户数：登录应用后至当前时间，至少登录过一次的用户数；

第N日留存：指的是新增用户日之后的第N日依然登录的用户占新增用户的比例

第1日留存率（即“次留”）：（当天新增的用户中，新增日之后的第1天还登录的用户数）/第一天新增总用户数；

第3日留存率：（当天新增的用户中，新增日之后的第3天还登录的用户数）/第一天新增总用户数；

第7日留存率：（当天新增的用户中，新增日之后的第7天还登录的用户数）/第一天新增总用户数；

第30日留存率：（当天新增的用户中，新增日之后的第30天还登录的用户数）/第一天新增总用户数；

 

• 表结构：

仅考虑了最简单的情况，创建一个名为user_login的表，表中有两个字段，分别是用户ID：u_id，用户登录日期：log_day

CREATE TABLE `user_login`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `u_id` int(11) NOT NULL,
  `log_day` date NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic

• 测试数据库：MySQL 8.0
• 1.计算某一日的用户留存率，这里以'2020-01-01'为例

select *,
concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率,
concat(round(100*三十日留存用户/日新增用户数,2),'%')  三十日留存率 
from 
(	
	select a.log_day 日期,
	count(distinct a.u_id)  日新增用户数,
	count(distinct b.u_id)  次日留存用户,
	count(distinct c.u_id)  三日留存用户,
	count(distinct d.u_id)  七日留存用户,
	count(distinct e.u_id)  三十日留存用户
	from 
	(
		-- 确保是新增用户
		select distinct u_id, log_day
		from user_login  
		where log_day = '2020-01-01' AND u_id not in (select distinct u_id
							      from user_login
							      where log_day < '2020-01-01')
	) a
	left join user_login b on a.u_id = b.u_id  and  DATEDIFF(b.log_day,a.log_day) = 1
	left join user_login c on a.u_id = c.u_id  and  DATEDIFF(c.log_day,a.log_day) = 3
	left join user_login d on a.u_id = d.u_id  and  DATEDIFF(d.log_day,a.log_day) = 7
	left join user_login e on a.u_id = e.u_id  and  DATEDIFF(e.log_day,a.log_day) = 30
	group by a.log_day
) p;

执行结果如下。这里简单生成了一些数据，实际上留存率应该是随着时间的增加不断降低的。

2.计算每日用户留存率，代码如下

select *,
concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率,
concat(round(100*三十日留存用户/日新增用户数,2),'%')  三十日留存率 
from 
(	
	select 
	c.log_day 日期,
	count(distinct c.u_id)  日新增用户数,
	count(distinct d.u_id)  次日留存用户,
	count(distinct e.u_id)  三日留存用户,
	count(distinct f.u_id)  七日留存用户,
	count(distinct g.u_id)  三十日留存用户
	from 
	(
		-- 确保是新增用户
		select a.*
		from user_login a 
		left join user_login b on a.u_id = b.u_id and b.log_day < a.log_day
		where b.log_day is null
	) c
	left join user_login d on c.u_id = d.u_id  and  DATEDIFF(d.log_day,c.log_day) = 1 
	left join user_login e on c.u_id = e.u_id  and  DATEDIFF(e.log_day,c.log_day) = 3
	left join user_login f on c.u_id = f.u_id  and  DATEDIFF(f.log_day,c.log_day) = 7
	left join user_login g on c.u_id = g.u_id  and  DATEDIFF(g.log_day,c.log_day) = 30
	group by c.log_day
) p;

执行结果如下。

这里只是简单模拟了下用户留存率的计算，实际业务中的要求肯定会复杂很多，而且针对大批量的数据还需要对SQL进行优化等等。