The 7 tables you might ever need from the BigQuery Data Transfer
Author: Balazs V. PH.D
BigQuery Data Transfer
The BigQuery Data Transfer is one of the most valuable services within BigQuery (although it is hard to pick anything as “most valuable”, after all it is an all-around versatile database – BigQuery is evolving fast with new features coming out all the time around data architecture, SQL dialect / functionality and user experience). I’ve posted about what all it offers on a high level previously and examples of useful insights you can get from it.
Google Ads Data Transfer
Let’s zone into the Google Ads Data Transfer this time, as Google Ads is definitely one of the most important platforms for digital marketers, and thereby it is one of the most significant data sources as well. You can set it up very easily in the Data Transfers tab (the Google Ads customer ID is pretty much the only thing you’ll need), and it’s a fully managed service that dumps out practically all the relevant information into a single dataset. No need to know API coding, or writing / downloading / running Google Ads scripts, or using third party connector tools. (The screenshot shows the recommended configuration – of course there are a few options to tweak if you’re an advanced user. We always use it the way as shown here.)
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
- You want to build a cross-channel marketing report (taking simple dimensions and metrics from each platform)
- You have multiple Google Ads accounts and want to combine the data in one place
- Or you just want to apply some further categorization / processing with SQL, or
- 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.
MarketingLens Digital needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at any time. For information on how to unsubscribe, as well as our privacy practices and commitment to protecting your privacy, please review our Privacy Policy.
Making the complex digital landscape simple