漏斗分析函数--ByteHouse 企业版-火山引擎

文档中心

立即注册

导航

漏斗分析函数

最近更新时间：2025.06.06 15:23:50首次发布时间：2024.06.28 11:59:38

本文介绍漏斗分析函数的基础概念和函数的基本用法。

功能概述

漏斗分析函数适用于观察选定时间范围内每一个时间单位（天）内用户按一定时间范围划分的漏斗分层汇总情况。

漏斗计算逻辑简介：

先使用 finderFunnel 计算每个用户在一段时间窗口内的表现；
再使用 funnelRep 聚合 finderFunnel 的结果，形成漏斗每一层所有用户的汇总结果。

函数示意

finderFunnel()

finderFunnel(window, start_timestamp, check_granularity, watch_numbers, relatedNum, window_type, time_zone, time_interval)(server_time,client_timestamp, [param1, param2, ..., ]
check_event1, check_event2...)

参数说明

参数	参数说明
window	分析观察的窗口总时长时长，单位和`client_timestamp` 一致，下例中为 86400 *7 秒，也即 7 天。
start_timestamp	分析开始时间戳，如分析从 2022-06-01 开始，所以开始时间戳为 `toUInt64(toUnixTimestamp('2022-06-01')) = 1654012800`。
check_granularity	一个观察步长持续时间，多大粒度内来计算转化分析，单位和`client_timestamp` 一致，如 1 天则为 86400（单位：秒）。
watch_numbers	观察几个步长，如`check_granularity`是86400（1 天），`watch_numbners = 7`，指代从`start_timestamp`开始观察 7 天每天的漏斗情况。
relatedNum	属性关联相关参数，未使用该功能时设置为 0 即可。
window_type	相对窗口类型，定义是否计算当天的转化，设置 `window_type=1` 表示计算当天的转化。
time_zone	指定时区，如 'Asia/Shanghai'。
time_interval	是否开启时间间隔，当 `time_interval` 值为 1 时，开启时间间隔开关，返回结果中将额外包含对应事件的 `client_time` 序列。当开启时间间隔时，需要添加 `relatedNum`、`window_type`、`time_zone` 参数进行占位。
server_timestamp	事件发生服务器时间戳列，需转化为 UInt64 类型，用于函数运行时，计算事件所属时间槽位/步进。
client_timestamp	事件发生客户端时间戳列，需转化为 UInt64 类型，用于函数运行时对数据进行排序。
[param1, param2, ..., ]	属性关联相关参数，未使用该功能时不设置即可。
check_event	计算转化的事件列表，在 `window` 定义的分析时长范围内满足条件的将会被认为事件触发有效。如有 3 个事件，则输入：EventName = 'E0001', EventName = 'E0002', EventName = 'E0003'。

funnelRep()

使用 funnelRep 聚合 finderFunnel 的结果，形成漏斗每一层所有用户的汇总结果。

funnelRep(number_steps, evnet_count)(funnel_res)

参数说明

参数	参数说明
number_steps	UInt 型数字，包含的时间槽位数量，通常和 `finderFunnel`函数中`watch_numners`保持一致。
evnet_count	UInt 型数字，表示事件转化链上事件总数，通常和`finderFunnel`函数中`check_event`的时间数量保持一致。
funnel_res	转化步骤列表，由 `finderFunnel` 子查询产生，包含每一个用户产生的所有转化步骤列表。

返回结果说明
返回 2 维度数组，输出的二维数组中，总计有 1 个汇总的结果 + 与number_steps 个子数组，第一个子数组是汇总的结果，第二个子数组是第一个时间槽在window定义的分析时长范围内的漏斗计算结果，以此类推。

funnelRep2()

funnelRep2() 函数在 funnelRep() 函数的基础上增加了 targe_step 和 taget_interval_group 参数，聚合 finderFunnel 的结果，针对每一步间隔的明细数据做处理。

funnelRep2(number_steps, evnet_count, targe_step, taget_interval_group)(funnel_res.1, funnel_res.2)

参数说明

参数	参数说明
number_check event_number funnel_res.1	与 funnelRep 保持一致。
targe_step	设置目标步骤，取值范围： [0, 总步骤数-1]。	当传入 i 时，表示统计第 i 层（第 i 到 i+1 步）的时间间隔。当传入 0 时，表示统计从第一步起到最后一步的时间间隔。
taget_interval_group	时间间隔分布序列，类型： list，需要显式传入起始阙值。	例如，当传入[0, 1000, 2000, 3000]，表示期望的时间间隔的分布区间为 [0, 1000), [1000, 2000), [2000, 3000]。
funnel_res.2	funnel 返回的时间间隔列。

返回结果说明

(
// 某层转化的转化人数分布区间，二维数组，outer(inner)
// outer[0] 为汇总值，outer[i] 表示第i个观测单元
// inner[i] 为该层转化的转化间隔落在（i+1）个区间的人数
[ [500, 300, 200], [200, 200, 100], [xxx, xxx, xxx], [xxx, xxx, xxx] ],
// 算术统计分布区间，二维数组，outer(inner)
// outer[0] 为汇总转化间隔的算术统计，outer[i] 为第 i 个区间观测单元 的算术统计
[ [avg, max, min, mid, 75-quantile, 25-quantile], [...], [...], [...] ]
)

漏斗分析示例

示例模型

