Google Ads' 'Invalid clicks' KPI in Fivetran data model

[dmitryungurean]

Hi folks. Trying to migrate some of existing, legacy Google Ads reporting to Fivetran + Bigquery.

Synced all the data from Google Ads into Bigquery using Fivetran.

Found all the KPIs that I need in [account_stats] table ... all except 'Invalid clicks'. Any help will be appreciated. Thanks.

SELECT

  date as `Day`,

  sum(clicks) as `Clicks`,

  sum(impressions) as `Impressions`,

  sum(clicks)/sum(impressions) as `CTR`,

  sum(cost_micros)/1000000/sum(clicks) as `Avg_CPC`,

  sum(cost_micros)/1000000 as `Cost_Spend`,

  sum(cost_micros)/sum(impressions)/1000 as `Avg_CPM`

FROM `my_project.my_dataset.account_stats`

GROUP BY `date`;

Day - OK
Clicks - OK
Impressions - OK
CTR - OK
Avg. CPC  - OK
Cost - OK
Invalid clicks - cannof find
Invalid click rate - cannot fine

[dipenranpara · Accepted solution]

Hi @dmitryungurean 

Thanks for reaching out!

I understand you are unable to find "invalid clicks" and "invalid click rate" metrics in Google Ads connector's "Account_stats" table. Ideally, we don't sync those metrics in this table as it's a prebuilt table and has limited fields. In order to fetch those metrics, you'll have to create a custom report.

Basically, we fetch "Account_stats" from the "customer" type of report from Google Ads API. The available fields in that report are mentioned here.

Steps to be followed:

1. Go to Fivetran connector -> setup -> Edit connection details
2. Click on "+ Add custom report"
3. Give destination table name and select "customer" under "Report name"
4. Add all the required metrics (similar to fields available in "Account_stats" table) along with "metrics.invalid_clicks" and "metrics.invalid_click_rate" like the below screenshot.

Hope this helps 🙂

[dmitryungurean]

Hi @dipenranpara. Wow, you saved me ... thanks a lot 🙏