The 7 tables you might ever need from the BigQuery Data Transfer.

It has one “feature” that might be off-putting at first: the fact that it creates approximately 120 tables (and an equal number of views), making it a bit hard to find one’s way around it and perhaps get started with some meaty analysis. Though there is an option to specify only the tables you need, or exclude certain tables, our recommendation is to bring everything (definitely including “new” and PMax tables, but we also tend to bring the empty ones). Here is the good news though: you might get far enough by just looking at 7 tables and ignoring the rest for now.

3 statistics tables:

• ads_AdBasicStats_<customerid>
• ads_AdGroupBasicStats_<customerid>
• ads_CampaignBasicStats_<customerid>

These are – as their name suspiciously implies – reporting tables with the most common dimensions and metrics – impressions, clicks, conversions and costs, broken down by the most important segmenting dimensions such as date and ad network type (and a few others). Using SQL you can combine them into a single table which goes down to ad level information wherever possible, but providing adgroup and campaign level info if it is not (e.g. PMax campaigns).

3 reference tables:

• ads_Ad_<customerid>
• ads_AdGroup_<customerid>
• ads_Campaign_<customerid>

These contain the most important information and configuration details about these different hierarchy levels. They’d warrant their own post, but one of the most important attribute they provide are the *names* of campaigns, ad groups and ads – that too in a historical manner i.e. you can look back on campaign name changes in the past, which helps joining to other data sources like web analytics.

Then, a sort of transactional level table: ads_ClickStats_<customerid>. This holds every single click ID (gclid) that has ever occurred, and contains the information on which exact item in Google Ads provided this. It is an excellent data enrichment tool for Google Analytics data, so that you don’t have to rely so much on the traffic source columns in GA4 itself. It has a good couple of other uses too which I’ll detail in a future post.

These are relatively simple tables, you might get all this info by just exploring a Google Ads data source in Looker Studio, they can be very useful if

1. You want to build a cross-channel marketing report (taking simple dimensions and metrics from each platform)
2. You have multiple Google Ads accounts and want to combine the data in one place
3. Or you just want to apply some further categorization / processing with SQL, or
4. you want to get started with easier stuff before descending into the depths of this gold mine.

Follow MarketingLens on LinkedIn or get in touch if you would like to learn more about extracting value from Google Ads data in BigQuery.

Making the complex digital landscape simple