作者：潘彼得

本文為「心中有數」CDA征文作品

無論是產品經理、產品運營還是數據分析師，在評估一個產品的用戶使用情況時肯定離不開留存率、忠誠度等觀測指標。這些指標可以反映用戶對于產品的粘性、產品用戶價值質量的高低，及時了解用戶留存、流失趨勢，有助于幫助產品做更好的功能迭代，也有助于運營及時進程運營策略的調整，比如：當新用戶留存率低的時候，是不是需要調整新用戶的活動策略，或者當老用戶留存率低的時候，是不是某個產品功能的問題，或者活動對老用戶不友好而導致流失等等……

今天，就給大家分享下如何用SQL實現留存率的計算，以及日常工作中如何分析留存率這個指標。

01、什么是留存率

留存顧名思義是指留下來。留存用戶是指用戶在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當天的新客戶總數。

02、如何用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 0AND remain_days<=7 THEN a.operator_id ELSE NULL END) AS "第7日留存用戶數"
,COUNT(DISTINCT CASE WHEN 7AND remain_days<=30 THEN a.operator_id ELSE NULL END)as "第30日留存用戶數"
,COUNT(DISTINCT CASE WHEN 30AND 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 0and remain_days<=7 then a.operator_id else null end)/count(distinct a.operator_id),2) as "7日留存率"
,round(COUNT(DISTINCT CASE when 7and remain_days<=30 then a.operator_id else null end)/count(distinct a.operator_id),2) as "30日留存率"
,round(COUNT(DISTINCT CASE when 30and 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
;

結果輸出如下：

到這里，一個簡單的新客留存情況就完成啦！

03、分析留存率為何下降的原因

本次計算的是新客留存率，因此我們要分析的就是新客留存率下降的原因，分析原因可以從幾個角度出發：人、產品、運營方式。

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

• 新客戶不是產品的目標客戶
• 新客戶純屬是為了薅羊毛
• 新手引導體驗較差
• 新客戶沒有很好的體驗到產品功能，導致產品對新的客戶沒有吸引力
• 產品界面對新手不友好
• 產品活動吸引力不大

……

04、如何提高留存率

想要提高留存率建議“對癥下藥”，通過留存率分析，找出留存率下降或一直很低的主要原因：產品核心功能不行？運營手段不行？還是沒有吸引到目標客戶？

針對這幾點，給大家一些參考的方式：

• 用戶畫像：以上表數據為例，發現有留存率為0的用戶可以看下用戶畫像判斷是否是產品的目標用戶，如果是目標用戶但是沒有留存，可以進一步進行用戶調研等方式，了解用戶為何沒有留存；
• 產品活動運營：通過活動刺激用戶，提升用戶活躍性和留存率；
• 恰當的消息推送：結合用戶畫像、用戶喜好在合理的時間推動活動、運營消息，比頻繁推送效果要好的多；
• 產品功能使用情況：結合用戶在APP的行為軌跡，可以觀測用戶在第一次訪問時是否有使用核心功能、在核心功能停留的時間等指標，可以發現產品功能是否對新手友好，進而去做產品功能的迭代改善。

……

05、一個好的留存率標準是什么

對于這樣一個普遍的指標，不同的模型或業務場景下有不同的標準。前Airbnb 供給側增長團隊負責人 Lenny Rachitsky 和Eventbrite 首席產品官Casey Winters 在對Uber、Evernote、ServeyMonkey、Twitter、Facebook、Notion等20多款產品的增長專家進行了采訪后，得出了一些結論。

根據業務類型的不同，各位增長專家認為好的留存率的標準分別如下：

6個月用戶留存率標準應該是：

12個月的收入留存率標準應該是：

以上只是一些建議供大家參考，實際業務場景中還需要根據業務需求或業務體量進行合適的調整。