基于Purchase事件的Google BigQuery独立访客转化率计算问题

你的代码计算出的转化率偏低，核心问题是过滤条件错误限制了数据源范围，导致分母（总独立访客数）被错误缩小，同时子查询的统计逻辑也存在问题，以下是具体问题和修正方案：

原代码的核心问题 #

• WHERE子句中h.eventInfo.eventCategory = 'Purchase'会过滤掉所有没有Purchase事件的会话和访客，因此子查询仅包含有购买行为的访客，分母COUNT(unique_visitors)实际是有购买的访客数，而非全量独立访客数，这直接导致转化率计算逻辑完全错误。
• 子查询中按fullVisitorId分组后，COUNT(DISTINCT fullvisitorId)的结果始终为1，属于无效统计。
• purchase_event的统计方式是计数该访客的所有Purchase事件次数，但独立访客转化率通常关注的是有购买行为的访客数量，而非事件次数。

修正后的代码 #

如果你的需求是有购买行为的独立访客数 / 总独立访客数，可以用以下代码：

WITH total_visitors AS (
  -- 统计时间范围内的总独立访客数
  SELECT COUNT(DISTINCT fullvisitorId) AS total_unique_visitors
  FROM `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ga_sessions_*`
  WHERE _table_suffix BETWEEN '20200601' AND '20200630'
),
purchased_visitors AS (
  -- 统计有Purchase事件的独立访客数
  SELECT COUNT(DISTINCT fullvisitorId) AS purchased_unique_visitors
  FROM `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ga_sessions_*` AS ga,
  UNNEST(Hits) AS h
  WHERE _table_suffix BETWEEN '20200601' AND '20200630'
    AND h.eventInfo.eventCategory = 'Purchase'
)
SELECT 
  purchased_unique_visitors / total_unique_visitors AS user_conversion_rate
FROM total_visitors, purchased_visitors

如果你的需求是Purchase事件总次数 / 总独立访客数，可以调整为：

WITH total_visitors AS (
  SELECT COUNT(DISTINCT fullvisitorId) AS total_unique_visitors
  FROM `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ga_sessions_*`
  WHERE _table_suffix BETWEEN '20200601' AND '20200630'
),
purchase_events AS (
  SELECT COUNT(*) AS total_purchase_events
  FROM `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ga_sessions_*` AS ga,
  UNNEST(Hits) AS h
  WHERE _table_suffix BETWEEN '20200601' AND '20200630'
    AND h.eventInfo.eventCategory = 'Purchase'
)
SELECT 
  total_purchase_events / total_unique_visitors AS user_conversion_rate
FROM total_visitors, purchase_events

补充说明 #

• 用CTE（WITH子句）拆分统计逻辑，更清晰易维护。
• 确保总访客数的统计范围和购买行为的统计范围完全一致（时间区间相同），避免数据偏差。

内容的提问来源于stack exchange，提问作者Miles Woolgar