如何使用漏斗分析函数与留存函数_云原生数据仓库AnalyticDB MySQL版(AnalyticDB for MySQL)-阿里云帮助中心

在数据分析中，漏斗分析是衡量和优化用户在特定流程中的转化率的重要工具，留存分析则用于衡量用户对产品或服务的持续使用程度，这两者都是衡量产品健康度和用户满意度的重要指标。漏斗分析函数与留存函数是专门为了处理和分析这类数据而设计，旨在简化查询过程，提高效率。本文将为您介绍漏斗分析函数和留存函数的使用方法和应用场景。

背景信息

事件（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 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)