用户行为漏斗分析

基于 ClickZetta Lakehouse Dynamic Table + 窗口函数 + MERGE INTO，将原始用户行为事件流自动聚合为分渠道漏斗转化指标，端到端延迟 ≤1 小时，定位最大流失环节，驱动转化率优化决策。

一、业务背景

用户行为漏斗分析是电商运营的核心工具，通过量化每个转化步骤的用户损失，帮助团队找到投入产出比最高的优化方向。

二、行业痛点

量化数据

• 全球电商平均整体转化率（浏览→支付）仅 2–3%，意味着 97% 以上的访客未完成购买（Baymard Institute, 2025）
• 加购放弃率高达 70.22%——每 10 个加购用户，7 个不结账（Baymard Institute, 50 项研究均值）
• 移动端放弃率 85.65%，显著高于桌面端，但移动端占全渠道订单比例已达 68%（Statista, Q1 2025）
• 加购→结账环节是最大流失点，超过 48% 的用户因额外费用（运费/税）在结账页离开
• 加购率行业均值约 11–11.5%（ecdb.com, 2025），下单→支付转化率通常低于 50%

传统方案的三大缺陷

缺陷一：数据延迟，决策滞后 传统数仓基于 T+1 批量调度，漏斗报表次日才能看到。大促期间某渠道突发转化率崩塌，运营团队当天无法感知，错失窗口期。

缺陷二：多系统割裂，渠道无法对比 APP、PC、小程序的行为数据分散在不同系统，BI 工具需人工对齐口径，渠道间转化率横向对比报告通常需要跨部门协作才能产出，耗时数天。

缺陷三：指标计算复杂，维护成本高 漏斗指标依赖复杂 SQL（多重 COUNT DISTINCT + CASE WHEN + 除法保护），每次新增渠道或步骤都要改多处逻辑，数据血缘不清晰，QA 困难。

过渡

解决以上问题的关键在于：用统一的事件表存储所有渠道行为，用 SQL 声明式表达漏斗计算逻辑，让数据平台自动完成增量刷新和指标维护。ClickZetta Lakehouse 的 Dynamic Table 机制让漏斗 Pipeline 变成一张"活的聚合表"，无需额外调度器。

三、解决方案

整体架构

预览

数据模型

-- 源表：统一事件流（多渠道、多步骤） -- 分区列 event_time 必须在 PRIMARY KEY 首位 CREATE TABLE user_events ( event_id STRING NOT NULL, user_id STRING NOT NULL, session_id STRING, event_type STRING COMMENT '浏览/加购/下单/支付/退款', product_id STRING, channel STRING COMMENT '渠道：APP/PC/小程序', event_time TIMESTAMP_NTZ NOT NULL, PRIMARY KEY (event_time, event_id) ) PARTITIONED BY (DAYS(event_time)); -- 汇总表：带转化率的分渠道每日漏斗 CREATE TABLE user_funnel_daily ( stat_date STRING NOT NULL COMMENT 'YYYY-MM-DD', channel STRING NOT NULL, pv_users BIGINT COMMENT '浏览UV', cart_users BIGINT COMMENT '加购UV', order_users BIGINT COMMENT '下单UV', pay_users BIGINT COMMENT '支付UV', cart_rate DOUBLE COMMENT '浏览→加购率', order_rate DOUBLE COMMENT '加购→下单率', pay_rate DOUBLE COMMENT '下单→支付率', full_conv_rate DOUBLE COMMENT '浏览→支付整体转化率', PRIMARY KEY (stat_date, channel) );

三层流水线

[APP / PC / 小程序埋点] │ INSERT INTO user_events ▼ ┌─────────────────────────────────────────────────────────┐ │ ClickZetta Lakehouse │ │ │ │ user_events（源表，按天分区） │ │ │ │ │ │ COUNT DISTINCT + CASE WHEN │ │ │ REFRESH INTERVAL 1 HOUR │ │ ▼ │ │ user_funnel_daily_view（Dynamic Table，漏斗 UV 聚合） │ │ │ │ │ │ MERGE INTO（转化率计算 + 写入汇总表） │ │ ▼ │ │ user_funnel_daily（汇总表，含所有转化率字段） │ └─────────────────────────────────────────────────────────┘ │ SELECT channel, avg_cart_pct, avg_full_conv_pct ▼ [BI 看板 / 运营报告 / 告警规则]

第一层（源表）：

user_events

event_type

channel

第二层（Dynamic Table）：

user_funnel_daily_view

COUNT DISTINCT CASE WHEN

(stat_date, channel)

第三层（MERGE INTO）：将 Dynamic Table 的原始 UV 数据加工为转化率指标，用

NULLIF

四、ClickZetta 技术优势

Dynamic Table — 漏斗聚合的自动维护

