Query your Google Merchant Center Transfers 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 pseudocolumn 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 statistics

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

SELECT
_PARTITIONDATEASdate,
COUNT(*)ASnum_products,
COUNTIF(ARRAY_LENGTH(issues)>0)ASnum_products_with_issues,
SUM(ARRAY_LENGTH(issues))ASnum_issues
FROM
dataset.Products_merchant_id
WHERE
_PARTITIONDATE>='YYYY-MM-DD'
GROUPBY
date
ORDERBY
dateDESC

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
_PARTITIONDATEASdate,
disapproved_country,
COUNT(*)ASnum_products
FROM
dataset.Products_merchant_id,
UNNEST(destinations)ASdestination,
UNNEST(disapproved_countries)ASdisapproved_country
WHERE
_PARTITIONDATE>='YYYY-MM-DD'
GROUPBY
date,disapproved_country
ORDERBY
dateDESC

Products with disapproved issues

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

SELECT
_PARTITIONDATEASdate,
applicable_country,
COUNT(DISTINCTCONCAT(CAST(merchant_idASSTRING),':',product_id))
ASnum_distinct_products
FROM
dataset.Products_merchant_id,
UNNEST(issues)ASissue,
UNNEST(issue.applicable_countries)asapplicable_country
WHERE
_PARTITIONDATE>='YYYY-MM-DD'AND
issue.servability='disapproved'
GROUPBY
date,applicable_country
ORDERBY
dateDESC