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

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

一、背景需求

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

1. 定位用戶流失具體原因。
2. 檢測某個(gè)專題活動(dòng)效果。
3. 針對不同版本，轉(zhuǎn)化率情況對比。

二、概述

2.1 概念介紹

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

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

定義如下：

無序漏斗：在漏斗的周期內(nèi)，不限定漏斗多個(gè)步驟之間事件發(fā)生的順序。

【計(jì)算規(guī)則】：假設(shè)一個(gè)漏斗中包含了 A、B、C 3個(gè)步驟，A步驟發(fā)生的時(shí)間可以在B步驟之前，也可以在B的后面。用戶的行為順序?yàn)锳、B、C的組合都算成功的漏斗轉(zhuǎn)化。即使漏斗步驟之間穿插一些其他事件步驟，依然視作該用戶完成一次成功的漏斗轉(zhuǎn)化。

有序漏斗：在漏斗的周期內(nèi)，嚴(yán)格限定漏斗每個(gè)步驟之間的發(fā)生順序。

【計(jì)算規(guī)則】：假設(shè)一個(gè)漏斗中包含了 A、B、C 3個(gè)步驟，A步驟發(fā)生的時(shí)間必須在B步驟之前，用戶的行為順序必須為A->B->C 。

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

 三、 用漏斗進(jìn)行的數(shù)據(jù)分析

了解了上面的關(guān)于漏斗模型的基本概念，我們看一下如何創(chuàng)建一個(gè)漏斗。

3.1 選一個(gè)漏斗類型

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

3.2  添加漏斗步驟

漏斗步驟就是漏斗分析的核心部分，步驟間統(tǒng)計(jì)數(shù)據(jù)的對比，就是我們分析步驟間數(shù)據(jù)的轉(zhuǎn)化和流失的關(guān)鍵指標(biāo)。

比如我們以一個(gè)“下載應(yīng)用領(lǐng)紅包”的活動(dòng)為例。預(yù)設(shè)的用戶的行為路徑是：用戶首先進(jìn)入【紅包首頁】，發(fā)現(xiàn)最新的紅包活動(dòng)“下載應(yīng)用，領(lǐng)取紅包”，點(diǎn)擊進(jìn)入【紅包活動(dòng)頁】，根據(jù)提示跳轉(zhuǎn)到【應(yīng)用下載頁】，選擇自己感興趣的應(yīng)用下載，完成后，進(jìn)入【提現(xiàn)頁面】領(lǐng)取活動(dòng)獎(jiǎng)勵(lì)。從上面描述的場景中，我們可以提取出以下關(guān)鍵的四步。

圖3.1 “下載應(yīng)用領(lǐng)紅包”活動(dòng)步驟

3.3 確定漏斗的時(shí)間區(qū)間和周期

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

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

3.4 漏斗數(shù)據(jù)的展示

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

    表3.1 “下載應(yīng)用領(lǐng)紅包”活動(dòng)分步數(shù)據(jù)

以表3.1中2021-05-27日的數(shù)據(jù)為例，觸達(dá)第一步“紅包首頁”的用戶數(shù)量為150,000，在同一天內(nèi)同時(shí)觸發(fā)第一步“紅包首頁”和第二步“紅包活動(dòng)頁”的人數(shù)為11,700。其他數(shù)據(jù)的含義以此類推。

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

從中可以直觀的反應(yīng)出用戶在“紅包首頁”、“紅包活動(dòng)頁”、“應(yīng)用下載頁”、“提現(xiàn)頁”四步中每一步的人數(shù)和轉(zhuǎn)化率。

比如，觸達(dá)“紅包首頁”頁面的人數(shù)為400,000，經(jīng)過”紅包首頁“，觸達(dá)”紅包活動(dòng)頁“頁面的人數(shù)為30,000。則這兩個(gè)階段的轉(zhuǎn)化率為：30,000÷400,000=7.5%。

通過對各個(gè)階段人數(shù)和轉(zhuǎn)化率的比對，就能比較直觀的發(fā)現(xiàn)我們這個(gè) “下載應(yīng)用領(lǐng)紅包”的活動(dòng)用戶流失的環(huán)節(jié)所在，并以此排查原因和優(yōu)化各個(gè)環(huán)節(jié)。

