在《??用戶行為分析模型實踐（一）—— 路徑分析模型??》中，講述了基于平臺化查詢中查詢時間短、需要可視化的要求，并結合現有的存儲計算資源以及具體需求，我們在實現中將路徑數據進行枚舉后分為兩次進行合并。

本次帶來的是系列文章的第2篇，本文詳細介紹漏斗模型的概念及基本原理，并闡述了其在平臺內部的具體實現。針對實際使用過程的問題，探索基于 ClickHouse漏斗模型實踐方案。

一、背景需求

漏斗分析是衡量轉化效果、進行轉化分析的重要工具，是一種常見的流程式的數據分析方法。它能夠幫助你清晰地了解轉化情況，從多角度剖析對比，定位流失原因，提升轉化表現。他主要立足于三大需求場景：

1. 定位用戶流失具體原因。
2. 檢測某個專題活動效果。
3. 針對不同版本，轉化率情況對比。

二、概述

2.1 概念介紹

漏斗模型主要用于分析一個多步驟過程中每一步的轉化與流失情況。其中有幾個概念要了解：

其中漏斗模型分為兩種：無序漏斗和有序漏斗。

定義如下：

無序漏斗：在漏斗的周期內，不限定漏斗多個步驟之間事件發生的順序。

【計算規則】：假設一個漏斗中包含了 A、B、C 3個步驟，A步驟發生的時間可以在B步驟之前，也可以在B的后面。用戶的行為順序為A、B、C的組合都算成功的漏斗轉化。即使漏斗步驟之間穿插一些其他事件步驟，依然視作該用戶完成一次成功的漏斗轉化。

有序漏斗：在漏斗的周期內，嚴格限定漏斗每個步驟之間的發生順序。

【計算規則】：假設一個漏斗中包含了 A、B、C 3個步驟，A步驟發生的時間必須在B步驟之前，用戶的行為順序必須為A->B->C 。

和無序漏斗一樣，漏斗步驟之間穿插一些其他事件步驟，依然視作該用戶完成一次成功的漏斗轉化。

 三、 用漏斗進行的數據分析

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

3.1 選一個漏斗類型

漏斗模型的類型一般分為有序漏斗和無序漏斗，它們的概念已在2.1做了詳細的介紹。我們這里以無序漏斗為例，創建漏斗模型。

3.2  添加漏斗步驟

漏斗步驟就是漏斗分析的核心部分，步驟間統計數據的對比，就是我們分析步驟間數據的轉化和流失的關鍵指標。

比如我們以一個“下載應用領紅包”的活動為例。預設的用戶的行為路徑是：用戶首先進入【紅包首頁】，發現最新的紅包活動“下載應用，領取紅包”，點擊進入【紅包活動頁】，根據提示跳轉到【應用下載頁】，選擇自己感興趣的應用下載，完成后，進入【提現頁面】領取活動獎勵。從上面描述的場景中，我們可以提取出以下關鍵的四步。

圖3.1 “下載應用領紅包”活動步驟

3.3 確定漏斗的時間區間和周期

這里多了一個時間區間的概念，與前文介紹的周期容易混淆。一般來說，此類數據的數倉表是按照時間分區的。所以選擇時間區間，本質就是選擇要計算的數據范圍。

周期是指一個漏斗從第一步流轉到最后一步的時間限制，即是用來界定怎樣才是一個完整的漏斗。在本例中，我們按照天為周期進行處理，選擇時間區間為“2021-05-27”、“2021-05-28”、“2021-05-29”。

3.4 漏斗數據的展示

依據我們設計的漏斗模型（具體模型設計，下文會提及），可以計算出下表的數據：

    表3.1 “下載應用領紅包”活動分步數據

以表3.1中2021-05-27日的數據為例，觸達第一步“紅包首頁”的用戶數量為150,000，在同一天內同時觸發第一步“紅包首頁”和第二步“紅包活動頁”的人數為11,700。其他數據的含義以此類推。

將表3.1中的數據每步按照日期加起來，就得到2021-05-27至2021-05-29日數據的漏斗圖（圖3.2）。

從中可以直觀的反應出用戶在“紅包首頁”、“紅包活動頁”、“應用下載頁”、“提現頁”四步中每一步的人數和轉化率。

