BigQuery Google Data Transfer Impressions & Cost Don't Match Google Ads UI
Disclaimer...I'm a Noob
I am writing a query from CampaignStats table that aggregates based on a stripped campaign label. The query returns correct values for all metrics except Impressions and Cost. No matter what I've tried so far, this figure still doesn't match. Here are the totals for two of my campaigns from yesterday (June 17th):
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
Utilizing the CampaignBasicStats table, I receive aggregated totals for all metrics that match the UI, including Impressions and Cost. The issue is there are metrics in CampaignStats and getting some illumination/information on what I may not be doing correct, will help in the future.
I did a JOIN with the Campaign table originally; the below query refers to a permanent table that I pulled out separately in case this was a cause of the discrepancy.
Code Below:
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
Maybe a bit late on this. You should use the CampaignBasicStats instead, to remove any potential duplicates.