互联网项目统计七日留存率_MYSQL实现方案

一、背景

留存率：是用户分析的核心指标之一。它也是经典的AARRR模型（海盗模型）中就有一个重要节点——留存（Acquisition）。留存率的计算也是用户分析模型的计算基础，那么如何在数据库中用SQL实现呢？

二、什么是留存率？

常见的留存率有次日留存、三日留存、7日留存、14日留存、30日留存、90日留存等等，不同产品用户行为的频率是有差别的，留存率的设定也应该视不同产品而定，有些低频的产品用周或月的颗粒度就够了。

留存率计算逻辑：

假如某日新增了100个用户，第二天登录了50个，则次日留存率为50/100=50%，第三天登录了30个，则第二日留存率为30/100=30%，以此类推，第7天登录了10个用户，则7日留存率就是10/100=10%。

以12月1日的新增用户为例，如果12月2日也登录了，就算做次日留存；如果12月3日又登录了，就算做三日留存；12月6日再次登录，就算作7日留存。

1、数据说明

计算留存率只需要2个字段：用户ID (user_id) 和 登录日期 (login_time)

• t_user_login：表名

• user_id: 用户id，也可用设备ID等

• login_time：登录日期时间，例如：2020-05-25 16:03:05

2、实现步骤：

• 步骤一：从数据库中提取user_id和login_time, 并计算 first_day, 用于存储每个用户ID最早登录日期（最小日期）；

• 步骤二：用登录日期-最早登录日期，得到每个登录日期距离最早登录日期的时间间隔，即留存日期；

• 步骤三：对不同留存日期的user_id进行汇总就是留存人数，除以首日登录人数，就得到了不同留存时间的留存率。

3、SQL实现

SELECT
    log_day '日期',
    count( user_id_day0 ) '新增数量',
    count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
    count( user_id_day2 ) / count( user_id_day0 ) '3日留存率',
    count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
    count( user_id_day30 ) / count( user_id_day0 ) '30日留存率' FROM
    (
    SELECT DISTINCT
        log_day,
        a.user_id_day0,
        b.user_id AS user_id_day1,
        c.user_id AS user_id_day3,
        d.user_id AS user_id_day7,
        e.user_id AS user_id_day30 
    FROM
        ( SELECT DISTINCT 
                Date( login_time ) AS log_day, 
                user_id AS user_id_day0 
                FROM 
                t_user_login 
                GROUP BY user_id 
                ORDER BY log_day 
                ) a
        LEFT JOIN t_user_login b ON DATEDIFF( DATE( b.login_time ), a.log_day ) = 1 
        AND a.user_id_day0 = b.user_id
        LEFT JOIN t_user_login c ON DATEDIFF( date( c.login_time ), a.log_day ) = 2 
        AND a.user_id_day0 = c.user_id
        LEFT JOIN t_user_login d ON datediff( date( d.login_time ), a.log_day ) = 7
        AND a.user_id_day0 = d.user_id
        LEFT JOIN t_user_login e ON datediff( date( e.login_time ), a.log_day ) = 30 
        AND a.user_id_day0 = e.user_id 
    ) temp 
GROUP BY
    log_day

第二种方式

select  aa.date(login_time) 日期,
    aa.活跃用户数,
    aa.次日留存用户数,
    aa.三日留存用户数,
    aa.七日留存用户数, 
    concat(round(100 * 次日留存用户数/活跃用户数, 2), '%') 次日留存率,
    concat(round(100 * 三日留存用户数/活跃用户数, 2), '%') 三日留存率,
    concat(round(100 * 七日留存用户数/活跃用户数, 2), '%') 七日留存率from (    select a.date(login_time) 日期,
        count(distinct a.user_id) as 活跃用户数,
        count(distinct b.user_id) as  次日留存用户数,
        count(distinct c.user_id) as 三日留存用户数,
        count(distinct d.user_id) as 七日留存用户数    from t_user_login a
    left join act_user_info b on a.user_id = b.user_id and b.date(login_time) = a.date(login_time) + 1
    left join act_user_info c on a.user_id = c.user_id and c.date(login_time) = a.date(login_time) + 3
    left join act_user_info d on a.user_id = d.user_id and d.date(login_time) = a.date(login_time) + 7
    group by a.date(login_time)
) aa;

代码亲测有效，但数据计算的过程耗时较长，如果有更高效的实现方式，也你的欢迎分享。

============================================

附录：

百度提供的计算规则：

留存率=新增用户中登录用户数/新增用户数*100%(一般统计周期为天)

新增用户数：在某个时间段(一般为第一整天)新登录应用的用户数；

登录用户数：登录应用后至当前时间，至少登录过一次的用户数；

次日留存率：(当天新增的用户中，在注册的第2天还登录的用户数)/第一天新增总用户数；

第3日留存率：(第一天新增用户中，在注册的第3天还有登录的用户数)/第一天新增总用户数；

第7日留存率：(第一天新增的用户中，在注册的第7天还有登录的用户数)/第一天新增总用户数；

第30日留存率：(第一天新增的用户中，在注册的第30天还有登录的用户数)/第一天新增总用户数。

其作用和目的：一般我们认为，如果用户在第7天登录，那么该用户很有可能成为稳定用户，对于产品更新或新产品上线可作为一个不错的验证方式。不过该中统计规则有一定的片面性，针对于高频和低频要考虑适当的变体。比如，一款健康管理的软件(该例子可能不够恰当)，假设其应用场景是用户每天晚饭后会登录，那么用户再使用后即会退出，当天可能不会再查看。对于这种相对低频的应用，并非新闻类、社交类那么高频(所有碎片时间都可作为使用场景)。那么，很有可能有的用户2天或3天登录一次，那么针对这种情况，7天可变体为7-10天的留存率。其他，依具体情况而定。特别说明，留存率只能作为衡量产品效果的一个侧面，需要与其他数据相互配合，后续有机会再做交流。

本文来源： cnblogs