Connecting Merchant Center to BigQuery

This article provides an overview of how to integrate Google Merchant Center and BigQuery. Specifically, the article provides background on Merchant Center and information on how it is automatically integrated with BigQuery, along with information about the data transferred between the two systems. In addition, the article discusses several advanced use cases using the transferred data.

About Merchant Center

Merchant Center is a tool that makes your product data available to Google Shopping and other Google services so that you can reach millions of consumers while they are actively shopping on Google, YouTube, and across the web. Through Merchant Center, you can upload your store, brand, and product data so shoppers can see your online and in-store inventory. You can edit that data whenever you want, so that the right information appears in your ads.

Merchant Center also contains several reports to help optimize your performance on Google. The following are examples of useful reports:

  • Product data feed diagnostics to help you maintain data quality by identifying and resolving any issues with your local product and inventory data.
  • Product suggestions to help you make decisions about new products to stock and how to price them, for example, by showing you the most popular products on Google Shopping that you aren't advertising.

Connecting Merchant Center and BigQuery

There are multiple approaches, from automated to manual, for importing product data from Merchant Center into BigQuery. Which approach you take depends on how often you need product data refreshed and whether you want to maintain your own integration between Merchant Center and BigQuery.

Automated integration

Using BigQuery Data Transfer Service, you can regularly and automatically import product data from Merchant Center into BigQuery. The imported data contains fields such as product title, price, and product image links. For a full listing of all product data fields, see the Merchant Center Product data specification.

The following diagram shows the flow of product data from merchants to BigQuery through BigQuery Data Transfer Service.

product data flow from merchants to BigQuery

The diagram shows how merchants work with partners to upload product feeds into Merchant Center. Google Shopping processes that data, which is used to serve ads. BigQuery Data Transfer Service periodically retrieves data from the internal Google Shopping storage system that powers Merchant Center, and transfers product data directly into BigQuery. The automated integration between Merchant Center and BigQuery is available as a private alpha service. To set up and configure BigQuery Data Transfer Service to import data from Merchant Center into BigQuery, work with your Google Cloud account manager or contact sales.

Manual integration

To manually integrate Merchant Center and BigQuery, you create a custom application that periodically reads product data from Merchant Center and writes that data directly into BigQuery. Your custom application reads product data from Merchant Center by using the Shopping Content API, and writes that data into BigQuery. In Google Cloud, you deploy custom applications by using App Engine, Google Kubernetes Engine (GKE), or Compute Engine. For more information about which platform to use, see Choosing a Compute Option.

To periodically access the Content API, you need a scheduling mechanism capable of starting your application. For App Engine applications, use the App Engine Cron Service to configure scheduled execution of application methods at regular intervals. For GKE or Compute Engine applications, use either Cloud Composer or a built-in operating system scheduling service such as cron to execute your application at regular intervals. When you use this latter approach, your custom application is responsible for managing any issues related to Content API quotas, retries, or errors in retrieving product data.

Use cases

After importing product data into BigQuery, you can use that data in several ways in your applications and systems. The following use cases are examples of applications or integrations that you can build, using the product data from Merchant Center.

Product feed diagnostics and troubleshooting

When you use BigQuery Data Transfer Service with Merchant Center, diagnostic information about your product feed can be transferred along with product data. To enable product feed diagnostics, work with your Google Cloud account manager or contact sales.

You can use this diagnostic information to troubleshoot common errors such as missing or invalid attributes from your online product feed, local product feed, or local inventory feed. You can transform the product data in BigQuery by using standard SQL queries.

Next, you can follow the instructions to connect Merchant Center and Cloud Storage, as documented in Submit feeds via Cloud Storage. Finally, you can export the updated BigQuery table as a CSV file and store it in the Cloud Storage bucket you set up through Merchant Center. You can use Dataprep by Trifacta or Cloud Composer to automate the feed update workflow. Another approach is to directly access the Google Shopping Content API from a custom application to periodically update your product feed data.

The following diagram shows an architecture for handling product feed diagnostics.

handling product feed diagnostics

Product data APIs

Using the product data transferred into BigQuery, you can build microservices or APIs to provide product metadata or pricing information for other applications (such as online stores or mobile apps). You can use these APIs as part of a larger online store microservices deployment or with an off-the-shelf commerce platform such as SAP Hybris.

To build a product or pricing data service, you first use a tool such as Dataflow to extract the product data from BigQuery, apply any necessary data transformations or filtering, and load the data into Firestore, Cloud Spanner, or Cloud SQL. These services provide transactional guarantees and are suitable for storing product and pricing data. For more information about choosing among Firestore, Spanner, and Cloud SQL, see Choosing a storage option.

After loading product data into a storage system, you can use containers and GKE to build and deploy your product and pricing microservices. Services running in GKE can then be used as APIs in larger microservices deployments or with mobile apps by using Cloud Endpoints. Endpoints provides several built-in tools to manage APIs, such as authentication, monitoring, and other deployment tools. The following diagram shows the infrastructure of such a system.

product and pricing microsservices

Digital shopping assistant

In addition to building product and pricing APIs, you can use the product data transferred from Merchant Center to create digital shopping assistants powered by Dialogflow. Dialogflow provides a suite of tools to build and manage natural conversational experiences. You can deploy these experiences to a number of consumer-facing products, and integrate them into existing web-based and mobile applications. Shopping assistants created using Dialogflow can provide product and pricing search capabilities to consumers across different devices and applications, including Google Assistant.

As with the product API use case, you can extract, transform, and load product data in BigQuery to a Google Cloud storage system like Firestore that supports real-time transactional workloads. For more information, see Choosing a storage option.

The next step is to use Dialogflow intents and entities to create the conversational interaction structure that you want. Intents in Dialogflow provide the structure of the interaction, and fulfillment is handled by Cloud Functions or by Cloud Functions for Firebase. The fulfillment function pulls data from the specified storage system and supplies it back to Dialogflow as part of the conversational interaction.

The following diagram shows the architecture of this digital shopping assistant.

digital shopping assistant

For more information, refer to the Dialogflow getting started guide.

What's next