圖 3.2 “下載應(yīng)用領(lǐng)紅包”活動(dòng)漏斗圖

 四、整體功能設(shè)計(jì)及漏斗分析模型的實(shí)現(xiàn)

4.1 功能整體架構(gòu)設(shè)計(jì)

圖 4.1 漏斗分析整體架構(gòu)設(shè)計(jì)

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

（1）配置

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

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

（2）計(jì)算

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

（3）存儲

結(jié)果集持久化到數(shù)據(jù)庫中，可通過后臺服務(wù)展示給用戶。

4.2 無序漏斗實(shí)現(xiàn)邏輯

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

在模型的設(shè)計(jì)上，采用的思想是：

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

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

圖4.2 無序漏斗計(jì)算邏輯

4.3 有序漏斗實(shí)現(xiàn)邏輯

有序漏斗顧名思義，將嚴(yán)格漏斗每步之間的順序。整個(gè)實(shí)現(xiàn)邏輯可分為以下幾步：

（1）獲取規(guī)定時(shí)間區(qū)間內(nèi)的數(shù)據(jù)集。

為了方便講解，示例數(shù)據(jù)如下圖所示，其中，day為數(shù)據(jù)上報(bào)的時(shí)間，userId為用戶唯一標(biāo)識，event為事件，event_time為事件發(fā)生時(shí)間。

（2）按照漏斗步驟計(jì)算每行數(shù)據(jù)處于的漏斗步驟。

假設(shè)需要統(tǒng)計(jì)分析的漏斗步驟為：“啟動(dòng)”->“首頁”->“詳情”?！皢?dòng)”標(biāo)記為1，“首頁”標(biāo)記為2，“詳情”標(biāo)記為3，記錄在event_step字段上。

（3）對上述數(shù)據(jù)進(jìn)行處理，得到每個(gè)用戶在當(dāng)天有序的事件上報(bào)列表。

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

（4）計(jì)算每一步漏斗的人數(shù)。

按照day與level分組計(jì)算每一步漏斗的人數(shù)，也是是每個(gè)level的uv。

需要注意的是，因?yàn)橛?jì)算的是每一步漏斗的人數(shù)，所以步驟與步驟之間人數(shù)是沒有交集的，但事實(shí)上，根據(jù)有序漏斗的計(jì)算邏輯，觸達(dá)漏斗后面的步驟，一會觸達(dá)其前面的漏斗步驟。

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

4.4 存在的問題與下一步優(yōu)化的方向

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

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

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

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

5.1 主要函數(shù)介紹

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

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

（2）arrayWithConstant(length,param)

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

select arrayWithConstant(3,1);

Result：

arrayWithConstant(3, 1)

[1,1,1]

（3）arrayEnumerate(arr)

• 定義：返回?cái)?shù)組下標(biāo)
• 參數(shù)：arr：數(shù)組
• 例：SQL：?

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

Result：

arrayEnumerate([11, 22, 33])

[1,2,3]

（4）groupArray(x)

• 定義：創(chuàng)建數(shù)組
• 例：SQL： 

select groupArray(1);

Result：

groupArray(1)

[1]

（5）arrayCount([func,] arr1)

• 定義：返回?cái)?shù)組中符合函數(shù)func的元素的數(shù)量
• 參數(shù)：
  ① func：lambda表達(dá)式
  ② arr1：數(shù)組
• 例：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)

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

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

Result：

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

1

5.2 模型構(gòu)建過程

5.2.1  數(shù)據(jù)準(zhǔn)備

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

首先構(gòu)建一個(gè)ClickHouse表funnel_test，包含用戶唯一標(biāo)識userId，事件名稱event，事件發(fā)生日期day。

建表語句如下：

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

 插入測試數(shù)據(jù)：

