043-经典SQL|经典SQL之留存率 043-经典SQL|sql

【043-经典SQL|经典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 FROMtmp_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 elsecount( 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推送，要做到精准化，前提是你需要了解你的用户，给用户进行标签管理。针对不用标签的用户进行不同内容的推送，提高用户体验。
善用签到机制提高用户粘性，签到功能，对于提高用户粘性有很大的帮助。签到可获取积分，连续签到，积分叠加等形式。
用内容留住用户，优质的内容是留住用户的关键。