CREATE DYNAMIC TABLE user_funnel_daily_view REFRESH INTERVAL 1 HOUR AS SELECT DATE_FORMAT(event_time, 'yyyy-MM-dd') AS stat_date, channel, COUNT(DISTINCT CASE WHEN event_type = '浏览' THEN user_id END) AS pv_users, COUNT(DISTINCT CASE WHEN event_type = '加购' THEN user_id END) AS cart_users, COUNT(DISTINCT CASE WHEN event_type = '下单' THEN user_id END) AS order_users, COUNT(DISTINCT CASE WHEN event_type = '支付' THEN user_id END) AS pay_users FROM user_events WHERE event_time >= CURRENT_TIMESTAMP() - INTERVAL 30 DAY GROUP BY DATE_FORMAT(event_time, 'yyyy-MM-dd'), channel;

本场景适合 Dynamic Table 的原因：漏斗指标是对历史窗口数据的聚合，每小时刷新足够满足运营决策需求，且不依赖行级实时推理。Dynamic Table 的声明式定义让漏斗逻辑版本可追溯，指标口径变更只需修改 SQL 定义，无需重写调度脚本。

MERGE INTO — 幂等的汇总表更新

MERGE INTO user_funnel_daily AS t USING (SELECT ..., ROUND(cart_users * 1.0 / NULLIF(pv_users, 0), 4) AS cart_rate, ...) AS s ON t.stat_date = s.stat_date AND t.channel = s.channel WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *;

本场景适合 MERGE INTO 的原因：漏斗汇总表需要同时支持"历史日期补数据"和"当日实时更新"两种写入模式。MERGE INTO 的 UPSERT 语义保证操作幂等，同一天多次刷新不产生重复行，数据质量可保证。

分区裁剪 — 大表查询加速

user_events

DAYS(event_time)

WHERE event_time >= CURRENT_TIMESTAMP() - INTERVAL 30 DAY

NULLIF 防除零 — 数据健壮性

ROUND(cart_users * 1.0 / NULLIF(pv_users, 0), 4) AS cart_rate

新渠道上线初期或数据回填场景中，某些

(stat_date, channel)

pv_users = 0

五、客户价值

ROI 对照

运营效率

• 大促实时监控：漏斗每小时刷新，大促开始 1 小时内可看到各渠道转化率变化，发现异常立即调整运营策略
• 渠道 ROI 决策：APP / PC / 小程序转化率横向对比，直接支撑广告预算分配决策，避免在低转化渠道浪费投放
• 流失环节定位：三段流失（浏览→加购、加购→下单、下单→支付）量化分拆，确定 A/B 测试方向（页面优化 vs 促销策略 vs 支付体验）

行业基准对照

六、快速上手

前置依赖

1. ClickZetta Lakehouse workspace（标准 SQL 能力，无需额外组件）
2. 埋点数据接入（或使用 test_data.sql 中的模拟数据验证）

执行顺序

# 1. 建表 run setup.sql # 2. 插入测试数据（10 条覆盖4个用户、3个渠道的行为序列） run test_data.sql # 3. 创建 Dynamic Table + 触发漏斗计算 + MERGE 写入汇总表 run pipeline.sql

验证查询

-- 分渠道转化率对比 SELECT channel, ROUND(AVG(cart_rate) * 100, 2) AS avg_cart_pct, ROUND(AVG(order_rate) * 100, 2) AS avg_order_pct, ROUND(AVG(pay_rate) * 100, 2) AS avg_pay_pct, ROUND(AVG(full_conv_rate) * 100, 2) AS avg_full_conv_pct FROM user_funnel_daily WHERE stat_date >= DATE_FORMAT(CURRENT_TIMESTAMP() - INTERVAL 7 DAY, 'yyyy-MM-dd') GROUP BY channel ORDER BY avg_full_conv_pct DESC; -- 预期结果（基于 test_data.sql）： -- APP : 加购率 50%, 下单率 100%, 支付率 100%, 整体 50%（U001 完整转化） -- PC : 加购率 100%, 下单率 0%, 支付率 N/A, 整体 0%（U002 加购未下单） -- 小程序 : 加购率 0%, 下单率 N/A, 支付率 N/A, 整体 0%（U003 仅浏览） -- 流失分析 SELECT stat_date, channel, (pv_users - cart_users) AS lost_pv_to_cart, (cart_users - order_users) AS lost_cart_to_order, (order_users - pay_users) AS lost_order_to_pay FROM user_funnel_daily ORDER BY stat_date DESC, channel;

相关文档

Dynamic Table

REFRESH INTERVAL

change_tracking

MERGE INTO

分区表

PARTITIONED BY (DAYS(...))

event_time

聚合与条件函数

COUNT(DISTINCT ...)

NULLIF(pv_users, 0)

event_time

yyyy-MM-dd

COUNT

SUM

AVG

进阶参考