用户七日留存率分析

背景

APP分析中经常用到AARRR模型（海盗模型）用来分析APP的现状，其中一个重要节点就是提高留存（Acquisition），而留存率这个指标在这个阶段可以说是核心指标也不为过。那如何用SQL计算留存率呢？

留存率计算方法

假如今天新增了100名用户，第二天登陆了50名，则次日留存率为50/100=50%，第三天登录了30名，则第二日留存率为30/100=30%,以此类推。

用SQL的计算思路

• 用SQL调取出user_id和用户login_time的表，获得新增用户登录时间表。
  根据user_id和login_time，增加一列first_day，此列存着每个用户最早登录时间。
  有了最早登录时间和所有的登录时间，再增加一列by_day，这一列是用login_time - first_day ，得到0，1，2，3，4，5......，这就得到了某一天登录离第一次登录有多长时间。

• 

• 然后从表中提取数据，找到first_day对应的with_first列中0有多少个，1有多少个，一直到7以上。

• 

根据此表，就很容易计算出每天引流的留存率。

实际操作

数据：是我用excel随便模拟的数据，与真实情况不符。

数据库：MySQL

步骤一：从数据库中提取出user_id和login_time并排序

select 
    user_id,
    str_to_date(login_time,'%Y/%m/%d') login_time
from user_info
group by 1,2;

步骤二：增加一列first_day，存储每个用户ID最早登录时间

SELECT
    b.user_id,
    b.login_time,
    c.first_day
FROM 
    (select 
        user_id,
        str_to_date(login_time,'%Y/%m/%d') login_time
    from user_info
    group by 1,2) b
LEFT JOIN
    (SELECT       ---找到user_id对应的最早登录时间，然后匹配带登录时间的user_id
        user_id,
        min(login_time) first_day
    FROM 
       (select 
            user_id,
            str_to_date(login_time,'%Y/%m/%d') login_time
        from user_info
        group by 1,2) a
    group by 1) c
on b.user_id = c.user_id
order by 1,2;

步骤三：用登录时间-最早登录时间得到一列by_day

SELECT 
    user_id,
    login_time,
    first_day,
    DATEDIFF(login_time,first_day) as by_day
FROM
  (SELECT
      b.user_id,
      b.login_time,
      c.first_day
  FROM 
      (SELECT
          user_id,
          str_to_date(login_time,'%Y/%m/%d') login_time
       FROM user_info
       GROUP BY 1,2) b
  LEFT JOIN
    (SELECT
        user_id,
        min(login_time) first_day
     FROM
         (select 
              user_id,
              str_to_date(login_time,'%Y/%m/%d') login_time
          from user_info
          group by 1,2) a
     group by 1) c
   on b.user_id = c.user_id
   order by 1,2) e
order by 1,2

最后一步：提取字段作为列名

SELECT
    first_day,
    sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day = 1 then 1 else 0 end) day_1,
    sum(case when by_day = 2 then 1 else 0 end) day_2,
    sum(case when by_day = 3 then 1 else 0 end) day_3,
    sum(case when by_day = 4 then 1 else 0 end) day_4,
    sum(case when by_day = 5 then 1 else 0 end) day_5,
    sum(case when by_day = 6 then 1 else 0 end) day_6,
    sum(case when by_day >= 7 then 1 else 0 end) day_7plus
FROM
   (SELECT 
      user_id,
      login_time,
      first_day,
      DATEDIFF(login_time,first_day) as by_day
   FROM
     (SELECT
        b.user_id,
        b.login_time,
        c.first_day
      FROM 
        (SELECT
            user_id,
            str_to_date(login_time,'%Y/%m/%d') login_time
         FROM user_info
         GROUP BY 1,2) b
    LEFT JOIN
      (SELECT
          user_id,
          min(login_time) first_day
       FROM
           (select 
                user_id,
                str_to_date(login_time,'%Y/%m/%d') login_time
            FROM 
                user_info
            group by 1,2) a
       group by 1) c
     on b.user_id = c.user_id
     order by 1,2) e
  order by 1,2) f
group by 1
order by 1

结语

根据最后得到的数据，我们直接用除法或者加一个SQL语句，就能算出来留存率，之后的分析就是看自己了。

参考博客

https://blog.treasuredata.com/blog/2016/07/22/rolling-retention-done-right-in-sql/