Google Merchant Center transfers

Beta

This product is in a pre-release state and might change or have limited support. For more information, see the product launch stages.

The BigQuery Data Transfer Service for Google Merchant Center allows you to automatically schedule and manage recurring load jobs for Google Merchant Center reporting data.

Limitations

Merchant Center transfers are subject to the following limitations:

• After creating a transfer for your Merchant ID, you should wait up to 90 minutes before your Merchant account data is prepared and available for transfer.
• You cannot configure a Maximum backfill duration. Historical backfills are currently unsupported. You can set up a backfill only for today.
• Products and product issues data in BigQuery do not represent the real-time view of your Merchant account. The data is transferred in batch mode and can be up to 15 hours behind. To see the freshness of your data in BigQuery, view the "Data freshness" timestamp on the Products table's Details tab.

Supported reports

The BigQuery Data Transfer Service for Google Merchant Center currently supports the following data:

• Products and Product Issues

  This includes product data provided to Merchant Center through feeds or via the Content API for Shopping. It also includes item level issues detected by Google for your products. This data can be viewed in the Google Merchant Center or by querying the Content API for Shopping.

For information on how Merchant Center reporting data is loaded into BigQuery, see Google Merchant Center table schema.

The BigQuery Data Transfer Service for Google Merchant Center currently supports the following reporting options:

Reporting option	Support
Schedule	Daily, at the time the transfer is first created (default). You can configure the time of day.

Multi-client account (MCA) support

Existing customers with multiple Merchant IDs are encouraged to configure a parent Multi-Client Account (MCA). Configuring an MCA allows you to create a single transfer for all your Merchant IDs.

Using Google Merchant Center MCAs provides several benefits over using individual Merchant IDs:

• You no longer need to manage multiple transfers to transfer reporting data for multiple Merchant IDs.
• Queries involving multiple Merchant IDs are much simpler to write because all Merchant ID data is stored in the same table.
• Using MCAs alleviates potential BigQuery load job quota issues because all your Merchant ID data is loaded in the same job.

One possible disadvantage of using MCAs is that your subsequent query costs are likely to be higher. Because all of your data is stored in the same table, queries that retrieve data for an individual Merchant ID must still scan the entire table.

Note: The BigQuery Data Transfer Service pulls reports for all listed Merchant IDs. If there are no products in Google Shopping for a specific day, you may not see Merchant IDs in the BigQuery table.

If you are using an MCA, the MCA ID is listed under aggregator_id and the individual sub-accounts are listed under merchant_id. For accounts that do not use an MCA, aggregator_id is set to null.

Before you begin

Before you create a Google Merchant Center transfer:

• Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
• Create a BigQuery dataset to store the Google Merchant Center data.
• If you intend to create transfers using the classic BigQuery web UI, allow pop-ups from bigquery.cloud.google.com so you can view the permissions window. You must allow the BigQuery Data Transfer Service for Google Merchant Center permission to manage your product listings and accounts in Google Shopping.
• If you intend to setup transfer run notifications for Pub/Sub, you must have pubsub.topics.setIamPolicy permissions. Pub/Sub permissions are not required if you just set up email notifications. For more information, see BigQuery Data Transfer Service Run Notifications.

Required permissions

Ensure that the person creating the transfer has the following required permissions:

• BigQuery:

  • bigquery.transfers.update permissions to create the transfer
  • bigquery.datasets.update permissions on the target dataset

  The bigquery.admin predefined Cloud IAM role includes bigquery.transfers.update and bigquery.datasets.update permissions. For more information on Cloud IAM roles in BigQuery Data Transfer Service, see Access control reference.

• Google Merchant Center: Access to the Google Merchant Center account that is used in the transfer configuration. You can verify access by clicking the Users section in the Google Merchant Center UI.

Setting up a Google Merchant Center transfer

Setting up a data transfer for Google Merchant Center reporting requires a:

• Merchant ID or Multi-client account ID: This is the Merchant ID shown in the upper left section of the Google Merchant Center UI.

To create a data transfer for Google Merchant Center reporting:

Console

1. Go to the BigQuery web UI in the Cloud Console.

   Go to the Cloud Console

2. Click Transfers.

3. Click Create Transfer.

