作者：潘彼得

本文為「心中有數(shù)」CDA征文作品

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

今天，就給大家分享下如何用SQL實(shí)現(xiàn)留存率的計(jì)算，以及日常工作中如何分析留存率這個指標(biāo)。

01、什么是留存率

留存顧名思義是指留下來。留存用戶是指用戶在APP產(chǎn)生行為后，在固定的第N日繼續(xù)訪問或使用APP的用戶。留存率是指用戶在一段時間后或固定的間隔后產(chǎn)生留存用戶的比例。

用戶留存率有很多種：新客留存率、老客留存率、活躍用戶留存率、購買留存率、或者某個功能使用用戶的留存率等，通常計(jì)算的時間間隔為次日、3日、7日、30日、60日，根據(jù)業(yè)務(wù)不同需求可以選擇不同的計(jì)算方式以及時間間隔，重點(diǎn)是要和產(chǎn)品負(fù)責(zé)人以及運(yùn)營人員對好指標(biāo)口徑。

本次分享的留存率是關(guān)于新客留存率，計(jì)算公式：第N日新客留存率=某日的新注冊用戶在第N日內(nèi)訪問過APP的留存用戶數(shù)/某日的新注冊用戶總數(shù)。

次日新客留存率（第1日新客留存率）：指注冊APP后的第二天有訪問APP的行為的留存用戶/注冊APP當(dāng)天的新客總數(shù)。

第3日新客留存率：指注冊APP后第3天內(nèi)內(nèi)訪問過APP的留存用戶/注冊APP當(dāng)天的新客戶總數(shù)。

第7日新客留存率：指注冊APP后第7天內(nèi)訪問過APP的留存用戶/注冊APP當(dāng)天的新客戶總數(shù)。

第30日新客留存率：指注冊APP后第30內(nèi)訪問過APP的留存用戶/注冊APP當(dāng)天的新客戶總數(shù)。

02、如何用SQL實(shí)現(xiàn)留存率計(jì)算

1. 計(jì)算新客留存率用到的字段信息有：用戶ID，用戶登錄日期；

創(chuàng)建一張只有用戶ID和用戶登錄日期的表：

CREATE TABLE IF NOT EXISTS user_log
(
'id' int(11) NOT NULL AUTO_INCREMENT,
'operator_id' STRING COMMENT '登錄用戶ID',
'create_time' DATETIME COMMENT '創(chuàng)建時間'
PRIMARY KEY (`id`) USING BTREE
)
COMMENT '用戶登錄表'

2.計(jì)算過程：

為便于理解，下面的步驟分為兩步講解：留存時間的計(jì)算，留存用戶的計(jì)算，日常時間中如果覺得兩個步驟麻煩可以將兩個代碼進(jìn)行合并。

第一步：

計(jì)算用戶注冊APP日期、登錄APP日期、登錄APP日期距離注冊APP日期的天數(shù)差

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 “登錄時間距離注冊日期的天數(shù)”
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
)
;

結(jié)果如下：可以發(fā)現(xiàn)用戶1注冊日期在2020年11月10日，最近登錄日期在20219月20日，留存達(dá)314天。

第二步：

利用已經(jīng)計(jì)算出的時間計(jì)算用戶留存率。

例如，想要計(jì)算用戶次日留存率只需在上面的計(jì)算結(jié)果表找出登錄時間距離注冊日期的天數(shù)=1的數(shù)據(jù)，想要計(jì)算用戶7日留存率，只需要在上表找出登錄時間距離注冊日期<=7的記錄，代碼如下：

SELECT a.register_day as "注冊日期"
,COUNT(DISTINCT a.operator_id) AS "當(dāng)天新增人總數(shù)"
,COUNT(DISTINCT CASE WHEN remain_days=1 THEN a.operator_id ELSE NULL END) AS "次日留存用戶數(shù)"
,COUNT(DISTINCT CASE WHEN 0AND remain_days<=7 THEN a.operator_id ELSE NULL END) AS "第7日留存用戶數(shù)"
,COUNT(DISTINCT CASE WHEN 7AND remain_days<=30 THEN a.operator_id ELSE NULL END)as "第30日留存用戶數(shù)"
,COUNT(DISTINCT CASE WHEN 30AND remain_days<=60 THEN a.operator_id ELSE NULL END) as "第60日留存用戶數(shù)"
,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
;

結(jié)果輸出如下：

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

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

本次計(jì)算的是新客留存率，因此我們要分析的就是新客留存率下降的原因，分析原因可以從幾個角度出發(fā)：人、產(chǎn)品、運(yùn)營方式。

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

• 新客戶不是產(chǎn)品的目標(biāo)客戶
• 新客戶純屬是為了薅羊毛
• 新手引導(dǎo)體驗(yàn)較差
• 新客戶沒有很好的體驗(yàn)到產(chǎn)品功能，導(dǎo)致產(chǎn)品對新的客戶沒有吸引力
• 產(chǎn)品界面對新手不友好
• 產(chǎn)品活動吸引力不大

……

04、如何提高留存率

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

針對這幾點(diǎn)，給大家一些參考的方式：

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

……

05、一個好的留存率標(biāo)準(zhǔn)是什么

對于這樣一個普遍的指標(biāo)，不同的模型或業(yè)務(wù)場景下有不同的標(biāo)準(zhǔn)。前Airbnb 供給側(cè)增長團(tuán)隊(duì)負(fù)責(zé)人 Lenny Rachitsky 和Eventbrite 首席產(chǎn)品官Casey Winters 在對Uber、Evernote、ServeyMonkey、Twitter、Facebook、Notion等20多款產(chǎn)品的增長專家進(jìn)行了采訪后，得出了一些結(jié)論。

根據(jù)業(yè)務(wù)類型的不同，各位增長專家認(rèn)為好的留存率的標(biāo)準(zhǔn)分別如下：

6個月用戶留存率標(biāo)準(zhǔn)應(yīng)該是：

12個月的收入留存率標(biāo)準(zhǔn)應(yīng)該是：

以上只是一些建議供大家參考，實(shí)際業(yè)務(wù)場景中還需要根據(jù)業(yè)務(wù)需求或業(yè)務(wù)體量進(jìn)行合適的調(diào)整。