全部產品
Search

搜尋本產品

    AnalyticDB：漏鬥留存函數

    文件中心

    AnalyticDB：漏鬥留存函數

    更新時間：Sep 25, 2024

    本文介紹如何在AnalyticDB for MySQL中使用漏鬥留存函數進行漏鬥分析。

    前提條件

    叢集核心版本需為3.1.6.0及以上。

    說明

    查看湖倉版叢集的核心版本，請執行SELECT adb_version();。如需升級核心版本，請聯絡支援人員。

    背景資訊

    漏鬥分析是常見的轉化分析方法，它可以反映使用者各個階段行為的轉化率。漏鬥分析被廣泛應用在使用者行為分析和App資料分析的情境，例如流量分析、產品目標轉化率等資料營運與資料分析任務。AnalyticDB for MySQL支援的漏鬥留存函數如下：

    測試資料集

    為方便您測試漏鬥留存函數，本文使用了天池實驗室的淘寶行為資料作為分析資料，下載連結，請參見淘寶使用者購物行為資料集。

    測試資料集中，使用者的行為分為如下幾種。

    行為類型

    說明

    pv

    商品詳情頁pv，相當於點擊。

    buy

    商品購買。

    cart

    將商品加入購物車。

    fav

    收藏商品。

    進行測試前，您需要將測試資料集上傳至OSS中，再通過OSS匯入到AnalyticDB for MySQL。

    1. 上傳測試資料集至OSS，具體操作，請參見控制台上傳檔案。

    2. 將資料通過OSS外表匯入到AnalyticDB for MySQL，操作樣本如下：

      1. 建立OSS外表。

        CREATE TABLE `user_behavior_oss` (
  `user_id` string,
  `item_id` string,
  `cate_id` string,
  `event` string,
   `ts` bigint) ENGINE = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"******",
"accesskey":"*******",
"url":"oss://<bucket-name>/user_behavior/",
"delimiter":","
}'

        關於OSS外表的文法介紹，請參見OSS外表文法。

      2. 建立測試資料表。

        CREATE TABLE user_behavior(
  uid string,
  event string,
  ts string
)

      3. 將OSS外表資料匯入測試資料表。

        SUBMIT JOB INSERT OVERWRITE user_behavior SELECT user_id , event, ts FROM user_behavior_oss;

    window_funnel

    漏鬥函數（window_funnel函數）用於在滑動的時間視窗中搜尋事件列表並計算事件列表中發生的最大連續的事件數目。系統會根據您定義的事件列表，從第一個事件開始匹配，依次做有序最長的匹配，返回最大連續事件數目。具體介紹如下：

    • 定義的事件列表為c1、c2、c3，使用者資料為c1、c2、c3、c4，則函數傳回值為3。

    • 定義的事件列表為c1、c2、c3，使用者資料為c4、c3、c2、c1，則函數傳回值為1。

    • 定義的事件列表為c1、c2、c3，使用者資料為c4、c3，則函數傳回值為0。

    文法

    window_funnel(window, mode, timestamp, cond1, cond2, ..., condN)

    參數說明

    參數

    說明

    window

    滑動的時間視窗的大小。

    mode

    模式。該參數為擴充參數，目前僅支援default。

    timestamp

    時間列，資料類型需為BIGINT，否則，需使用相應的方法進行轉換。

    例如，資料類型為TIMESTAMP，需使用TIMESTAMPDIFF函數求時間戳記列和初始時間的差值：

    select uid, window_funnel(cast(1800 as integer),"default", TIMESTAMPDIFF('second','2017-11-25 00:00:00.000',ts),event = 'pv',event = 'fav',event = 'cart',event = 'buy') as funnel from user_behavior where ts > '2017-11-25 00:00:00.000' and ts < '2017-11-26 00:00:000.000'

    cond

    事件的步驟。

    樣本

    假如您希望分析2017年11月25日0點到2017年11月26日0點這段時間，使用者按照瀏覽商品、收藏商品、加入購物車到購買商品這種轉化路徑的轉化情況，SQL中的條件如下：

    • 滑動的時間視窗的大小為30分鐘，即1800秒。

    • 查詢時間範圍為2017-11-25 00:00:00（時間戳記為1511539200）至2017-11-26 00:00:00（時間戳記為1511625600）。

    • 事件的步驟為瀏覽商品>收藏商品>加入購物車>購買商品。

    SQL查詢語句如下。

    SELECT funnel, count(1) FROM (SELECT uid, window_funnel(cast(1800 as integer),"default", ts, event='pv',  event='fav', event='cart', event='buy') AS funnel FROM user_behavior WHERE ts > 1511539200 AND ts < 1511625600 group by uid) GROUP BY funnel;

    返回資訊如下。

    +--------+----------+
