Google Ads to BigQuery: 4 + 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 it 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 4 + 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 toward 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:
- Open Google Ads and navigate to the report you want to download.
- Click Download from the top of the report.
- Select .csv as the format.
Now the report is downloaded, we’ll need to add it to Google BigQuery. To do this:
- Open your Google BigQuery console.
- Choose the dataset which will contain the new report table.
- Click on Create Table from the Dataset menu.
- Select Upload as the source of the table, your file, and CSV as the file format.
- Select your dataset and the table name for the uploaded report.
- Under Auto detect, tick Schema and input parameters. Then click Create Table.
Congratulations! Your report is now loaded in BigQuery and ready for analysis!
This simple manual way is a go if you want to transfer your data from Google Ads to BigQuery one time. However, for recurring dataflow, you’d better connect Google Ads to BigQuery. Let’s take a look at how this can be done.
No-code Google Ads BigQuery integration
Would you like to automate exports of data on a schedule, for example, every hour on Tuesday and Friday? This is essential to automate reporting or building a self-updating analytical dashboard like this one.
You can do this quite easily with the help of the Google Ads BigQuery integration by Coupler.io.
Coupler.io is a data automation and analytics platform that provides an ETL tool for data integration and a customized consulting service to help companies make data-driven decisions.
You can automate exports of different ad information from Google Ads with the following simple steps:
- Sign up to Coupler.io, click Add importer, then choose Google Ads as a source app, and Google BigQuery as a destination app. Click Proceed to set up each of the chosen applications.
Source
- Connect your Google account with access to Google Ads.
- Select the data entity you want to load from Google Ads. You can choose from many options for raw data (Ads, Campaigns, User List, etc.) and reports (Ad Performance, Campaign Performance, etc.).
- Enter your customer ID. If you have multiple IDs, you can enter all of them separated by a comma.
Optional parameters allow you to specify your manager account ID, start/end dates for the exported data, and the conversion window for your report.
Coupler.io also supports data stitching, i.e., you can add multiple sources the data from which will be consolidated in the chosen destination. So, if you want to load and merge data from multiple Google Ads accounts, click Add one more source and configure it in the same way described above.
Now you can proceed to the destination settings.
Destination
- Connect your BigQuery account. For this, you will need to provide the Key file for your BigQuery project. See these instructions on how to get the key.
- Once the BQ account is connected, specify the names of the dataset and table where your Google Ads data will be loaded. You can create new dataset and table by typing new names.
- Toggle on Autodetect table schema – this will help to structure your data correctly while importing.
Schedule
To automate data load from Google Ads, toggle on Automatic data refresh and configure the desired schedule, including frequency, days of week, time zone, and preference.
Click Save and Run to launch the first export of Googe Ads data to Google Bigquery. Once is the import is ready, click View Results to see what the exported Google Ads data in BigQuery:
You can use this raw data to build automated reports or interactive dashboards like the one we featured at the very beginning of this section. If you want to have a similar dashboard or a custom one, check out the data analytics consulting service at Coupler.io. Our data experts will help you with any types of data visualization or more advanced data management tasks.
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:
- Go to Google Cloud platform and open your BigQuery console.
- From the left pane, choose Transfers.
- Select Create a Transfer in the appropriate window.
- In the Source Type field, select the Google Ads (formerly AdWords) option.
- Give a distinctive name to the transfer (e.g., *** Ads Transfer).
- Schedule your transfer by selecting the frequency and start time of the transfer. We suggest running the transfer daily.
- Select (or create) your dataset under which all the tables will be created.
- 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.
- 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.
- Toggle on Email notifications and click 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 data from Google Ads 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.
- Open Google Ads and navigate to the account of preference.
- Click on Tools on the top bar.
- Select Scripts from the Bulk Actions category.
- Click on the + sign to create a new script.
- Give a name to the script (e.g. Google Ads to BigQuery).
- Click on Advanced APIs.
- Enable BigQuery and click Save.
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 4 ways of importing your data to BigQuery effectively and efficiently. As a bonus, we will show you another way to implement this data pipeline using a combination of tools:
- Google Ads add-on for Google Sheets
- Google Sheets to BigQuery integration by Coupler.io
So, basically, we will complete the following steps:
- Query data from Google Ads to Google Sheets using the add-on.
- Synchronize Google Sheets with BigQuery using Coupler.io.
Why would you do this if you have a direct Google Ads to BigQuery integration?
This option may be useful if you manage your reporting and analytics in Google Sheets and BigQuery is mostly used as a backup copy. Or, if you’ve already used Google Ads add-on for Google Sheets for data exports and do not want to abandon it. In this case, you can benefit from both tools and still get your job done.
Query data from Google Ads to Google Sheets
- Install the Google Ads add-on for Google Sheets.
- Query the appropriate data from our Google Ads account.
- 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.
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.
Below you can see how an example campaign performance report will look like:
Now, let’s see how you can load this report directly to BigQuery using Coupler.io.
Synchronize Google Ads to Google BigQuery via Google Sheets
We’ve already introduced Coupler.io and its direct Google Ads BigQuery integration. For this example, we’ll check out its Google Sheets to BigQuery connector that will let you load the Google Ads data from Google Sheets.
Sign up to Coupler.io with your Google account, click Add importer, select Google Sheets as a source app and Google BigQuery as a destination app.
Source
- 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.
Destination
- 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.
Schedule
You’ll need to customize the schedule for your automatic exports of Google Ads data from Google Sheets to BigQuery.
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.
Which option to choose to connect Google Ads to BigQuery?
In this post, we reviewed 4 + 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 need to get your Google Ads data to BigQuert on a recurring basis knowing that the data will be refreshed according to the specified schedule, use Coupler.io
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.
Choose wisely and good luck with your data!
