学习笔记 - 留存率

    今天介绍一个对互联网公司来说一个比较重要的指标计算方法 - 留存率。留存通俗来讲就是回头客，用户第一次体验了产品之后，是否有第二次，第三次，第n次重复使用和体验，是数据分析笔试中几乎必考的计算指标。本篇会结合博主学习到的项目示例，对留存率的计算有一个完完整整的梳理，篇幅会相对较长，但建议大家认真阅读。

数据源： o_retailers_trade_user

user_id	item_id	behavior_type	user_geohash	item_category	time
98047837	232431562	1		4245	2019-12-06 02
97726136	383583590	1		5894	2019-12-09 20
98607707	64749712	1		2883	2019-12-18 11
98662432	320593836	1	96nn52n	6562	2019-12-06 10
.....
100509623	236195070	1		1863	2019-12-15 14
101781721	46475830	1	9rgt17q	13230	2019-12-16 21

字段说明：

字段	说明
user_id	⽤户ID
item_id	商品ID
behavior_type	⽤户⾏为类型（1-曝光； 2-购买； 3-加⼊购物⻋； 4-加⼊收藏夹。）
user_geohash	地理位置
item_category	品类ID
time	⽤户⾏为发⽣的时间

1. 数据导入

-- 创建表格代码
create table o_retailers_trade_user
(
user_id int (9),
item_id int (9),
behavior_type int (1),
user_geohash varchar (14),
item_category int (5),
time varchar (13)
)

2. 数据预处理

数据预处理就是对表中字段进行一个格式的调整过程。

2.1 time字段

精确到了小时，此处只需要精确到日即可。

方法1，用函数对字段time 进行修改；提取到dates（年月日）

--- SUBSTRING(s,n,len)函数进行字段截取；
select substring(time,1,10) dates from  o_retailers_trade_user

--- date_format(date, format)函数进行日期格式调整；
select date_format(time,'%Y-%m-%d')dates from  o_retailers_trade_user;

方法2，对表格进行修改新增新字段；

