Google Merchant Center Data Transfer to BigQuery (BigQuery Data Transfer Service)

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. Thus, the dataset generated there is incomplete and usable only 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 script is based on this Export Google Ads Reports into BigQuery – Single Account.

Installation in a nutshell

1. Open Google Ads account
2. Navigate to“Tool > Scripts > +” to create a new script.
3. Name the script e.g. “Shopping API Transfer to BigQuery”.
4. Copy the script
5. Set the variable “TRUNCATE_EXISTING_TABLES = true” so that the table structure is created during the first run.
6. Open “Advanced APIs” at the top right and enable “BigQuery” and “Shopping Content”.
7. Create dataset in BigQuery (important to define the location and autom. expiration time of the data)
8. In the script, fill the fields variable MERCHANT_CENTER_ID, BIGQUERY_PROJECT_ID, BIGQUERY_DATASET_ID, BIGQUERY_TABLE_NAME with your own values.
9. Press “Preview” at the bottom right
10. Use the yellow button to authorize. (The logged in Google account needs access to BigQuery as well as the Merchant Center)
11. Run “Preview” again, so that the script runs the first time.
12. Correct the dataset in BigQuery.
13. After the first run, TRUNCATE_EXISTING_TABLES should be set to false, otherwise past data will be overwritten.
14. 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.