4. On the Create Transfer page:

   • In the Source type section, for Source, choose Google Merchant Center.

   • In the Transfer config name section, for Display name, enter a name for the transfer such as My Transfer. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.

     

   • In the Schedule options section, for Schedule, click Start at a set time.

     • For Repeats, choose an option for how often to run the transfer.

       • Daily (default)
       • Weekly
       • Monthly
       • Custom
       • On-demand

     • For Start date and run time, enter the date and time to start the transfer. This value should be at least 90 minutes from the current UTC time. If you chose Start now, this option is disabled.

       

       If you leave the schedule options set to Start now, the first transfer run will start immediately, and it will fail with the following error message: No data to transfer found for the Merchant account. If you have just created this transfer, you may need to wait for up to 90 minutes before the data of your Merchant account are prepared and available for the transfer. If this occurs, wait for 90 minutes and then set up a backfill for today or wait until tomorrow for the next scheduled run.

   • In the Destination settings section, for Destination dataset, choose the dataset you created to store your data.

     

   • In the Data source details section, for Merchant ID, enter your Merchant ID or MCA ID.

     

   • (Optional) In the Notification options section:

     • Click the toggle to enable email notifications. When you enable this option, the transfer administrator receives an email notification when a transfer run fails.
     • For Select a Pub/Sub topic, choose your topic name or click Create a topic. This option configures Pub/Sub run notifications for your transfer.

5. Click Save.

Classic UI

1. Go to the classic BigQuery web UI.

   Go to the classic BigQuery web UI

2. Click Transfers.

3. Click Add Transfer.

4. On the New Transfer page:

   • For Source, choose Google Merchant Center.
   • For Display name, enter a name for the transfer such as My Transfer. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.

   • For Schedule, click Edit and change the Starting (UTC) time to at least 90 minutes from the current time.

     

     If you leave the default scheduling settings, the first transfer run will start immediately, and it will fail with the following error message: No data to transfer found for the Merchant account. If you have just created this transfer, you may need to wait for up to 90 minutes before the data of your Merchant account are prepared and available for the transfer. If this occurs, wait for 90 minutes and then set up a backfill for today or wait until tomorrow for the next scheduled run.

   • For Destination dataset, choose the dataset you created to store your Google Merchant Center data.

   • For Merchant ID, enter your Merchant ID or MCA account ID.

     

   • (Optional) Expand the Advanced section and configure run notifications for your transfer.

     • For Pub/Sub topic, enter your topic name, for example, projects/myproject/topics/mytopic.
     • Check Send email notifications to allow email notifications of transfer run failures.

     • Don't check Disabled when setting up a transfer. See Working with transfers to disable existing transfers.

       

5. Click Add.

6. When prompted, click Allow to give the BigQuery Data Transfer Service permission to manage your product listings and Merchant accounts for Google Shopping and to access and manage the data in BigQuery. You must allow pop-ups from bigquery.cloud.google.com to view the permissions window.

   

CLI

Enter the bq mk command and supply the transfer creation flag — --transfer_config. The following flags are also required:

• --data_source
• --target_dataset
• --display_name
• --params

You can also supply the --project_id flag to specify a particular project. If --project_id isn't specified, the default project is used.

bq mk \
--transfer_config \
--project_id=project_id \
--target_dataset=dataset \
--display_name=name \
--params='parameters' \
--data_source=data_source

Where:

• project_id is your project ID.
• dataset is the target dataset for the transfer configuration.
• name is the display name for the transfer configuration. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.
• parameters contains the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. For Google Merchant Center, you must supply the merchant_id parameter.
• data_source is the data source — merchant_center.

Note: Currently, you cannot configure notifications by using the command-line tool.

For example, the following command creates a Google Merchant Center transfer named My Transfer using Merchant ID 1234 and target dataset mydataset. The transfer is created in your default project.

bq mk \
--transfer_config \
--target_dataset=mydataset \
--display_name='My Transfer' \
--params='{"merchant_id":"1234"}' \
--data_source=merchant_center

The first time you run the command, you receive a message like the following:

[URL omitted] Please copy and paste the above URL into your web browser and follow the instructions to retrieve an authentication code.

Follow the instructions in the message and paste the authentication code on the command line.

