如何在AnalyticDBMySQL中进行漏斗分析_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

本文介绍如何在AnalyticDB for MySQL中使用漏斗留存函数进行漏斗分析。

前提条件

集群内核版本需为3.1.6.0及以上。

说明

查看企业版或湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

背景信息

漏斗分析是常见的转化分析方法，它可以反映用户各个阶段行为的转化率。漏斗分析被广泛应用在用户行为分析和App数据分析的场景，例如流量分析、产品目标转化率等数据运营与数据分析任务。AnalyticDB for MySQL支持的漏斗留存函数如下：

window_funnel：用于在滑动的时间窗口中搜索事件列表并计算事件列表中发生的最大连续的事件数。
retention：用于分析事件是否满足指定条件。
retention_range_count与retention_range_sum：用于记录和汇总留存情况。

测试数据集

为方便您测试漏斗留存函数，本文使用了天池实验室的淘宝行为数据作为分析数据，下载链接，请参见淘宝用户购物行为数据集。

测试数据集中，用户的行为分为如下几种。

行为类型	说明
pv	商品详情页pv，相当于点击。
buy	商品购买。
cart	将商品加入购物车。
fav	收藏商品。

进行测试前，您需要将测试数据集上传至OSS中，再通过OSS导入到AnalyticDB for MySQL。

上传测试数据集至OSS，具体操作，请参见控制台上传文件。

将数据通过OSS外表导入到AnalyticDB for MySQL，操作示例如下：

创建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外表语法。

创建测试数据表。

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

将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。

创建测试表并插入测试数据。

创建表。

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

插入数据。

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 |
+-------+-------+------------+

计算每个用户的留存情况。

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]]              |
+-------+-----------------------------+

计算每天的留存率。

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 |
+------------+--------------+--------------+