一、背景需求

根據CNNIC的統計數據顯示，中國互聯網用戶已達10.79億人，互聯網普及率達到79.4%。互聯網雖然仍然在快速增長，但是用戶規模逐漸飽和，互聯網事實上已經進入了存量用戶時代，整體的流量競爭也越來越激烈，用戶的留存的重要性也越來越高于拉新。因此，如何識別忠實用戶，了解目標用戶群的留存表現？如何分析用戶流失情況，優化產品？如何分析目標用戶是否完成了期望的行為等等就是我們數據分析的重要課題，而留存分析模型就是我們解決這些問題的重要工具。

二、概述

2.1 概念介紹

留存分析模型主要用于分析觸發了起始事件的用戶在后續時間周期內觸發了后續事件（即回訪事件）的比率，該模型可以較好的反映出用戶的忠實度或者說是用戶的粘性。對于留存分析模型有幾個重要的概念要了解：

留存分析一般需要指定起始事件和回訪事件，但起始事件和回訪事件可以相同，也可以不同：

1、起始事件、回訪事件可以選擇相同事件，這種可以很直觀地看出觸發事件的忠實用戶量。

例如：在簽到過程中，起始事件為簽到成功，回訪事件也是簽到成功，在一段時間內，連續觸發該事件的用戶數量即可為忠實用戶量。

2、起始事件、回訪事件可以選不同事件，這種就是比較正常流程下的用戶留存數據。

例如：在某個活動中，從下單到成功支付的場景內，起始事件為下單，回訪事件為支付成功，這種在一段時間內，觸發這兩個事件的同一用戶為指定流程下的用戶留存數據。

2.2 分析思路

留存率作為一個產品的核心指標之一，我們提升產品力，改善使用體驗，分析目標用戶很大程度上也是為了提升這個指標。比如，我們可以通過計算N日的留存率，來評估某個迭代是否是正向的。如圖1所示，某應用優化了首頁布局，推出了新版本A，就可以聯同舊版本B，分別計算每日的用戶留存率，它一般會組成一個衰減的留存曲線。曲線衰減的越慢，說明我們的留存率越高，也就能體現我們首頁的修改是有正向的效果的。當然有時留存的提升可能只有百分之零點幾，但是在一個很大的用戶基數的前提下，也可能產生一些質變的效果。我們也可以將特定的人群圈定為用戶組，針對不同的用戶組進行留存分析，挖掘更加忠實的用戶群體。

圖1  新版本A與舊版本B的留存對比

三、用留存進行的數據分析

了解了上面的關于留存模型的基本概念，我們看一下如何創建一個留存。

3.1 選一個起始事件、一個回訪事件

起始事件：打開瀏覽器。

回訪事件：退出瀏覽器。

3.2 設置留存天數

設置一個留存天數為3天。

3.3 確定留存的時間區間

這里的時間區間概念是指，你需要查看的日期區間,例如選擇時間區間為2023-01-06~2023-01-08，則是只計算2023-01-06到2023-01-08這3天，每天的當日留存，第1日留存，第2日留存，第3日留存。

3.4 留存數據的展示及計算邏輯

起始用戶數 = 計算日期觸發起始事件用戶數。

• 當日留存用戶數 = 當日觸發回訪事件的用戶與當日觸發起始事件用戶交集。
• 第1日留存用戶數 = 次日觸發回訪事件的用戶與計算日期觸發起始事件用戶交集。
• 第2日留存用戶數 = 2日后觸發回訪事件的用戶與計算日期觸發起始事件用戶交集。
• 第3日留存用戶數 = 3日后觸發回訪事件的用戶與計算日期觸發起始事件用戶交集。
• 當日留存率 = 當日留存用戶數/起始用戶數*100%。
• 第1日留存率 = 第1日留存用戶數/起始用戶數*100%。
• 第2日留存率  = 第2日留存用戶數/起始用戶數*100%。
• 第3日留存率  = 第3日留存用戶數/起始用戶數*100%。

用戶留存數表（即表1）表示起始事件為“打開瀏覽器”、回訪事件為“退出瀏覽器”時，對應2023-01-06~2023-01-09每天的近3天的留存用戶數據。

表1 用戶留存數表

用戶留存率表（即表2）表示 起始事件為“打開瀏覽器”、回訪事件為“退出瀏覽器”時，對應2023-01-06~2023-01-08每天的近3天的留存用戶占比數據。

表2 用戶留存率表

以表1中2023-01-06日的數據為例，1月6日起始用戶數1000：是指觸發起始事件“打開瀏覽器”的用戶數；當日留存用戶數900：是指在觸發起始事件用戶中當日又觸發回訪事件為“退出瀏覽器“的用戶人數；

