如何使用漏鬥分析函數與留存函數 - AnalyticDB

在資料分析中，漏鬥分析是衡量和最佳化使用者在特定流程中的轉化率的重要工具，留存分析則用于衡量使用者對產品或服務的持續使用程度，這兩者都是衡量產品健康度和使用者滿意度的重要指標。漏鬥分析函數與留存函數是專門為了處理和分析這類資料而設計，旨在簡化查詢過程，提高效率。本文將為您介紹漏鬥分析函數和留存函數的使用方法和應用情境。

背景資訊

事件（Event）代表了使用者的某個或一系列有意義的行為，比如遊戲App的下載、註冊、登入等，通過分析使用者的各項行為資料還原使用者真實的使用過程，從而提升產品轉化率，助力業務增長。常見的使用者行為分析包括事件分析、漏鬥分析、留存分析等，其中漏鬥和留存是最常見的使用者行為分析情境：

漏鬥分析：是一種用來分析使用者在指定階段轉化情況的分析模型，可以分析使用者在各個階段的行為轉化率，然後通過轉化率來衡量每一個階段的表現，從而助力產品進行針對化的最佳化，提升轉化率。
留存分析：是一種用來分析使用者參與情況或活躍程度的分析模型，考察初始行為的使用者中有多少會進行後續行為，從而衡量產品對使用者的價值。

漏鬥分析函數

函數定義

漏鬥分析常針對一系列預定義的步驟，計算使用者從第一步到最後一步的完成率。具體的做法為搜尋滑動時間視窗內的事件列表，計算條件匹配的事件鏈裡的最大連續事件數目。該函數遵循以下規則：

從事件鏈中的第一個條件開始判斷。如果資料中包含合格事件，則向計數器加1，並以此事件對應的時間作為滑動視窗的起始時間。如果未能找到符合第一個條件的資料，則返回為0。
在滑動視窗內，如果事件鏈中的事件按順序發生，則計數器遞增。如果超出了時間視窗，則計數器不再增加。
如有多條合格事件鏈，則輸出最長的事件鏈。

函數文法

SELECT windowFunnel(time_frame, mode, time, ARRAY[Cond1,Cond2,...CondN]) 
FROM tbl 
GROUP BY uid;

參數說明

參數	說明
time_frame	滑動視窗的大小，類型為bigint。單位預設為s。
mode	事件鏈的篩選模式，類型為text。取值範圍："default", "strict"。預設值為default，表示執行一般的漏鬥計算。模式為strict時表示deduplication模式，即篩選出的事件鏈不能有重複的事件。假設array參數為[event_type='A',event_type='B',event_type='C',event='D']，原事件鏈為 "A-B-C-B-D"。由於事件B重複，那麼篩選出的事件鏈只能是 "A-B-C"。
time	包含時間戳記的列。目前支援timestamp類型。
array[Cond1,Cond2,...CondN]	定義的事件鏈，類型為array。

傳回值說明

返回bigint類型的值，值為滑動視窗內滿足條件的最大連續事件數目。

使用樣本

在進行漏鬥分析和留存分析之前，應確保事件數目據的準確性和完整性。以下為分析使用者從瀏覽網頁到下訂單事件的SQL樣本。

CREATE TABLE action
(
    uid        INT,
    event_type TEXT,
    time       TIMESTAMP
);

