如何通过BigQuery复现Google Analytics的Top Conversion Path并获取各节点时间戳

核心逻辑梳理 #

要复现该功能，本质是完成转化用户路径回溯-触点拼接-指标聚合三个核心步骤，具体逻辑如下：

• 第一步：确定转化基准。先筛选你要分析的目标转化事件（比如Purchase），拉取所有发生过该转化的用户ID，以及对应每个转化事件的发生时间戳，作为路径的终止节点。
• 第二步：回溯触点数据。对每个转化事件，回溯该用户在转化前自定义归因窗口期（通常默认30天，可按需调整）内的所有会话，按会话发生时间从早到晚排序，每个会话提取source/medium作为路径触点，同时记录该触点对应的会话时间戳。
• 第三步：路径聚合统计。将每个转化对应的所有触点按顺序拼接成完整路径，同时保留各触点的时间戳数组，再按路径分组统计对应转化次数、路径总价值、路径长度等指标，即可对齐GA原生Top Conversion Path的输出字段。

核心SQL参考（BigQuery标准SQL） #

WITH
-- 1. 提取所有目标转化事件，这里以Purchase为例
conversions AS (
  SELECT
    fullVisitorId,
    visitStartTime AS conversion_time,
    totals.transactionRevenue / 1000000 AS revenue -- 转换为货币单位
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20170101' AND '20170301' -- 自定义分析时间范围
    AND totals.transactions >= 1 -- 筛选有购买的会话
),
-- 2. 提取所有用户的会话触点数据
all_touchpoints AS (
  SELECT
    fullVisitorId,
    visitStartTime AS touchpoint_time,
    CONCAT(trafficSource.source, ' / ', trafficSource.medium) AS source_medium,
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20161201' AND '20170301' -- 比转化时间范围多往前推30天（归因窗口期）
),
-- 3. 为每个转化匹配窗口期内的所有触点
path_mapping AS (
  SELECT
    c.fullVisitorId,
    c.conversion_time,
    c.revenue,
    -- 按时间排序拼接触点，同时保留时间戳
    ARRAY_AGG(
      STRUCT(t.source_medium AS touchpoint, t.touchpoint_time AS timestamp)
      ORDER BY t.touchpoint_time ASC
    ) AS conversion_path
  FROM
    conversions c
  LEFT JOIN
    all_touchpoints t
    ON c.fullVisitorId = t.fullVisitorId
    -- 触点必须发生在转化前，且在30天归因窗口期内
    AND t.touchpoint_time < c.conversion_time
    AND TIMESTAMP_SECONDS(c.conversion_time) - TIMESTAMP_SECONDS(t.touchpoint_time) <= INTERVAL 30 DAY
  GROUP BY
    1,2,3
)
-- 4. 按路径聚合统计核心指标
SELECT
  -- 拼接路径字符串展示，比如A > B > C > Purchase
  ARRAY_TO_STRING(ARRAY(SELECT touchpoint FROM UNNEST(conversion_path)), ' > ') || ' > Purchase' AS conversion_path,
  COUNT(*) AS conversions, -- 该路径对应的转化次数
  SUM(revenue) AS total_revenue, -- 该路径带来的总价值
  ARRAY_LENGTH(conversion_path) AS path_length, -- 路径长度
  -- 保留每个触点的时间戳，需要时直接提取即可
  ARRAY(SELECT timestamp FROM UNNEST(conversion_path)) AS touchpoint_timestamps
FROM
  path_mapping
GROUP BY
  conversion_path, path_length
ORDER BY
  conversions DESC

注意事项 #

• 归因窗口期可根据业务需求调整，修改SQL中的INTERVAL 30 DAY参数即可，要和你GA后台的归因设置保持一致，统计结果才会对齐。
• 如果要分析其他转化事件（比如表单提交、加购等），只需要修改conversions CTE里的转化筛选条件即可。
• 若同一个用户在分析周期内发生多次转化，上述逻辑会为每个转化单独回溯路径，不会出现路径重复计算的问题。

内容的提问来源于stack exchange，提问作者Nhi Yen