| funnel | count(1) |
+--------+----------+
|      0 |    19687 |
|      2 |    78458 |
|      3 |    11640 |
|      1 |   596104 |
|      4 |      746 |
+--------+----------+
5 rows in set (0.64 sec)

    retention

    留存函數（retention函數）可以將一組條件作為參數，分析事件是否滿足該條件。

    文法

    retention(cond1, cond2, ..., cond32)

    參數說明

    參數

    說明

    cond

    分析條件，類型為UInt8，最少支援1個條件，最多支援32個條件。

    如果滿足該條件，傳回值計數加1；如果不滿足該條件，傳回值計數加0。

    樣本

    假如您希望分析從2017年11月25日開始，使用者的留存情況，SQL中的條件如下：

    • 分析開始日期為2017-11-25。

    • 第一天活躍使用者數為sum(r[1])，第二到七天的使用者留存數為sum(r[2])，sum(r[3])...sum(r[7])。

    SQL查詢語句如下。

    SELECT sum(r[1]),sum(r[2]),sum(r[3]),sum(r[4]),sum(r[5]),sum(r[6]),sum(r[7]) FROM (SELECT retention(ds='2017-11-25' AND event='pv', ds='2017-11-25', ds='2017-11-26',ds='2017-11-27',ds='2017-11-28',ds='2017-11-29',ds='2017-11-30') AS r FROM user_behavior_date GROUP BY uid);

    返回資訊如下。

    +-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| sum(r[1]) | sum(r[2]) | sum(r[3]) | sum(r[4]) | sum(r[5]) | sum(r[6]) | sum(r[7]) |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|    686953 |    686953 |    544367 |    529979 |    523516 |    524530 |    528105 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
1 row in set (2.96 sec)

    retention_range_count與retention_range_sum

    留存分析函數（retention_range_count與retention_range_sum函數）可以用於使用者增長分析情境，協助繪製資料視覺效果圖形，分析使用者的留存情況。retention_range_count函數可以記錄每個使用者的留存情況（返回結果為二維數組，該結果可以作為retention_range_sum函數的輸入內容）；retention_range_sum函數可以匯總計算所有使用者每天的留存率。

    文法

    • retention_range_count函數

      retention_range_count(is_first, is_active, dt, intervals, outputFormat)

    • retention_range_sum函數

      retention_range_sum(retention_range_count_result)

    參數說明

    參數

    說明

    is_first

    是否符合初始行為。判斷條件如下：

    • true：符合初始行為。

    • false：不符合初始行為。

    is_active

    是否符合後續留存行為。判斷條件如下：

    • true：符合後續留存行為。

    • false：不符合後續留存行為。

    dt

    行為發生的日期，格式為date，例如2022-05-01。

    intervals[]

    留存間隔，最多支援15個留存間隔。

    outputFormat

    輸出格式，取值如下：

    • outputFormat=normal時，輸出格式為[[d1(起始日期), 1, 0...], [d2(起始日期), 1, 0...], [d3(起始日期), 1, 0...]], 其中1代表有留存事件，0代表沒有留存事件。

    • outputFormat=expand時，輸出格式為[[d1(起始日期),d1+1(留存日期)], [d1, d1+2], [d2, d2+1], [d2, d2+3]]。

    預設值為normal。

    樣本

    假如您有一段2022年5月1日到2022年5月4日的使用者資料，希望計算出2022年5月1日和2022年5月2日的留存情況，其中啟用事件為login，留存事件為pay。

    1. 建立測試表並插入測試資料。

      1. 建立表。

        CREATE TABLE event(uid string, event string, ds date);

      2. 插入資料。

        INSERT INTO event VALUES("user1", "pay", "2022-05-01"),("user1", "login", "2022-05-01"),("user1", "pay", "2022-05-02"),("user1", "login", "2022-05-02"),("user2", "login", "2022-05-01"),("user3", "login", "2022-05-02"),("user3", "pay", "2022-05-03"),("user3", "pay", "2022-05-04");

        測試資料如下。

        +-------+-------+------------+
| uid   | event | ds         |
+-------+-------+------------+
| user1 | login | 2022-05-01 |
| user1 | pay   | 2022-05-01 |
| user1 | login | 2022-05-02 |
| user1 | pay   | 2022-05-02 |
| user2 | login | 2022-05-01 |
| user3 | login | 2022-05-02 |
| user3 | pay   | 2022-05-03 |
| user3 | pay   | 2022-05-04 |
+-------+-------+------------+

    2. 計算每個使用者的留存情況。

      SELECT
  uid,
  r
FROM
  (
    SELECT
      uid,
      retention_range_count(event = 'login', event = 'pay', ds, array(1, 2)) AS r
    FROM
      event
    GROUP BY
      uid
  ) AS t
ORDER BY
  uid;

      返回資訊如下。

      +-------+-----------------------------+
| uid   | r                           |
+-------+-----------------------------+
| user1 | [[738642,0,0],[738641,1,0]] |
| user2 | [[738641,0,0]]              |
| user3 | [[738642,1,1]]              |
+-------+-----------------------------+

    3. 計算每天的留存率。

      SELECT
  from_days(u [1]) AS ds,
  u [3] / u [2] AS retention_d1,
  u [4] / u [2] AS retention_d2
FROM
  (
    SELECT
      retention_range_sum(r) AS r
    FROM
      (
        SELECT
          uid,
          retention_range_count(event = 'login', event = 'pay', ds, array(1, 2)) AS r
        FROM
          event
        GROUP BY
          uid
      ) AS t
    ORDER BY
      uid
  ) AS r,
  unnest(r.r) AS t(u);

      返回資訊如下。

      +------------+--------------+--------------+
| ds         | retention_d1 | retention_d2 |
+------------+--------------+--------------+
| 2022-05-02 |          0.5 |          0.5 |
| 2022-05-01 |          0.5 |          0.0 |
+------------+--------------+--------------+