第1日留存用戶數500：是指1月7日觸發回訪事件且與1月6日中觸發起始事件的交集用戶數；第2日留存用戶數300：是指1月8日觸發回訪事件且與1月6日中觸發起始事件的交集用戶數300，以此類推到第3日，此時計算出來的2023-01-06這一天的3天的留存數據！

圖2：觸發起始事件、回訪事件對應3日內的留存用戶趨勢圖

四、整體功能設計及留存分析模型的實現

4.1（離線）功能整體架構設計

圖3 留存分析模型hive架構圖

整體架構主要分為配置、計算、存儲、展示四階段。

1. 配置

此階段主要是工程端的后臺服務實現。用戶在平臺按照自身需求設置起始事件及回訪事件、過濾條件、用戶群篩選、維度篩選等配置。后臺服務收到配置請求后，依據留存分析類型選擇不同任務組裝器進行sql任務的組裝。

2. 計算

平臺根據接收到的查詢方式，選擇離線查詢Spark引擎進行分析計算。離線計算結果同步到MySQL。

3. 存儲

離線結果集持久化到MySQL數據庫中，可通過后臺服務展示給用戶。

4. 展示

離線結果根據圖表配置ID查詢MySQL結果表數據進行展示，即時查詢通過配置后直接查詢展示結果。

4.2（離線）留存不同條件下實現SQL

離線通用執行hive任務SQL

離線留存hive執行SQL

select
    '起始留存計算日期' as origin_day,
    a.day as day,
    datediff('起始留存計算日期', a.day) as diff,
    count(distinct (a.uid)) as user,
    count(distinct (case when b.uid is not null then b.uid end)) as retention
FROM
    (
    SELECT
        day,
        uid
    FROM
        abcd.test
    WHERE
        day >= if('起始時間' >= date_sub('起始留存計算日期', '留存天數'),'起始時間',date_sub('起始留存計算日期', '留存天數'))
        AND day <= if('結束時間' <= '起始留存計算日期','結束時間','起始留存計算日期')
        AND event_id = '起始事件')
    ) a
LEFT JOIN (
    SELECT
        s.uid     
    FROM
        abcd.test s         
    WHERE
        s.day = '起始留存計算日期'
        AND s.event_id = '回訪事件'                    
    GROUP BY
        s.uid
) b ON
    a.uid = b.uid
WHERE
    day >= if('起始時間' >= date_sub('起始留存計算日期', '留存天數'),'起始時間',date_sub('起始留存計算日期', '留存天數'))
    AND day <= if('結束時間' <= '起始留存計算日期','結束時間','起始留存計算日期')
GROUP BY a.day

SQL 當中字段含義分別為：

【origin_day】：起始留存計算日期

【day】：最終留存計算日期

【diff】：第幾日留存

【user】：起始用戶數

【retention】：留存數

以上SQL含義：查詢起始留存計算日期分別到起始時間、結束時間這個區間段中的每天的詳細留存數據，不可一次性計算出時間區間內完整的留存數據，需經過多天累計查詢，且此SQL執行的結果在留存結果表中展示樣例為倒三角填充。

例如：我們定了起始事件和回訪事件后，去計算2022-05-01~2022-05-05的每一天的3日留存，此時，起始時間是2022-05-01，結束2022-05-05，留存天數3天。

針對此案例，起始留存計算日期開始日期應該為2022-05-01~2022-05-08，才能計算出2022-05-01~2022-05-05的每一天的3日留存。

第一步：計算起始留存日期 = 2022-05-01時，最終留存計算日期區間2022-05-01~2022-05-05日每天的留存數據，從時間上看，該起始留存計算日期只能計算出2022-05-01日當日留存，執行后結果如下（表3）：

表3  

起始留存計算日期2022-05-01在2022-05-01~2022-05-05區間內留存詳情數據

此時轉換后留存數據表格為（表4）：

表4 

起始留存計算日期2022-05-01在2022-05-01~2022-05-05區間內轉換后留存數據表

第二步：計算起始留存日期 = 2022-05-02時，最終留存計算日期區間2022-05-01~2022-05-05日每天的留存數據，該起始留存計算日期可計算2022-05-01日的第1日留存用戶數及2022-05-02日當日留存用戶數據，執行后結果如下（表5）：

表5  

起始留存計算日期2022-05-02在2022-05-01~2022-05-05區間內留存詳情數據

此時轉換后留存數據表格為（表6）：

表6

起始留存計算日期2022-05-02在2022-05-01~2022-05-05區間內轉換后留存數據表

第三步：計算起始留存日期 = 2022-05-03時，最終留存計算日期區間2022-05-01~2022-05-05日每天的留存數據，該起始留存計算日期可計算2022-05-01日的第2日留存用戶數、2022-05-02日第1日留存用戶數據、2022-05-03日當日留存用戶數據，執行后結果如下（表7）：