比如，觸達“紅包首頁”頁面的人數為400,000，經過”紅包首頁“，觸達”紅包活動頁“頁面的人數為30,000。則這兩個階段的轉化率為：30,000÷400,000=7.5%。

通過對各個階段人數和轉化率的比對，就能比較直觀的發現我們這個 “下載應用領紅包”的活動用戶流失的環節所在，并以此排查原因和優化各個環節。

圖 3.2 “下載應用領紅包”活動漏斗圖

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

4.1 功能整體架構設計

圖 4.1 漏斗分析整體架構設計

整體工程主要分為配置、計算、存儲三階段。

（1）配置

此階段主要是工程端的后臺服務實現。用戶在前端按照自身需求設置漏斗類型、漏斗步驟、篩選條件、時間區間和周期等配置。后臺服務收到配置請求后，依據漏斗類型選擇不同任務組裝器進行任務的組裝。

其中，漏斗類型是無序漏斗使用的Hive SQL 任務組裝器，而更加復雜的有序漏斗可以使用 Spark任務組裝器。組裝后生成的任務包含了漏斗模型的計算邏輯，比如 Hive SQL或者 Spark 任務。

（2）計算

平臺根據接收到的任務的類型，選擇Hive或者 Spark引擎進行分析計算。計算結果同步到 MySQL 或者ClickHouse集群。

（3）存儲

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

4.2 無序漏斗實現邏輯

無序漏斗并不限制其多個步驟之間的發生順序，只要在限定的周期內完成即可。

在模型的設計上，采用的思想是：

在一個周期內，按照步驟順序依次計算漏斗每一步驟的人數，并且下一層的計算的人群范圍要等于上一次計算完成的人群范圍，通過每一步的人群范圍可以計算出想要的指標，比如每步的人數（uv）或者訪問量（pv）。

如圖4.2 所示。其中，圈選的人群為每一步的觸達的人數，計算的結果集就是基于此人群得到計算結果。步驟1的圈選人群會作為步驟2漏斗計算的一個篩選條件，參與后續計算。依次類推完成漏斗的每一步計算。最終匯集每一步的計算結果集形成類似于表3.1 的結果數據。

圖4.2 無序漏斗計算邏輯

4.3 有序漏斗實現邏輯

有序漏斗顧名思義，將嚴格漏斗每步之間的順序。整個實現邏輯可分為以下幾步：

（1）獲取規定時間區間內的數據集。

為了方便講解，示例數據如下圖所示，其中，day為數據上報的時間，userId為用戶唯一標識，event為事件，event_time為事件發生時間。

（2）按照漏斗步驟計算每行數據處于的漏斗步驟。

假設需要統計分析的漏斗步驟為：“啟動”->“首頁”->“詳情”?！皢印睒擞洖?，“首頁”標記為2，“詳情”標記為3，記錄在event_step字段上。

（3）對上述數據進行處理，得到每個用戶在當天有序的事件上報列表。

將上述數據按照day,userId分組，按event_time順序，分別求取event_step和event_time的有序集合，并根據event_step獲取漏斗觸達的最大深度，記為level，如下：

（4）計算每一步漏斗的人數。

按照day與level分組計算每一步漏斗的人數，也是是每個level的uv。

需要注意的是，因為計算的是每一步漏斗的人數，所以步驟與步驟之間人數是沒有交集的，但事實上，根據有序漏斗的計算邏輯，觸達漏斗后面的步驟，一會觸達其前面的漏斗步驟。

所以，前面的步驟一定要加上其后所有步驟的的人數，才是該步真正的人數。如上面的例子，對于2021-05-01的數據，level=1的uv為1，level=2的uv為0，level=3的uv為1，所以level=1實際總人數為三步人數之和，也就是2。依次類推，由此可以得到所有步驟真正的總人數。

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

問題：現階段用戶通過自定義的配置，生成相應的Spark或者Hive任務計算出模型的結果并生成報表，進而展示給用戶。這樣的流程在提供給用戶靈活的配置和個性化的查詢的同時，兼顧了節約存儲資源。美中不足的是報表的生成過程，依然需要耗費一定的時間成本，尤其是有序漏斗采用了Spark計算，對于隊列資源也會產生較大的消耗。這點在用戶短時間創建大量的分析報表時，體現的尤為明顯。

