如何簡單、高效的進行留存和漏斗分析

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 的 Group By 聚合查詢爲什麼跑的這麼快？

總結

Databend 留存（RETENTION）函數和漏斗分析（WINDOW_FUNNEL）函數去年已經實現，把複雜的邏輯進行封裝，讓用戶使用起來更加方便。

Databend 作爲一個新一代雲數倉，在設計上做了一個很大的轉變：數據不再是重心，用戶的體驗纔是。

對於一個數倉產品，相信大部分用戶都希望：

不再爲存儲空間發愁
不再爲計算資源發愁
開箱即用，真正的按需、按量付費

隨着雲基礎設施的發展，我們在 Databend Platform 裏讓這一切都變成了可能。

基於開源 Databend 內核、AWS EC2 計算資源、S3 的對象存儲，加上自研的 Serverless Infrastructure，Databend 團隊即將推出他們的第一個企業級產品：Databend Platform。

來，讓我們一起看看在 Databend Platform 裏如何做漏斗分析：

選擇 Worksheet
選擇休眠的 Warehouse
執行漏斗分析 SQL，自動喚醒 Warehouse

4.Warehouse 自動休眠

就是這麼簡單

關於 Databend

Databend 是一款開源、彈性、低成本，基於對象存儲也可以做實時分析的新式數倉。期待您的關注，一起探索雲原生數倉解決方案，打造新一代開源 Data Cloud。

Databend 文檔：https://databend.rs/
Twitter：https://twitter.com/Datafuse_Labs
Slack：https://datafusecloud.slack.com/
Wechat：Databend
GitHub ：https://github.com/datafuselabs/databend

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/ucu0uoaNpUrAa2cASQkdGg

前言

留存分析

數據表

構造數據集

留存分析

漏斗分析

數據表

構造數據集

漏斗分析

性能

總結

猜你喜歡