基于ClickHouse的用户行为(路径)分析实践
前言
ClickHouse为用户提供了丰富的多参聚合函数(parametric aggregate function)和基于数组+Lambda表达式的高阶函数(higher-order function),将它们灵活使用可以达到魔法般的效果。在我们的体系中,ClickHouse定位点击流数仓,所以下面举几个用它来做用户行为(路径)分析的实战例子,包括:
- 路径匹配
- 智能路径检测
- 有序漏斗转化
- 用户留存
- Session统计
路径匹配
CK默认提供了sequenceMatch函数检查是否有事件链满足输入的模式,sequenceCount函数则统计满足输入模式的事件链的数量。示例:
SELECT
site_id,
sequenceMatch('(?1)(?t<=15)(?2).*(?3)')(
ts_date_time,
event_type = 'shtLogon',
event_type = 'shtKkclick' AND column_type = 'homePage',
event_type = 'shtAddCart'
) AS is_match
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;
┌─site_id─┬─is_match─┐
│ 10030 │ 1 │
│ 10339 │ 1 │
│ 10266 │ 1 │
│ 10022 │ 1 │
└─────────┴──────────┘
SELECT
site_id,
sequenceCount('(?1)(?t<=15)(?2).*(?3)')(
ts_date_time,
event_type = 'shtLogon',
event_type = 'shtKkclick' AND column_type = 'homePage',
event_type = 'shtAddCart'
) AS seq_count
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;
┌─site_id─┬─seq_count─┐
│ 10030 │ 33611 │
│ 10339 │ 14045 │
│ 10266 │