其他
用SQL进行用户留存率计算
The following article is from 可以叫我才哥 Author 道才
来源公众号:可以叫我才哥 ID:data955
已获得原公众号的授权转载
今天我们来分享一个常见案例,用SQL来计算用户留存率。
目录:
1. 案例数据
2. 思路分析
3. 完整代码
1. 案例数据
这里我们一共两份日志数据,分别是用户账号创建
的日志以及用户登录
的日志。
账号创建日志
用户登录日志
以上案例数据 后台回复 955
可以在SQL文件夹里data领取
2. 思路分析
所谓留存,就是指某日创建的账号在后续自然日登录的比例,比如3月1日新增账号创建数为100,在3月2日这部分用户登录数为51,那么3月1日新增用户的次日留存率为51/100=51%。
注意:我这里用的MYSQL
环境
基于上述的理解,我们大概就有了以下思路:
考虑到用户每天登录的次数不一定只有一次,为了方面后续的数据处理,可以先对登录数据按照日期和用户id进行去重 DISTINCT
处理
SELECT DISTINCT
STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
role_id
FROM
role_login
为了计算某条登录日志是该用户创建账号后的第几天登录,我们可以用用户登录日志和账号创建日志进行 inner join
(这里考虑到不在统计周期内的创建账号的用户数据也会记录在用户登录日志里,所以去掉)
SELECT
login_log.role_id,
create_date,
login_date
FROM
((
SELECT DISTINCT
STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
role_id
FROM
role_login
) login_log
INNER JOIN ( SELECT DISTINCT STR_TO_DATE( $part_date, '%Y-%m-%d' ) create_date, role_id FROM role_create ) create_log ON ( login_log.role_id = create_log.role_id ))
然后用登录日期字段和创建账户字段进行差值 DATEDIFF
获取第几天登录
SELECT
login_log.role_id,
create_date,
DATEDIFF( login_date, create_date ) day_diff
FROM
...
对于第0天登录的数据则可以理解为新增用户数,第N(≥1)天登录的数据则为这批新增用户后续有登录的用户数
SELECT
create_date
, count((CASE WHEN (day_diff = 0) THEN role_id END)) 新增用户数
, count((CASE WHEN (day_diff = 1) THEN role_id END)) 次日留存
, count((CASE WHEN (day_diff = 2) THEN role_id END)) 3日留存
, count((CASE WHEN (day_diff = 7) THEN role_id END)) 7日留存
FROM
temp_1
GROUP BY
create_date
用第N天登录的数据 / 新增用户数 就是对应第N天留存率
3. 完整代码
SELECT
create_date
, 新增用户数
, concat(CAST(ROUND((100 * 次日留存) / 新增用户数,2) AS char), '%') 次日留存率
, concat(CAST(ROUND((100 * 3日留存) / 新增用户数,2) AS char), '%') 3日留存率
, concat(CAST(ROUND((100 * 7日留存) / 新增用户数,2) AS char), '%') 7日留存率
FROM
(
SELECT
create_date
, count((CASE WHEN (day_diff = 0) THEN role_id END)) 新增用户数
, count((CASE WHEN (day_diff = 1) THEN role_id END)) 次日留存
, count((CASE WHEN (day_diff = 2) THEN role_id END)) 3日留存
, count((CASE WHEN (day_diff = 7) THEN role_id END)) 7日留存
FROM
(
SELECT
login_log.role_id
, create_date
, DATEDIFF(login_date, create_date) day_diff
FROM
((
SELECT DISTINCT
STR_TO_DATE($part_date, '%Y-%m-%d') login_date
, role_id
FROM
role_login
) login_log
INNER JOIN (
SELECT DISTINCT
STR_TO_DATE($part_date, '%Y-%m-%d') create_date
, role_id
FROM
role_create
) create_log ON (login_log.role_id = create_log.role_id))
) temp_1
GROUP BY create_date
) temp_2
ORDER BY create_date ASC
以上就是本次全部内容,由于不同的sql环境语法存在些许差异,大家视情况而处理吧。
后续我们会陆续进行SQL与Python的常见数据处理计算案例分享,敬请期待!
程序员专属卫衣
商品直购链接 👇 推荐阅读: