用SQL进行用户留存率计算

大家好，我是才哥。

今天我们来分享一个常见案例，用SQL来计算用户留存率。

1. 案例数据
2. 思路分析
3. 完整代码

1. 案例数据

这里我们一共两份日志数据，分别是用户账号创建的日志以及用户登录的日志。

账号创建日志

账号创建日志

用户登录日志

登录日志

以上案例数据后台回复 955 可以在SQL文件夹里data领取

2. 思路分析

所谓留存，就是指某日创建的账号在后续自然日登录的比例，比如3月1日新增账号创建数为100，在3月2日这部分用户登录数为51，那么3月1日新增用户的次日留存率为51/100=51%。

注意：我这里用的MYSQL环境

基于上述的理解，我们大概就有了以下思路：

考虑到用户每天登录的次数不一定只有一次，为了方面后续的数据处理，可以先对登录数据按照日期和用户id进行去重DISTINCT处理

代码语言：javascript复制

SELECT DISTINCT
 STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
 role_id 
FROM
 role_login

为了计算某条登录日志是该用户创建账号后的第几天登录，我们可以用用户登录日志和账号创建日志进行inner join（这里考虑到不在统计周期内的创建账号的用户数据也会记录在用户登录日志里，所以去掉）

代码语言：javascript复制

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获取第几天登录

代码语言：javascript复制

SELECT
 login_log.role_id,
 create_date,
 DATEDIFF( login_date, create_date ) day_diff 
FROM
...

对于第0天登录的数据则可以理解为新增用户数，第N（≥1)天登录的数据则为这批新增用户后续有登录的用户数

代码语言：javascript复制

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. 完整代码

代码语言：javascript复制

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的常见数据处理计算案例分享，敬请期待！

访问管理 sql 数据处理

0 人点赞