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