優化方向：將一定時期內的相關的數倉數據同步到ClickHouse，依托ClickHouse強大的即時計算和分析能力，為用戶提供所查即所得的使用體驗。用戶可以根據自身業務需求選擇即時查詢或者離線報表。例如，比如需要大量組合各類條件進行對比分析的可以選擇即時模塊。需要長期觀察的報表可以選擇離線的例行報表。這樣就達到的存儲和查詢效率的平衡。

下面，就對漏斗模型在ClickHouse上的應用做一些探索。

 五、基于 ClickHouse 的漏斗分析模型

5.1 主要函數介紹

（1）windowFunnel(window, [mode, [mode, ... ]])(timestamp, cond1, cond2, ..., condN)

• 定義：
  在所定義的滑動窗口內，依次檢索事件鏈條。函數在這個事件連上觸及的事件的最大數量。
• 補充：
  ① 該函數檢索到事件在窗口內的第一個事件，則將事件計數器設置為1，此時就是滑動窗口的啟動時刻。
  ② 如果來自鏈的事件在窗口內順序發生，則計數器遞增，如果事件序列終端，則計數器不會增加。
  ③ 如果數據在不同的完成點具有多個事件鏈，則該函數將僅輸出最長鏈的大小。
• 參數：
  ①【timestamp】 ：表中代表時間的列。函數會按照這個時間排序
  ② 【cond】：事件鏈的約束條件
  ③【window】：滑動窗口的長度，表示首尾兩個事件條件的間隙。單位依據timestamp的參數而定。即：timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window
  ④ 【mode】：可選的一些配置：
  【strict】: 事件鏈中，如果有事件是不唯一的，則重復的事件的將被排除，同時函數停止計算。
  【strict_orde】：事件鏈中的事件，要嚴格保證先后次序。
  【strict_increase】：事件鏈的中事件，其事件戳要保持完全遞增。

（2）arrayWithConstant(length,param)

• 定義：
  生成一個指定長度的數組
• 參數：
  ① length：數組長度
  ② param：填充字段
• 例：SQL：

select arrayWithConstant(3,1);

Result：

arrayWithConstant(3, 1)

[1,1,1]

（3）arrayEnumerate(arr)

• 定義：返回數組下標
• 參數：arr：數組
• 例：SQL：?

select arrayEnumerate([11,22,33]);

Result：

arrayEnumerate([11, 22, 33])

[1,2,3]

（4）groupArray(x)

• 定義：創建數組
• 例：SQL： 

select groupArray(1);

Result：

groupArray(1)

[1]

（5）arrayCount([func,] arr1)

• 定義：返回數組中符合函數func的元素的數量
• 參數：
  ① func：lambda表達式
  ② arr1：數組
• 例：SQL：

select arrayCount(x-> x!=1,[11,22,33]);

Result：

arrayCount(lambda(tuple(x), notEquals(x, 1)), [11, 22, 33])

3

（6）hasAll(set, subset)

• 定義：檢查一個數組是否是另一個數組的子集，如果是就返回1
• 參數：
  ① set：具有一組元素的任何類型的數組。
  ② subset：任何類型的數組，其元素應該被測試為set的子集。
• 例：SQL：

select hasAll([11,22,33], [11]);

Result：

hasAll([11, 22, 33], [11])

1

5.2 模型構建過程

5.2.1  數據準備

為了更加清晰的講解整個過程，我們舉一個例子演示一下整個過程。

首先構建一個ClickHouse表funnel_test，包含用戶唯一標識userId，事件名稱event，事件發生日期day。

建表語句如下：

create table funnel_test
 (
     userId  String,
     event String,
     day   DateTime
 )
     engine = MergeTree PARTITION BY userId
         ORDER BY xxHash32(userId);

 插入測試數據：

