Back to Blog

Google Ads to BigQuery: 3 + 1 Ways to Import Your Data

Google Ads is one of the most popular platforms when it comes to online advertising. Businesses of every size use Google Ads to create online ads promoting their products and services to reach as many potential customers as possible. 

Google Ads produces abundant amounts of data daily, and in this post, we will go through 3 + 1 ways to transfer the data from Google Ads to BigQuery in order to have your reports ready for analysis, so let’s get started! 

Why connect Google Ads data to BigQuery

Promoting your business through Google Ads requires a budget, which you need to make sure is utilized optimally. To do this, a lot of analysis needs to take place, and what’s a better tool for it than Google BigQuery?

Analyzing your marketing data will help you identify which efforts are bringing the best results and shift your budget towards them. Moreover, you will be able to identify possible campaigns or specific ads that are underperforming so you can stop them and save some of your budget. Last but not least, by analyzing your marketing data, you can identify what your customers are looking for and plan your business decisions for the future.

How to export Google Ads reports to BigQuery

The easiest and simplest way to load your data to BigQuery is by using CSV files. BigQuery can automatically create a table based on your CSV file schema, so let’s see how we can do this:

  1. Open Google Ads and navigate to the report you want to download.
  2. Click Download from the top of the report.
16 ads report
  1. Select .csv as the format.
17 ads download

Now the report is downloaded, we’ll need to add it to Google BigQuery. To do this:

  1. Open your Google BigQuery console.
  2. Choose the dataset which will contain the new report table.
  3. Click on Create Table from the Dataset menu.
18 bigquery create table
  1. Select Upload as the source of the table, your file, and CSV as the file format.
19 bigquery table source
  1. Select your dataset and the table name for the uploaded report.
20 bigquery table destination
  1. Under Auto detect, tick Schema and input parameters. Then click Create Table.

Congratulations! Your report is now loaded in BigQuery and ready for analysis!

How to transfer Google Ads data to BigQuery using Data Transfer Service

Another way you can transfer your Google Ads data to BigQuery is by using the official BigQuery Data Transfer Service. This useful feature allows you to automatically transfer all your necessary Google Ads data to BigQuery and keep them up to date. Before starting, please make sure you have BigQuery Admin permissions and read access to the appropriate Google Ads account or Google Ads manager account (formerly My Client Center or Google MCC). 

Let’s see how you can set it up:

  1. Go to Google Cloud platform and open your BigQuery console.
  2. From the left pane, choose Transfers.
01 transfers
  1. Select Create a Transfer in the appropriate window.
02 create transfer
  1. In the Source Type field, select the Google Ads (formerly AdWords) option.
03 select source
  1. Give a distinctive name to the transfer (e.g., *** Ads Transfer).
04 transfer name
  1. Schedule your transfer by selecting the frequency and start time of the transfer. We suggest running the transfer daily.
05 transfer schedule
  1. Select (or create) your dataset under which all the tables will be created.
06 transfer dataset
  1. Provide the customer ID of the Google Ads account (e.g., 123-123-1234). The service accepts both standard and manager accounts so if you need multiple accounts in one transfer, you can add the ID of the manager (MCC) account.
07 transfer id
  1. You can add a customized Refresh window (from 0 to 30) which means that on each transfer, the services will import and update the last X days of data. If you leave it blank, the default is set to 7 days.
08 transfer refresh window
  1. Toggle on Email notifications and click Save.
09 transfer save

This export will generate a set of tables and reports containing all the data you’ll need. Moreover, through backfilling, it will keep your data up to date, so you won’t need to do this all over again.

How to send Google Ads data to BigQuery using Google Ads Scripts

While the Data Transfer Service provides a fast and easy way to transfer all your data to Google BigQuery, there are times where you need only specific reports or to manipulate the data before uploading.

In this case, and if you’re familiar with coding, Google Ads scripts can be your best friend. Through custom JavaScript code, you can query, manipulate, and send Google Ads data to BigQuery. Let’s see how you can set up such a report and a code example on how you can query a campaign performance report and send it to Google BigQuery.

  1. Open Google Ads and navigate to the account of preference.
  2. Click on Tools on the top bar.
10 ads tools
  1. Select Scripts from the Bulk Actions category.
11 ads scripts
  1. Click on the + sign to create a new script.
12 ads new script
  1. Give a name to the script (e.g. Google Ads to BigQuery).
13 ads name script
  1. Click on Advanced APIs.
14 ads advanced apis
  1. Enable BigQuery and click Save.
15 ads enable apis

