BigQuery imports from GoogleAds show all CPM related Fields with value 0
Hello wonderful person!
I've followed this guide to import google ads campaign info to a BigQuery database.
My goal is to create a simple query that can be stored as a view and accessed from Data Studio to make a report. But some fields like AverageCpm are always set to 0.
I also have a data studio report made using google ads as source for reference and I can access all the campaigns from the google ads platform.
Here is the query I'm working on:
SELECT c.ExternalCustomerId, c.CampaignName as name, c.CampaignStatus, cs.date as dia, SUM(cs.Impressions) AS Impressions, SUM(cs.Interactions) AS Interactions, AVG(cs.AverageCpm) AS CPM, SUM(cs.Cost) AS Cost FROM `<DB>.google_ads.Campaign_<ACCOUNT_ID>` c LEFT JOIN `<DB>.google_ads.CampaignStats_<ACCOUNT_ID>` cs ON (c.CampaignId = cs.CampaignId AND cs._DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -80 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) WHERE c._DATA_DATE = c._LATEST_DATE and c.CampaignName = 'THE_NAME_OF_MY_CAMPAIGN' GROUP BY 1, 2, 3 , 4 ORDER BY CampaignName, dia
The field Impressions, returns with a value that is consistent with my reference datastudio report and the info I see in google ads stats, so I feel I'm in the right track.
My problem is that some fields like CampaignStats.AverageCpm , CampaignStats.Cost are always 0.
For example, the query:
Select * from `<DB>.google_ads.p_CampaignStats_<ACCOUNT_ID>` where AverageCpm >0;
Returns with no results.
I'm thinking permission problems? But I have administrator access to all the company's accounts.
Database is backfilled correctly.
I've tried generating a new dataset: Same problem and I don't see if there is a way to configure how google makes the imports.
What else could it be? What else can I do?
Thank you very very much!
Answer by Roman Petrochenkov, check his youtube channel he is the best.
AVG(cs.AverageCpm) AS CPM, Is not correct Since average of averages is not average of the total.
You need to calculate CPM manually as SUM(Impressions)/SUM(NULLIF(Cost, 0)) as CPM
Although, I would recommend against calculating it in the BQ and would recommend to calculate int in the BI (data studio in this case).