如何在BigQuery中正确连接Google Ads表计算合规展示量与潜在点击量

问题 #

通过Data Transfer导入BigQuery的Google Ads数据包含两张表：

• p_ads_CampaignBasicStats_：字段为customer_id、campaign_id、segments_date、metrics_impressions、metrics_clicks
• p_ads_CampaignCrossDeviceStats_：字段为customer_id、campaign_id、segments_date、metrics_search_impression_share、metrics_search_click_share

需要按customer_id、campaign_id、segments_date关联两张表，计算合规展示量（eligible impressions）和潜在点击量（potential clicks），但现有查询计算结果与Google Ads UI数据差距极大，求正确的表连接与计算方式。

原查询代码：

WITH Metrics AS (
  SELECT 
    customer_id,
    campaign_id,
    segments_date,
    SUM(metrics_impressions) AS Impressions,
    SUM(metrics_clicks) AS Clicks
  FROM `project.dataset.p_ads_CampaignBasicStats_*`
  WHERE segments_date BETWEEN '2025-01-01' AND '2025-01-21'
  GROUP BY customer_id, campaign_id, segments_date
),

SOV AS (
  SELECT 
    cb.customer_id,
    cb.campaign_id,
    cb.segments_date,
    SUM(cd.metrics_search_impression_share * cb.metrics_impressions) / NULLIF(SUM(cb.metrics_impressions), 0) AS Search_Impression_Share,
    SUM(cd.metrics_search_click_share * cb.metrics_clicks) / NULLIF(SUM(cb.metrics_clicks), 0) AS Search_Click_Share
  FROM `project.dataset.p_ads_CampaignCrossDeviceStats_*` AS cd
  LEFT JOIN `project.dataset.p_ads_CampaignBasicStats_*` AS cb
    ON cd.customer_id = cb.customer_id
    AND cd.campaign_id = cb.campaign_id
    AND cd.segments_date = cb.segments_date
  WHERE cb.segments_date BETWEEN '2025-01-01' AND '2025-01-21'
  GROUP BY cb.customer_id, cb.campaign_id, cb.segments_date
)

SELECT 
    SUM(m.Impressions) AS Total_Impressions, 
    SUM(m.Clicks) AS Total_Clicks, 
    SUM(CASE 
        WHEN s.Search_Impression_Share IS NULL OR s.Search_Impression_Share = 0 THEN NULL
        ELSE m.Impressions / s.Search_Impression_Share
    END) AS Total_Eligible_Impressions,
    SUM(CASE 
        WHEN s.Search_Click_Share IS NULL OR s.Search_Click_Share = 0 THEN NULL
        ELSE m.Clicks / s.Search_Click_Share
    END) AS Total_Potential_Clicks

FROM Metrics AS m
LEFT JOIN SOV AS s
ON m.customer_id = s.customer_id 
AND m.campaign_id = s.campaign_id 
AND m.segments_date = s.segments_date;

正确的查询方式与修正说明 #

原查询核心问题 #

1. 连接逻辑失效：SOV CTE中使用cd LEFT JOIN cb后，通过WHERE cb.segments_date过滤等价于INNER JOIN，会丢失仅存在基础数据的记录；同时直接关联原始表再聚合，易因重复行导致计算偏差。
2. 份额计算逻辑错误：metrics_search_impression_share本身是0-1区间的百分比值，无需用基础数据加权后求平均，应先按维度聚合两张表的原始数据，再关联计算。

修正后的查询代码 #

WITH AggregatedBasic AS (
  -- 先聚合基础统计数据，确保每个维度组合唯一
  SELECT 
    customer_id,
    campaign_id,
    segments_date,
    SUM(metrics_impressions) AS total_impressions,
    SUM(metrics_clicks) AS total_clicks
  FROM `project.dataset.p_ads_CampaignBasicStats_*`
  WHERE segments_date BETWEEN '2025-01-01' AND '2025-01-21'
  GROUP BY customer_id, campaign_id, segments_date
),
AggregatedCrossDevice AS (
  -- 聚合交叉设备统计数据，确保每个维度组合唯一
  SELECT 
    customer_id,
    campaign_id,
    segments_date,
    -- 搜索展示份额为百分比，若原始表维度唯一可直接取值，否则取平均
    AVG(metrics_search_impression_share) AS avg_search_impression_share,
    AVG(metrics_search_click_share) AS avg_search_click_share
  FROM `project.dataset.p_ads_CampaignCrossDeviceStats_*`
  WHERE segments_date BETWEEN '2025-01-01' AND '2025-01-21'
  GROUP BY customer_id, campaign_id, segments_date
)
-- 关联聚合后的表，计算目标指标
SELECT 
  SUM(ab.total_impressions) AS total_impressions,
  SUM(ab.total_clicks) AS total_clicks,
  -- 合规展示量 = 实际展示量 / 搜索展示份额（份额为0或null时跳过）
  SUM(CASE 
        WHEN acd.avg_search_impression_share IS NULL OR acd.avg_search_impression_share = 0 THEN NULL
        ELSE ab.total_impressions / acd.avg_search_impression_share
      END) AS total_eligible_impressions,
  -- 潜在点击量 = 实际点击量 / 搜索点击份额（份额为0或null时跳过）
  SUM(CASE 
        WHEN acd.avg_search_click_share IS NULL OR acd.avg_search_click_share = 0 THEN NULL
        ELSE ab.total_clicks / acd.avg_search_click_share
      END) AS total_potential_clicks
FROM AggregatedBasic ab
LEFT JOIN AggregatedCrossDevice acd
  ON ab.customer_id = acd.customer_id
  AND ab.campaign_id = acd.campaign_id
  AND ab.segments_date = acd.segments_date;

关键逻辑说明 #

1. 先聚合再关联：分别对两张表按目标维度聚合，确保每个维度组合仅一行数据，避免关联时产生笛卡尔积导致数据重复。
2. 正确处理份额指标：metrics_search_impression_share是对应维度下的搜索展示份额，直接取平均值（若原始表维度唯一则直接取值）即可。
3. 保留全量基础数据：使用AggregatedBasic LEFT JOIN AggregatedCrossDevice，确保不会丢失仅存在基础统计的记录。

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