共计 9356 个字符，预计需要花费 24 分钟才能阅读完成。
作者：vivo 互联网大数据团队 - Wu Yonggang
在《用户行为分析模型实际（一）—— 路径分析模型》中，讲述了基于平台化查问中查问工夫短、须要可视化的要求，并联合现有的存储计算资源以及具体需要，咱们在实现中将门路数据进行枚举后分为两次进行合并。
本次带来的是系列文章的第 2 篇，本文具体介绍漏斗模型的概念及基本原理，并论述了其在平台外部的具体实现。针对实际应用过程的问题，摸索基于 ClickHouse 漏斗模型实际计划。
一、背景需要
漏斗剖析是掂量转化成果、进行转化剖析的重要工具，是一种常见的流程式的数据分析办法。它可能帮忙你清晰地理解转化状况，从多角度分析比照，定位散失起因，晋升转化体现。他次要立足于三大需要场景：
- 定位用户散失具体起因。
- 检测某个专题流动成果。
- 针对不同版本，转化率状况比照。
二、概述
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）依照漏斗步骤计算每行数据处于的漏斗步骤。
假如须要统计分析的漏斗步骤为：“启动”->“首页”->“详情”。‘“启动”标记为 1，“首页”标记为 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])
15.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 尽管领有品种繁多的函数反对计算剖析，然而在短少便捷的自定义函数性能，在某些细分场景下并不非常贴合业务，这一点也是将来能够增强和冲破的方向。