How to Transfer Google Ads Data into Google BigQuery
As ad platforms have automated many of the manual tasks digital advertisers once did, the average PPC professional’s role is evolving. Once heavily tactical, now many PPC pros find themselves taking on more data strategist and analyst work to drive the best performance for their clients.
To work with the complex models and algorithms fueling the ad platforms, PPC advertisers must be able to look at large quantities of data, analyze it for trends and insights, and then feed that data back into the software. The key to making all of this work is having the necessary data available at the PPC advertiser’s fingertips.
It wasn’t that long ago that to get the data they needed, digital advertisers had to aggregate performance metrics manually, purchase third-party software, or deploy complex, custom in-house tools (requiring IT buy-in, engineering resources, API integrations, physical servers, and database architectures). But now, cloud-based solutions have made it infinitely easier and cheaper for digital advertisers to generate PPC data warehouses to access, analyze, and report on this data at scale.
Created and designed by Google, BigQuery is a great data warehousing option for PPC agencies.
BigQuery is a powerful piece of the modern PPC marketer’s toolkit. Since Google dominates the search engine/search advertising market in the US, we wanted to teach advertisers three options for transferring Google Ads data into BigQuery:
- 1. Google Ads Transfer Service
- 2. Data Pipelines and Connectors
- 3. Shape’s Advertising Data Infrastructure
Using Google Ads Transfer Service to Get Google Ads Data into BigQuery
Google is doing everything in its power to make getting Google Ads data into Google BigQuery simple and affordable. The Google Cloud Platform has a direct Google Ads Transfer service that can automatically import new data daily from individual accounts or manager accounts. Upon spin up, Google PPC data is automatically populated for the previous seven days and can be backfilled further.
Around 80 unique data views provide highly-segmented PPC data for advertisers to analyze covering everything from campaign data to parental status stats. But while transferring Google Ads data to Google BigQuery is free, a PPC advertiser may be on the hook for costs accumulated if they exceed Google’s data querying and storage thresholds.
Directions:
1. Generate a Google Cloud Project (free trial available).
2. You’ll be taken to a “Basic Checklist.” You’ve already completed steps 1 and 2. Click step 3: find “Product + API.” This takes you to Google Cloud Platform Marketplace.
3. Search/select “Google Ads Transfers.” Click Enroll.
4. You’ll be taken through the steps to configure the Google Ads data transfer for the project you just created. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
5. Set Source = “Google Ads (formerly AdWords)"
6. Set Transfer Config Name = Name this something that indicates the job being run. (Ex: Shape Google Ads Transfer)
7. Set Schedule Options.
- a. Select what time you’d like data refreshed (Start Now or Start at set time)
- b. Select if you want data refreshed on a schedule or on-demand
- c. Select the start date and run time when data will be refreshed
8. Enter a Dataset ID = Choose (or create) a name for your dataset displayed in BigQuery.
- a. If creating a new Dataset ID, follow the steps on the “Create Dataset” form to add an ID name, data location, table expiration time, and encryption key. Give a clear indication of what’s included in the dataset (ex: Company Name Google Ads).
9. Enter Data Source Details - Customer ID = Enter a single 10-digit Google Ads account ID or a Managed Account ID.
10. Select Exclude Removed/Disabled Items = Check this if you do not wish to transfer/store disabled items, metrics, etc.
11. Set a Refresh Window = Data will be pulled for the last 7 days and can be configured to pull up to 30 days.
12. Optional:
- a. Choose to be notified when a transfer has errors or fails to run
- b. Select a Cloud Pub/Sub topic
13. Click “Save.” Your data warehouse will now begin to populate with data for the Refresh Window you set.
14. Navigate to Google BigQuery and click your Dataset ID.
15. You can now start writing SQL queries against 80+ data views in Google BigQuery, or export your data to Google Data Studio and other third-party tools for further analysis.
For more detail, Google offers support documentation for the Google Cloud Platform and Google Ads Transfer.
Using Data Pipelines/Connectors to Get Google Ads Data into BigQuery
Another alternative to populate a Google BigQuery data warehouse with Google Ads data is to use a third-party data pipeline or connector. There are a number of solutions that exist such as Supermetrics, Funnel, and Improvado. With each of these solutions, the advertiser is responsible for launching, managing, and paying for a data warehouse. The connectors/pipelines transfer Google Ads data to the data warehouse on a regular basis. Depending on the solution, pricing may fluctuate based on the number of Google Ads accounts and/or data sources linked, the frequency or size of data transfers, and the level of support needed.
Although we can’t cover spin up directions for each connector individually, we’ve selected SuperMetrics for BigQuery given that Supermetrics is used by many PPC advertisers.
Directions:
1. Sign up for a free trial of Supermetrics for BigQuery (note: there is a free trial but access may not be granted immediately).
2. Add your Google Ads data sources to Supermetrics.
3. Generate a Google Cloud Project (free trial available).
4. You’ll be taken to a “Basic Checklist.” You’ve already completed steps 1 and 2. Click step 3: find “Product + API.” This takes you to the Google Cloud Platform Marketplace.
5. Search/select “Google Ads by Supermetrics.” Click Enroll.
6. Select the name of the project you generated in steps 3 & 4. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
7. Navigate to BigQuery and click your project name on the left-hand navigation.
8. Click “Create Dataset.” Fill out a dataset ID and location as needed. Save.
9. Using the left-hand navigation again, click Transfers. Select +Create Transfer.
10. You’ll be asked to configure the “Google Ads by Supermetrics” transfer using a similar form outlined in step 4 of the Google Ads Transfer directions.
- a. Note: Set the Source to “Google Ads by Supermetrics” and enter all additional settings as desired.
11. Under the “Third-Party Connection” section, click “Connect Source”. Follow steps to authorize Supermetrics as a third-party connector.
12. Using the Supermetrics pop up modal, sign in to Supermetrics for BigQuery. Authorize the Google Ads login as needed.
13. Select the ad accounts you wish to transfer data from and submit.
14. Your data warehouse will now begin to populate with data. Once the data has successfully been run, you can navigate back to BigQuery to access your data.
15. IMPORTANT: To backfill historical data, you’ll need to follow additional steps outlined in Supermetrics support documentation.
Using Shape’s Advertising Data Infrastructure to get Google Ads Data into BigQuery
We designed the Shape ADI to be the fastest and easiest way for digital advertisers to generate and populate a new BigQuery data warehouse with Google Ads data. Shape manages the Google Cloud Platform and dataset spinups, the data transfer, and even backfills data for 30 days (more can be requested).
Everyone who spins up an ADI gets access to five campaign-level data views for free, and Shape handles the cost of data storage and queries. If advertisers want to enable premium views, pricing scales based on the number of views enabled (versus based on total data sources or ad accounts which can get expensive fast for agencies).
Directions:
2. Add your Google Ads data sources during the onboarding process.
- a. Optional: Create Clients and Budgets in Shape. You can combine campaigns into unique groupings to track spend and metrics in whichever combinations you choose.
3. On the main dashboard, click ADI. On the ADI page, click “Enable Warehouse.”
4. Shape will automatically generate a BigQuery data warehouse for you and enable five always-free campaign-level data views.
5. Once your data warehouse has been generated and 30-days worth of data backfilled, you’ll be taken to the Data Warehouse that lists all available data views.
6. Select a View Name to open that view directly in BigQuery OR click the “Open in BigQuery” to access your entire BigQuery data warehouse.
7. Your data warehouse is already populated and backfilled with data. You can query your data in BigQuery or export it to tools such as Google Data Studio and other third-party tools.
8. Optional: If you want to enable premium views, click the checkbox next to a premium view. Click the pencil that appears at the top of the table.
- a. On the “Edit View Status” screen, change the Status to “Enabled.”
For additional detail on Shape’s ADI including data warehouse support, check out Shape’s Help Center.