漏斗分析函数

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

使用限制

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

• 仅DWS9.0.1版本及以上支持漏斗函数（windowFunnel）和留存函数（retention）函数，range_retention_count和range_retention_sum。

场景说明

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

• 浏览商品

• 点击商品

• 加入购物车

• 支付货款

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

CREATE TABLE IF NOT EXISTS windowFunnel_setuptb1
(
    user_id INT , --聚合键
    event_type text, --事件类型
    event_time timestamp not null ,--事件发生时间,
    Offerings text,
    userlevel int,
    Carrier varchar    
) with (orientation = column)
distribute by hash(user_id);

插入数据

INSERT INTO windowFunnel_setuptb1 VALUES
(1,'浏览页面','2021-01-31 11:05:00','iphone',1,'淘宝'),
(1,'点击商品','2021-01-31 11:10:00','iphone',1,'淘宝'),
(1,'加入购物车','2021-01-31 11:20:00','iphone',1,'淘宝'),
(1,'支付货款','2021-01-31 11:30:00','iphone',1,'淘宝'),
(1,'点击商品','2021-01-31 11:20:00','iphone',1,'京东'),
(1,'加入购物车','2021-01-31 11:20:00','iphone',1,'京东'),
(1,'支付货款','2021-01-31 11:30:00','iphone',1,'淘宝'),
(1,'浏览页面','2021-01-31 11:41:00','water',1,'拼多多'),
(1,'点击商品','2021-01-31 11:43:00','water',1,'拼多多'),
(1,'加入购物车','2021-01-31 12:00:00','water',1,'淘宝'),
(1,'浏览页面','2021-01-31 11:10:00','book',1,'京东'),
(1,'浏览页面','2021-01-31 11:20:00','book',1,'京东'),
(1,'点击商品','2021-01-31 11:23:00','book',1,'京东'),
(2,'加入购物车','2021-01-31 11:00:00','flowers',0,'淘宝'),
(2,'支付货款','2021-01-31 11:10:00','flowers',0,'淘宝'),
(2,'浏览页面','2021-01-31 11:30:00','flowers',0,'淘宝'),
(2,'加入购物车','2021-01-31 11:40:00','flowers',0,'淘宝'),
(1,'浏览页面','2021-01-31 11:28:00','flowers',1,'淘宝'),
(3,'浏览页面','2021-01-31 11:20:00','zhi',0,'淘宝'),
(3,'点击商品','2021-01-31 12:00:00','zhi',0,'淘宝'),
(4,'浏览页面','2021-01-31 11:20:00','book',3,'京东'),
(4,'点击商品','2021-01-31 11:21:00','book',3,'京东'),
(4,'浏览页面','2021-01-31 12:20:00','book',3,'京东'),
(4,'加入购物车','2021-01-31 12:21:00','postgres',3,'京东'),
(4,'加入购物车','2021-01-31 12:21:00','book',3,'京东'),
(5,'加入购物车','2021-01-31 11:10:00','book',0,'京东'),
(5,'浏览页面','2021-01-31 11:50:00','book',0,'京东'),
(5,'点击商品','2021-01-31 11:53:00','book',0,'京东'),
(5,'加入购物车','2021-01-31 12:00:00','book',0,'京东'),
(6,'浏览页面','2021-01-31 11:50:00','ayqie',0,'vmall'),
(6,'点击商品','2021-01-31 12:00:00','ayqie',0,'vmall'),
(6,'加入购物车','2021-01-31 12:10:00','ayqie',0,'vmall'),
(7,'浏览页面','2021-01-31 11:50:00','ayqie',0,'vmall');
INSERT INTO windowFunnel_setuptb1 VALUES
(generate_series(8,10),'浏览页面','2021-01-31 11:20:00','cat',0,'vmall');
INSERT INTO windowFunnel_setuptb1 VALUES
(generate_series(11,20),'点击商品','2021-01-31 11:30:00','dog',0,'vmall');

漏斗函数

• 函数说明
  漏斗函数（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一种模式 ，默认为default
  timestamp	包含时间的列，支持timestamp、int、bigint类型。
  cond	事件的每个步骤，最多32个

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

  • 统计间隔：30分钟（即1800秒）

  • 统计时间段：2021-01-31 11:00:00至2021-01-31 12: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 windowFunnel_setuptb1
              WHERE event_time >= TIMESTAMP '2021-01-31 11:00:00'
                  AND event_time <= TIMESTAMP '2021-01-31 12:20: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
  ;
  

  显示结果如下所示：

然后可以根据结果来绘制漏斗图

留存函数（retention）

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

• 函数语法

  retention(cond1, cond2, ..., cond32);
  

• 参数说明

  参数	说明
  cond	返回结果的表达式。返回值包括：1，条件满足。0，条件不满足。

• 使用示例

  WITH
      r_detail AS ( 
          SELECT 
              r
              ,user_id 
          FROM ( 
              SELECT 
                  user_id 
                  ,retention( 
                  date(event_time) = '2021-01-31', date(event_time) = '2021-02-01', date(event_time) = '2021-02-02',date(event_time) = '2021-02-03', date(event_time) = '2021-02-04'
                      ) AS r 
              FROM retention_setuptb1
              WHERE event_time >= TIMESTAMP '2021-01-31 11:00:00'
                  AND event_time <= TIMESTAMP '2021-02-06 11:30:00'
              GROUP BY user_id 
              ) AS basic_table )
  SELECT DATE '2021-01-31 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 r_detail
  ORDER BY "访问日期"
  ;
  

  显示结果如下

  访问日期	第1天活跃用户	次日留存	3日留存	7日留存
  2021-01-31 00:00:00	18	.22222222222222222222	.16666666666666666667	.05555555555555555556