insert into funnel_test values(1,'啟動(dòng)','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,'啟動(dòng)','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,'啟動(dòng)','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,'啟動(dòng)','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');

如果數(shù)據(jù)表如下：

表 5.1 漏斗模型測試數(shù)據(jù)

5.2.2 有序漏斗計(jì)算

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

（1）使用ClickHouse的漏斗構(gòu)建函數(shù)windowFunnel()查詢 

SELECT userId,
        windowFunnel(86400)(
                     day,
                     event = '啟動(dòng)',
                     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中，設(shè)置了漏斗周期為86400秒（1天），這個(gè)周期的單位是依據(jù)timestamp決定的。整個(gè)漏斗分為了4步驟：啟動(dòng)、首頁、詳情、下載。時(shí)間區(qū)間為“2021-05-01”到“2021-05-06”之間。執(zhí)行后，得到如下結(jié)果：

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

（2）獲取每個(gè)用戶在每個(gè)層級的明細(xì)數(shù)據(jù)

通過上一步我們計(jì)算出了每個(gè)用戶在設(shè)定的周期內(nèi)觸達(dá)的最大的層級。下面接著要計(jì)算每個(gè)用戶在每個(gè)層級的明細(xì)數(shù)據(jù)，計(jì)算邏輯如下：

SELECT userId,
             arrayWithConstant(level, 1)       levels,
             arrayJoin(arrayEnumerate(levels)) level_index
 
      FROM (
            SELECT userId,
                   windowFunnel(86400)(
                                day,
                                event = '啟動(dòng)',
                                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
               );

將這個(gè)最大的層級轉(zhuǎn)化為相應(yīng)大小的數(shù)組，從中得到數(shù)組下標(biāo)集合，然后將這個(gè)下標(biāo)的集合按其中元素展開為多行。這樣就得到每個(gè)用戶在每個(gè)層級上明細(xì)數(shù)據(jù)。

例如userId=1的最大層級為4，通過arryWithConstant函數(shù)生成數(shù)組[1,1,1,1]，然后取這個(gè)數(shù)組下標(biāo)得到新的數(shù)組[1,2,3,4]，這些下標(biāo)其實(shí)對應(yīng)著漏斗的“啟動(dòng)”，“首頁”，“詳情”，“下載”這四個(gè)層級。

將下標(biāo)數(shù)組通過arrayJoin函數(shù)展開，得到userId=1的各層明細(xì)數(shù)據(jù)：

全部userId的執(zhí)行結(jié)果如下：

（3） 計(jì)算漏斗各層的用戶數(shù)

將上面步驟得到的明細(xì)數(shù)據(jù)按照漏斗層級分組聚合，就得到了每個(gè)層級的用戶數(shù)?？傮w邏輯如下：

SELECT  transform(level_index,[1,2,3,4],['啟動(dòng)','首頁','詳情','下載'],'其他') as event,
        count(1)
FROM (
      SELECT userId,
             arrayWithConstant(level, 1)       levels,
             arrayJoin(arrayEnumerate(levels)) level_index
      FROM (
            SELECT userId,
                   windowFunnel(86400)(
                                day,
                                event = '啟動(dòng)',
                                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;

結(jié)果為：

5.2.3 無序漏斗計(jì)算

假定，漏斗的步驟為：啟動(dòng)->首頁

（1）確定計(jì)算的數(shù)據(jù)范圍

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

結(jié)果如下：

（2）計(jì)算每個(gè)userId的訪問量(pv)和訪問用戶數(shù)(uv)。

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

pv計(jì)算：

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

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

uv計(jì)算：

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

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

select day,
       userId,
       groupArray(event) as events,
       arrayCount(x-> x = '啟動(dòng)', events)  as level1_pv,
       if(has(events, '啟動(dòng)'), arrayCount(x-> x = '首頁', events), 0)  as level2_pv,
       hasAll(events, ['啟動(dòng)'])  as level1_uv,
       hasAll(events, ['啟動(dòng)','首頁'])  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;

得到結(jié)果：

（3）按天統(tǒng)計(jì)

按天統(tǒng)計(jì)，計(jì)算出每天的用戶數(shù)及每個(gè)層級的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 = '啟動(dòng)', events) as level1_pv,
             if(has(events, '啟動(dòng)'), arrayCount(x-> x = '首頁', events), 0) as level2_pv,
             hasAll(events, ['啟動(dòng)']) as level1_uv,
             hasAll(events, ['啟動(dòng)','首頁']) 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;

計(jì)算結(jié)果如下：

 六、寫在最后

漏斗分析是數(shù)據(jù)分析中的一個(gè)重要的分析手段，通過它獲取的各個(gè)環(huán)節(jié)的訪問量、轉(zhuǎn)化率、流失率等數(shù)據(jù)，為我們評估業(yè)務(wù)流程的合理性，提升用戶體驗(yàn)，加強(qiáng)用戶的留存率都起到了重要作用。

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

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