The article shows the transfer of Google Ads API data to Google BigQuery using custom reporting, which can be configured individually.

Possible transmission routes

You can transfer the Google Ads API data into BigQuery in several ways. One option is to create an ETL (Extract, Transform, Load) process using a third-party ETL tool. These tools extract the data from the Google Ads API, convert it to the right format, and then upload it to BigQuery.

Another way is through 2 existing Google Transfer Services for Google Ads, which already come with BigQuery. Unfortunately, the API version of the service often lags behind here and so you don’t get the latest data types that Google has introduced. Especially with regard to Pmax campaigns and their asset groups, this is currently a limitation that is worth overcoming.

A simple approach to transfer the data to BigQuery is to use an apps script, which can be customized to your own data needs.

Apps Script for transferring Google Ads API data to BigQuery

The script is installed in the Google Apps Script environment and executed daily. During execution, the previously configured tables are taken and queried using the Google Ads API. The data is then transferred to Google BigQuery.

Advantages and disadvantages of this solution

Advantages:

  • Flexible, API innovations can be responded to individually.
  • Data saving: Only configured tables are transferred.
  • Cost-effective, as no additional infrastructure needs to be set up.

Disadvantages:

  • Daily transfer is limited due to API limits.
  • If the data is needed in real time, this solution is not suitable
  • The data is stored in BigQuery as it is delivered by the API. This means that additional reporting must be set up to prepare the data for analysis

Installation

  1. Install script
  2. Set transfer window
  3. Specify data with GAQL Query Builder
  4. Start transmission

Install script

Copy and paste the script from below into a Google Ads script or Apps script. All script configuration takes place in the CONFIG variable. The rest should only be edited if you know what you are doing.

In the script environment, BigQuery must still be hired as an extra service.

Google Ads Script erweiterte APIs

Then the variable of the script must be configured.

Configure variables

In the CONFIG variable all settings are made that the script needs. The variables should be self-explanatory in terms of naming. Otherwise, the comment behind it helps.

Copy to Clipboard

Specify data with GAQL Query Builder

In the CONFIG.REPORTS array API calls can be. Here, each call is an object with the variables as shown in the table and in the example. For building the API calls I strongly recommend to use the Google Ads API Query Builderas it first shows which fields are allowed to be combined at all.

Variable Value
NAME The table name in the Google Ads api‘asset_group_product_group_view‘.
CONDITION Here you can insert a “WHERE” statement
FIELDS The fields required in the report.
RETURN_FIELDS Fields that are sent by the API without a request. Necessary to create the table fields in BigQuery
PARTITION_FIELD Necessary if the segemens_date field is to be used for partitioning. Leave blank in all other fields.

Additionally the fields must be defined with

Copy to Clipboard
Variable Value
FieldName The name of the field as shown in the Google Ads Api.
Format Data types as defined for BigQuery.
Alias FieldName Conversion that works for Google BigQuery as colmun name.

As in the code example for the actually query shows how the fields are used during the API call.

Copy to Clipboard

Here is an example of a fully configured retrieval of ‘ad_group_ad’:

Copy to Clipboard

Start transmission

If all components are configured correctly, the Google Ads Api call can be started.

The complete script for free use:

Here I publish the script as a complete version. I am looking forward to comments, feedback, improvements and of course your extension of the retrieval configurations at (CONFIG.REPORTS).