表7

起始留存計算日期2022-05-03在2022-05-01~2022-05-05區間內留存詳情數據

此時轉換后留存數據表格為（表8）：

表8

起始留存計算日期2022-05-03在2022-05-01~2022-05-05區間內轉換后留存數據表

第四步：以此類推，計算起始留存日期 = 2022-05-08時，最終留存計算日期區間2022-05-01~2022-05-05日每天的留存數據，該起始留存計算日期可計算2022-05-05日的第3日留存用戶數，執行后結果如下（表9）：

表9

起始留存計算日期2022-05-08在2022-05-01~2022-05-05區間內留存詳情數據

最終數據展示完全后會是一個完整的表格（可得如下結果表10）：

表10

2022-05-01~2022-05-05的每一天的3日留存數據表

4.3 存在的問題與下一步優化的方向

存在的問題：

用戶在平臺上進行報表創建后，在產出報表結果上耗時較長；當配置報表查詢周期長，數據量大的情況下，存在計算資源消耗過大的情況。

優化方向：

為了優化報表生成過程，可以考慮使用ClickHouse來處理數據。ClickHouse是一個高性能、分布式、列式存儲的數據庫系統，特別適合處理大規模數據和復雜查詢。

具體而言，可以采用以下ClickHouse特性：

• 將數據導入ClickHouse中，以便更快地查詢和計算。ClickHouse支持高效的數據導入和壓縮方式，可以大大減少數據的存儲空間和查詢時間。
  
• 利用ClickHouse的列式存儲和分布式計算能力，實現增量計算和數據預處理。通過使用ClickHouse的分布式計算能力，可以將計算任務分配給多個節點并行處理，從而加快計算速度。同時，通過使用ClickHouse的列式存儲能力，可以避免不必要的數據讀取和計算，提高計算效率。
  
• 利用ClickHouse的緩存機制，提高查詢效率。ClickHouse支持高效的緩存機制，可以將查詢結果緩存在內存中，以便更快地響應查詢請求。
  
• 利用ClickHouse的SQL查詢語言，實現靈活的數據分析和報表生成。ClickHouse支持SQL查詢語言，可以方便地進行數據分析和報表生成，同時也支持復雜查詢和聚合操作，可以滿足各種數據分析需求。

通過利用ClickHouse上述特性，進一步提高整個數據分析過程的效率和準確性。

五、基于ClickHouse的留存分析模型

5.1 利用ClickHouse查詢速度快的特性改造離線留存圖表產出方式

利用ClickHouse進行實時留存查詢

傳統的離線留存計算通常需要借助Hadoop、Spark等大數據處理框架，需要消耗大量計算資源和時間。而利用ClickHouse進行離線留存計算，可以大大提高計算速度和效率，可以實現秒級響應和高并發查詢。

具體步驟如下：

1. 將用戶行為數據導入ClickHouse；
2. 根據查詢配置數據組裝留存SQL用于查詢；
3. 利用ClickHouse的高速查詢功能，實時查詢留存率數據。

利用ClickHouse進行留存圖表的產出

利用ClickHouse進行留存計算和查詢后，可以通過數據可視化工具對留存數據進行圖表化展示，從而更加直觀地了解用戶留存情況。例如：

1. 利用數據可視化工具連接ClickHouse數據庫，查看留存率數據或者通過http請求查詢結果表數據；
2. 通過數據可視化工具繪制留存圖表，并進行定制化設計和樣式調整。

結合hive、ClickHouse兩者優點，可將架構做如下優化，對于歷史較長時間日期的結果回溯進行hive查詢處理，可在ClickHouse中存儲的數據作為每天例行查詢存儲結果。

例行：是指創建一次圖表每日例行執行報表任務，產出數據（例行可回溯ClickHouse中存儲日期的留存數據）。

手動：是指在指定時間范圍內執行，執行完成產出任務停止。

圖4 結合ClickHouse、hive后留存分析模型架構圖

5.2 主要函數介紹

Retention

該函數將一組條件作為參數，類型為1到32個 UInt8 類型的參數，用來表示事件是否滿足特定條件。任何條件都可以指定為參數（如 WHERE)。

除了第一個以外，條件成對適用：如果第一個和第二個是真的，第二個結果將是真的，如果第一個和第三個是真的，第三個結果將是真的，等等。

① 語法

retention(cond1, cond2, ..., cond32);

② 參數

cond — 返回 UInt8 結果（1或0）的表達式。

③ 返回值

數組為1或0。

1 — 條件滿足。

0 — 條件不滿足。

④ 類型

UInt8

ClickHouse查詢SQL

ClickHouse即時查詢留存SQL