insert into funnel_test values(1,'啟動','2021-05-01 11:00:00');
 insert into funnel_test values(1,'首頁','2021-05-01 11:10:00');
 insert into funnel_test values(1,'詳情','2021-05-01 11:20:00');
 insert into funnel_test values(1,'瀏覽','2021-05-01 11:30:00');
 insert into funnel_test values(1,'下載','2021-05-01 11:40:00');
                                                 
 insert into funnel_test values(2,'啟動','2021-05-02 11:00:00');
 insert into funnel_test values(2,'首頁','2021-05-02 11:10:00');
 insert into funnel_test values(2,'瀏覽','2021-05-02 11:20:00');
 insert into funnel_test values(2,'下載','2021-05-02 11:30:00');
 
 insert into funnel_test values(3,'啟動','2021-05-01 11:00:00');
 insert into funnel_test values(3,'首頁','2021-05-02 11:00:00');
 insert into funnel_test values(3,'詳情','2021-05-03 11:00:00');
 insert into funnel_test values(3,'下載','2021-05-04 11:00:00');
 
 insert into funnel_test values(4,'啟動','2021-05-03 11:00:00');
 insert into funnel_test values(4,'首頁','2021-05-03 11:01:00');
 insert into funnel_test values(4,'首頁','2021-05-03 11:02:00');
 insert into funnel_test values(4,'詳情','2021-05-03 11:03:00');
 insert into funnel_test values(4,'詳情','2021-05-03 11:04:00');
 insert into funnel_test values(4,'下載','2021-05-03 11:05:00');

如果數據表如下：

表 5.1 漏斗模型測試數據

5.2.2 有序漏斗計算

假定，漏斗的步驟為：啟動->首頁->詳情->下載

（1）使用ClickHouse的漏斗構建函數windowFunnel()查詢 

SELECT userId,
        windowFunnel(86400)(
                     day,
                     event = '啟動',
                     event = '首頁',
                     event = '詳情',
                     event = '下載'
            ) AS level
 FROM (
       SELECT day, event, userId
       FROM funnel_test
       WHERE toDate(day) >= '2021-05-01'
         and toDate(day) <= '2021-05-06'
          )
 GROUP BY userId
 order by userId;

從上述SQL中，設置了漏斗周期為86400秒（1天），這個周期的單位是依據timestamp決定的。整個漏斗分為了4步驟：啟動、首頁、詳情、下載。時間區間為“2021-05-01”到“2021-05-06”之間。執行后，得到如下結果：

從結果中，可以看到各個userId在規定周期內，觸達的最大的漏斗層級，也就是執行了漏斗步驟了幾步。例如，userId=1，在一天內，按序訪問了啟動->首頁->詳情->下載這四步，得到最大層級就是4。當然，我們也可以漏斗函數配置為”strict_order“模式，他將嚴格保證先后次序，還是userId為1的情況，在”2021-05-01“這一天，”詳情“與”下載“間多了個”瀏覽“的動作，所以此刻，userId=1可觸達的層級就是3，因為，在”strict_order“下，”詳情“阻斷了整個事件鏈路。

（2）獲取每個用戶在每個層級的明細數據

通過上一步我們計算出了每個用戶在設定的周期內觸達的最大的層級。下面接著要計算每個用戶在每個層級的明細數據，計算邏輯如下：

SELECT userId,
             arrayWithConstant(level, 1)       levels,
             arrayJoin(arrayEnumerate(levels)) level_index
 
      FROM (
            SELECT userId,
                   windowFunnel(86400)(
                                day,
                                event = '啟動',
                                event = '首頁',
                                event = '詳情',
                                event = '下載'
                       ) AS level
            FROM (
                  SELECT day, event, userId
                  FROM funnel_test
                  WHERE toDate(day) >= '2021-05-01'
                    and toDate(day) <= '2021-05-06'
                     )
            GROUP BY userId
               );

將這個最大的層級轉化為相應大小的數組，從中得到數組下標集合，然后將這個下標的集合按其中元素展開為多行。這樣就得到每個用戶在每個層級上明細數據。

例如userId=1的最大層級為4，通過arryWithConstant函數生成數組[1,1,1,1]，然后取這個數組下標得到新的數組[1,2,3,4]，這些下標其實對應著漏斗的“啟動”，“首頁”，“詳情”，“下載”這四個層級。

將下標數組通過arrayJoin函數展開，得到userId=1的各層明細數據：

全部userId的執行結果如下：

（3） 計算漏斗各層的用戶數

