The BigQuery Data Transfer Service for Google Merchant Center lets you automaticallyschedule and manage recurring load jobs for Google Merchant Center reportingdata.
Supported reports
The BigQuery Data Transfer Service for Google Merchant Center supports the following data:
Products and product issues
The products and product issues report includes product data provided to Merchant Center through feeds or using the Content API for Shopping. This report also includes item level issues detected by Google for your products. You can view product and product issues data 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
The regional inventories report includes additional product data aboutregional availability and pricing overrides of your products.For informationon how this data is loaded into BigQuery, see the Google MerchantCenter regional inventories tableschema.
Local Inventories
The local inventories report includes additional product data about localinventory of your products. This report contains data on local pricing,availability, quantity, pick-up and in-store product location. For informationon how this data is loaded into BigQuery, see the Google MerchantCenter local inventories tableschema.
Performance
The performance report provides granular segmentation of your performance dataacross both Ads and Free Listings. For information on how this data is loadedinto BigQuery, see the Google Merchant Center performance tableschema.
Best Sellers
The Best sellers report provides the same data found in the Google MerchantCenter UI and lets you backfill the data across countries or categories forup to 2 years. This includes data about the most popular products and brands inShopping ads and unpaid listings, as well as whether or not you have them inyour inventory. This report is based on the best sellersreport available through Google Merchant Center. For information on how this data isloaded into BigQuery, see the Google Merchant Center bestsellers tables schemas.
Price Competitiveness
Formerly known as the price benchmarks report, the price competitiveness report includes product level attributes and price benchmark data and is basedon the same definitions as the price competitivenessreport available through Google Merchant Center. For information on how this data is loaded into BigQuery, see the Google Merchant Center price competitiveness table schema.
Price Insights
Use the price insights report to see suggested sale prices for yourproducts, and predictions for the performance that you can expect if you updateyour products' prices. Using the price insights report can help you price yourproducts more effectively. For more information on how to use the data in thisreport, see Improve product pricing with the price insightsreport for moreinformation on how to use the data in this report. For information on how thisdata is loaded into BigQuery, see the Google Merchant CenterPrice Insights tableschema.
Product Targeting
Enable the Product Targeting report when you set up a transfer to expose Adstargeting information when you load data from Google Shopping intoBigQuery. For information on how the data is loaded intoBigQuery, see the Google Merchant Center Product Targeting table schema.
Reporting options
The BigQuery Data Transfer Service for Google Merchant Center supports the followingreporting 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. |
Data ingestion from Google Merchant Center transfers
When you transfer data from Google Merchant Center into BigQuery, thedata is loaded into BigQuery tables that are partitioned by date.The table partition that the data is loaded into corresponds to the date fromthe data source. If you schedule multiple transfers for the same date,BigQuery Data Transfer Service overwrites the partition for that specific date withthe latest data. Multiple transfers in the same day or running backfills don'tresult in duplicate data, and partitions for other dates are not affected.
Limitations
Some reports might have their own constraints, such as different windows ofsupport for historical backfills. The following sections describe thelimitations for each report.
Historical Backfills Support
Not all reports support historical backfills in the same way. The following area list of reports and the level of support for historical backfills.
- Products and product issues - 14 days
- Local Inventories - 14 days
- Regional Inventories - 14 days
- Performance - 2 years
- Best sellers - 2 years
- Price competitiveness - No backfill support
- Price insights - No backfill support
Product and product issues
- Products and product issues data in BigQuery does not represent thereal-time view of your Merchant account. To see the timeline of yourdata, refer to the
product_data_timestampfield in the schema.
Price benchmarks
- Price benchmarks data does not represent the real-time view of pricebenchmarks. To see the timeline of your data, refer to the
price_benchmark_timestampfield 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 thereport (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_timestampfieldin theBestSellers_TopProducts_andBestSellers_TopBrands_schema. - There may be rows in the
Products_table with agoogle_brand_idthat does not map to any brands in theBestSellers_TopBrands_table. - Brand IDs in the
google_brand_idfield might change over time to reflect thechanging nature of businesses and brands. Because of this, tracking a set ofbrands 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 onnon-leaf categories, use thegoogle_product_category_idsfield, asshown in Best sellers sample queries.
Multi-client account (MCA) support
Existing customers with multiple Merchant IDs are encouraged to configure aparent Multi-Client Account (MCA).Configuring an MCA lets you create a single transfer for all your MerchantIDs.
Using Google Merchant Center MCAs provides several benefits over usingindividual Merchant IDs:
- You no longer need to manage multiple transfers to transfer reporting data formultiple Merchant IDs.
- Queries involving multiple Merchant IDs are much simpler to write because allMerchant ID data is stored in the same table.
- Using MCAs alleviates potential BigQuery load job quota issuesbecause all your Merchant ID data is loaded in the same job.
One possible disadvantage of using MCAs is that your subsequent query costs arelikely 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 theentire table.
If you are using an MCA, the MCA ID is listed under aggregator_id and theindividual sub-accounts are listed under merchant_id. For accounts thatdon't use an MCA, aggregator_id is set to null.
Not all reports support MCAs. MCAs are supported by the following reports:
- Products and product issues
- Local Inventories
- Regional Inventories
- Product Performance
- Price Competitiveness
- Price Insights
- Product Targeting
Query your data
When your data is transferred to BigQuery, the data iswritten to ingestion-time partitioned tables. For more information, seeIntroduction to partitioned tables.
When you query your Google Merchant Center table, youmust 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 onhandling nested and repeated data, seeDifferences in repeated field handlingin the GoogleSQL documentation.
Google Merchant Center sample queries
You can use the following Google Merchant Center sample queries to analyze yourtransferred data. You can also use the queries in a visualization tool such asLooker Studio.
In each of the following queries, replace dataset with your datasetname. Replace merchant_id with your Merchant ID. If you're using anMCA, 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 withissues, 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_issuesFROM dataset.Products_merchant_idWHERE _PARTITIONDATE >= 'YYYY-MM-DD'GROUP BY dateORDER BY date DESC
Products disapproved for Shopping Ads
The following SQL sample query provides the number of products that are notapproved for display in Shopping Ads, separated by country. Disapprovalcan result from the destination beingexcludedor because of an issue with the product.
SELECT _PARTITIONDATE AS date, disapproved_country, COUNT(*) AS num_productsFROM dataset.Products_merchant_id, UNNEST(destinations) AS destination, UNNEST(disapproved_countries) AS disapproved_countryWHERE _PARTITIONDATE >= 'YYYY-MM-DD'GROUP BY date, disapproved_countryORDER BY date DESC
Products with disapproved issues
The following SQL sample query retrieves the number of products with disapprovedissues, separated by country.
SELECT _PARTITIONDATE AS date, applicable_country, COUNT(DISTINCT CONCAT(CAST(merchant_id AS STRING), ':', product_id)) AS num_distinct_productsFROM dataset.Products_merchant_id, UNNEST(issues) AS issue, UNNEST(issue.applicable_countries) as applicable_countryWHERE _PARTITIONDATE >= 'YYYY-MM-DD' AND issue.servability = 'disapproved'GROUP BY date, applicable_countryORDER BY date DESC
Price benchmark sample queries
Compare product prices to benchmarks
The following SQL query joins Products and Price Benchmarks data to returnthe 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_saleFROM productsINNER JOIN benchmarksON 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 "Smartphones" category inthe US.
SELECT rank, previous_rank, relative_demand.bucket, (SELECT name FROM top_products.product_title WHERE locale = 'en-US') AS product_title, brand, price_rangeFROM dataset.BestSellers_TopProducts_merchant_id AS top_productsWHERE _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_ andBestSellers_TopProducts_ data to return a list of top products youhave 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.gtinsFROM latest_top_products AS top_productsINNER JOIN latest_top_products_inventory AS inventoryUSING (rank_id)
Top brands for a given category and country
The following SQL query returns top brands for the "Smartphones" category inthe US.
SELECT rank, previous_rank, brandFROM dataset.BestSellers_TopBrands_merchant_idWHERE _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 topbrands, 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