Note: When you create a Google Merchant Center transfer by using the command-line tool, the transfer configuration is set up using the default values for Schedule (every 24 hours at creation time). The first transfer run will start immediately, and it will fail with the following error message: `No data to transfer found for the Merchant account.

If you have just created this transfer, you may need to wait for up to 90 minutes before the data of your Merchant account are prepared and available for the transfer.` If this occurs, wait for 90 minutes and then set up a backfill for today or wait until tomorrow for the next scheduled run.

API

Use the projects.locations.transferConfigs.create method and supply an instance of the TransferConfig resource.

Troubleshooting Google Merchant Center transfer setup

If you are having issues setting up your transfer, see Google Merchant Center transfer issues in Troubleshooting BigQuery Data Transfer Service transfer setup.

Querying your data

When your data is transferred to BigQuery, the data is written to ingestion-time partitioned tables. For more information, see Introduction to partitioned tables.

When you query your Google Merchant Center table, you must use the _PARTITIONTIME or _PARTITIONDATE pseudo-column in your query. For more information, see Querying partitioned tables.

The Products_ table contains nested and repeated fields. For information on handling nested and repeated data, see Differences in repeated field handling in the standard SQL documentation.

Google Merchant Center sample queries

You can use the following Google Merchant Center sample queries to analyze your transferred data. You can also use the queries in a visualization tool such as Google Data Studio.

In each of the following queries, replace dataset with your dataset name. Replace merchant_id with your Merchant ID. If you're using an MCA, replace merchant_id with your MCA ID.

Products and product issues statistics

The following sample query provides the number of products, products with issues, and issues by day.

Console

SELECT
  _PARTITIONDATE AS date,
  COUNT(*) AS num_products,
  COUNTIF(ARRAY_LENGTH(issues) > 0) AS num_products_with_issues,
  SUM(ARRAY_LENGTH(issues)) AS num_issues
FROM
  dataset.Products_merchant_id
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD'
GROUP BY
  date
ORDER BY
  date DESC

CLI

bq query --use_legacy_sql=false '
SELECT
  _PARTITIONDATE AS date,
  COUNT(*) AS num_products,
  COUNTIF(ARRAY_LENGTH(issues) > 0) AS num_products_with_issues,
  SUM(ARRAY_LENGTH(issues)) AS num_issues
FROM
  dataset.Products_merchant_id
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD'
GROUP BY
  date
ORDER BY
  date DESC'

Products disapproved for Shopping Ads

The following sample query provides the number of products that are not approved for display in Shopping Ads. Disapproval can be a result of being excluded or because of an issue with the product.

Console

SELECT
  _PARTITIONDATE AS date,
  COUNT(*) AS num_products
FROM
  dataset.Products_merchant_id,
  UNNEST(destinations) AS destination
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD' AND
  destination.status = 'disapproved'
GROUP BY
  date
ORDER BY
  date DESC

CLI

bq query --use_legacy_sql=false '
SELECT
  _PARTITIONDATE AS date,
  COUNT(*) AS num_products
FROM
  dataset.Products_merchant_id,
  UNNEST(destinations) AS destination
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD' AND
  destination.status = 'disapproved'
GROUP BY
  date
ORDER BY
  date DESC'

Products with disapproved issues

The following sample query retrieves the number of products with disapproved issues.

Console

SELECT
  _PARTITIONDATE AS date,
  COUNT(DISTINCT CONCAT(CAST(merchant_id AS STRING), ':', product_id))
      AS num_distinct_products
FROM
  dataset.Products_merchant_id,
  UNNEST(issues) AS issue
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD' AND
  issue.servability = 'disapproved'
GROUP BY
  date
ORDER BY
  date DESC

CLI

bq query --use_legacy_sql=false '
SELECT
  _PARTITIONDATE AS date,
  COUNT(DISTINCT CONCAT(CAST(merchant_id AS STRING), ':', product_id))
      AS num_distinct_products
FROM
  dataset.Products_merchant_id,
  UNNEST(issues) AS issue
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD' AND
  issue.servability = 'disapproved'
GROUP BY
  date
ORDER BY
  date DESC'

Note: This query constructs a unique key by using merchant_id and product_id. This is only required if you have an MCA account. When you use an MCA account, there is the potential for product_id collisions across multiple sub-accounts.