將上面步驟得到的明細數據按照漏斗層級分組聚合，就得到了每個層級的用戶數。總體邏輯如下：

SELECT  transform(level_index,[1,2,3,4],['啟動','首頁','詳情','下載'],'其他') as event,
        count(1)
FROM (
      SELECT userId,
             arrayWithConstant(level, 1)       levels,
             arrayJoin(arrayEnumerate(levels)) level_index
      FROM (
            SELECT userId,
                   windowFunnel(86400)(
                                day,
                                event = '啟動',
                                event = '首頁',
                                event = '詳情',
                                event = '下載'
                       ) AS level
            FROM (
                  SELECT day, event, userId
                  FROM funnel_test
                  WHERE toDate(day) >= '2021-05-01'
                    and toDate(day) <= '2021-05-06'
                     )
            GROUP BY userId
               )
         )
group by level_index
ORDER BY level_index;

結果為：

5.2.3 無序漏斗計算

假定，漏斗的步驟為：啟動->首頁

（1）確定計算的數據范圍

SELECT toDate(day),
       event,
       userId
FROM funnel_test
WHERE toDate(day) >= '2021-05-01'
  and toDate(day) <= '2021-05-06';

結果如下：

（2）計算每個userId的訪問量(pv)和訪問用戶數(uv)。

先按照時間與userId分組，通過groupArray函數獲取事件（event）的集合。

pv計算：

【漏斗第一層級】：直接查詢事件集合中，漏斗第一步事件的總數。

【漏斗第二層級】：在第一層級事件存在的情況下，查詢第二層級的數量。后面的層級以此類推。

uv計算：

【漏斗第一層級】：如果事件集合中，包含第一步事件，則記為1，表示存在。

【漏斗第二層級】：事件集合中，同時包含第一與第二層級事件，則記為1。后面的層級依此類推。

select day,
       userId,
       groupArray(event) as events,
       arrayCount(x-> x = '啟動', events)  as level1_pv,
       if(has(events, '啟動'), arrayCount(x-> x = '首頁', events), 0)  as level2_pv,
       hasAll(events, ['啟動'])  as level1_uv,
       hasAll(events, ['啟動','首頁'])  as level2_uv
from (
      SELECT toDate(day) as day,
             event,
             userId
      FROM funnel_test
      WHERE toDate(day) >= '2021-05-01'
        and toDate(day) <= '2021-05-06')
group by day, userId;

得到結果：

（3）按天統計

按天統計，計算出每天的用戶數及每個層級的pv，uv。

SELECT day           AS day,     
       sum(level1_pv) AS sum_level1_pv,
       sum(level2_pv) AS sum_level2_pv,
       sum(level1_uv) as sum_level1_uv,
       sum(level2_uv) as sum_level2_uv
from (
      select day,
             userId,
             groupArray(event) as events,
             arrayCount(x-> x = '啟動', events) as level1_pv,
             if(has(events, '啟動'), arrayCount(x-> x = '首頁', events), 0) as level2_pv,
             hasAll(events, ['啟動']) as level1_uv,
             hasAll(events, ['啟動','首頁']) as level2_uv
      from (
            SELECT toDate(day) as day,
                   event,
                   userId
            FROM funnel_test
            WHERE toDate(day) >= '2021-05-01'
              and toDate(day) <= '2021-05-06')
      group by day, userId
         )
group by day
order by day;

計算結果如下：

 六、寫在最后

漏斗分析是數據分析中的一個重要的分析手段，通過它獲取的各個環節的訪問量、轉化率、流失率等數據，為我們評估業務流程的合理性，提升用戶體驗，加強用戶的留存率都起到了重要作用。

本文簡述了現有基于 Hive/Spark 的漏斗模型的實現邏輯，此種方式在允許用戶高度自定義查詢的同時，節約了存儲資源。但是會消耗一定的時間成本和隊列資源。

為了優化此類問題，本文討論了基于 ClickHouse 的漏斗模型實現，在模型的計算速率取得了較為理想的效果。ClickHouse 雖然擁有種類繁多的函數支持計算分析，但是在缺少便捷的自定義函數功能，在某些細分場景下并不十分貼合業務，這一點也是未來可以加強和突破的方向。