漏斗分析函数 - 实时数仓Hologres

漏斗分析是常见的转化分析方法，它用于反映用户各个阶段行为的转化率。漏斗分析被广泛应用于用户行为分析和App数据分析的流量分析、产品目标转化等数据运营与数据分析。本文将为您介绍在Hologres中漏斗分析相关函数的使用。

使用限制

Hologres针对各场景的漏斗分析提供了漏斗函数（windowFunnel）、留存函数（retention）、range_retention_count和range_retention_sum四个函数，用于帮助用户进行漏斗分析。具体使用限制如下：

仅Hologres V0.9 及以上版本支持漏斗函数（windowFunnel）和留存函数（retention）函数，请前往Hologres管控台的实例详情页查看当前实例版本。
仅Hologres V0.10 及以上版本支持range_retention_count和range_retention_sum函数。如果您的实例是V0.9以下版本，请您使用自助升级或加入实时数仓Hologres交流群（钉钉群号：32314975）申请升级实例。
在使用之前，需要执行以下语句才可以通过语句调用函数。extension是DB级别的函数，一个DB只需执行一次即可。
```
create extension flow_analysis; --开启extension
```

场景说明

本文以一个简化的购买场景为例，介绍漏斗分析相关的函数用法。一般用户完整的购买流程如下：

浏览商品
收藏商品
加入购物车
购买商品

其对应的样例数据具体如下：

用户ID（user_id）	事件类型（event_type）	发生事件时间（event_time）
4913	浏览商品	2014-11-18 00:00:00
501286	浏览商品	2014-11-18 00:00:00
501286	加入购物车	2014-11-18 00:00:00
632347	浏览商品	2014-11-18 00:00:00
814199	浏览商品	2014-11-18 00:00:00
814199	收藏商品	2014-11-18 00:00:00
1259845	浏览商品	2014-11-18 00:00:00
1259845	购买商品	2014-11-18 00:00:00
1498131	浏览商品	2014-11-18 00:00:00
1498131	收藏商品	2014-11-18 00:00:00
1857066	浏览商品	2014-11-18 00:00:00
1926899	浏览商品	2014-11-18 00:00:00
3318666	浏览商品	2014-11-18 00:00:00
3324323	浏览商品	2014-11-18 00:00:00
3442818	浏览商品	2014-11-18 00:00:00

对应的建表语句如下所示：

BEGIN;
DROP TABLE IF EXISTS public.user_analysis;
CREATE TABLE IF NOT EXISTS public.user_analysis
(
    user_id INT NOT NULL ,
      event_type    TEXT ,
    event_time TIMESTAMP NOT NULL 
);
call set_table_property('user_analysis', 'distribution_key', 'user_id');
call set_table_property('user_analysis', 'segment_key', 'event_time');
call set_table_property('user_analysis', 'clustering_key', 'event_time');
call set_table_property('user_analysis', 'bitmap_columns', 'event_type');
COMMIT;

漏斗函数（windowFunnel）

函数说明
漏斗函数（windowFunnel）可以搜索滑动时间窗口中的事件列表，并计算条件匹配的事件列表的最大长度。
搜索事件列表，从第一个事件开始匹配，依次做最长、有序匹配，返回匹配的最大长度。一旦匹配失败，结束整个匹配。
假设在窗口足够大的条件下：
- 条件事件为c1，c2，c3，而用户数据为c1，c2，c3，c4，最终匹配到c1，c2，c3，函数返回值为3。
- 条件事件为c1，c2，c3，而用户数据为c4，c3，c2，c1，最终匹配到c1，函数返回值为1。
- 条件事件为c1，c2，c3，而用户数据为c4，c3，最终没有匹配到事件，函数返回值为0。

函数语法

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

参数说明

参数	说明
window	窗口大小，即从指定的第一个事件开始，往后推移一个窗口来提取相关事件数据。
mode	模式。支持default和strict两种模式，默认为default。当设置strict时，`windowFunnel()`仅对唯一值应用匹配条件。
timestamp	包含时间的列，支持timestamp、int、bigint类型。
cond	事件的每个步骤。

使用示例

如果您希望分析一段时间内，用户按照固定转化路径的转化漏斗情况，可以参照如下SQL进行分析，SQL中的各个条件如下：

统计间隔：30分钟（即1800秒）
统计时间段：2014-11-25 00:00:00至2014-11-26 00:00:00
转化路径：浏览商品>收藏商品>加入购物车>购买商品

WITH
    level_detail AS ( 
        SELECT 
            level
            ,COUNT(1) AS count_user 
        FROM ( 
            SELECT 
                user_id 
                ,windowFunnel( 
                    1800
                    ,'default'
                    ,event_time
                    ,event_type = '浏览商品'
                    ,event_type = '收藏商品'
                    ,event_type = '加入购物车'
                    ,event_type = '购买商品' 
                    ) AS level 
            FROM public.user_analysis
            WHERE event_time >= TIMESTAMP '2014-11-25 00:00:00'
                AND event_time < TIMESTAMP '2014-11-26 00:00:00'
            GROUP BY user_id 
            ) AS basic_table 
        GROUP BY level 
        ORDER BY level ASC )
