关于后端:如何简单高效的进行留存和漏斗分析

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

前言

随着挪动互联网的倒退，咱们时刻都在生产着数据。
如果你做了一款 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 的 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. 就是这么简略

对于 Databend

Databend 是一款开源、弹性、低成本，基于对象存储也能够做实时剖析的旧式数仓。期待您的关注，一起摸索云原生数仓解决方案，打造新一代开源 Data Cloud。

• Databend 文档：https://databend.rs/
• Twitter：https://twitter.com/Datafuse_…
• Slack：https://datafusecloud.slack.com/
• Wechat：Databend
• GitHub：https://github.com/datafusela…

文章首发于公众号：Databend