什么是留存,比如在20200701这天操作了“点击banner”的用户有100个,这部分用户在20200702这天操作了“点击app签到”的有20个,那么对于分析时间是20200701,且“点击banner”的用户在次日“点击app签到”的留存率是20%。
关于用户留存模型是各大商业数据分析平台必不可少的功能,企业一般用该模型衡量用户的活跃情况,也是能直接反应产品功能价值的直接指标;如,boss想要了解商城改版后,对用户加购以及后续下单情况的影响等。如下图,这就是一个典型的留存分析功能:
通常实现上述需求的传统做法是多表关联,了解clickhouse的攻城狮都清楚,多表关联简直就是clickhouse的天敌;如一张用户行为日志表中至少包含:用户id、行为事件、操作时间、地点属性等,想分析20200909日河南省注册用户次日的下单情况,那么SQL一般会这么写:
select count(distinct t1.uid) r1, count(distinct t2.uid) r2 from
(
select uid from action_log where day='20200909' and action='login' and province='河南省'
) as t1 left join
(
select uid from action_log where day='20200910' and action='order' and province='河南省'
) as t2
using uid
这种方式书写简单、好理解,但是性能会很差,在超大数据集上进行运算是不仅仅影响用户体验,还会因长期占有物理资源而拖垮整个clickhouse上的业务。
解决方法有两种:
通过Roaringbitmap进行用户行为分析是腾讯广告业务中常用的一种实现方案,点击查看 ,文章中内容较多这里挑选干货进行讲解:
bitmap可以理解为一个长度很长且只存储0/1数字的集合,如某个用户通过特定的哈希算法映射到位图内时,那么该位置就会被置为1,否则为0;通过这种方式对数据进行压缩,空间利用率可提示数十倍,数据可以很容易被系统cache,大大减少了IO操作。
在查询之前需要先对数据进行预处理,这里额外构建两张表,用来存储用户的位图信息。
向位图表插入数据,原始数据十几亿,插入后结果只有几万行,而且随着数据范围的再扩大,位图表的数据增量变化也不会很明显
用户基本信息表:table_attribute_bit
同理table_attribute_bit插入后数据也得到了极大的压缩,最终数据如下图:
应用案例
a. 操作了某个行为的用户在后续某一天操作了另一个行为的留存: 如“20200701点击了banner的用户在次日点击app签到的留存人数”,就可以用以下的sql快速求解:
b. 操作了某个行为并且带有某个属性的用户在后续的某一天操作了另一个行为的留存: 如“20200701点击了banner且来自广东/江西/河南的用户在次日点击app签到的留存人数”:
c. 操作了某个行为并且带有某几个属性的用户在后续的某一天操作了另一个行为的留存: 如“20200701点击了banner、来自广东且新进渠道是小米商店的用户在次日点击app签到的留存人数”:
其中bitmapCardinality用来计算位图中不重复数据个数,在大数据量下会有一定的数据误差,bitmapAnd用来计算两个bitmap的与操作,即返回同时出现在两个bitmap中用户数量
该方法的确比较灵活,不仅仅能解决留存问题,还有很多关于事件分析的需求等待我们去探索;然而它的缺点是操作复杂,且不支持对实时数据的分析
通过上面的例子不难看出,腾讯的做法虽然提升了查询的性能,但是操作过于复杂,不便于用户理解和后期的维护;关于这些痛点易企秀数仓这边做法是采用retention进行实现 retention function是clickhouse中高级聚合函数,较bitmap的方式实现留存分析会更加简单、高效;语法如下:
retention(cond1, cond2, ..., cond32);
## cond 为判断条件
## 支持最长32个参数的输入,也就是说 至少支持一个完整自然月的留存分析查询
其中满足条件1的数据会置为1,之后的每一个表达式成立的前提都要建立在条件1成立的基础之上,这正好符合我们对留存模型的定义
那么我们还以上面的3个场景为例方便对比说明:
SELECT
sum(r[1]) AS r1,
sum(r[2]) AS r2,
r2/r1
FROM
(
SELECT
uid,
retention(date = '20200701' and type='点击banner', date = '20200702' and type='点击app签到' ) AS r
FROM action_log
WHERE date IN ('20200701', '20200702')
GROUP BY uid
)
SELECT
sum(r[1]) AS r1,
sum(r[2]) AS r2,
r2/r1
FROM
(
SELECT
uid,
retention(date = '20200701' and type='点击banner', date = '20200702' and type='点击app签到' ) AS r
FROM action_log
WHERE date IN ('20200701', '20200702') and province IN ('广东', '江西', '河南')
GROUP BY uid
)
不过该方式与bitmap比也有缺陷,那就是如果用户日志表中不存储用户属性信息时,就需要与用户属性表进行关联查询,两张大表关联,查询性能会相当慢。
什么是有序漏斗,有序漏斗需要满足所有用户事件链上的操作都是逡巡时间先后关系的,且漏斗事件不能有断层,触达当前事件层的用户也需要经历前面的事件层
接上一章智能路径分析,假设我们已经得到了触达支付购买的路径有 “首页->详情页->购买页->支付“ 和 “搜索页->详情页->购买页->支付“ 两个主要路径,但是我们不清楚哪条路径转化率高,那么这个时候漏斗分析就派上用场了
漏斗模型是一个倒置的金字塔形状,主要用来分析页面与页面 功能模块之前的转化情况,下面一层都是基于紧邻的上一层转化而来的,也就是说前一个条件是后一个条件成立的基础;解决此类场景clickhouse提供了一个名叫windowFunnel的函数来实现:
windowFunnel(window)(timestamp, cond1, cond2, ..., condN)
为了便于大家理解,这里举个简单的栗子:
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分组统计就会丢失之前的层级数据,导致漏斗不能呈现金字塔状
继续使用上面的测试数据,通过数组的高阶函数对上述结果数据进行二次加工处理以获取完整漏斗展示效果。
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
为什么要有路径分析,举个最简单的例子,你的领导想要知道用户在完成下单前的一个小时都做了什么?绝大多数人拿到这个需求的做法就是进行数据抽样观察以及进行一些简单的问卷调参工作,这种方式不但费时费力还不具有代表性,那么这个时候你就需要一套用户行为路径分析的模型作为支撑,才能快速帮组你找到最佳答案
clickhouse是我见过最完美的OLAP数据库,它不仅将性能发挥到了极致,还在数据分析层面做了大量改进和支撑,为用户提供了大量的高级聚合函数和基于数组的高阶lambda函数。
企业中常用的路径分析模型一般有两种:
因为我们接下来要通过sequenceCount完成模型的开发,所以需要先来了解一下该函数的使用:
sequenceCount(pattern)(timestamp, cond1, cond2, ...)
该函数通过pattern指定事件链,当用户行为完全满足事件链的定义是会+1;其中time时间类型或时间戳,单位是秒,如果两个事件发生在同一秒时,是无法准确区分事件的发生先后关系的,所以会存在一定的误差。
pattern支持3中匹配模式:
例如,boos要看在会员购买页超过10分钟才下单的用户数据 那么就可以这么写
SELECT
count(1) AS c1,
sum(cn) AS c2
FROM
(
SELECT
u_i,
sequenceCount('(?1)(?t>600)(?2)')(toDateTime(time), act = '会员购买页', act = '会员支付成功') AS cn
FROM app.scene_tracker
WHERE day = '2020-09-07'
GROUP BY u_i
)
WHERE cn >= 1
┌──c1─┬──c2─┐
│ 102 │ 109 │
└─────┴─────┘
根据上面数据可以看出完成支付之前在会员购买页停留超过10分钟的用户有100多个,那么是什么原因导致用户迟迟不肯下单,接下来我们就可以使用智能路径针对这100个用户展开分析,看看他们在此期间都做了什么。
智能路径分析模型比较复杂,但同时支持的分析需求也会更加复杂,如分析给定期望的路径终点、途经点和最大事件时间间隔,统计出每条路径的用户数,并按照用户数对路径进行倒序排列 虽然clickhouse没有提供现成的分析函数支持到该场景,但是可以通过clickhouse提供的高阶数组函数进行曲线救国,大致SQL如下:
SELECT
result_chain,
uniqCombined(user_id) AS user_count
FROM (
WITH
toDateTime(maxIf(time, act = '会员支付成功')) AS end_event_maxt,
arrayCompact(arraySort(
x -> x.1,
arrayFilter(
x -> x.1 <= end_event_maxt,
groupArray((toDateTime(time), (act, page_name)))
)
)) AS sorted_events,
arrayEnumerate(sorted_events) AS event_idxs,
arrayFilter(
(x, y, z) -> z.1 <= end_event_maxt AND (z.2.1 = '会员支付成功' OR y > 600),
event_idxs,
arrayDifference(sorted_events.1),
sorted_events
) AS gap_idxs,
arrayMap(x -> x + 1, gap_idxs) AS gap_idxs_,
arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,
arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events
SELECT
user_id,
arrayJoin(split_events) AS event_chain_,
arrayCompact(event_chain_.2) AS event_chain,
hasAll(event_chain, [('pay_button_click', '会员购买页')]) AS has_midway_hit,
arrayStringConcat(arrayMap(
x -> concat(x.1, '#', x.2),
event_chain
), ' -> ') AS result_chain
FROM (
SELECT time,act,page_name,u_i as user_id
FROM app.scene_tracker
WHERE toDate(time) >= '2020-09-30' AND toDate(time) <= '2020-10-02'
AND user_id IN (10266,10022,10339,10030)
)
GROUP BY user_id
HAVING length(event_chain) > 1
)
WHERE event_chain[length(event_chain)].1 = '会员支付成功'
AND has_midway_hit = 1
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;
实现思路:
不设置途经点,且仅以用户最后一次到达目标事件作为参考
SELECT
result_chain,
uniqCombined(user_id) AS user_count
FROM (
select
u_i as user_id,
arrayStringConcat( #获取访问路径字符串
arrayCompact( #相邻事件去重
arrayMap(
b - > tupleElement(b, 1),
arraySort( #对用户事件进行排序得到用户日志的先后顺序
y - > tupleElement(y, 2),
arrayFilter(
(x, y) - > y - x.2 > 3600 #找到目标节点前1小时内的所有事件
arrayMap(
(x, y) - > (x, y),
groupArray(e_t),
groupArray(time)
),
arrayWithConstant(
length(groupArray(time)),
maxIf(time, e_t = '会员支付成功') #设置目标节点
)
)
)
)
),
'->'
) result_chain
from
bw.scene_tracker
where
toDate(time) >= '2020-09-30' AND toDate(time) <= '2020-10-02' AND user_id IN (10266,10022,10339,10030)
group by
u_i
) tab
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;
简单说一下上面用到的几个高阶函数:
SELECT arrayJoin([1, 2, 3, 4]) AS data
┌─data─┐
│ 1 │
│ 2 │
│ 3 │
│ 4 │
└──────┘
SELECT uniqCombined(data)
FROM
(
SELECT arrayJoin([1, 2, 3, 1, 4, 2]) AS data
)
┌─uniqCombined(data)─┐
│ 4 │
└────────────────────┘
SELECT arrayCompact([1, 2, 3, 3, 1, 1, 4, 2]) AS data
┌─data──────────┐
│ [1,2,3,1,4,2] │
└───────────────┘
SELECT arraySort(x -> (x.1), [(1, 'a'), (4, 'd'), (2, 'b'), (3, 'c')]) AS data
┌─data──────────────────────────────┐
│ [(1,'a'),(2,'b'),(3,'c'),(4,'d')] │
└───────────────────────────────────┘
SELECT arrayFilter(x -> (x > 2), [12, 3, 4, 1, 0]) AS data
┌─data─────┐
│ [12,3,4] │
└──────────┘
SELECT
a.2,
groupArray(a.1)
FROM
(
SELECT arrayJoin([(1, 'a'), (4, 'a'), (3, 'a'), (2, 'c')]) AS a
)
GROUP BY a.2
┌─tupleElement(a, 2)─┬─groupArray(tupleElement(a, 1))─┐
│ c │ [2] │
│ a │ [1,4,3] │
└────────────────────┴────────────────────────────────┘
SELECT arrayEnumerate([1, 2, 3, 3, 1, 1, 4, 2]) AS data
┌─data──────────────┐
│ [1,2,3,4,5,6,7,8] │
└───────────────────┘
SELECT arrayDifference([3, 1, 1, 4, 2]) AS data
┌─data──────────┐
│ [0,-2,0,3,-2] │
└───────────────┘
SELECT arrayMap(x -> concat(toString(x.1), ':', x.2), [(1, 'a'), (4, 'a'), (3, 'a'), (2, 'c')]) AS data
┌─data──────────────────────┐
│ ['1:a','4:a','3:a','2:c'] │
└───────────────────────────┘
SELECT arraySplit((x, y) -> y, ['a', 'b', 'c', 'd', 'e'], [1, 0, 0, 1, 0]) AS data
┌─data──────────────────────┐
│ [['a','b','c'],['d','e']] │
└───────────────────────────┘
## 遇到下标为1时进行分割,分割点为下一个 数组的起始点;注意,首项为1还是0不影响结果
SELECT has([1, 2, 3, 4], 2) AS data
┌─data─┐
│ 1 │
└──────┘
SELECT hasAll([1, 2, 3, 4], [4, 2]) AS data
┌─data─┐
│ 1 │
└──────┘
---
SELECT hasAll([1, 2, 3, 4], [0, 2]) AS data
┌─data─┐
│ 0 │
└──────┘
SELECT arrayStringConcat(['a', 'b', 'c'], '->') AS data
┌─data────┐
│ a->b->c │
└─────────┘
SELECT arrayWithConstant(4, 'abc') AS data
┌─data──────────────────────┐
│ ['abc','abc','abc','abc'] │
└───────────────────────────┘
什么是session,Session即会话,是指在指定的时间段内在您的网站/H5/小程序/APP上发生的一系列用户行为的集合。例如,一次会话可以包含多个页面浏览、交互事件等。
Session 是具备时间属性的,根据不同的切割规则,可以生成不同长度的 Session; 可见,Session统计与上述智能路径检测的场景有相似之处,都需要寻找用户行为链的边界进行处理;session分析主要包含两部分:
SELECT
ts_date,
sum(length(session_gaps)) AS session_cnt
FROM (
WITH
arraySort(groupArray(toUnixTimestamp(time))) AS times,
arrayDifference(times) AS times_diff
SELECT
toDate(time) as ts_date,
arrayFilter(x -> x > 1800, times_diff) AS session_gaps
FROM app.scene_tracker
WHERE toDate(time) >= '2020-09-06' AND toDate(time) <= '2020-09-07'
GROUP BY ts_date,user_id
)
GROUP BY ts_date
SELECT
ts_date,
count(1) session_cnt,
sum(length(event_chain)) AS event_cnt,
round(event_cnt/session_cnt)
FROM (
WITH
arraySort(groupArray(( toUnixTimestamp(time), act ))) AS sorted_events,
arrayEnumerate(sorted_events) AS event_idxs,
arrayFilter(
(x,y,z) -> y>1800 or z.2='会员支付成功',
event_idxs,
arrayDifference(sorted_events.1),
sorted_events
) as gap_index ,
arrayMap(x->x+1, gap_index) as gap_index_ ,
arrayMap(x -> if(has(gap_index_, x), 1, 0), event_idxs) as gap_marks ,
arraySplit((x,y) -> y, sorted_events, gap_marks) as session_chain
SELECT
toDate(time) as ts_date,
u_i as user_id,
arrayJoin(session_chain) event_chain_,
arrayCompact(x->x.2, event_chain_) event_chain
FROM app.scene_tracker
WHERE toDate(time) >= '2020-09-06' AND toDate(time) <= '2020-09-07'
GROUP BY ts_date,user_id
)
GROUP BY ts_date