Google Merchant Center transfers
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.
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 this data is loaded into BigQuery, see the Google Merchant Center product table schema.
Regional inventories
This includes additional product data about regional availability and pricing overrides of your products.
For information on how this data is loaded into BigQuery, see the Google Merchant Center regional inventories table schema.
Local inventories
This includes additional product data about local inventory of your products. Contains data on local pricing, availability, quantity, pick-up and in-store product location.
For information on how this data is loaded into BigQuery, see the Google Merchant Center local inventories table schema.
Price benchmarks
This includes product level price benchmark data and is based on the same definitions as the price competitiveness report available through Google Merchant Center.
For information on how this data is loaded into BigQuery, see the Google Merchant Center price benchmarks table schema.
Best sellers
This includes data about the most popular products and brands in Shopping ads and unpaid listings, as well as whether or not you have them in your inventory.
It is based on the best sellers report available through Google Merchant Center.
Best sellers reports are composed of the following tables:
BestSellers_TopProducts_
is a ranked list of top products per country and Google product category. This table represents data from all merchants across Shopping ads and unpaid listings.BestSellers_TopProducts_Inventory_
is a mapping table between top products and your inventory.BestSellers_TopBrands_
is a ranked list of top brands, computed per country and Google product category. It represents data from all merchants across Shopping ads and unpaid listings.
For information on how best sellers data is loaded into BigQuery, see the Google Merchant Center top products, product inventory, and top brands table schemas.
The BigQuery Data Transfer Service for Google Merchant Center currently supports the following reporting options:
Reporting option | Support |
---|---|
Schedule | Configurable to daily, weekly, monthly, or custom. By default, this is set to daily when the transfer is created. The minimum interval between transfers is 6 hours. |
Limitations
All Merchant Center transfers are subject to the following limitations:
- After creating a transfer for your Merchant ID, you may need to wait up to a day 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.
Product and product issues
- Products and product issues data in BigQuery does not represent the
real-time view of your Merchant account. To see the timeline of your
data, refer to the
product_data_timestamp
field in the schema.
Price benchmarks
- Price benchmarks data does not represent the real-time view of price
benchmarks. To see the timeline of your data, refer to the
price_benchmark_timestamp
field in the Price Benchmarks schema. - Not all products will have a price benchmark.
Best sellers
- Not all countries will contain ranking data. Countries included in the report (using ISO 3166-1 alpha-2): AT, AU, BE, BR, CA, CH, CZ, DE, DK, ES, FI, FR, GB, HU, IE, IN, IT, JP, MX, NL, NO, NZ, PL, PT, RO, RU, SE, SK, TR, UA and US.
- Best sellers data does not represent a real-time view of brand rankings.
To see the timeline of your data, refer to the
rank_timestamp
field in theBestSellers_TopProducts_
andBestSellers_TopBrands_
schema. - Only the most popular items in Shopping ads and unpaid listings are included
in the
BestSellers_TopProducts_
table. - Only the most popular brands in Shopping ads and unpaid listings are included
in the
BestSellers_TopBrands_
table. There may be rows in theProducts_
table with agoogle_brand_id
that does not map to any brands in theBestSellers_TopBrands_
table. - Brand IDs in the
google_brand_id
field might change over time to reflect the changing nature of businesses and brands. Because of this, tracking a set of brands over a long period of time is not guaranteed. - While brands are ranked across many different categories, all products in the
Products_
table are in leaf categories. To join brands and products on non-leaf categories, use thegoogle_product_category_ids
field, as shown in Best sellers sample queries.
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.
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 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- Both
bigquery.datasets.get
andbigquery.datasets.update
permissions on the target dataset
The
bigquery.admin
predefined IAM role includesbigquery.transfers.update
,bigquery.datasets.update
andbigquery.datasets.get
permissions. For more information on IAM roles in BigQuery Data Transfer Service, see Access control.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.
To access price competitiveness and best sellers data, you must first enable the Market Insights Program.
Set up a Google Merchant Center transfer
Setting up a data transfer for Google Merchant Center reporting requires the following:
- Merchant ID or Multi-client account ID: This is the Merchant ID shown in the Google Merchant Center UI.
To create a data transfer for Google Merchant Center reporting:
Console
Go to the BigQuery page in the Google Cloud console.
Click Transfers.
Click Create Transfer.
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 24 hours later than 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 a day before the data of your Merchant account are prepared and available for the transfer.
The next scheduled run should run successfully. If the data of your Merchant account are prepared on the same date in UTC time, you can set up a backfill for today's 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.
Select the report(s) that you would like to transfer. See Supported Reports for more details.
(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.
Click Save.
bq
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 transfers, you must supply the
merchant_id
parameter. - The
export_products
parameter specifies whether to transfer product and product issues data. This parameter is included by default, even if you don't specify theexport_products
parameter. Google recommends that you include this parameter explicitly and set it to "true". - The
export_regional_inventories
parameter specifies whether to transfer regional inventories data. - The
export_local_inventories
parameter specifies whether to transfer local inventories data. - The
export_price_benchmarks
parameter specifies whether to transfer price benchmarking data. - The
export_best_sellers
parameter specifies whether to transfer best sellers data.
- For Google Merchant Center transfers, you must supply the
- data_source is the data source —
merchant_center
.
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","export_products":"true","export_regional_inventories":"true","export_local_inventories":"true","export_price_benchmarks":"true","export_best_sellers":"true"}' \
--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.
If you have just created this transfer, you may need to wait for up to a day before the data of your Merchant account are prepared and available for the transfer.` The next scheduled run should run successfully. If the data of your Merchant account are prepared on the same date in UTC time, you can set up a backfill for today's run.
API
Use the projects.locations.transferConfigs.create
method and supply an instance of the TransferConfig
resource.
Troubleshoot 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.
Query 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 GoogleSQL 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 Looker 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 sample queries
Products and product issues statistics
The following SQL sample query provides the number of products, products with issues, and issues by day.
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 SQL sample query provides the number of products that are not approved for display in Shopping Ads, separated by country. Disapproval can result from the destination being excluded or because of an issue with the product.
SELECT _PARTITIONDATE AS date, disapproved_country, COUNT(*) AS num_products FROM dataset.Products_merchant_id, UNNEST(destinations) AS destination, UNNEST(disapproved_countries) AS disapproved_country WHERE _PARTITIONDATE >= 'YYYY-MM-DD' GROUP BY date, disapproved_country ORDER BY date DESC
Products with disapproved issues
The following SQL sample query retrieves the number of products with disapproved issues, separated by country.
SELECT _PARTITIONDATE AS date, applicable_country, COUNT(DISTINCT CONCAT(CAST(merchant_id AS STRING), ':', product_id)) AS num_distinct_products FROM dataset.Products_merchant_id, UNNEST(issues) AS issue, UNNEST(issue.applicable_countries) as applicable_country WHERE _PARTITIONDATE >= 'YYYY-MM-DD' AND issue.servability = 'disapproved' GROUP BY date, applicable_country ORDER BY date DESC
Price benchmark sample queries
Compare product prices to benchmarks
The following SQL query joins Products
and Price Benchmarks
data to return
the list of products and associated benchmarks.
WITH products AS ( SELECT _PARTITIONDATE AS date, * FROM dataset.Products_merchant_id WHERE _PARTITIONDATE >= 'YYYY-MM-DD' ), benchmarks AS ( SELECT _PARTITIONDATE AS date, * FROM dataset.Products_PriceBenchmarks_merchant_id WHERE _PARTITIONDATE >= 'YYYY-MM-DD' ) SELECT products.date, products.product_id, products.merchant_id, products.aggregator_id, products.price, products.sale_price, benchmarks.price_benchmark_value, benchmarks.price_benchmark_currency, benchmarks.country_of_sale FROM products INNER JOIN benchmarks ON products.product_id = benchmarks.product_id AND products.merchant_id = benchmarks.merchant_id AND products.date = benchmarks.date
Best sellers sample queries
Top products for a given category and country
The following SQL query returns top products for the smartphone category in the US.
SELECT rank, previous_rank, relative_demand.bucket, (SELECT name FROM top_products.product_title WHERE locale = 'en-US') AS product_title, brand, price_range FROM dataset.BestSellers_TopProducts_merchant_id AS top_products WHERE _PARTITIONDATE = 'YYYY-MM-DD' AND ranking_category = 267 /*Smartphones*/ AND ranking_country = 'US' ORDER BY rank
Top products in your inventory
The following SQL query joins BestSellers_TopProducts_Inventory_
and
BestSellers_TopProducts_
data to return a list of top products you currently
have in your inventory.
WITH latest_top_products AS ( SELECT * FROM dataset.BestSellers_TopProducts_merchant_id WHERE _PARTITIONDATE = 'YYYY-MM-DD' ), latest_top_products_inventory AS ( SELECT * FROM dataset.BestSellers_TopProducts_Inventory_merchant_id WHERE _PARTITIONDATE = 'YYYY-MM-DD' ) SELECT top_products.rank, inventory.product_id, (SELECT ANY_VALUE(name) FROM top_products.product_title) AS product_title, top_products.brand, top_products.gtins FROM latest_top_products AS top_products INNER JOIN latest_top_products_inventory AS inventory USING (rank_id)
Top brands for a given category and country
The following SQL query returns top brands for the smartphone category in the US.
SELECT rank, previous_rank, brand FROM dataset.BestSellers_TopBrands_merchant_id WHERE _PARTITIONDATE = 'YYYY-MM-DD' AND ranking_category = 267 /*Smartphones*/ AND ranking_country = 'US' ORDER BY rank
Products of top brands in your inventory
The following SQL query returns a list of products in your inventory from top brands, listed by category and country.
WITH latest_top_brands AS ( SELECT * FROM dataset.BestSellers_TopBrands_merchant_id WHERE _PARTITIONDATE = 'YYYY-MM-DD' ), latest_products AS ( SELECT product.*, product_category_id FROM dataset.Products_merchant_id AS product, UNNEST(product.google_product_category_ids) AS product_category_id, UNNEST(destinations) AS destination, UNNEST(destination.approved_countries) AS approved_country WHERE _PARTITIONDATE = 'YYYY-MM-DD' ) SELECT top_brands.brand, (SELECT name FROM top_brands.ranking_category_path WHERE locale = 'en-US') AS ranking_category, top_brands.ranking_country, top_brands.rank, products.product_id, products.title FROM latest_top_brands AS top_brands INNER JOIN latest_products AS products ON top_brands.google_brand_id = products.google_brand_id AND top_brands.ranking_category = product_category_id AND top_brands.ranking_country = products.approved_country