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

使用限制

Hologres针对各场景的漏斗分析提供了漏斗函数(windowFunnel)留存函数(retention)range_retention_countrange_retention_sum四个函数,用于帮助用户进行漏斗分析。具体使用限制如下:
  • 仅Hologres V0.9 及以上版本支持漏斗函数(windowFunnel)和留存函数(retention)函数,请前往Hologres管控台的实例详情页查看当前实例版本。
  • 仅Hologres V0.10 及以上版本支持range_retention_count和range_retention_sum函数。如果您的实例是V0.9以下版本,请您提交工单升级实例。
  • 在使用之前,需要执行以下语句才可以通过语句调用函数。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)
    如果您使用HoloWeb执行查询,即可直接得到一张漏斗图。漏斗图

留存函数(retention)

  • 函数说明

    该函数将一组条件作为参数,类型为1到32个UInt8类型的参数,用来表示事件是否满足特定条件。

  • 函数语法
    retention(cond1, cond2, ..., cond32);
  • 参数说明
    参数 说明
    cond 返回结果的表达式。返回值包括:
    • 1,条件满足。
    • 0,条件不满足。
  • 使用示例
    如果您希望分析从某一天开始,用户的留存情况,可以使用如下SQL进行分析,SQL中的场景如下:
    • 开始分析日期是2014年11月25日。
    • 分别统计了第一天活跃数、次日留存、3日留存和7日留存。
    -- 根据计算数组中SUM(r[index])获取2014-11-25活跃用户在第2、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 表示未登录。
        (
            WITH 
                first_day_table AS ( SELECT TIMESTAMP '2014-11-25 00:00:00' AS first_day)
            SELECT
                user_id,
                retention(
                    DATE(event_time) = (SELECT DATE(first_day) FROM first_day_table),
                    DATE(event_time) = (SELECT DATE(first_day + INTERVAL '1 day') FROM first_day_table),
                    DATE(event_time) = (SELECT DATE(first_day + INTERVAL '2 day') FROM first_day_table),
                    DATE(event_time) = (SELECT DATE(first_day + INTERVAL '6 day') FROM first_day_table)
                    ) AS r
    -- 过滤2014-11-25活跃用户在后续1~7日登录数据
                FROM    public.user_analysis
                WHERE   (event_time >= TIMESTAMP '2014-11-25 00:00:00')
                AND     (event_time <= TIMESTAMP '2014-11-25 00:00:00' + INTERVAL '6 day')
                GROUP BY user_id
            ) AS basic_table
    GROUP BY "访问日期"
    ;
    显示结果如下所示:
      访问日期  | 第1天活跃用户 | 次日留存 | 3日留存  | 7日留存  
    ------------+---------------+----------+----------+----------
     2014-11-25 |          6351 | 0.796410 | 0.769013 | 0.088647
    (1 row)

留存场景扩展函数

  • 函数说明
    留存分析是最常见的典型用户增长分析场景,用户经常需要绘制数据可视化图形,分析用户的留存情况。可视化图形基于该场景,Hologres构造了range_retention_countrange_retention_sum两个函数用于服务该场景。range_retention_count返回值为bigint数组,不支持直接读取,但可以作为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"
     }
  • 函数语法
    range_retention_count(is_first,is_active,dt,retention_interval,retention_granularity)
  • 参数说明
    参数 类型 说明
    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
  • 使用示例
    如果您需要分析一段时间内用户的留存情况,且希望将浏览商品作为用户的初始行为,您可以使用如下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)