SELECT  CASE level    WHEN 0 THEN '用户总量'
                      WHEN 1 THEN '浏览商品'
                      WHEN 2 THEN '收藏商品'
                      WHEN 3 THEN '加入购物车'
                      WHEN 4 THEN '购买商品' 
              END
        ,SUM(count_user) over ( ORDER BY level DESC )
FROM    level_detail
GROUP BY level
         ,count_user
ORDER BY level ASC
;

显示结果如下所示：

    case    | sum  
------------+------
 用户总量   | 6351
 浏览商品   | 6346
 收藏商品   |  997
 加入购物车 |   88
 购买商品   |    9
(5 rows)

留存函数（retention）

函数说明
该函数将一组条件作为参数，类型为1到32个UInt8类型的参数，用来表示事件是否满足特定条件。
函数语法
```
retention(cond1, cond2, ..., cond32);
```
参数说明
参数
说明
cond
返回结果的表达式。返回值包括：
1，条件满足。
0，条件不满足。

使用示例

如果您希望分析从某一天开始，用户的留存情况，可以使用如下SQL进行分析，SQL中的场景如下：

开始分析日期是2014年11月25日。
分别统计了第一天活跃数、次日留存、3日留存和7日留存。

SELECT
    DATE '2014-11-25 00:00:00' AS "访问日期",
    SUM(r[1])::NUMERIC AS "第1天活跃用户",
    SUM(r[2])::NUMERIC/SUM(r[1])::NUMERIC AS "次日留存",
    SUM(r[3])::NUMERIC/SUM(r[1])::NUMERIC AS "3日留存",
    SUM(r[4])::NUMERIC/SUM(r[1])::NUMERIC AS "7日留存"
FROM
-- 计算2014-11-25活跃用户在第2、3、7日的登录情况。r数组表示每天登录情况，1表示登录，0 表示未登录。
    (
        SELECT
            identitycode,
            retention(
                DATE(ts) =  DATE(TIMESTAMP '2014-11-25 00:00:00') ,
                DATE(ts) =  DATE(TIMESTAMP '2014-11-25 00:00:00' + INTERVAL '1 day'),
                DATE(ts) =  DATE(TIMESTAMP '2014-11-25 00:00:00' + INTERVAL '2 day'),
                DATE(ts) =  DATE(TIMESTAMP '2014-11-25 00:00:00' + INTERVAL '6 day') 
                ) AS r
-- 过滤2014-11-25活跃用户在后续1～7日登录数据
            FROM    public.service_log_test
            WHERE   (ts >= TIMESTAMP '2014-11-25 00:00:00')
            AND     (ts <= TIMESTAMP '2014-11-25 00:00:00' + INTERVAL '6 day')
            GROUP BY identitycode
        ) AS basic_table
GROUP BY "访问日期"
;

显示结果如下所示：

  访问日期  | 第1天活跃用户 | 次日留存 | 3日留存  | 7日留存  
------------+---------------+----------+----------+----------
 2014-11-25 |          6351 | 0.796410 | 0.769013 | 0.088647
(1 row)

留存场景扩展函数

函数说明
留存分析是最常见的典型用户增长分析场景，用户经常需要绘制数据可视化图形，分析用户的留存情况。基于该场景，Hologres构造了range_retention_count和range_retention_sum两个函数用于服务该场景。range_retention_count返回值为bigint数组。output_format='normal'不支持直接读取，但可以作为range_retention_sum的输入，range_retention_sum返回值为text数组，示例如下：
```
 {
  "20210306,562574,413024,343126,291178,215240,0,0,0,0,0,0,0,0,0,0,0", 
  "20210309,502617,376019,332303,273917,208421,0,0,0,0,0,0,0,0,0,0,0",
   "20210305,550118,412264,357542,297458,216549,0,0,0,0,0,0,0,0,0,0,0",
   "20210307,560659,402810,344581,291851,214705,0,0,0,0,0,0,0,0,0,0,0",
   "20210308,527392,383356,346084,282764,212309,0,0,0,0,0,0,0,0,0,0,0"
 }
```
当output_format= 'expand'时，range_retention_count可单独使用，可以直接读取明细，示例如下：
```
{
  7038619894114387000,
  7038619894114560000
}
```
数组中的数字高32位代表初始日期，低32位代表留存日期，日期表达方式是距离1970-01-01的秒数，您可以进行位运算取得这两个数字。

函数语法

-- Hologres V0.10及以上版本支持
range_retention_count(is_first,
                      is_active,
                      dt,
                      retention_interval,
                      retention_granularity)
-- Hologres V1.1及以上版本支持，如不支持请升级至最新版本
range_retention_count(is_first,
                      is_active,
                      dt,
                      retention_interval,
                      retention_granularity,
                      output_format)

参数说明

参数	类型	说明
is_first	bool	是否符合初始行为。 true：符合初始行为。 false：不符合初始行为。
is_active	bool	是否符合后续留存行为。 true：符合后续留存行为。 false：不符合后续留存行为。
dt	date	发生行为日期。如2020-12-12
retention_interval	int[]	留存间隔，最多支持15个留存间隔。如ARRAY[1,3,5,7,15,30]
retention_granularity	text	留存粒度，支持如下三种： day week month
output_format	text	输出格式，支持如下两种： normal（默认） expand 说明 Hologres V1.1.38及以上版本支持此参数。 expand可取得每日留存明细。

