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
  _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