Integration of Google Merchant Center with BigQuery

Learn how to connect Google Merchant Center to Google BigQuery using WitCloud. Collect a daily snapshot of your products, their approval status and price competitiveness, and analyze your shopping feed.

Updated

Introduction

This module integrates with the Google Merchant Center API and stores two sets of data in Google Cloud BigQuery: your products (attributes, approval status per country and item-level issues) and the price competitiveness report (your price compared with the benchmark price). Use them to analyze and optimize your shopping feed.

Configuration guide

Follow the steps below to connect Google Merchant Center with BigQuery. Once the setup is complete, your data will be transferred to your BigQuery project automatically.

Authorize with a Google account that has access to the Merchant Center accounts you want to export — WitCloud lists the accounts that Google account can reach.

Module setup

Go to Integrations in the menu and click Add integration.

Integrations list with the Add integration button

Select Merchant Center from the list of available integrations.

Integration catalog filtered to Google Merchant Center

Enter the Collect name, e.g. Merchant Center PL.

Collect name field filled in with the example name Merchant Center PL

Authorization

If you have already authorized Google Merchant Center, pick that token from the Select Merchant Center authorization drop-down and skip the Google sign-in. The drop-down lists only Google tokens that were granted access to Merchant Center.

Click Authorize.

Authorize button next to the Select Merchant Center authorization field

In the Google window, choose the Google account you want to use and allow the permissions WitCloud asks for: managing your product listings and accounts for Google Shopping, and viewing your basic profile information. Back in WitCloud, the new token is selected for you.

Accounts and advanced settings

Open the Select accounts drop-down and choose the Merchant Center accounts to export. Sub-accounts of an aggregator (MCA) account are listed as well. Each account is shown as its name followed by its ID in brackets.

Select accounts drop-down open with example Merchant Center accounts

You can select several accounts at once. Each selected account becomes a separate data collect named after the Collect name, a dash and the account (for example "Merchant Center PL - Demo Store PL (100000001)"), with its own tables in BigQuery.

Select accounts field with two example Merchant Center accounts selected

Optionally, click Show advanced settings:

  • Tables to collect — all tables are selected by default (Products and Price Competitiveness); remove the ones you do not need. At least one table is required.
  • Data freshness (hours) — how often, in hours, the data is refreshed. The default is 4, the minimum is 4 and the maximum is 12. Manual re-runs within that window are skipped.

Advanced settings with the Tables to collect and Data freshness (hours) fields

Click Create to complete the configuration.

Collect settings filled in with the Create button

Done! Your Google Merchant Center data will now be synchronized with BigQuery.

Data synchronization

Google Merchant Center data is a daily current-state snapshot of your products, so there is no historical data step: each run replaces that day's data with the latest state, and the history builds up day by day from the moment you create the collect.

A data synchronization schedule is created automatically and runs once per day. More information on this topic can be found here.

Data schema

Each account has its own tables: the table name ends with the ID of the data collect, e.g. gmc_products_<collect id> and gmc_price_competitiveness_<collect id>. Expand the tables below to view the full data schema collected from Google Merchant Center.

