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 groupArray(b) from (select * from generateRandom('b UInt16') limit 40000)) as uid,
(select groupArray('登录') from numbers(40000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(40000)) as tag,
(select groupArray(toDateTime('2022-04-14')) from numbers(40000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

2.2.1、插入伪造的2022年04月15号有登录且是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 30000)) as uid,
(select groupArray('登录') from numbers(30000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(30000)) as tag,
(select groupArray(toDateTime('2022-04-15')) from numbers(30000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

2.2.1、插入伪造的2022年04月16号有登录且是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 20000)) as uid,
(select groupArray('登录') from numbers(20000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(20000)) as tag,
(select groupArray(toDateTime('2022-04-16')) from numbers(20000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

2.2.1、插入伪造的2022年04月17号有登录且是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 10000)) as uid,
(select groupArray('登录') from numbers(10000)) as event_type,
(select groupArray('2022年4月10号注册的新用户') from numbers(10000)) as tag,
(select groupArray(toDateTime('2022-04-17')) from numbers(10000)) as time
select arrayJoin(arrayZip(uid,event_type,tag,time)) as b)

3、留存分析实现代码

3.1、bitmapAndCardinality函数

with (select toString(groupBitmap(uid)) from behavior where tag='2022年4月10号注册的新用户') as new_num,
    (select ['2022-04-11','2022-04-12','2022-04-13','2022-04-14','2022-04-15','2022-04-16','2022-04-17']) as list_date
        select 
            array(array(list_date[1],new_num,toString(bitmapAndCardinality(list_bit[1],list_bit[1])),
            toString(bitmapAndCardinality(list_bit[1],list_bit[2])),toString(bitmapAndCardinality(list_bit[1],list_bit[3])),
            toString(bitmapAndCardinality(list_bit[1],list_bit[4])),toString(bitmapAndCardinality(list_bit[1],list_bit[5])),
            toString(bitmapAndCardinality(list_bit[1],list_bit[6])),toString(bitmapAndCardinality(list_bit[1],list_bit[7]))),
                array(list_date[2],new_num,toString(bitmapAndCardinality(list_bit[2],list_bit[2])),toString(bitmapAndCardinality(list_bit[2],list_bit[3])),
                toString(bitmapAndCardinality(list_bit[2],list_bit[4])),toString(bitmapAndCardinality(list_bit[2],list_bit[5])),
                toString(bitmapAndCardinality(list_bit[2],list_bit[6])),toString(bitmapAndCardinality(list_bit[2],list_bit[7])),''),
                    array(list_date[3],new_num,toString(bitmapAndCardinality(list_bit[3],list_bit[3])),toString(bitmapAndCardinality(list_bit[3],list_bit[4])),
                    toString(bitmapAndCardinality(list_bit[3],list_bit[5])),toString(bitmapAndCardinality(list_bit[3],list_bit[6])),
                    toString(bitmapAndCardinality(list_bit[3],list_bit[7])),'',''),
                        array(list_date[4],new_num,toString(bitmapAndCardinality(list_bit[4],list_bit[4])),toString(bitmapAndCardinality(list_bit[4],list_bit[5])),
                        toString(bitmapAndCardinality(list_bit[4],list_bit[6])),toString(bitmapAndCardinality(list_bit[4],list_bit[7])),'','',''),
                            array(list_date[5],new_num,toString(bitmapAndCardinality(list_bit[5],list_bit[5])),toString(bitmapAndCardinality(list_bit[5],list_bit[6])),
                            toString(bitmapAndCardinality(list_bit[5],list_bit[7])),'','','',''),
                                array(list_date[6],new_num,toString(bitmapAndCardinality(list_bit[6],list_bit[6])),toString(bitmapAndCardinality(list_bit[6],list_bit[7])),'','','','',''),
                                    array(list_date[7],new_num,toString(bitmapAndCardinality(list_bit[7],list_bit[7])),'','','','','','')) as list_retention
        from
        (select toString(groupArray(date)) as date,groupArray(user_bit) as list_bit from 
           (select toDate(time) as date, groupBitmapState(distinct uid) as user_bit from behavior where tag='2022年4月10号注册的新用户' and event_type='登录' group by date order by date) )

bitmapAndCardinality的一些详细计算可以在这篇文章参考一下，有些函数附上了图解：
ClickHouse 集成Bitmap(2022-01-16更新)
也可以参考官网：
Bitmap Functions

3.2、retention函数

with (select toString(groupBitmap(uid)) from behavior where tag='2022年4月10号注册的新用户') as new_num,
(select ['2022-04-11','2022-04-12','2022-04-13','2022-04-14','2022-04-15','2022-04-16','2022-04-17']) as list_date
    select array(array(list_date[1],new_num,toString(sum(r20220411[1])),toString(sum(r20220411[2])),toString(sum(r20220411[3])),
                toString(sum(r20220411[4])),toString(sum(r20220411[5])),toString(sum(r20220411[6])),toString(sum(r20220411[7]))),
            array(list_date[2],new_num,toString(sum(r20220412[1])),toString(sum(r20220412[2])),toString(sum(r20220412[3])),toString(sum(r20220412[4])),
                toString(sum(r20220412[5])),toString(sum(r20220412[6])),''),
            array(list_date[3],new_num,toString(sum(r20220413[1])),toString(sum(r20220413[2])),toString(sum(r20220413[3])),toString(sum(r20220413[4])),
                toString(sum(r20220413[5])),'',''),
            array(list_date[4],new_num,toString(sum(r20220414[1])),toString(sum(r20220414[2])),toString(sum(r20220414[3])),toString(sum(r20220414[4])),'','',''),
            array(list_date[5],new_num,toString(sum(r20220415[1])),toString(sum(r20220415[2])),toString(sum(r20220415[3])),'','','',''),
            array(list_date[6],new_num,toString(sum(r20220416[1])),toString(sum(r20220416[2])),'','','','',''),
            (select array(list_date[7],new_num,toString(r20220417),'','','','','','') from (select groupBitmap(uid) r20220417 from behavior where tag='2022年4月10号注册的新用户'
                and event_type='登录' and time== toDate('2022-04-17')))) as list_retention
    from(
        SELECT
            uid,
            retention(time = toDate('2022-04-11'), time = toDate('2022-04-12'), time = toDate('2022-04-13'),time = toDate('2022-04-14'),
                time = toDate('2022-04-15'), time = toDate('2022-04-16'), time = toDate('2022-04-17') ) AS r20220411,
            retention(time = toDate('2022-04-12'), time = toDate('2022-04-13'),time = toDate('2022-04-14'),time = toDate('2022-04-15'), 
                time = toDate('2022-04-16'), time = toDate('2022-04-17') ) AS r20220412,
            retention(time = toDate('2022-04-13'),time = toDate('2022-04-14'),time = toDate('2022-04-15'), time = toDate('2022-04-16'), time = toDate('2022-04-17') ) AS r20220413,
            retention(time = toDate('2022-04-14'),time = toDate('2022-04-15'), time = toDate('2022-04-16'), time = toDate('2022-04-17') ) AS r20220414,
            retention(time = toDate('2022-04-15'), time = toDate('2022-04-16'), time = toDate('2022-04-17') ) AS r20220415,
            retention(time = toDate('2022-04-16'), time = toDate('2022-04-17') ) AS r20220416
        FROM behavior where tag='2022年4月10号注册的新用户' and event_type='登录'
        GROUP BY uid)

关于这个retention函数，官网有着例子：
retention(cond1, cond2, …, cond32)

其中：cond — 返回 UInt8 结果（1或0）的表达式。即数组为1或0。1 — 条件满足。0 — 条件不满足。

简单来说，这个函数的作用就是计算从瞄点cond1开始，后面的cond2依赖于cond1，cond3会依赖于cond2和cond2，以此类推，condN依赖于从cond1到condN-1的所有条件。以上面所写的代码为例，就是2022年04月16日有登录行为（cond1）的客户杨某，在2022年04月17日仍然有登录行为（cond2），这样，它返回的结果才为1。

4、留存图的pyecharts的实现

数据取自本文所伪造的样例数据所计算的结果。

import pyecharts.options as opts
from pyecharts.charts import Funnel
from pyecharts.globals import CurrentConfig,NotebookType
CurrentConfig.NOTEBOOK_TYPE = NotebookType.JUPYTER_NOTEBOOK

table = Table()

headers = ['日期','2022年4月10号注册的新增用户数','当天留存人数','一天后','两天后','三天后','四天后','五天后','六天后']
rows = [['2022-04-11','64614','42982','25877','22920','19655','15811','11266','6006'],
  ['2022-04-12','64614','39480','21041','18010','14588','10376','5503',''],
  ['2022-04-13', '64614', '34881', '15821', '12863', '9121', '4955', '', ''],
  ['2022-04-14', '64614', '29922', '10851', '7875', '4206', '', '', ''],
  ['2022-04-15', '64614', '24064', '6353', '3428', '', '', '', ''],
  ['2022-04-16', '64614', '17223', '2522', '', '', '', '', ''],
  ['2022-04-17', '64614', '9255', '', '', '', '', '', '']]
table.add(headers, rows)
table.set_global_opts(
    title_opts=ComponentTitleOpts(title="留存分析", subtitle="周留存图")
)
table.render_notebook()