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 

Tag:google-ads-api, sql, google-bigquery

Only one comment.

  1. Mike

    Maybe a bit late on this. You should use the CampaignBasicStats instead, to remove any potential duplicates.

Add a new comment.