Data schema for gmc_products
Field NameModeType
nameNULLABLESTRING
offerIdNULLABLESTRING
contentLanguageNULLABLESTRING
feedLabelNULLABLESTRING
dataSourceNULLABLESTRING
productAttributesNULLABLERECORD
productAttributes.titleNULLABLESTRING
productAttributes.descriptionNULLABLESTRING
productAttributes.linkNULLABLESTRING
productAttributes.imageLinkNULLABLESTRING
productAttributes.additionalImageLinksREPEATEDSTRING
productAttributes.priceNULLABLERECORD
productAttributes.price.amountMicrosNULLABLEINTEGER
productAttributes.price.currencyCodeNULLABLESTRING
productAttributes.salePriceNULLABLERECORD
productAttributes.salePrice.amountMicrosNULLABLEINTEGER
productAttributes.salePrice.currencyCodeNULLABLESTRING
productAttributes.salePriceEffectiveDateNULLABLERECORD
productAttributes.salePriceEffectiveDate.startTimeNULLABLETIMESTAMP
productAttributes.salePriceEffectiveDate.endTimeNULLABLETIMESTAMP
productAttributes.costOfGoodsSoldNULLABLERECORD
productAttributes.costOfGoodsSold.amountMicrosNULLABLEINTEGER
productAttributes.costOfGoodsSold.currencyCodeNULLABLESTRING
productAttributes.availabilityNULLABLESTRING
productAttributes.availabilityDateNULLABLETIMESTAMP
productAttributes.expirationDateNULLABLETIMESTAMP
productAttributes.conditionNULLABLESTRING
productAttributes.brandNULLABLESTRING
productAttributes.gtinsREPEATEDSTRING
productAttributes.mpnNULLABLESTRING
productAttributes.googleProductCategoryNULLABLESTRING
productAttributes.productTypesREPEATEDSTRING
productAttributes.itemGroupIdNULLABLESTRING
productAttributes.customLabel0NULLABLESTRING
productAttributes.customLabel1NULLABLESTRING
productAttributes.customLabel2NULLABLESTRING
productAttributes.customLabel3NULLABLESTRING
productAttributes.customLabel4NULLABLESTRING
productAttributes.colorNULLABLESTRING
productAttributes.sizeNULLABLESTRING
productAttributes.sizeSystemNULLABLESTRING
productAttributes.sizeTypesREPEATEDSTRING
productAttributes.genderNULLABLESTRING
productAttributes.ageGroupNULLABLESTRING
productAttributes.materialNULLABLESTRING
productAttributes.patternNULLABLESTRING
productAttributes.promotionIdsREPEATEDSTRING
productAttributes.shippingLabelNULLABLESTRING
productAttributes.multipackNULLABLEINTEGER
productAttributes.isBundleNULLABLEBOOLEAN
productAttributes.identifierExistsNULLABLEBOOLEAN
customAttributesREPEATEDRECORD
customAttributes.nameNULLABLESTRING
customAttributes.valueNULLABLESTRING
productStatusNULLABLERECORD
productStatus.creationDateNULLABLETIMESTAMP
productStatus.lastUpdateDateNULLABLETIMESTAMP
productStatus.googleExpirationDateNULLABLETIMESTAMP
productStatus.destinationStatusesREPEATEDRECORD
productStatus.destinationStatuses.reportingContextNULLABLESTRING
productStatus.destinationStatuses.approvedCountriesREPEATEDSTRING
productStatus.destinationStatuses.disapprovedCountriesREPEATEDSTRING
productStatus.destinationStatuses.pendingCountriesREPEATEDSTRING
productStatus.itemLevelIssuesREPEATEDRECORD
productStatus.itemLevelIssues.codeNULLABLESTRING
productStatus.itemLevelIssues.severityNULLABLESTRING
productStatus.itemLevelIssues.resolutionNULLABLESTRING
productStatus.itemLevelIssues.attributeNULLABLESTRING
productStatus.itemLevelIssues.reportingContextNULLABLESTRING
productStatus.itemLevelIssues.descriptionNULLABLESTRING
productStatus.itemLevelIssues.detailNULLABLESTRING
productStatus.itemLevelIssues.documentationNULLABLESTRING
productStatus.itemLevelIssues.applicableCountriesREPEATEDSTRING
Data schema for gmc_price_competitiveness
Field NameModeType
idNULLABLESTRING
offerIdNULLABLESTRING
titleNULLABLESTRING
brandNULLABLESTRING
reportCountryCodeNULLABLESTRING
categoryL1NULLABLESTRING
categoryL2NULLABLESTRING
categoryL3NULLABLESTRING
categoryL4NULLABLESTRING
categoryL5NULLABLESTRING
productTypeL1NULLABLESTRING
productTypeL2NULLABLESTRING
productTypeL3NULLABLESTRING
productTypeL4NULLABLESTRING
productTypeL5NULLABLESTRING
priceNULLABLERECORD
price.amountMicrosNULLABLEINTEGER
price.currencyCodeNULLABLESTRING
benchmarkPriceNULLABLERECORD
benchmarkPrice.amountMicrosNULLABLEINTEGER
benchmarkPrice.currencyCodeNULLABLESTRING