经典SQL之留存率

3天留存率，7天留存率场景也是常考SQL题之一，今天来手撕留存率。

什么是留存率

n日留存率 = 第n天还在登录的用户数/新增的用户数

如：假如某日新增了100个用户，第2天登录了50个，则第2天留存率为50/100=50%，第3天登录了30个，则第3天留存率为30/100=30%，以此类推，第7天登录了10个用户，则7日留存率就是10/100=10%。

表

drop table if exists tmp_db.user_login_details;
create table tmp_db.user_login_details 
(Userid int  -- 用户ID
 ,Login_time timestamp  -- 登录时间
)

测试数据

-- 搞点测试数据
insert overwrite table tmp_db.user_login_details
select 1010123,'2021-08-02 11:05:02'
union all 
select 1010123,'2021-08-03 12:03:05'
union all 
select 1010123,'2021-08-04 11:05:02'
union all 
select 1010123,'2021-08-05 11:05:02'
union all 
select 1010123,'2021-08-05 18:05:02'
union all 
select 1010128,'2021-08-02 11:05:02'
union all 
select 1010444,'2021-08-02 11:05:02'
union all 
select 1010444,'2021-08-04 11:05:02'
union all 
select 1010444,'2021-08-05 11:05:02'
union all 
select 1010444,'2021-08-09 11:05:02'
union all 
select 1010199,'2021-08-02 11:05:02'
union all 
select 1010199,'2021-08-04 11:05:02'
union all 
select 1010199,'2021-08-05 11:05:02'
union all 
select 1010199,'2021-08-05 15:05:02'
union all
select 1010155,'2021-08-02 11:05:02'
union all 
select 1010155,'2021-08-04 11:05:02'
union all 
select 1010155,'2021-08-05 11:05:02'
union all 
select 1010155,'2021-08-07 11:05:02'
union all
select 1010166,'2021-08-02 11:05:02'
union all 
select 1010166,'2021-08-04 11:05:02'

处理数据，插入临时表

如果一个用户一天多次登录那么就会有重复，这里是为了去重，如果用户有登录，一天一个用户一条数据即可

drop table if exists tmp_db.user_login_details_01;
create table tmp_db.user_login_details_01 AS
SELECT Date(login_time) AS log_day, 
       Userid AS Userid 
  FROM  tmp_db.user_login_details 
 GROUP BY Userid,Date(login_time) 

计算

SELECT log_date,
       count( Userid_day0 ) AS user_cnt,   -- 新增用户数
       case when count(Userid_day0) = 0  
	        then 0 
			else  count( Userid_day1 ) / count(Userid_day0) 
			end as retention_ratio2 , -- 次日留存率
       case when count(Userid_day0) = 0  
	        then 0 
			else count(Userid_day2) / count(Userid_day0) 
			end as retention_ratio3  -- 3日留存率
  FROM
      (SELECT a.log_date,
              a.Userid AS Userid_day0,
              b.Userid AS Userid_day1,
              c.Userid AS Userid_day2 
         FROM tmp_db.user_login_details_01 a
         LEFT JOIN tmp_db.user_login_details_01 b 
		   ON a.Userid = b.Userid 
		  AND DATE_add(a.log_date,1) =b.log_date
         LEFT JOIN tmp_db.user_login_details_01 c 
		   ON a.Userid = c.Userid 
		  AND DATE_add(a.log_date,2) =c.log_date
      ) temp 
 GROUP BY log_date 

如何提高用户留存率

• 营销活动刺激。营销活动能够刺激用户消费，而一个好的营销活动绝对能够大幅度提升用户的留存和复购率
• 合理的消息推送，过于频繁的push会造成用户流失，但合理适时进行push推送能起到唤醒用户的作用，push推送，要做到精准化，前提是你需要了解你的用户，给用户进行标签管理。针对不用标签的用户进行不同内容的推送，提高用户体验。
• 善用签到机制提高用户粘性，签到功能，对于提高用户粘性有很大的帮助。签到可获取积分，连续签到，积分叠加等形式。
• 用内容留住用户，优质的内容是留住用户的关键。