如何基于BigQuery中GA数据的页面与事件构建转化漏斗

实现思路 #

• 先明确定义漏斗各步骤的匹配规则，支持页面访问、事件触发两种行为类型，可自由扩展到10步以上
• 给每个会话的所有行为标记命中的漏斗步骤，仅保留匹配到漏斗规则的行为记录
• 按会话/用户维度校验步骤完成顺序：仅要求后序步骤的触发时间（hitNumber）晚于前序步骤，无需连续
• 按日期聚合统计各步骤的去重独立用户数、去重独立会话数，输出宽表结果

可直接复用的SQL实现 #

-- 步骤1：定义漏斗规则，新增/修改步骤仅需要调整这部分即可
WITH funnel_steps AS (
  SELECT 1 AS step_id, '访问homepage.com' AS step_name, 'page' AS hit_type, 'homepage.com' AS url_condition, NULL AS event_category, NULL AS event_action, NULL AS event_label
  UNION ALL
  SELECT 2 AS step_id, '查看哈利波特书籍详情' AS step_name, 'event' AS hit_type, NULL AS url_condition, 'View' AS event_category, 'Book' AS event_action, 'Harry_Potter' AS event_label
  UNION ALL
  SELECT 3 AS step_id, '购买哈利波特书籍' AS step_name, 'event' AS hit_type, NULL AS url_condition, 'Purchase' AS event_category, 'Book' AS event_action, 'Harry_Potter' AS event_label
  -- 新增步骤直接追加UNION ALL即可，step_id按顺序递增
),
-- 步骤2：匹配所有命中漏斗规则的行为记录
hit_matched AS (
  SELECT
    t.date,
    t.fullVisitorId,
    t.sessionId,
    t.hitNumber,
    s.step_id
  FROM `你的GA导出表实际路径` t -- 替换为你自己的表路径
  LEFT JOIN funnel_steps s
  ON t.type = s.hit_type
    AND (s.url_condition IS NULL OR t.url = s.url_condition)
    AND (s.event_category IS NULL OR t.eventCategory = s.event_category)
    AND (s.event_action IS NULL OR t.eventAction = s.event_action)
    AND (s.event_label IS NULL OR t.eventLabel = s.event_label)
  WHERE s.step_id IS NOT NULL
),
-- 步骤3：按会话/用户维度标记各步骤完成状态，要求步骤按顺序触发
user_session_progress AS (
  SELECT
    date,
    fullVisitorId,
    sessionId,
    MAX(IF(step_id = 1, 1, 0)) AS completed_step1,
    MAX(IF(step_id = 2 AND EXISTS(
      SELECT 1 FROM hit_matched t2 
      WHERE t2.fullVisitorId = t1.fullVisitorId 
        AND t2.sessionId = t1.sessionId 
        AND t2.step_id = 1 
        AND t2.hitNumber < t1.hitNumber
    ), 1, 0)) AS completed_step2,
    MAX(IF(step_id = 3 AND EXISTS(
      SELECT 1 FROM hit_matched t2 
      WHERE t2.fullVisitorId = t1.fullVisitorId 
        AND t2.sessionId = t1.sessionId 
        AND t2.step_id = 2 
        AND t2.hitNumber < t1.hitNumber
    ), 1, 0)) AS completed_step3
    -- 新增步骤按照上面的格式追加即可，修改对应的step_id和前置步骤判断条件
  FROM hit_matched t1
  GROUP BY date, fullVisitorId, sessionId
)
-- 步骤4：按日期聚合输出宽表结果
SELECT
  date,
  COUNT(DISTINCT IF(completed_step1 = 1, fullVisitorId, NULL)) AS step1_uv,
  COUNT(DISTINCT IF(completed_step1 = 1, sessionId, NULL)) AS step1_sv,
  COUNT(DISTINCT IF(completed_step2 = 1, fullVisitorId, NULL)) AS step2_uv,
  COUNT(DISTINCT IF(completed_step2 = 1, sessionId, NULL)) AS step2_sv,
  COUNT(DISTINCT IF(completed_step3 = 1, fullVisitorId, NULL)) AS step3_uv,
  COUNT(DISTINCT IF(completed_step3 = 1, sessionId, NULL)) AS step3_sv
  -- 新增步骤对应的统计列直接追加即可
FROM user_session_progress
GROUP BY date
ORDER BY date

自定义调整说明 #

• 如果需要支持跨日期的漏斗转化（比如用户第一天访问首页，第二天完成购买也算），只需要把date字段从GROUP BY和子查询的关联条件中移除即可
• 已经默认实现非严格连续要求，只要后序步骤的hitNumber大于前序步骤就算完成，中间的其他行为不影响统计
• 步骤数量无上限，按照示例格式新增漏斗规则、完成状态标记、统计列即可适配超过10步的需求

内容的提问来源于stack exchange，提问作者James Harrington