clickhouse数据模型之有序漏斗分析
2020-09-10 本文已影响0人
郭彦超
什么是有序漏斗，有序漏斗需要满足所有用户事件链上的操作都是逡巡时间先后关系的，且漏斗事件不能有断层，触达当前事件层的用户也需要经历前面的事件层
前言
接上一章智能路径分析，假设我们已经得到了触达支付购买的路径有 “首页->详情页->购买页->支付“ 和 “搜索页->详情页->购买页->支付“ 两个主要路径，但是我们不清楚哪条路径转化率高，那么这个时候漏斗分析就派上用场了
漏斗模型是一个倒置的金字塔形状，主要用来分析页面与页面 功能模块之前的转化情况，下面一层都是基于紧邻的上一层转化而来的，也就是说前一个条件是后一个条件成立的基础；解决此类场景clickhouse提供了一个名叫windowFunnel的函数来实现：
windowFunnel(window)(timestamp, cond1, cond2, ..., condN)
- window：窗口大小，从第一个事件开始，往后推移一个窗口大小来提取事件数据
- timestamp：可以是时间或时间戳类型，用来对时事件进行排序
- cond：每层满足的事件
为了便于大家理解，这里举个简单的栗子：
# 创建一张用户行为表，至少包含时间、事件、用户id
CREATE TABLE test.action
(
`uid` Int32,
`event_type` String,
`time` datetime
)
ENGINE = MergeTree()
PARTITION BY uid
ORDER BY xxHash32(uid)
SAMPLE BY xxHash32(uid)
SETTINGS index_granularity = 8192
插入测试数据
insert into action values(1,'浏览','2020-01-02 11:00:00');
insert into action values(1,'点击','2020-01-02 11:10:00');
insert into action values(1,'下单','2020-01-02 11:20:00');
insert into action values(1,'支付','2020-01-02 11:30:00');
insert into action values(2,'下单','2020-01-02 11:00:00');
insert into action values(2,'支付','2020-01-02 11:10:00');
insert into action values(1,'浏览','2020-01-02 11:00:00');
insert into action values(3,'浏览','2020-01-02 11:20:00');
insert into action values(3,'点击','2020-01-02 12:00:00');
insert into action values(4,'浏览','2020-01-02 11:50:00');
insert into action values(4,'点击','2020-01-02 12:00:00');
insert into action values(5,'浏览','2020-01-02 11:50:00');
insert into action values(5,'点击','2020-01-02 12:00:00');
insert into action values(5,'下单','2020-01-02 11:10:00');
insert into action values(6,'浏览','2020-01-02 11:50:00');
insert into action values(6,'点击','2020-01-02 12:00:00');
insert into action values(6,'下单','2020-01-02 12:10:00');
已30分钟作为一个时间窗口，看下windowFunnel返回了什么样的数据
SELECT
user_id,
windowFunnel(1800)(time, event_type = '浏览', event_type = '点击', event_type = '下单', event_type = '支付') AS level
FROM
(
SELECT
time,
event_type,
uid AS user_id
FROM action
)
GROUP BY user_id
┌─user_id─┬─level─┐
│ 3 │ 1 │
│ 2 │ 0 │
│ 5 │ 2 │
│ 1 │ 4 │
│ 6 │ 3 │
└─────────┴───────┘
这里level只记录了路径中最后一次事件所属的层级，如果直接对level分组统计就会丢失之前的层级数据，导致漏斗不能呈现金字塔状
模型
继续使用上面的测试数据，通过数组的高阶函数对上述结果数据进行二次加工处理以获取完整漏斗展示效果。
- 案例
分析"2020-01-02"这天 路径为“浏览->点击->下单->支付”的转化情况。
SELECT level_index,count(1) FROM
(
SELECT user_id,
arrayWithConstant(level, 1) levels,
arrayJoin(arrayEnumerate( levels )) level_index
FROM (
SELECT
user_id,
windowFunnel(1800)(
time,
event_type = '浏览',
event_type = '点击' ,
event_type = '下单',
event_type = '支付'
) AS level
FROM (
SELECT time, event_type , uid as user_id
FROM test.action
WHERE toDate(time) = '2020-01-02'
)
GROUP BY user_id
)
)
group by level_index
ORDER BY level_index
image.png
