如何简单、高效的进行留存和漏斗分析

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

前言

随着移动互联网的发展，我们时刻都在生产着数据。
如果你做了一款 APP，3 月份新增用户 1000 人，你是不是想了解在未来的某些时间段内，这部分用户里有多少人持续使用了你的 APP？
如果你在经营一个电商，你可能更加关注用户在登录，访问（某个商品），下单，付款流程里每个环节的转化率，了解用户行为轨迹变化，以精准优化产品设计。比如，如果 Andorid 用户在 下单 到 付款这个环节转化率明显低于其他客户端，说明 Andorid 客户端在 付款 这个环节上存在一些问题。
这就是我们经常说的用户留存和漏斗转化率分析。
大部分数仓要满足这两个需求，基本都要写一堆 SQL 来进行复杂表达，且性能低下，因为这两个分析会重度依赖 GROUP BY，百万级数据可能就会在分钟级。
本篇就来聊聊 Databend 如何做到简洁、高效的满足这两个需求，使用一个简单的 SQL， 在千万级的数据集上也可以轻松搞定。

留存分析

数据表

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%

漏斗分析

数据表

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

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

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

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

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

基于开源 Databend 内核、AWS EC2 计算资源、S3 的对象存储，加上自研的 Serverless Infrastructure，Databend 团队即将推出他们的第一个企业级产品：Databend Platform。
来，让我们一起看看在 Databend Platform 里如何做漏斗分析：
1.选择 Worksheet
2.选择休眠的 Warehouse
3.执行漏斗分析 SQL，自动唤醒 Warehouse
4.Warehouse 自动休眠
5.就是这么简单