【原】干货 | 如何用SQL做留存率分析

CDA数据分析师 2021-09-24

展开全文

作者：潘彼得

本文为「心中有数」CDA征文作品

无论是产品经理、产品运营还是数据分析师，在评估一个产品的用户使用情况时肯定离不开留存率、忠诚度等观测指标。这些指标可以反映用户对于产品的粘性、产品用户价值质量的高低，及时了解用户留存、流失趋势，有助于帮助产品做更好的功能迭代，也有助于运营及时进程运营策略的调整，比如：当新用户留存率低的时候，是不是需要调整新用户的活动策略，或者当老用户留存率低的时候，是不是某个产品功能的问题，或者活动对老用户不友好而导致流失等等……

今天，就给大家分享下如何用SQL实现留存率的计算，以及日常工作中如何分析留存率这个指标。

什么是留存率

留存顾名思义是指留下来。留存用户是指用户在APP产生行为后，在固定的第N日继续访问或使用APP的用户。留存率是指用户在一段时间后或固定的间隔后产生留存用户的比例。

用户留存率有很多种：新客留存率、老客留存率、活跃用户留存率、购买留存率、或者某个功能使用用户的留存率等，通常计算的时间间隔为次日、3日、7日、30日、60日，根据业务不同需求可以选择不同的计算方式以及时间间隔，重点是要和产品负责人以及运营人员对好指标口径。

本次分享的留存率是关于新客留存率，计算公式：第N日新客留存率=某日的新注册用户在第N日内访问过APP的留存用户数/某日的新注册用户总数。

次日新客留存率（第1日新客留存率）：指注册APP后的第二天有访问APP的行为的留存用户/注册APP当天的新客总数。

第3日新客留存率：指注册APP后第3天内内访问过APP的留存用户/注册APP当天的新客户总数。

第7日新客留存率：指注册APP后第7天内访问过APP的留存用户/注册APP当天的新客户总数。

第30日新客留存率：指注册APP后第30内访问过APP的留存用户/注册APP当天的新客户总数。

如何用SQL实现留存率计算

1. 计算新客留存率用到的字段信息有：用户ID，用户登录日期；

创建一张只有用户ID和用户登录日期的表：

CREATE TABLE IF NOT EXISTS user_log
(
    'id' int(11) NOT NULL AUTO_INCREMENT,
    'operator_id'        STRING   COMMENT '登录用户ID',
    'create_time'        DATETIME COMMENT '创建时间'
    PRIMARY KEY (`id`) USING BTREE
) 
COMMENT '用户登录表'

2.计算过程：

为便于理解，下面的步骤分为两步讲解：留存时间的计算，留存用户的计算，日常时间中如果觉得两个步骤麻烦可以将两个代码进行合并。

第一步：

计算用户注册APP日期、登录APP日期、登录APP日期距离注册APP日期的天数差

CREATE view IF NOT EXISTS view_retention_rate AS
SELECT  t2.operator_id as “用户ID”
        ,to_date(register_day) as “注册日期”
        ,login_day as "登录日期"
        ,datediff(login_day,register_day) AS “登录时间距离注册日期的天数”
FROM    (
(
        SELECT DISTINCT  operator_id
                         ,to_date(create_time) AS login_day
        FROM    user_log
) t1
        LEFT JOIN (
                SELECT  operator_id
                        ,min(to_date(create_time)) AS register_day --用户访问APP的最小时间即为首次注册时间
                FROM    user_log
                GROUP BY operator_id
                  ) t2
        ON      t1.operator_id = t2.operator_id
        )
;

结果如下：可以发现用户1注册日期在2020年11月10日，最近登录日期在20219月20日，留存达314天。

第二步：

利用已经计算出的时间计算用户留存率。

例如，想要计算用户次日留存率只需在上面的计算结果表找出登录时间距离注册日期的天数=1的数据，想要计算用户7日留存率，只需要在上表找出登录时间距离注册日期<=7的记录，代码如下：

SELECT  a.register_day as "注册日期"
        ,COUNT(DISTINCT a.operator_id) AS "当天新增人总数"
        ,COUNT(DISTINCT CASE    WHEN remain_days=1 THEN a.operator_id ELSE NULL END) AS "次日留存用户数"
        ,COUNT(DISTINCT CASE    WHEN 0<remain_days AND remain_days<=7 THEN a.operator_id ELSE NULL END) AS "第7日留存用户数"
        ,COUNT(DISTINCT CASE    WHEN 7<remain_days AND remain_days<=30 THEN a.operator_id ELSE NULL END)as "第30日留存用户数"
        ,COUNT(DISTINCT CASE    WHEN 30<remain_days AND remain_days<=60 THEN a.operator_id ELSE NULL END) as "第60日留存用户数"
        ,round(COUNT(DISTINCT CASE    WHEN remain_days=1 THEN a.operator_id ELSE NULL END)/count(DISTINCT a.operator_id),2) AS "次日留存率"
        ,round(COUNT(DISTINCT CASE when 0<remain_days and remain_days<=7 then a.operator_id else null end)/count(distinct a.operator_id),2) as "7日留存率"
        ,round(COUNT(DISTINCT CASE when 7<remain_days and remain_days<=30 then a.operator_id else null end)/count(distinct a.operator_id),2) as "30日留存率"
        ,round(COUNT(DISTINCT CASE when 30<remain_days and remain_days<=60 then a.operator_id else null end)/count(distinct a.operator_id),2) as "60日留存率"   
FROM    view_Retention_rate a
GROUP BY a.register_day
;

结果输出如下：

到这里，一个简单的新客留存情况就完成啦！

分析留存率为何下降的原因

本次计算的是新客留存率，因此我们要分析的就是新客留存率下降的原因，分析原因可以从几个角度出发：人、产品、运营方式。

新客留存率下降的主要原因有：

新客户不是产品的目标客户
新客户纯属是为了薅羊毛
新手引导体验较差
新客户没有很好的体验到产品功能，导致产品对新的客户没有吸引力
产品界面对新手不友好
产品活动吸引力不大

……

如何提高留存率

想要提高留存率建议“对症下药”，通过留存率分析，找出留存率下降或一直很低的主要原因：产品核心功能不行？运营手段不行？还是没有吸引到目标客户？

针对这几点，给大家一些参考的方式：

用户画像：以上表数据为例，发现有留存率为0的用户可以看下用户画像判断是否是产品的目标用户，如果是目标用户但是没有留存，可以进一步进行用户调研等方式，了解用户为何没有留存；
产品活动运营：通过活动刺激用户，提升用户活跃性和留存率；
恰当的消息推送：结合用户画像、用户喜好在合理的时间推动活动、运营消息，比频繁推送效果要好的多；
产品功能使用情况：结合用户在APP的行为轨迹，可以观测用户在第一次访问时是否有使用核心功能、在核心功能停留的时间等指标，可以发现产品功能是否对新手友好，进而去做产品功能的迭代改善。

……

一个好的留存率标准是什么

对于这样一个普遍的指标，不同的模型或业务场景下有不同的标准。前Airbnb 供给侧增长团队负责人 Lenny Rachitsky 和Eventbrite 首席产品官Casey Winters 在对Uber、Evernote、ServeyMonkey、Twitter、Facebook、Notion等20多款产品的增长专家进行了采访后，得出了一些结论。

根据业务类型的不同，各位增长专家认为好的留存率的标准分别如下：

6个月用户留存率标准应该是：