How to set up the Google Ads data transfer to BigQuery

This tutorial will show you how to activate the Google Ads data transfer to BigQuery. The availability of granular campaign performance data in the same environment as your GA4 event data, opens up a world of possibilities.

Other reasons to activate the Google Ads Data Transfer

• Having your Google Ads data in BigQuery allows you to perform deeper and more customized analysis than what is available within Google Ads.
• Being in BigQuery means you can easily combine Google Ads data with other datasets (not limited to GA4). This helps in attributing conversions and understanding the customer journey better.
• Use GA4 event data to create highly tailored audience segments. This can be useful for retargeting or lookalike campaigns in Google Ads.
• While Google Ads data will give you cost metrics, GA4 event data can offer revenue or value metrics. Combining these can provide a clearer picture of ROI.

Requirements

• An active Google Ads (MCC) account with campaign data (you'll need a customer id later on).
• An active Google Cloud account with billing enabled.
• A BigQuery project and data set (see step 1) where you'd like to transfer your Google Ads data.
• Necessary permissions to both Google Ads and BigQuery for linking and data transfer.
• See documentation for all requirement details.

Step 1: Create a data set in BigQuery

Go to the BigQuery Console. Click on the three dots next to your project name in the side bar and choose Create data set. Name the data set and configure it according to your needs. Make sure the data set is in the same (multi)region as your GA4 event data (e.g. multiregion US or EU).

Step 2: Configure Data Transfer

Go back to Google Cloud Console. Navigate in the left side bar to BigQuery > Data transfers.

Click on Create transfer.

If this is your first transfer, enable the Data Transfer API.

Then refresh and choose Google Ads as the source.

Now configure the transfer settings. Display name can be anything. Schedule frequency can be hours, days, weeks or months. Pick your newly created data set and insert your Google Ads customer id (or MMC id).

Select Include PMax Campaign Tables if you run Performance Max campaigns. Refresh window is set to 7 days by default and can be extended to 30 days. Enable email notifications if desired, and click Save.

Step 3: Backfill transfer

The transfer is scheduled for the next morning.

To run the transfer manually click Schedule backfill.

Run a one time transfer or let the transfer run for your historical data by setting a start and end date.

For previous dates it will run a transfer every half an hour, so backfilling a longer period can take hours, days or even weeks. If you do a one time transfer it should be finished in a couple of minutes.

Step 4: First query

Run your first query (sample queries). Replace <insert customer_id> two times:

select
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  sum(cs.metrics_impressions) as impressions,
  sum(cs.metrics_interactions) as interactions,
  (sum(cs.metrics_cost_micros) / 1000000) as cost
from
  `google_ads.ads_Campaign_<insert customer_id>` c
left join
  `google_ads.ads_CampaignBasicStats_<insert customer_id>` cs
on
  (c.campaign_id = cs.campaign_id
  and cs._data_date between
  date_add(current_date(), interval -31 day) and date_add(current_date(), interval -1 day))
where
  c._data_date = c._latest_date
group by
  1, 2, 3
order by
  impressions desc

Step 5: Explore other schemas and reports

The Google Ads Data Transfer delivers a lot of partitioned tables and views. Luckily there is a lot of information available on how to map these with the Google Ads reports you are probably familiar with.

Google Ads report transformation | BigQuery | Google Cloud

Google Cloud

Now it's your turn!

I hope you've enjoyed this tutorial and feel a bit more confident to utilise your own Google Ads data in BigQuery. Drop a line in the comments if you have any questions, feedback or suggestions related to this article.