如下示例用表是由用户行为事实表 Log 和用户维度表 User 组成常用的星型模型，后文将基于此表演示漏斗分析函数的具体用法。

CREATE TABLE -- 事件日志表
  default.LOG (
    `Visitor` UInt32,
    `EventName` String,
    `EventTime` DateTime,
    `Province` String,
    `City` String
  ) ENGINE = Distributed('your_cluster', 'default', 'LOG_Example_local')
  
  CREATE TABLE -- 用户表
  default.User (
    `Visitor` UInt32,
    `Fvisit_Time` DateTime,
    `Fvisit_Province` String,
    `Fvisit_City` String
  ) ENGINE = Distributed('your_cluster', 'default', 'User_Example_local')
  
  -- 仅展示 Distributed 表 Schema，local 表略

基础转化模型

基础转化模型使用 finderFunnel() 和 funnelRep() 统计每一步事件的转化情况。
以下示例指代返回从 2022/6/1-2022/6/8 的 7 天中， 'E0001', 'E0002', 'E0003' 3 个事件的转化漏斗：

SELECT funnelRep(7, 3)(funnel_res)
FROM
(
    SELECT finderFunnel(7 * 86400, 1654012800, 86400, 7)(toUInt64(toUnixTimestamp(LOG.EventTime)), toUInt64(toUnixTimestamp(LOG.EventTime)), LOG.EventName = 'E0001', LOG.EventName = 'E0002', LOG.EventName = 'E0003') AS funnel_res
    FROM LOG
    LEFT JOIN
    (
        SELECT *
        FROM User AS t
    ) AS U ON LOG.Visitor = U.Visitor
    WHERE (EventTime >= '2022-06-01 00:00:00') AND (EventTime <'2022-06-08 00:00:00') AND (EventName IN ('E0001', 'E0002', 'E0003'))
    GROUP BY Visitor
)
SETTINGS distributed_product_mode = 'local', distributed_group_by_no_merge = 1

上面的示例 SQL 返回为

┌─funnelRep(7, 3)(funnel_res)─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [[5994501,5944849,56423],[3791527,3780488,39431],[3792876,3767458,33024],[3792031,3733089,26709],[3791519,3656585,20668],[3791859,3480158,14385],[3793389,3077233,8691],[3792344,2143108,3641]] │

即整体漏斗（E0001->E0002->E0003）为：[5994501,5944849,56423]
第 1 天的漏斗为：[3791527,3780488,39431]
...
第 7 天的漏斗：[3792344,2143108,3641]

统计转化间隔

以下示例使用 funnelRep2() 函数观察 6 天的数据及两个转化事件，并统计每一步之间的转化间隔。

SELECT funnelRep2(6, 2, 0, [0, 60000, 120000, 180000, 240000, 300000, 360000, 420000, 480000, 540000, 600000])(funnel_res.1, funnel_res.2) AS col
FROM
(
    SELECT finderFunnel(600000, 1684512000, 86400, 6, 0, 0, 'Asia/Shanghai', 1)(toUInt64(toUnixTimestamp(LOG.EventTime)), toUInt64(toUnixTimestamp(LOG.EventTime)), LOG.EventName = 'E0001', LOG.EventName = 'E0002') AS funnel_res
    FROM LOG
    WHERE (EventTime >= '2023-05-20 00:00:00') AND (EventTime <'2023-05-26 00:00:00') AND (EventName IN ('E0001', 'E0002'))
    GROUP BY Visitor
)

6：表示共观察 6 天。
2：表示两个转化事件。
0：需要统计每一步之间的转化间隔。
[0, 60000, 120000, 180000, 240000, 300000, 360000, 420000, 480000, 540000, 600000]：表示分别以时间间隔 [0, 60000), [60000, 120000), ..., [540000, 600000] 来划分区间，共十个区间。

输出结果

(
 [
 [1845,100,45,24,16,14,17,11,12,32], // 表示每个区间中转化人数的总汇总值
 [370,19,12,3,4,4,2,1,5,5], // 表示第一天落在各个区间中的转化人数的汇总值
 [87,6,3,1,1,0,2,0,2,4], // 表示第二天落在各个区间中的转化人数的汇总值
 [99,4,1,2,0,0,2,0,6], // 表示第三天...依此类推
 [442,25,10,5,2,1,4,1,2,7],
 [437,25,8,6,3,6,3,1,1,6],
 [410,21,11,7,6,3,6,62,4]
 ],
 [
 [38240.619092627596,596767,2,4829.31494140625,20458.8353515625,790.316650390625], // 表示六天内所有转化时间间隔的总算术统计值，六个元素分别对应 avg（平均值）, max（最长值）, min（最短值）, mid（中位数）, 75-quantile（75分位数）, 25-quantile（25分位数）
 [37865.0494117647,588283,28,5586.3125,19187.166015625,816.0833129882812], // 表示第一天转化间隔的算术统计值，六个元素对应同上
 [59552.33018867925,587526,14,7210.5,25011,1087], // 表示第二天的...
 [52667.491228070176,596132,47,3111,19174,781], // 第三天的...依此类推
 [32494.533066132266,596547,23,4434.09375,16213.638671875,731.5277709960938],
 [34384.957661290326,59484,24,4429,23285.33203125,744.5555419921875],
 [40416.285711428572,596767,2,5539,21438.056640625,833.88888549804688]
 ]
)