In BigQuery, automatic data transfer is available for Google Merchant Center data. Unfortunately, this service has the problem that this connector has not been adapted for the Conent API for Shopping v2.1. As a result, the data generated there is incomplete and can only be used to a limited extent.
This article shows how to replace the import function with a Google Apps script, which then transfers the data to BigQuery on a daily basis.
The script is installed and configured as a Google Ads script, so anyone with a Google Ads account can use this solution. The Skript is based on this solution from Google.
Installation in a nutshell
- Open Google Ads account
- Navigate to“Tool > Scripts > +” to create a new script.
- Name the script e.g. “Shopping API Transfer to BigQuery”.
- Copy the script
- Set the variable “TRUNCATE_EXISTING_TABLES = true” so that the table structure is created during the first run.
- Open “Advanced APIs” at the top right and enable “BigQuery” and “Shopping Content”.
- Create dataset in BigQuery (important to define the location and autom. expiration time of the data)
- In the script, fill the fields variable MERCHANT_CENTER_ID, BIGQUERY_PROJECT_ID, BIGQUERY_DATASET_ID, BIGQUERY_TABLE_NAME with your own values.
- Press “Preview” at the bottom right
- Use the yellow button to authorize. (The logged in Google account needs access to BigQuery as well as the Merchant Center)
- Run “Preview” again, so that the script runs the first time.
- Correct the dataset in BigQuery.
- After the first run, TRUNCATE_EXISTING_TABLES should be set to false, otherwise past data will be overwritten.
- Define the time of the script call in the script overview and set it to “daily”.
With the script it is easy to transfer data from GMC to BigQuery. In addition, it is easy to react to changes in the retrieval semantics in the “Fields” variable.