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!

Tag:google-ads-api, google-bigquery

Only one comment.

  1. jerego

    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).

Add a new comment.