Google Merchant Center products table schema

Overview

When your Google Merchant Center reporting data is transferred to BigQuery, the format of product and product issues data corresponds primarily to the format of the relevant fields of the Content API's Products and Productstatuses resources.

The data is written to a table named Products_ MERCHANT_ID if you are using an individual Merchant ID, or Products_ AGGREGATOR_ID if you're using an MCA account.

Schema

The Products_ table has the following schema:

Column	BigQuery data type	Description	Example data
`product_data_timestamp`	`TIMESTAMP`	Timestamp of the product data.	2023-09-14 11:49:50 UTC
`product_id`	`STRING`	Content API's REST ID of the product in the form: `channel:content_language:feed_label:offer_id` . This is the primary key.	online:en:AU:666840730
`merchant_id`	`INTEGER`	Merchant account ID.	1234
`aggregator_id`	`INTEGER`	Aggregator account ID for multi-client accounts.	12345
`offer_id`	`STRING`	Merchant provided id of the product .	tddy123uk
`title`	`STRING`	Title of the item.	TN2351 black USB
`description`	`STRING`	Description of the item.	The TN2351 black USB has redefined how XJS can impact LLCD experiences.
`link`	`STRING`	Merchant provided URL of the landing page of the product.	https://www.example.com/tn2351-black-usb/6538811?skuId=1234
`mobile_link`	`STRING`	Merchant provided URL of a mobile-optimized version of the landing page.	https://www.example.com/tn2351-black-usb/6538811?skuId=1234
`image_link`	`STRING`	Merchant provided URL of the main product image .	https://www.example.com/tn2351-black-usb/6538811?skuId=1234
`additional_image_links`	`STRING` , `REPEATED`	Merchant provided additional URLs of images of the item.
`content_language`	`STRING`	The two-letter ISO 639-1 language code for the item.	en
`target_country`	`STRING`	Deprecated (always set to NULL) as part of a change to allow products to target multiple countries . Instead, use the following fields to read the status of each targeted country: destinations.approved_countries , destinations.pending_countries , destinations.disapproved_countries . Issues can now apply to certain target countries and not others, as indicated in the field issues.applicable_countries .	null
`feed_label`	`STRING`	The merchant provided feed label for the item, or `-` if not provided.	US
`channel`	`STRING`	The item's channel, either `online` or `local` .	local, online
`expiration_date`	`TIMESTAMP`	Merchant provided date and time on which the item should expire , as specified upon insertion. Set to null if not provided.	2023-10-14 00:00:00 UTC
`google_expiration_date`	`TIMESTAMP`	Date and time on which the item expires in Google Shopping. Never set to null.	2023-10-14 00:00:00 UTC
`adult`	`BOOLEAN`	Set to true if the item is targeted towards adults.	true, false
`age_group`	`STRING`	Merchant provided target age group of the item. NULL if not provided.	newborn, infant, toddler, kids, adult
`availability`	`STRING`	Merchant provided availability status of the item.	in stock, out of stock
`availability_date`	`TIMESTAMP`	Merchant provided date and time when a pre-ordered product becomes available for delivery. NULL if not provided.	2023-10-14 00:00:00 UTC
`brand`	`STRING`	Merchant provided brand of the item. NULL if not provided.	Brand Name
`google_brand_id`	`STRING`	Google brand ID of the item.	12759524623914508053
`color`	`STRING`	Merchant provided color of the item. NULL if not provided.	Silver, Gray, Multi
`condition`	`STRING`	Merchant provided Condition or state of the item.	new, used, refurbished
`custom_labels`	`RECORD`	Merchant provided custom labels for custom grouping of items in Shopping Ads. NULL if not provided.
`custom_labels.label_0`	`STRING`	Custom label 0.	my custom label
`custom_labels.label_1`	`STRING`	Custom label 1.	my custom label
`custom_labels.label_2`	`STRING`	Custom label 2.	my custom label
`custom_labels.label_3`	`STRING`	Custom label 3.	my custom label
`custom_labels.label_4`	`STRING`	Custom label 4.	my custom label
`gender`	`STRING`	Merchant provided target gender of the item. NULL if not provided.	unisex, male, female
`gtin`	`STRING`	Merchant provided Global Trade Item Number (GTIN) of the item. NULL if not provided.	3234567890126
`item_group_id`	`STRING`	Merchant provided Shared identifier for all variants of the same product. NULL if not provided.	AB12345
`material`	`STRING`	Merchant provided material of which the item is made. NULL if not provided.	Leather
`mpn`	`STRING`	Merchant provided Manufacturer Part Number (MPN) of the item. Set to NULL if not provided.	GO12345OOGLE
`pattern`	`STRING`	Merchant provided pattern . NULL if not provided.	Striped
`price`	`RECORD`	Merchant provided price of the item.
`price.value`	`NUMERIC`	The price of the item.	19.99
`price.currency`	`STRING`	The currency of the price.	USD
`sale_price`	`RECORD`	Merchant provided sale price of the item.
`sale_price.value`	`NUMERIC`	The sale price of the item. NULL if not provided.	19.99
`sale_price.currency`	`STRING`	The currency of the sale price. NULL if not provided.	USD
`sale_price_effective_start_date`	`TIMESTAMP`	Start date and time when the item is on sale.	2023-10-14 00:00:00 UTC
`sale_price_effective_end_date`	`TIMESTAMP`	End date and time when the item is on sale.	2023-10-14 00:00:00 UTC
`google_product_category`	`INTEGER`	The item's Google product category ID. NULL if not provided.	2271
`google_product_category_ids`	`INTEGER, REPEATED`	The full path of Google product categories to the item, stored as a set of IDs. NULL if not provided.
`google_product_category_path`	`STRING`	A human-readable version of the full path. Empty if not provided.	Apparel & Accessories > Clothing > Dresses
`product_type`	`STRING`	Merchant-provided category of the item.	Home > Women > Dresses > Maxi Dresses
`additional_product_types`	`STRING` , `REPEATED`	Additional categories of the item.
`promotion_ids`	`STRING` , `REPEATED`	The list of promotion IDs associated with the product.
`destinations`	`RECORD` , `REPEATED`	The intended destinations for the product.
`destinations.name`	`STRING`	The name of the destination; only `Shopping` is supported. This corresponds to the Marketing Methods "Shopping Ads" and "Local Inventory Ads" in Merchant Center.	Shopping
`destinations.status *`	`STRING`	Deprecated (always set to NULL) as part of a change to allow products to target multiple countries . Instead, use the following fields to read the status of each targeted country: destinations.approved_countries , destinations.pending_countries , destinations.disapproved_countries . Issues can now apply to certain target countries and not others, as indicated in the field issues.applicable_countries .	NULL
`destinations.approved_countries`	`STRING, REPEATED`	List of CLDR territory codes where the offer is approved.	US, CH
`destinations.pending_countries`	`STRING, REPEATED`	List of CLDR territory codes where the offer is pending.	US, CH
`destinations.disapproved_countries`	`STRING, REPEATED`	List of CLDR territory codes where the offer is disapproved.	US, CH
`issues`	`RECORD` , `REPEATED`	The list of item level issues associated with the product.
`issues.code`	`STRING`	The error code of the issue.	image_too_generic
`issues.servability`	`STRING`	How this issue affects serving of the offer.	disapproved, unaffected
`issues.resolution`	`STRING`	Whether the issue can be resolved by the merchant.	merchant_action, pending_processing
`issues.attribute_name`	`STRING`	The attribute's name, if the issue is caused by a single attribute. NULL otherwise.	image link
`issues.destination`	`STRING`	The destination the issue applies to. Always set to `Shopping` .	Shopping
`issues.short_description`	`STRING`	Short issue description in English.	Generic image
`issues.detailed_description`	`STRING`	Detailed issue description in English.	Use an image that shows the product
`issues.documentation`	`STRING`	URL of a web page to help with resolving this issue.	https://support.google.com/merchants/answer/6098288
`issues.applicable_countries`	`STRING, REPEATED`	List of CLDR territory codes where the issue applies.	CH

Query examples

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