With these settings, Google Ads Scripts will be able to connect directly to both Google Ads reports and BigQuery. Writing your own scripts, you can access and manipulate Google Ads reports and load them to Google BigQuery. 

You can read everything related to Google Ads Script in the official documentation and start building your custom scripts to upload any data you require.

Bonus: How to load data from Adwords to Google BigQuery

We have now presented 3 ways of importing your data to BigQuery effectively and efficiently. As a bonus, we will show you another way to implement this data pipeline.

This is not added along with the other ways as it is based on a Google Ads add-on for Google Sheets and Coupler.io, a third-party app for importing data. What we’re going to do here is:

  • Query data from Google Ads to Google Sheets using the add-on.
  • Synchronize Google Sheets with BigQuery using Coupler.io.

Query data from Google Ads to Google Sheets

  1. Install the Google Ads add-on for Google Sheets.
  2. Query the appropriate data from our Google Ads account.
  3. Have these data stored and regularly updated in this Google Sheet.

The add-on installation is pretty straightforward, and you can do it by visiting the Google Ads add-on page on the Google Workspace Marketplace and clicking on the Install button. After you provide the required permissions, the add-on will be ready to use on the Extensions tab of any Google Sheets spreadsheet.

21 google sheets add on

After you install the add-on, you can create the appropriate report by adding your own configurations and click Create Report to extract your data:

  • Account: The account that will be used as the source of the data
  • Date Range: The date range to query the data (the default is last 7 days).
  • Report Type: The type of report to query (e.g., campaign report or keyword report).
  • Name of the report: Give your own name to this specific report.
  • Columns: All the columns that should be included in the report.
  • Filters: Any filters that you might need in the report (e.g., enabled campaigns or a specific set of keywords).
  • Schedule Reports: Set the report to be refreshed on a daily, weekly, or monthly basis.
22 google sheets create report

Below you can see how an example campaign performance report will look like:

23 google sheets sample report

Now, let’s see how you can load this report directly to BigQuery using Coupler.io.

Synchronize Google Sheets with BigQuery using Coupler.io

Coupler.io is a tool for extracting data from multiple sources and loading it to Google Sheets, Excel, or BigQuery on a schedule. For example, you can use the JSON to BigQuery integration to automate exports of data from a JSON API. 

For now, Coupler.io does not support Google Ads as a source, but you can easily automate data loading from Google Sheets. We’ve blogged about a similar pipeline in Google Analytics to BigQuery.

Sign up to Coupler.io with your Google account, click Add new importer, name it, and complete these three steps:

Source

  • Choose Google Sheets as the source application and connect your Google account.
  • Choose the spreadsheet, as well as the sheet with the data exported from Google Ads. If you want to load data from multiple sheets to merge it into one master view, select those sheets.
  • Optionally, you can specify the range in the selected sheets. Click Jump to Destination Settings.
source

Destination

  • Choose BigQuery as the destination app and connect your BigQuery account. For this, you’ll need to add your .json key file (here is how you can get it). 
  • Enter the names of the BigQuery dataset and table where the Google Ads data from Google Sheets will be loaded.
  • Optionally, you can change the import mode and add a column with the date and time of the last data refresh. 
destination

Schedule

You’ll need to customize the schedule for your automatic exports of Google Ads data from Google Sheets to BigQuery.

9 stackby google sheets schedule

Note: Make sure you synchronize your schedules for exporting data from Google Ads to Google Sheets and from Google Sheets to BigQuery. We recommend you have at least one hour of time buffer between them.

Eventually, click Save and Run, and welcome your Google Ads records in BigQuery, with stop-and-go in Google Sheets. If you wish to have direct integration between Google Ads to BigQuery or some other source, let us know about this by filling out this form

Which option to choose to connect Google Ads to BigQuery?

In this post, we reviewed 3 + 1 ways to extract and load your Google Ads data to BigQuery. But what’s the best way to do it, you may ask? Well, that always depends on your case study.

If you want to extract all of your account(s) data and keep them up-to-date in real-time, then Data Transfer service is the way to go. 

If you are familiar with coding, then Google Ads Scripts is your best friend as it allows you to choose which data you upload and gives you room to manipulate the data before loading them to BigQuery.

Finally, if you’re looking to upload only a specific report for one-time analysis, then you can choose the third way and just export the necessary report and simply do a file upload.

As a bonus, we’ve shown you a way to frequently upload your data to BigQuery using Coupler.io. Using the official Google Sheets add-on, you can update a specific report with the latest data and automatically upload it to BigQuery with ease.

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free