GA页面浏览量与BigQuery展开自定义维度后数据不匹配求助

我太懂你这种头疼的感觉了——不用UNNEST的时候数据好好的，一展开自定义维度就乱了，这问题其实出在UNNEST会把单个会话拆成多行，直接按日期分组会导致数据被重复统计。

问题根源 #

你的当前查询里，UNNEST(customDimensions)会把每个GA会话里的每一个自定义维度都拆成独立的行。比如一个会话同时有index=2和3两个自定义维度，就会被拆成2行。这时候直接按date分组用MAX(CASE)，相当于同一个会话被多次计入统计，自然数据就和预期对不上了。

修正后的查询方案 #

正确的思路是先在会话级别提取好自定义维度的值，再聚合到日期维度，确保每个会话只被统计一次：

SELECT
  date AS Date,
  COUNT(*) AS pageviews, -- 这里可以替换成你需要的页面浏览量或其他指标
  MAX(Institution_ID) AS Institution_ID,
  MAX(Institution_Name) AS Institution_Name
FROM (
  SELECT
    date,
    -- 在会话维度提取对应的自定义维度值
    MAX(CASE WHEN cd.index=2 THEN cd.value ELSE NULL END) AS Institution_ID,
    MAX(CASE WHEN cd.index=3 THEN cd.value ELSE NULL END) AS Institution_Name
  FROM `ga_sessions_*`,
       UNNEST(customDimensions) AS cd
  GROUP BY date, fullVisitorId, visitId -- 用会话唯一标识分组，保证每个会话只处理一次
)
GROUP BY Date

进阶调整（按用户ID统计页面浏览量） #

如果你的核心需求是按自定义维度的用户ID查看页面浏览量，可以把用户ID加入外层分组，这样就能得到每个用户ID每天的浏览数据：

SELECT
  date AS Date,
  Institution_ID,
  Institution_Name,
  COUNT(*) AS pageviews
FROM (
  SELECT
    date,
    MAX(CASE WHEN cd.index=2 THEN cd.value ELSE NULL END) AS Institution_ID,
    MAX(CASE WHEN cd.index=3 THEN cd.value ELSE NULL END) AS Institution_Name
  FROM `ga_sessions_*`,
       UNNEST(customDimensions) AS cd
  GROUP BY date, fullVisitorId, visitId
)
WHERE Institution_ID IS NOT NULL -- 过滤掉没有用户ID的无效会话
GROUP BY Date, Institution_ID, Institution_Name

这样调整后，数据应该就能和你不用自定义维度时的结果匹配了。

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