sql - How to query Google Ads Tables on BigQuery - Stack Overflow

I need guidance on how to create a query in BigQuery using Google Ads data transferred via a Data Transfer. My issue is that there are two tables:

1. p_ads_CampaignBasicStats_, which contains customer_id, campaign_id, segments_date, metrics_impressions, and metrics_clicks.

2. p_ads_CampaignCrossDeviceStats_, which contains customer_id, campaign_id, segments_date, metrics_search_impression_share and metrics_search_click_share

I need to join both tables (on customer_id, campaign_id, and segments_date) to calculate eligible impressions and potential clicks, but none of the approaches I've tried work. The numbers I obtain are far from what I see in the Google Ads UI.

Could you advise on the correct way to join these tables?

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;