使用示例

如果您需要分析一段时间内用户的留存情况，且希望将浏览商品作为用户的初始行为，您可以使用如下SQL：

WITH tbl_detail AS (
        SELECT range_retention_count(is_first, is_active, dt, ARRAY[1, 3, 7],'day' ) AS detail
        FROM (
            SELECT user_id, event_time::DATE AS dt
                , CASE 
                    WHEN 
                    event_time >= timestamp '2014-11-25 00:00:00'
                    AND event_time < timestamp '2014-11-25 00:00:00' + INTERVAL '7' day
                    and event_type = '浏览商品'  --将浏览作为初始行为
                    THEN true
                    ELSE false
                END AS is_first
                , CASE 
                    WHEN event_time >= timestamp '2014-11-25 00:00:00' + INTERVAL '1' day
                    AND event_time < timestamp '2014-11-25 00:00:00' + INTERVAL '7' day + INTERVAL '7' day   --此处为从'2014-11-25 00:00:00'开始的第7天的7日留存，INTERVAL需要加2次
                    THEN true
                    ELSE false
                END AS is_active
            FROM public.user_analysis
        ) tbl
        GROUP BY user_id
    ),
    tbl_sum AS (
        SELECT regexp_split_to_array(unnest(range_retention_sum(detail)), ',') AS s
        FROM tbl_detail
    )
SELECT s[1] AS 访问日期
        ,s[3]::numeric / s[2]::numeric AS 第1天
        ,s[4]::numeric / s[2]::numeric AS 第3天
        ,s[5]::numeric / s[2]::numeric AS 第7天
FROM tbl_sum
ORDER BY s[1];

显示结果如下所示：

  访问日期|    第1天     |    第3天     |    第7天     
----------+--------------+--------------+--------------
 20141125 | 0.7962496060 | 0.7533879609 | 0.7666246454
 20141126 | 0.7904926806 | 0.7547615299 | 0.7684558476
 20141127 | 0.7711184521 | 0.7651407896 | 0.7564889098
 20141128 | 0.7799514955 | 0.7801131770 | 0.7414713015
 20141129 | 0.7946945337 | 0.7770096463 | 0.7500000000
 20141130 | 0.7944496707 | 0.7754782063 | 0.7483537158
 20141201 | 0.8017728870 | 0.7780834479 | 0.7545468439
(7 rows)

如果您需要分析从2021年2月1日开始至2021年2月7日的每日的1日留存用户、3日留存用户和7日留存用户留存在15日的付费次数，且希望将浏览商品作为用户的初一段时间内用户的留存付费情况，且将浏览作为统计留存率的初始行为，即可使用如下SQL：

WITH pay_table AS (
        SELECT event_date, user_id
        FROM retention_demo
        WHERE event_date BETWEEN '2021-02-01' AND '2021-02-15'
            AND event_type = '付款'
    )
SELECT tbl3.init_dt, tbl3.retention_1, tbl3.retention_3, tbl3.retention_7
FROM (
    SELECT to_timestamp(tbl2.init_ds * 86400)::date AS init_dt, COUNT(CASE
            WHEN tbl2.ds_reten - tbl2.init_ds = 1 THEN 1
            ELSE NULL
        END) AS retention_1, COUNT(CASE
            WHEN tbl2.ds_reten - tbl2.init_ds = 3 THEN 1
            ELSE NULL
        END) AS retention_3
        , COUNT(CASE
            WHEN tbl2.ds_reten - tbl2.init_ds = 7 THEN 1
            ELSE NULL
        END) AS retention_7
    FROM (
        SELECT user_id, unnest(r) >> 32 AS init_ds
            , unnest(r) & 4294967295 AS ds_reten
        FROM (
            SELECT user_id
                , range_retention_count(event_date >= '2021-02-01'     AND event_date < '2021-02-08' AND "event_type" = '浏览',
          event_date >= date '2021-02-01' + INTERVAL '1' day AND event_date < date '2021-02-08' + INTERVAL '7' day,
                    event_date, ARRAY[1, 3, 7], 'day', 'expand') AS r
            FROM retention_demo
            WHERE event_date BETWEEN '2021-02-01' AND '2021-02-15'
            GROUP BY user_id
        ) tbl1
    ) tbl2
        JOIN pay_table
        ON tbl2.user_id = pay_table.user_id
            AND to_timestamp(tbl2.ds_reten * 86400)::date = pay_table.event_date::date
    GROUP BY tbl2.init_ds
) tbl3;

显示结果如下所示。

  init_dt   | retention_1 | retention_3 | retention_7
------------+-------------+-------------+-------------
 2021-02-05 |           1 |           0 |           1
 2021-02-03 |           0 |           0 |           1
 2021-02-01 |           3 |           0 |           1
 2021-02-06 |           1 |           0 |           1
 2021-02-07 |           0 |           1 |           1
(5 rows)