Rust, Databend and the Cloud Warehouse（6) 如何简单、高效的进行留存和漏斗分析 [ 虎哥的博客 ]

Databend 是一个使用 Rust 研发、开源的、完全面向云架构的新式数仓，致力于提供极速的弹性扩展能力，打造按需、按量的 Data Cloud 产品体验。
开源地址：https://github.com/datafuselabs/databend

前言

随着移动互联网的发展，我们时刻都在生产着数据。

如果你做了一款 APP，3 月份新增用户 1000 人，你是不是想了解在未来的某些时间段内，这部分用户里有多少人持续使用了你的 APP？

如果你在经营一个电商，你可能更加关注用户在登录，访问（某个商品），下单，付款流程里每个环节的转化率，了解用户行为轨迹变化，以精准优化产品设计。比如，如果 Andorid 用户在 下单 到 付款这个环节转化率明显低于其他客户端，说明 Andorid 客户端在 付款 这个环节上存在一些问题。

这就是我们经常说的用户留存和漏斗转化率分析。

大部分数仓要满足这两个需求，基本都要写一堆 SQL 来进行复杂表达，且性能低下，因为这两个分析会重度依赖 GROUP BY，百万级数据可能就会在分钟级。

本篇就来聊聊 Databend 如何做到简洁、高效的满足这两个需求，使用一个简单的 SQL，在千万级的数据集上也可以轻松搞定。

留存分析

数据表

1	CREATE TABLE events(`user_id` INT, `visit_date` DATE);

user_id - 用户 ID
visit_date - 用户访问日期

构造数据集

构造用户访问记录。

# user_id 从 0 到 10000000 在 2022-05-15 访问数据
INSERT INTO events SELECT number AS user_id, '2022-05-15' FROM numbers(10000000);

# user_id 从 0 到 5000000 在 2022-05-16 访问数据
INSERT INTO events SELECT number AS user_id, '2022-05-16' FROM numbers(5000000);

# user_id 从 0 到 100000 在 2022-05-17 访问数据
INSERT INTO events SELECT number As user_id, '2022-05-17' FROM numbers(100000);

留存分析

SELECT
    sum(r[0]) AS r1,
    sum(r[1]) AS r2,
    sum(r[2]) AS r3
FROM
(
    SELECT
        user_id,
        retention(login_date = '2022-05-15', login_date = '2022-05-16', login_date = '2022-05-17') AS r
    FROM events
    GROUP BY user_id
);

这里使用 Databend retention 函数轻松搞定：

+----------+---------+--------+
| r1       | r2      | r3     |
+----------+---------+--------+
| 10000000 | 5000000 | 100000 |
+----------+---------+--------+

2022-05-15 有 10000000 人访问
2022-05-16 有 5000000 个用户持续访问，用户留存率 5000000/10000000 = 50%
2022-05-17 有100000个用户持续访问，用户留存率 100000/10000000 = 10%

漏斗分析

数据表

1	CREATE TABLE events(user_id BIGINT, event_name VARCHAR, event_timestamp TIMESTAMP);

user_id - 用户 ID
event_name - 事件类型：登录， 访问 ，下单 ，付款
event_timestamp - 事件时间（Databend TIMESTAMP 类型精度是小数点后 6 位，微秒（microsecond））

构造数据集

# 用户 100123 事件
INSERT INTO events VALUES(100123, '登录', '2022-05-14 10:01:00');
INSERT INTO events VALUES(100123, '访问', '2022-05-14 10:02:00');
INSERT INTO events VALUES(100123, '下单', '2022-05-14 10:04:00');
INSERT INTO events VALUES(100123, '付款', '2022-05-14 10:10:00');

# 用户 100125 事件
INSERT INTO events VALUES(100125, '登录', '2022-05-15 11:00:00');
INSERT INTO events VALUES(100125, '访问', '2022-05-15 11:01:00');
INSERT INTO events VALUES(100125, '下单', '2022-05-15 11:02:00');

# 用户 100126 事件
INSERT INTO events VALUES(100126, '登录', '2022-05-15 12:00:00');
INSERT INTO events VALUES(100126, '访问', '2022-05-15 12:01:00');

# 用户 100127 事件
INSERT INTO events VALUES(100127, '登录', '2022-05-15 11:30:00');
INSERT INTO events VALUES(100127, '访问', '2022-05-15 11:31:00');

漏斗分析

SELECT
    level,
    count() AS count
FROM
(
    SELECT
        user_id,
        window_funnel(3600000000)(event_timestamp, event_name = '登录', event_name = '访问', event_name = '下单', event_name = '付款') AS level
    FROM events
    GROUP BY user_id
)
GROUP BY level ORDER BY level ASC;

+-------+-------+
| level | count |
+-------+-------+
|     2 |     2 |
|     3 |     1 |
|     4 |     1 |
+-------+-------+

这里使用 Databend window_funnel 函数对用户在 1 小时窗口内，进行事件链下钻分析。

一小时内：有多少用户登录（level-1) –> 有多少用户访问（level-2） –> 有多少用户下单（level-3） –> 有多少用户付款（level-4）

从结果来看：

登录 --> 访问 这条事件链上总共有 2 个用户，100126 和 100127
登录 --> 访问 --> 下单 这条事件链上有 1 位用户，100125
登录 --> 访问 --> 下单 --> 购买 这条事件链上总共有 1 位用户，100123

这样我们就可以轻松计算出每个阶段的转化率。

性能

从上面示例可以看出，留存和漏斗分析都重度依赖 GROUP BY user_id，如果 user_id 较多，对 GROUP BY 计算速度有比较高的要求，Databend 在 GROUP BY上做了大量的优化，目前性能已经非常强悍，具体机制可以参考这篇文章 Databend 的 Group By 聚合查询为什么跑的这么快？

总结

Databend 留存（RETENTION）函数和漏斗分析（WINDOW_FUNNEL）函数去年已经实现，把复杂的逻辑进行封装，让用户使用起来更加方便。

Databend 作为一个新一代云数仓，在设计上做了一个很大的转变：数据不再是重心，用户的体验才是。

对于一个数仓产品，相信大部分用户都希望：

不再为存储空间发愁
不再为计算资源发愁
开箱即用，真正的按需、按量付费

随着云基础设施的发展，我们在 Databend Platform 里让这一切都变成了可能。

基于开源 Databend 内核、AWS EC2 计算资源、S3 的对象存储，加上自研的 Serverless Infrastructure，Databend 团队即将推出他们的第一个企业级产品：Databend Platform。

来，让我们一起看看在 Databend Platform 里如何做漏斗分析：

选择 Worksheet
选择休眠的 Warehouse
执行漏斗分析 SQL，自动唤醒 Warehouse
Warehouse 自动休眠
就是这么简单