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