INSERT INTO action
VALUES (1, 'Browse', '2020-01-02 11:00:00');
INSERT INTO action
VALUES (1, 'Click', '2020-01-02 11:10:00');
INSERT INTO action
VALUES (1, 'Order', '2020-01-02 11:20:00');
INSERT INTO action
VALUES (1, 'Pay', '2020-01-02 11:30:00');
INSERT INTO action
VALUES (1, 'Browse', '2020-01-02 11:00:00');
INSERT INTO action
VALUES (1, 'Browse', '2020-01-02 11:00:00');
INSERT INTO action
VALUES (2, 'Order', '2020-01-02 11:00:00');
INSERT INTO action
VALUES (2, 'Pay', '2020-01-02 11:10:00');
INSERT INTO action
VALUES (3, 'Browse', '2020-01-02 11:20:00');
INSERT INTO action
VALUES (3, 'Click', '2020-01-02 12:00:00');
INSERT INTO action
VALUES (4, 'Browse', '2020-01-02 11:50:00');
INSERT INTO action
VALUES (4, 'Click', '2020-01-02 12:00:00');
INSERT INTO action
VALUES (5, 'Browse', '2020-01-02 11:50:00');
INSERT INTO action
VALUES (5, 'Click', '2020-01-02 12:00:00');
INSERT INTO action
VALUES (5, 'Order', '2020-01-02 11:10:00');
INSERT INTO action
VALUES (6, 'Browse', '2020-01-02 11:50:00');
INSERT INTOaction
VALUES (6, 'Click', '2020-01-02 12:00:00');
INSERT INTO action
VALUES (6, 'Order', '2020-01-02 12:10:00');

漏鬥函數可以協助您分析每個使用者對應事件視窗下所達到的事件程度，這裡指定完成一條完整的事件流程度為4。如下所示SQL語句為查詢不同使用者對應的事件程度。

查詢使用者事件

SELECT uid,
       windowFunnel(1800, 'default',
                    TIME,
                    ARRAY [event_type = 'Browse', event_type = 'Click',
                           event_type = 'Order', event_type = 'Pay']) AS level
FROM action
GROUP BY uid
ORDER BY uid

返回結果

 uid | level
-----+-------
   1 |     4
   2 |     0
   3 |     1
   4 |     2
   5 |     2
   6 |     3
(6 ROWS)

留存函數

函數定義

留存函數用於計算一段時間內的使用者留存情況。該函數接收1到31個條件，從第一個條件開始判斷事件是否滿足條件，如果滿足條件則輸出 1，不滿足則輸出0，最終返回0和1的數組。通過統計結果為1的資料，計算使用者留存率。

函數文法

留存函數的文法如下。

SELECT retention(ARRAY[Cond1,...CondN]) FROM tbl;

參數說明

參數	說明
array[Conds]	條件運算式組成的數組，類型為array，數組內最多支援傳入31個條件，多個條件用逗號隔開。

傳回值說明

返回包含0和1的數組。數組裡0和1的個數與傳入的條件數一致。從數組的第一個條件開始依次判斷：

如果事件滿足當前條件，則輸出1。
如果事件不滿足當前條件，則當前位置及之後的所有位置均為0。

使用樣本

在進行漏鬥分析和留存分析之前，應確保事件數目據的準確性和完整性。以下為建立資料集並分析使用者購買事件的SQL樣本。

CREATE TABLE retention_test
(
    id     INTEGER,
    action text,
    time   TIMESTAMP
);

INSERT INTO retention_test
VALUES (1, 'pv', '2022-01-01 08:00:05'),
       (2, 'pv', '2022-01-01 10:20:08'),
       (1, 'buy', '2022-01-02 15:30:10'),
       (2, 'pv', '2022-01-02 17:30:05'),
       (3, 'buy', '2022-01-01 05:30:09'),
       (3, 'buy', '22022-01-02 08:10:15'),
       (4, 'pv', '2022-01-02 21:09:15'),
       (5, 'pv', '2022-01-01 22:10:53'),
       (5, 'pv', '2022-01-02 19:10:52'),
       (5, 'buy', '2022-01-02 20:00:50');

以下為查詢在2022-01-01訪問產品頁面，並且在之後一天2022-01-02進行購買的使用者明細和查詢結果。

查詢使用者購買明細

SELECT id,
       retention(ARRAY [action = 'pv' AND time::date = '2022-01-01', action = 'buy' AND time::date = '2022-01-02']) AS retention
FROM retention_test
GROUP BY id
ORDER BY id;

查詢結果

 id | retention
----+-----------
  1 | {1,1}
  2 | {1,0}
  3 | {0,0}
  4 | {0,0}
  5 | {1,1}
(5 ROWS)

對使用者購買事件的統計查詢和查詢結果如下。

