今天接收到一个问题,在MySQL里面,给定用户和登录时间,怎么计算次日留存率。当时太匆忙,一起也都是在其他软件里面计算的留存率,就没有给出答案,路上想了一下,其实主要就是时间的计算。后续的SQL更新也是类似的问题答案更新了,下面应该会开始更新PowerBI了。留存分析是用户分析最常用的一种分析方法,次日留存率、周留存率、月留存率这些指标对用户的分析管理以及后续的策略都有很重要的参考意义。今天这里先不具体解释留存的理论,后面专门来一篇,加上具体的数据案例,可能能够说的更清楚。
模拟了几天的数据,如下,28号有20个用户,29号就是第二天还有10个用户登录,次日留存率就是50%;30号还剩5个用户,对于29号的次日留存率就是50%,对于28日的第三日留存率就是25%;如果28号和29号合计有15个用户活跃,那么对于28号来说,两日内留存率就是15/20=75%。
整理一下↓
- 次日留存率,基准日之后的第一天的用户的回访比例
- 二日留存率,基准日之后的第二天的用户的回访比例
- 三日留存率,基准日之后的第三天的用户的回访比例
- 七日留存率,基准日之后的第七天的用户的回访比例
- 二日内留存率,次日+第二日的活跃用户占比
- 三日内留存率,次日+第二日+第三日的活跃用户占比**
- 七日内留存率,七日内的活跃用户占比
【第一步】
将表格自己和自己做一个连接,目的是用来比较相间隔的天数,用来计算基于当日后续几天有活跃数据。SQL语句和结果如下↓
SELECT
lc1.id,
lc1.user_id,
date(lc1.time) date1,
date(lc2.time) date2,
DATEDIFF(date(lc1.time),date(lc2.time))
FROM
liucun as lc1
LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id
【第二步】
通过聚合函数(CASE WHEN)或者IF判断语句,判断日期相差的天数,用来判断用户时候后续指定天数有活跃。如果天数为1,代表次日是活跃的,以此类推。然后把满足条件的user_id取出来,因为考虑每天用户可能多次登录,所以需要用DISTINCT去重,然后对user_id进行计数就行了。SQL语句和结果如下↓
SELECT
date1,
count(DISTINCT user_id) usercount,
COUNT(DISTINCT if(DATEDIFF(date2,date1)=1,user_id,null)) `if语法`,
COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END) `case语法`
FROM
(SELECT
lc1.id,
lc1.user_id,
date(lc1.time) date1,
date(lc2.time) date2,
DATEDIFF(date(lc1.time),date(lc2.time))
FROM
liucun as lc1
LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id) temp
GROUP BY
date1
【第三步】
就可以计算留存率了,只需要相除就行了。还可以用用CANCAT函数做成百分位显示的效果,SQL语句和结果如下↓
SELECT
date1,
COUNT(DISTINCT user_id) 当日用户数,
COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END) 次日用户数,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次日留存率
FROM
(SELECT
lc1.id,
lc1.user_id,
date(lc1.time) date1,
date(lc2.time) date2,
DATEDIFF(date(lc1.time),date(lc2.time))
FROM
liucun as lc1
LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id) temp
GROUP BY
date1
【第四步】
最后我再增加一些数据,做一个完整的次日、第三日、第七日、第十日、三日内、七日内、十日内留存率效果,要看更长的,按规律增加时间就行了,SQL语句和结果如下↓
SELECT
date1,
COUNT(DISTINCT user_id) 当日用户数,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次日留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 第三日留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=6 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 第七日留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)=9 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 第十日留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)>=1 AND DATEDIFF(date2,date1)<=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 三日内留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)>=1 AND DATEDIFF(date2,date1)<=6 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 七日内留存率,
CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2,date1)>=1 AND DATEDIFF(date2,date1)<=9 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 十日内留存率
FROM
(SELECT
lc1.id,
lc1.user_id,
date(lc1.time) date1,
date(lc2.time) date2,
DATEDIFF(date(lc1.time),date(lc2.time))
FROM
liucun as lc1
LEFT JOIN liucun as lc2 ON lc1.user_id = lc2.user_id) temp
GROUP BY
date1
好了,今天就先到这里,数据放在了我的阿里云空间里面,复制下面链接到浏览器就可以下载使用了。
https://danlu1.oss-cn-chengdu.aliyuncs.com/Excel/liucun.xlsx
End
◆ R语言分词_jiebaR包
◆ R语言_TreeMap
◆ R_脸谱图