Query your Google Merchant Center Transfers data

Preview

This product is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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 pseudocolumn in your query.For more information, seeQuerying partitioned tables.

TheProducts_ 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, replacedataset with your datasetname. Replacemerchant_id with your Merchant ID. If you're using anMCA, replacemerchant_id with your MCA ID.

Products and product issues statistics

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

SELECT_PARTITIONDATEASdate,COUNT(*)ASnum_products,COUNTIF(ARRAY_LENGTH(issues)>0)ASnum_products_with_issues,SUM(ARRAY_LENGTH(issues))ASnum_issuesFROMdataset.Products_merchant_idWHERE_PARTITIONDATE>='YYYY-MM-DD'GROUPBYdateORDERBYdateDESC

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_PARTITIONDATEASdate,disapproved_country,COUNT(*)ASnum_productsFROMdataset.Products_merchant_id,UNNEST(destinations)ASdestination,UNNEST(disapproved_countries)ASdisapproved_countryWHERE_PARTITIONDATE>='YYYY-MM-DD'GROUPBYdate,disapproved_countryORDERBYdateDESC

Products with disapproved issues

The following SQL sample query retrieves the number of products with disapprovedissues, separated by country.

SELECT_PARTITIONDATEASdate,applicable_country,COUNT(DISTINCTCONCAT(CAST(merchant_idASSTRING),':',product_id))ASnum_distinct_productsFROMdataset.Products_merchant_id,UNNEST(issues)ASissue,UNNEST(issue.applicable_countries)asapplicable_countryWHERE_PARTITIONDATE>='YYYY-MM-DD'ANDissue.servability='disapproved'GROUPBYdate,applicable_countryORDERBYdateDESC
Note: This query constructs a unique key by usingmerchant_id andproduct_id.This is only required if you have an MCA account. When you use an MCA account,there is the potential forproduct_id collisions across multiple sub-accounts.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.