使用者事件統計查詢

SELECT SUM(r[1]) AS day1, SUM(r[2]) AS day2
FROM (SELECT id,
             retention(ARRAY [action = 'pv' AND time::date = '2022-01-01',
             action = 'buy' AND time::date = '2022-01-02']) AS r
      FROM retention_test
      GROUP BY id
      ORDER BY id) t;

查詢結果

 day1 | day2
------+-----
   3  |   2
(1 ROW)

留存擴充函數

函數定義

留存分析是最常見的典型使用者增長分析情境，使用者經常需要繪製資料視覺效果圖形，分析使用者的留存情況。基於該情境，AnalyticDB for PostgreSQL提供了range_retention_count和range_retention_sum兩個函數用於服務該情境。

range_retention_count：用於記錄每個使用者的留存情況，傳回值為bigint數組。可以作為range_retention_sum的輸入值。
range_retention_sum：用於統計所有使用者每天的留存率。傳回值為text數組。

函數文法

range_retention_count

range_retention_count(is_first,  
                      is_active, 
                      dt, 
                      retention_intervals, 
                      retention_granularity)

range_retention_sum

range_retention_sum(range_retention_count)

參數說明

參數	類型	說明
is_first	boolean	是否符合初始行為。 true：符合初始行為。 false：不符合初始行為。
is_active	boolean	是否符合後續留存行為。 true：符合後續留存行為。 false：不符合後續留存行為。
dt	date	發生行為的日期。如2020-12-12。
retention_interval	int[]	留存間隔，最多支援15個留存間隔。例如：array[1,3,5,7,15,30]。
retention_granularity	text	留存粒度，目前僅支援day。

range_retention_count使用樣本

以下兩個SQL樣本可以獲知range_retention_count返回的每個使用者在對應時間t,t+1,t+2的留存情況，使用樣本如下。

準備資料集

CREATE TABLE event_tbl(uid INT, event TEXT, dt DATE);
INSERT INTO event_tbl VALUES
                        (1, 'pay', '2022-05-01'),(1, 'login', '2022-05-01'),
                        (1, 'pay', '2022-05-02'),(1, 'login', '2022-05-02'),
                        (2, 'login', '2022-05-01'),
                        (3, 'login', '2022-05-02'), (3, 'pay', '2022-05-03'),
                        (3, 'pay', '2022-05-04');

計算使用者留存明細

SELECT
    uid, r
FROM
    (
        SELECT
            uid,
            range_retention_count(event = 'login', event = 'pay', dt, 
            ARRAY [1, 2], 'day') AS r
        FROM
            event_tbl
        GROUP BY
            uid
    ) AS t
ORDER BY
    uid;

查詢明細

 uid |  r
-----+-----------------------------
   1 | {"{8156,1,0}","{8157,0,0}"}
   2 | {"{8156,0,0}"}
   3 | {"{8157,1,1}"}
(3 ROWS)

查詢日期

SELECT to_date(8156)
to_date
------------
2022-05-01

range_retention_sum使用樣本

range_retention_sum以range_retention_count的結果作為輸入，使用樣本如下。

計算留存率

WITH retention_count_info AS (
    SELECT
        uid,
        range_retention_count(event = 'login', event = 'pay', 
                               dt, array[1, 2], 'day') AS info
    FROM
        event_tbl
    GROUP BY
        uid
), retention_sum AS (
         SELECT regexp_split_to_array(unnest(range_retention_sum(info)), ',') AS s
         FROM retention_count_info
     ) SELECT to_date(s[1]::int) AS login_date,
             s[3]::numeric / s[2]::numeric AS retention_d1,
             s[4]::numeric / s[2]::numeric AS retention_d2
FROM retention_sum
ORDER BY login_date;

查詢結果

 login_date | retention_d1           | retention_d2
------------+------------------------+------------------------
 2022-05-01 | 0.50000000000000000000 | 0.00000000000000000000
 2022-05-02 | 0.50000000000000000000 | 0.50000000000000000000
(2 ROWS)