alter table o_retailers_trade_user add column date_time datetime null
-- 修改表格o_retailers_trade_user，插入字段date_time，类型为datetime（年月日时分秒），默认为空值(DDL语句）；

update o_retailers_trade_user set date_time = str_to_date(time,'%Y-%m-%d %H')
-- 更新o_retailers_trade_user 字段date_time数据，使其等于time中字段值，但讲time字段值先转化为日期格式；

alter table o_retailers_trade_user add column dates char(10) null
-- 增加字段dates,类型为char
update o_retailers_trade_user set dates = date(date_time)
-- 此时的dates就是年月日展示，类型为char;

此处选择方法2，需要预处理成一个我们想要的表格内容，以便后续的数据分析。

2.2 去重

create table temp_trade like o_retailers_trade_user
-- 创建一个与o_retailers_trade_user结构相同的表temp_trade；
insert into temp_trade select distinct * from  o_retailers_trade_user
-- 导入去重数据

后续的表计算也是用temp_trade

3. PV 和UV的计算

PV：page view即计算浏览量（behavior_type =1）；UV：即计算用户数；

select dates,
       count(distinct user_id) UV,
       count(case when behavior_type =1 then 1 else null end) PV,
       -- count(if behavior_type=1,user_id,null)
       count(case when behavior_type =1 then 1 else null end)/count(distinct user_id) 'PV/UV'
       -- PV/UV 浏览深度
from temp_trade
group by dates

相关结果：

dates	UV	PV	PV/UV
2019-11-18	226	1332	5.8938
2019-11-19	222	1286	5.7928
2019-11-20	231	1289	5.5801
2019-11-21	232	1247	5.375
...	...	...	...

4. 留存率计算

此处是对活跃用户的留存率计算。

思路分析：

	活跃用户	过了一天（次日留存率/1日留存率）	过了两天（两日留存率）	过了三天	...
2019-12-28	100	90（90%）	80（80%）	70（70%）

2019-12-28日的某用户有数据，如果在2019-12-28 后仍有数据，则是活跃的，即有留存；

​ 如果2019-12-29 有数据，则是活跃的，1日留存；

​ 如果2019-12-30 有数据，则是活跃的，2日留存；

​ ......

​ 如果date_1 有数据，则是活跃的, (date_1-dates) 相差n天，即为n日留存；

SQL语句实现：

4.1 去重

-- 首先取得按天的每日用户活跃明细（去重，一个用户可能在某天多次活跃）
select user_id,dates from temp_trade group by user_id,dates
-- 按此分组后，某用户在某天的多条记录也只会返回一条

-- 错误示例
select dates, distinct(user_id) from temp_trade group by dates
-- 分组需聚合，这样的语句在SQL中无法计算

结果：

dates	user_id
2019-12-06	98047837
2019-12-09	97726136
2019-12-18	98607707
2019-12-06	98662432
...	...

4.2 自关联

使用自关联，获取到同一用户在不同日期的活跃情况。

select * from 
(select user_id,dates from temp_trade group by user_id,dates)a left join 
(select user_id,dates from temp_trade group by user_id,dates)b 
on a.user_id=b.user_id
where a.dates<b.dates
-- b.dates大于a.dates才说明该用户在a.dates后仍有活跃，即留存

结果：

user_id	dates	user_id(1)	dates(1)
98047837	2019-12-06	98047837	2019-12-07
98047837	2019-12-06	98047837	2019-12-08
98047837	2019-12-06	98047837	2019-12-09
98047837	2019-12-06	98047837	2019-12-10
98047837	2019-12-06	98047837	2019-12-11
98047837	2019-12-06	98047837	2019-12-12
98047837	2019-12-06	98047837	2019-12-13
...	...	...	...

这样就得到用户基于dates，查询到后续的活跃日期，即dates（1）

4.3 每日活跃用户数（聚合）

select a.dates, count(distinct a.user_id) user_count, -- 当日活跃数
       count(distinct if(datediff(b.dates,a.dates)=1,a.user_id,null)) remain1, -- 次日留存数
    count(distinct if(datediff(b.dates,a.dates)=2,a.user_id,null)) remain2, -- 两日留存数
    count(distinct if(datediff(b.dates,a.dates)=3,a.user_id,null)) remain3,
    count(distinct if(datediff(b.dates,a.dates)=10,a.user_id,null)) remain10,
    count(distinct if(datediff(b.dates,a.dates)=15,a.user_id,null)) remain15,
    count(distinct if(datediff(b.dates,a.dates)=30,a.user_id,null)) remain30
from 
(select user_id,dates from temp_trade group by user_id,dates)a left join 
(select user_id,dates from temp_trade group by user_id,dates)b 
on a.user_id=b.user_id
where a.dates<b.dates
group by a.dates

结果为：

dates	user_count	remain1	remain2	remain3	remain10	remain15	remain30
2019-11-18	225	147	146	136	128	136	128
2019-11-19	222	154	141	137	136	135	0
2019-11-20	229	146	137	143	150	142	0
2019-11-21	231	144	146	151	144	143	0
2019-11-22	225	157	145	148	137	136	0
2019-11-23	241	161	160	141	153	144	0
2019-11-24	234	163	151	157	144	149	0
...	...	...	...	...	...	...	...

4.4 留存率计算

创建视图，也可以用with a as ()，这样会造成SQL语句过长，此处选用视图创建。

create view user_remain_view as 
select a.dates, count(distinct a.user_id) user_count, -- 当日活跃数
       count(distinct if(datediff(b.dates,a.dates)=1,a.user_id,null)) remain1, -- 次日留存数
    count(distinct if(datediff(b.dates,a.dates)=2,a.user_id,null)) remain2, -- 两日留存数
    count(distinct if(datediff(b.dates,a.dates)=3,a.user_id,null)) remain3,
    count(distinct if(datediff(b.dates,a.dates)=10,a.user_id,null)) remain10,
    count(distinct if(datediff(b.dates,a.dates)=15,a.user_id,null)) remain15,
    count(distinct if(datediff(b.dates,a.dates)=30,a.user_id,null)) remain30
from 
(select user_id,dates from temp_trade group by user_id,dates)a left join 
(select user_id,dates from temp_trade group by user_id,dates)b 
on a.user_id=b.user_id
where a.dates<b.dates
group by a.dates

select * from user_remain_view

留存率计算

select dates,user_count,
    concat(cast((remain1/user_count)*100 as decimal(10,2)),'%') day_1,
    -- cast 转换函数，decimal精确到两位小数；或者 concat(round((remain1/user_count)*100,2),'%') 
     concat(cast((remain2/user_count)*100 as decimal(10,2)),'%') day_2,
      concat(cast((remain3/user_count)*100 as decimal(10,2)),'%') day_3
   from
   user_remain_view
       

更多内容请关注公众号：thatDataJorney；

  回复103 可获得项目案例数据