Clickhouse 实现留存分析与数据可视化

1、前言

什么是留存分析？

留存，顾名思义，就是用户在你的产品中留下来、持续使用的意思。
留存为什么重要？留存是 AARRR 模型中重要的环节之一，只有做好了留存，才能保障新用户在注册后不会白白流失。有时候我们光看日活（DAU），会觉得数据不错，但有可能是因为近期有密集的推广拉新活动，注入了大量的新用户，但是留下来的用户不一定在增长，可能在减少，只不过被新用户数掩盖了所以看不出来。这就好像一个不断漏水的篮子，如果不去修补底下的裂缝，而只顾着往里倒水，是很难获得持续的增长的。

关于用户留存模型是各大商业数据分析平台必不可少的功能，企业一般用该模型衡量用户的活跃情况，是能直接反应产品功能价值的直接指标。

今天顺手分享一下我关于留存分析的代码实现：

2、环境准备

2.1、测试表

在clickhouse中创建样例表：

CREATE TABLE ftabcch.behavior
(
    `uid` Int32,
    `event_type` String,
    `tag` String,
    `time` datetime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(time)
ORDER BY uid
SETTINGS index_granularity = 8192

2.2、插入伪造的样例数据

为了方便展示，我在clickhouse上伪造了一批用户，从2022年4月10号注册之后，以自身日期为瞄点，每过一天之后仍然登录的客户的数据。

2.2.1、插入伪造的2022年04月11号有登录且是2022年4月10号注册的新用户数据

insert into ftabcch.behavior select tupleElement(b, 1) uid, tupleElement(b, 2) event_type,tupleElement(b, 3) tag, tupleElement(b, 4) time from (
with
(select groupArray(b) from (select * from generateRandom('b UInt16') limit 70000)) as uid,
(select groupArray('登录') from numbers(70000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(70000)) as tag,
(select groupArray(toDateTime('2022-04-11')) from numbers(70000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

2.2.1、插入伪造的2022年04月12号有登录且是2022年4月10号注册的新用户数据

insert into ftabcch.behavior select tupleElement(b, 1) uid, tupleElement(b, 2) event_type,tupleElement(b, 3) tag, tupleElement(b, 4) time from (
with
(select groupArray(b) from (select * from generateRandom('b UInt16') limit 60000)) as uid,
(select groupArray('登录') from numbers(60000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(60000)) as tag,
(select groupArray(toDateTime('2022-04-12')) from numbers(60000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

2.2.1、插入伪造的2022年04月13号有登录且是2022年4月10号注册的新用户数据

insert into ftabcch.behavior select tupleElement(b, 1) uid, tupleElement(b, 2) event_type,tupleElement(b, 3) tag, tupleElement(b, 4) time from (
with
(select groupArray(b) from (select * from generateRandom('b UInt16') limit 50000)) as uid,
(select groupArray('登录') from numbers(50000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(50000)) as tag,
(select groupArray(toDateTime('2022-04-13')) from numbers(50000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

2.2.1、插入伪造的2022年04月14号有登录且是2022年4月10号注册的新用户数据

insert into ftabcch.behavior select tupleElement(b, 1) uid, tupleElement(b, 2) event_type,tupleElement(b, 3) tag, tupleElement(b, 4) time from (
with
(select group