SELECT retention_date,
       USER,
       IF(DATEDIFF('day', retention_date, NOW()) >= 1, retain0, NULL)    AS retain0,
       IF(DATEDIFF('day', retention_date, NOW()) >= 2, retain1, NULL)    AS retain1,
       IF(DATEDIFF('day', retention_date, NOW()) >= 3, retain2, NULL)    AS retain2,
       IF(DATEDIFF('day', retention_date, NOW()) >= 4, retain3, NULL)    AS retain3
       CONCAT(toString(ROUND(retain0 / USER * 100, 4)), '%')    AS ratio0,
       CONCAT(toString(ROUND(retain1 / retain0 * 100, 4)), '%') AS ratio1,
       CONCAT(toString(ROUND(retain2 / retain0 * 100, 4)), '%') AS ratio2,
       CONCAT(toString(ROUND(retain3 / retain0 * 100, 4)), '%') AS ratio3
FROM (SELECT b.retention_date,
             COUNT(DISTINCT (uid)) AS USER,
             SUM(ret[1])            AS retain0,
             SUM(ret[2])            AS retain1,
             SUM(ret[3])            AS retain2,
             SUM(ret[4])            AS retain3
      FROM (
               SELECT j.retention_date,                      
                      uid,
                      retention(
                              j.day = j.retention_date,
                              j.day = j.retention_date + INTERVAL 1 DAY,
                              j.day = j.retention_date + INTERVAL 2 DAY,
                              j.day = j.retention_date + INTERVAL 3 DAY,
                              j.day = j.retention_date + INTERVAL 4 DAY
                          ) AS ret
               FROM (SELECT b.day AS retention_date,
                            b.uid,
                            t.day AS DAY
                     FROM (SELECT DAY, uid
                            FROM abcd.test2
                           WHERE DAY >= '開始時間'
                             AND DAY <= '結束時間'
                             AND event_id IN ('起始事件')
                           GROUP BY DAY, uid ) b
                              LEFT JOIN
                          (SELECT uid , DAY, event_id
                           FROM abcd.test2
                           WHERE DAY >= '開始時間'
                             AND DAY <= '結束時間'
                             AND event_id IN ('回訪事件')
                           GROUP BY DAY, uid, event_id) t ON b.uid = t.uid
                     ) j
               GROUP BY j.retention_date, j.uid ) b
      GROUP BY b.retention_date)

SQL 當中返回結果含義分別為：

retention_date：留存日期

user：起始用戶數

retain0：當日留存用戶數

retain1：第1日留存用戶數

retain2：第2日留存用戶數

retain3：第3日留存用戶數

ratio0：當日留存率

ratio1：第1日留存率

ratio2：第2日留存率

ratio3：第3日留存率

以上SQL含義：計算出指定時間區間內3日留存信息，可一次性查詢出指定區間內的所有3日留存數據，一個sql即可查詢完全。

例如：我們定了起始事件和回訪事件后，去計算2022-06-01~2022-06-04的每一天的3日留存，此時，起始時間是2022-06-01，結束2022-06-04，留存天數3天。

針對此案例，在不同的日期查詢數據完整性不一致，我們拿2022-06-04日和2022-06-07日兩日查詢舉例。

第一步：針對2022-06-04日進行計算2022-06-01~2022-06-04的每一天的3日留存，執行后留存數據展示結果如下（表11）。

表11

2022-06-04日計算2022-06-01~2022-06-04的每一天的3日留存數據表

第二步：針對2022-06-07日進行計算2022-06-01~2022-06-04的每一天的3日留存，執行后留存數據展示結果如下（表12）。

表12

2022-06-08日計算2022-06-01~2022-06-04的每一天的3日留存數據表

趨勢結果展示（圖5）：

圖5 留存分析模型趨勢圖

六、寫在最后

本文介紹的留存模型就是數據分析工具箱的核心分析模型，使用的范圍十分廣泛。它通過計算用戶在一段時間內的留存率，可以評估產品、服務或應用程序的用戶體驗和吸引力，提高用戶留存率和活躍度。在實際的生產中，業務可根據自身具體需求和用戶特征進行定制化設計，同時也可將通過留存分析得到的人群信息結合其他的數據分析方法進一步的深入分析。例如，從留存中得到的用戶人群信息，我們可以進一步的使用路徑分析的分析方法，分析用戶的訪問行為對于產品的影響。

數據分析的工具方法有很多，除了上面提到過得用于分析用戶在應用上的訪問行為的用戶路徑分析；也有衡量業務中關鍵事件之間轉化效果的漏斗分析；還有事件分析、歸因分析等等，他們共同組成的強大的數據分析工具箱，可以較為全面的分析用戶行為的潛在特征與規律，幫助產品或者決策者作為更加可靠的決策。