Google Merchant Center price benchmarks table

Overview

Price Benchmarks data in BigQuery helps merchants understand how other merchants are pricing the same product. When your Google Merchant Center reporting data is transferred to BigQuery, the format of the Products_PriceBenchmarks_ table provides a daily price benchmark per country and per product.

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

Schema

The Products_PriceBenchmarks table has the following schema:

Column	BigQuery data type	Description
`product_id`	`STRING`	Content API's REST ID of the product in the form: `channel:content_language:feed_label:offer_id` , similar to the way it's defined in the products table schema . This field is a primary key.
`merchant_id`	`INTEGER`	Merchant account ID.
`aggregator_id`	`INTEGER`	Aggregator account ID for multi-client accounts.
`country_of_sale`	`STRING`	Country where the user performed the query on Google.
`price_benchmark_value`	`FLOAT`	The average click-weighted price for a given product across all merchants who advertise that same product on Shopping ads. Products are matched based on their GTIN. For more details, see the Help Center article .
`price_benchmark_currency`	`STRING`	Currency of the benchmark value.
`price_benchmark_timestamp`	`DATETIME`	Timestamp of the benchmark.

Example: compare product prices to benchmarks

The following SQL query joins Products and Price Benchmarks data to return the list of products and associated benchmarks.

 WITH 
  
 products 
  
 AS 
 ( 
  
 SELECT 
  
 _PARTITIONDATE 
  
 AS 
  
 date 
 , 
  
 * 
  
 FROM 
  
  dataset 
 
 . 
 Products_ merchant_id 
 
  
 WHERE 
  
 _PARTITIONDATE 
  
 >= 
  
 ' YYYY-MM-DD 
' 
 ), 
 benchmarks 
  
 AS 
 ( 
  
 SELECT 
  
 _PARTITIONDATE 
  
 AS 
  
 date 
 , 
  
 * 
  
 FROM 
  
  dataset 
 
 . 
 Products_PriceBenchmarks_ merchant_id 
 
  
 WHERE 
  
 _PARTITIONDATE 
  
 >= 
  
 ' YYYY-MM-DD 
' 
 ) 
 SELECT 
  
 products 
 . 
 date 
 , 
  
 products 
 . 
 product_id 
 , 
  
 products 
 . 
 merchant_id 
 , 
  
 products 
 . 
 aggregator_id 
 , 
  
 products 
 . 
 price 
 , 
  
 products 
 . 
 sale_price 
 , 
  
 benchmarks 
 . 
 price_benchmark_value 
 , 
  
 benchmarks 
 . 
 price_benchmark_currency 
 , 
  
 benchmarks 
 . 
 country_of_sale 
 FROM 
  
 products 
 INNER 
  
 JOIN 
  
 benchmarks 
 ON 
  
 products 
 . 
 product_id 
  
 = 
  
 benchmarks 
 . 
 product_id 
  
 AND 
  
 products 
 . 
 merchant_id 
  
 = 
  
 benchmarks 
 . 
 merchant_id 
  
 AND 
  
 products 
 . 
 date 
  
 = 
  
 benchmarks 
 . 
 date