使用DLA轻松实现漏斗数据分析

2022-02-01 19:00:34

概述

漏斗分析是帮助运营人员分析一个多步骤过程中每一步的转化与流失情况。
假设我们在购买商品的过程中，需要触发的事件包括 “启动”，“登陆”，“搜索商品”，“查看商品”，“生成订单”等。
运营人员需要分析某段时间内（比如2017年1月5号到2017年2月5号），在全部用户中依次有序触发 “登陆” —> “搜索商品” —> “查看商品” —> “生成订单“ 事件的人群的转化流失情况，即计算全部用户中触发了“登陆”事件的总人数A，A中触发“搜索商品”事件的总人数B，B中触发“查看商品”事件的总人数C，以及C中触发“生成订单”事件的总人数D。展现形式如下：
同时，漏斗分析中包含“时间窗口”的概念，即需要保证所有事件在同一个窗口期内发生。比如时间窗口为1天，用户001触发“搜索商品”事件的时间和触发“登陆”事件的时间间隔在一天内，“搜索商品”事件才有效，否则视为无效。同理，用户001触发“查看商品”事件的时间和触发“登陆”事件的时间间隔也必须在一天内。时间窗口可以为1天、3天、7天或者1小时、6小时等任意长时间段。

最后，在漏斗分析中，可以设置事件属性。比如“搜索商品”事件，可以设置只计算“搜索商品”事件的属性中“content”字段为“computer”的用户。具体见详细数据。

数据模型描述

本文以如下数据示例来进行漏斗分析函数的使用说明，数据为文本文件格式，具体包含字段有：
（1）用户ID，字符串类型
（2）时间戳，毫秒级别，Long类型
（3）事件ID，Int类型，包含10001到10010十个事件
（4）事件名称，字符串类型，包含启动、登陆、搜索商品等十个事件
（5）事件属性，Json串格式
（6）日期，字符串类型
字段之间用以Tab（'t'）进行分割，数据总条数6亿左右，日期范围：2017/01/01到2017/02/28。Demo数据：

第一步：数据准备

DLA支持多种数据源融合查询分析，您的原始数据可以在数据库中，也可以是日志数据文件，上传到OSS中。本文以上述日志数据文件为例，假设传到OSS中，数据文件路径目录为：

oss://your_data_bucket/funnel_data/

第二步：DLA建表映射数据目录

首先创建schema，如果已经创建过schema，可跳过此步骤：

CREATE DATABASE `funnel_test_schema`
WITH DBPROPERTIES (
    catalog = 'oss',
    location = 'oss://your_data_bucket/'
)
COMMENT ''

建表：

CREATE EXTERNAL TABLE IF NOT EXISTS funnel_test ( 
     user_id bigint NOT NULL COMMENT '',
     event_time bigint NOT NULL COMMENT '',
     event_id int NOT NULL COMMENT '',
     event_name varchar NOT NULL COMMENT '',
     event_attr varchar NOT NULL COMMENT '',
     event_date date NOT NULL COMMENT ''
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE 
LOCATION 'oss://your_data_bucket/funnel_data/';

第三步：使用漏斗分析函数进行分析

假设，定义转化路径：
“启动” —> “登陆” —> “搜索商品” —> “浏览商品” —> “生成订单”
对应的事件ID为：
10001 —> 10002 —> 10003 —> 10004 —> 10007

如下示例，使用funnel_count函数，查询1月1号到20号20天, 时间窗口为7天, 事件个数为5个的漏斗，同时能够保存路径各节点的人群。注意：第二个参数的时间窗口的单位是毫秒级别。

SELECT user_id,
       funnel_count(event_time,
                    7 * 86400000,
                    event_id,
                    '10001,10002,10003,10004,10007') AS xwho_state
FROM funnel_test
WHERE event_id IN (10001, 10002, 10003, 10004, 10007)
  AND event_date BETWEEN '2017-01-01' AND '2019-01-20'
GROUP BY user_id;

再上述funnel_count函数的基础上，再使用funnel_sum函数，可以得出定义的转化路径的总体转化率：

SELECT funnel_sum(xwho_state, 5)
FROM (
  SELECT user_id,
         funnel_count(event_time,
                      7 * 86400000,
                      event_id,
                      '10001,10002,10003,10004,10007') AS xwho_state
  FROM funnel_test
  WHERE event_id IN (10001, 10002, 10003, 10004, 10007)
    AND event_date BETWEEN '2017-01-01' AND '2019-01-20'
  GROUP BY user_id 
);


-->
[4000000, 3999999, 3999864, 3989623, 3584579]

如果需要对事件对应的json属性列event_attr的某个属性进行过滤，如下，对10004类事件中price属性在3500到5000之间数据进行过滤，对应的Sample SQL为：

SELECT funnel_sum(xwho_state, 5)
FROM (
  SELECT user_id,
         funnel_count(event_time,
                      7 * 86400000,
                      event_id,
                      '10001,10002,10003,10004,10007') AS xwho_state
  FROM funnel_test
  WHERE event_id IN (10001, 10002, 10003, 10004, 10007)
    AND event_date BETWEEN '2017-01-01' AND '2019-01-20'
     OR (
              event_id = 10004 AND
              json_extract_scalar(event_attr, '$.price') BETWEEN 3500 AND 5000
        )
  GROUP BY user_id 
);

上述路径和事件都是通过id进行表征的，实际场景中，可能只有事件的名称（字符串），而没有事件对应的id，目前DLA也支持直接使用事件名称（字符串，比如本例中的event_name列）进行路径事件的表征，如：

SELECT funnel_sum(xwho_state, 7) AS funnel
FROM (SELECT user_id,
             funnel_count(event_time,
                          7 * 86400000,
                          event_name,
                          '启动,登陆,搜索商品,浏览商品,生成订单,订单付款,评价商品') 
             AS xwho_state
      FROM funnel_test
      WHERE event_name 
         IN ('启动', '登陆', '搜索商品', '浏览商品', '生成订单', '订单付款', '评价商品')
      GROUP BY user_id
     );

说明

关于漏斗问题和数据模型描述，部分内容转自：https://www.jianshu.com/p/2ffb4b6b54f5

码农公寓

概述

数据模型描述

第一步：数据准备

第二步：DLA建表映射数据目录

第三步：使用漏斗分析函数进行分析

说明

相关文章