BigQuery数据传输CampaignStats指标与Google Ads UI不匹配排查

免责声明：本人为BigQuery使用新手

我当前基于提取处理后的广告系列标签，对CampaignStats表编写聚合查询，除*Impressions（展示量）和Cost（消耗）*外，其余指标返回值均准确，多次调整查询逻辑后两个指标仍存在数值不匹配问题。以下为2022年6月17日sat_brand广告系列的统计数据：

CampaignStats表统计结果 #

Date    label   Impressions cost    clicks  avg_cpc
6/17/2022   sat_brand   2687    140.472666  15  9.3648444

CampaignBasicStats表统计结果 #

Date    label   Impressions cost    clicks  avg_cpc
6/17/2022   sat_brand   699 152.620961  15  10.17473073

使用CampaignBasicStats表聚合得到的所有指标（含展示量、消耗）均与Google Ads UI数值完全匹配，但因业务需要使用CampaignStats表中的独有指标，希望明确问题成因以便后续使用。

最初查询逻辑关联了Campaign表，为排除关联操作导致的数值偏差，我已将标签处理结果单独抽为永久表，当前使用的查询代码如下：

SELECT  
  cs.Date,
  EXTRACT(ISOWEEK FROM cs.DATE) AS isoweek,
  cl.label,
  (SUM(cs.Cost) / 1000000) AS cost,
  SUM(cs.Clicks) AS clicks,
  CASE WHEN SUM(Clicks)=0 OR SUM(Cost)=0 THEN 0 ELSE
    ((SUM(Cost)/SUM(Clicks))/1000000) END AS avg_cpc,
  SUM(cs.Impressions) AS Impressions,
  CASE WHEN SUM(cs.Clicks)=0 THEN 0 ELSE
    (SUM(cs.Clicks)/SUM(cs.Impressions)) END AS ctr,
  SUM(cs.Conversions) as conversions,
  CASE WHEN SUM(cs.Conversions)=0 OR SUM(cs.Clicks)=0 THEN 0 ELSE
    (SUM(cs.Conversions)/SUM(cs.Clicks)) END AS cvr,
  CASE WHEN SUM(cs.Conversions)=0 OR SUM(cs.Cost)=0 THEN 0 ELSE
    (SUM(cs.Cost)/SUM(cs.Conversions))/1000000 END AS cost_per_conversion
FROM 
  `bold-quanta-######.######_google_ads_dataset.CampaignStats_##########` cs
JOIN
  `bold-quanta-######.queried_permanent_tables.process_campaign_labels` cl
ON
  cs.CampaignId = cl.CampaignId
GROUP BY
  1, 2, 3

问题成因 #

这个数值偏差是Google Ads BigQuery数据传输服务的CampaignStats表本身的粒度与统计规则特性导致的，和JOIN逻辑无关：

• CampaignBasicStats表的粒度为日期+广告系列ID，每一行对应单个广告系列单日的全量汇总数据，直接聚合的结果天然和Google Ads UI数值一致。
• CampaignStats是细分维度拆分表，默认会按设备、受众、投放网络、广告位、关键词匹配类型等多维度组合拆分为多行存储，同一个广告系列同一天会对应多条细分记录。同时该表中Impressions、Cost这类曝光/消耗类指标在跨维度交叉归因时会存在重复计数，而Clicks、Conversions这类按用户实际交互计数的指标不会重复，这也是查询结果中点击量一致、展示量和消耗存在偏差的核心原因。
  案例中展示量从699变为2687，就是该广告系列当天的多组细分维度下的展示量被直接累加导致的，消耗偏差则是交叉归因+部分已删除/已归档细分项的残留数据共同导致的。

修复方案 #

1. 聚合前先明确业务需要用到的CampaignStats独有指标对应的细分维度，在查询中增加对应维度的过滤条件，排除不需要的细分项，避免跨维度重复累加。
2. 如果不需要用到细分维度的拆分指标，仅需要广告系列层级的汇总值，可以先在子查询中按Date、CampaignId对CampaignStats做去重聚合，过滤掉重复的交叉归因数据后，再关联标签表计算后续指标，参考调整后的逻辑：

WITH cs_dedup AS (
  SELECT
    Date,
    CampaignId,
    SUM(Cost) AS Cost,
    SUM(Clicks) AS Clicks,
    SUM(Impressions) AS Impressions,
    SUM(Conversions) AS Conversions
  FROM `bold-quanta-######.######_google_ads_dataset.CampaignStats_##########`
  -- 按需增加细分维度过滤条件，比如排除已删除维度、限定投放网络等
  GROUP BY 1,2
)
SELECT  
  cs.Date,
  EXTRACT(ISOWEEK FROM cs.DATE) AS isoweek,
  cl.label,
  (SUM(cs.Cost) / 1000000) AS cost,
  SUM(cs.Clicks) AS clicks,
  CASE WHEN SUM(Clicks)=0 OR SUM(Cost)=0 THEN 0 ELSE
    ((SUM(Cost)/SUM(Clicks))/1000000) END AS avg_cpc,
  SUM(cs.Impressions) AS Impressions,
  CASE WHEN SUM(cs.Clicks)=0 THEN 0 ELSE
    (SUM(cs.Clicks)/SUM(cs.Impressions)) END AS ctr,
  SUM(cs.Conversions) as conversions,
  CASE WHEN SUM(cs.Conversions)=0 OR SUM(cs.Clicks)=0 THEN 0 ELSE
    (SUM(cs.Conversions)/SUM(cs.Clicks)) END AS cvr,
  CASE WHEN SUM(cs.Conversions)=0 OR SUM(cs.Cost)=0 THEN 0 ELSE
    (SUM(cs.Cost)/SUM(cs.Conversions))/1000000 END AS cost_per_conversion
FROM cs_dedup cs
JOIN `bold-quanta-######.queried_permanent_tables.process_campaign_labels` cl
  ON cs.CampaignId = cl.CampaignId
GROUP BY 1,2,3

3. 如果调整后数值仍有微小偏差，检查CampaignStats表中是否包含非目标投放网络（比如Google搜索合作伙伴、展示网络合作站点）的流量，按需过滤即可和UI数值完全对齐。

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