Migrate the best sellers report

This document helps you migrate from the older version of the best sellers reports to its newer version. The older version of the report that exports the BestSellers_TopBrands_, BestSellers_TopProducts_, and BestSellers_TopProducts_Inventory_ tables will be deprecated on September 1, 2025.

The new best sellers report offers the following:

Tables exported by old and new reports

The following table compares the tables exported by the old and new reports:

Old report New report
BestSellers_TopBrands BestSellersBrandWeekly and BestSellersBrandMonthly
BestSellers_TopProducts BestSellersProductClusterWeekly and BestSellersProductClusterMonthly
BestSellers_TopProducts_Inventory BestSellersEntityProductMapping

The old report contains a single aggregation of best sellers data over an unspecified time window. The new report provides both the latest weekly and monthly aggregations of this data at the time of the request.

Compare BestSellers_TopBrands with BestSellersBrandWeekly and BestSellersBrandMonthly

The following table helps you identify fields in the BestSellers_TopBrands table that have equivalent replacements in the BestSellersBrandWeekly and BestSellersBrandMonthly tables. Replacements for some fields from the old table aren't available.

BestSellers_TopBrands (old) BestSellersBrandWeekly and BestSellersBrandMonthly (new)
rank_timestamp _PARTITIONDATE and _PARTITIONTIME
brand brand
google_brand_id
ranking_category category_id
ranking_category_path.locale
ranking_category_path.name
ranking_country country_code
rank_id
rank rank
previous_rank previous_rank
relative_demand.bucket relative_demand
relative_demand.min
relative_demand.max
previous_relative_demand.bucket previous_relative_demand
previous_relative_demand.min
previous_relative_demand.max
relative_demand_change

Compare BestSellers_TopProducts with BestSellersProductClusterWeekly and BestSellersProductClusterMonthly

The following table helps you identify fields in the BestSellers_TopProducts table that have equivalent replacements in the BestSellersProductClusterWeekly and BestSellersProductClusterMonthly tables. Replacements for some fields from the old table aren't available.

BestSellers_TopProducts (old) BestSellersProductClusterWeekly and BestSellersProductClusterMonthly (new)
rank_timestamp _PARTITIONDATE and _PARTITIONTIME
rank_id entity_id
rank rank
previous_rank previous_rank
ranking_country country_code
ranking_category report_category_id
ranking_category_path.locale
ranking_category_path.name
relative_demand.bucket relative_demand
relative_demand.min
relative_demand.max
previous_relative_demand.bucket previous_relative_demand
previous_relative_demand.min
previous_relative_demand.max
relative_demand_change
product_title.locale
product_title.name title (single title instead of an array for every locale)
gtins variant_gtins
google_brand_id
brand brand
google_product_category
category_l1, category_l2, category_l3, category_l4, category_l5
google_product_category_path.locale
google_product_category_path.name
price_range.min price_range.min_amount_micros
price_range.max price_range.max_amount_micros
price_range.currency price_range.currency_code
product_inventory_status
brand_inventory_status

Inventory mapping of best sellers data

In the old best sellers report, the best sellers data is mapped to the merchant's inventory data in a new generated table, using the rank_id column from the TopProducts table.

In the new best sellers report, the entity_id column is exported in the BestSellersProductCluster tables, which is mapped to all the product IDs from the merchant's inventory in the BestSellersEntityProductMapping table.

BestSellers_TopProductsInventory (old) BestSellersEntityProductMapping (new)
rank_id (found in BestSellers_TopProducts) entity_id (found in the BestSellersProductClustersWeekly and BestSellersProductClustersMonthly tables)
product_id product_id
merchant_id
aggregator_id

Example queries

This section highlights changes in example queries that are used to retrieve best sellers data.

Example 1: Retrieve top products for a given category and country

The following queries return top products for a given category and country.

Use the BestSellers_TopProducts table (old)

SELECT
  rank,
  previous_rank,
  relative_demand.bucket,
  (SELECT name FROM top_products.product_title WHERE locale = 'en-US') AS product_title,
  brand,
  price_range,
  google_product_category
FROM
  `DATASET.BestSellers_TopProducts_MERCHANT_ID` AS top_products
WHERE
  _PARTITIONDATE = 'DATE' AND
  ranking_category = 267 /*Smartphones*/ AND
  ranking_country = 'US'
ORDER BY
  rank;

Use the BestSellersProductClusterWeekly or BestSellersProductClusterMonthly table (new)

SELECT
  rank,
  previous_rank,
  relative_demand,
  title AS product_title,
  brand,
  price_range,
  category_l1,
  category_l2
FROM
  `DATASET.BestSellersProductClusterWeekly_MERCHANT_ID` AS top_products
WHERE
  _PARTITIONDATE = 'DATE' AND
  report_category_id = 267 /*Smartphones*/ AND
  country_code = 'US'
ORDER BY
  rank;

Example 2: Retrieve top products in your inventory

The following queries return a list of top products in your inventory.

Use the BestSellers_TopProducts table (old)

WITH latest_top_products AS
(
  SELECT
    *
  FROM
    `DATASET.BestSellers_TopProducts_MERCHANT_ID`
  WHERE
    _PARTITIONDATE = 'DATE'
),
latest_top_products_inventory AS
(
  SELECT
    *
  FROM
    `DATASET.BestSellers_TopProducts_Inventory_MERCHANT_ID`
  WHERE
    _PARTITIONDATE = 'DATE'
)
SELECT
  top_products.rank,
  inventory.product_id,
  (SELECT ANY_VALUE(name) FROM top_products.product_title) AS product_title,
  top_products.brand,
  top_products.gtins
FROM
  latest_top_products AS top_products
INNER JOIN
  latest_top_products_inventory AS inventory
USING (rank_id);

Use the BestSellersProductClusterWeekly or BestSellersProductClusterMonthly table (new)

WITH latest_top_products AS
(
  SELECT
    *
  FROM
    `DATASET.BestSellersProductClusterWeekly_MERCHANT_ID`
  WHERE
    _PARTITIONDATE = 'DATE'
),
latest_top_products_inventory AS
(
  SELECT
    *
  FROM
    `DATASET.BestSellersEntityProductMapping_MERCHANT_ID`
  WHERE
    _PARTITIONDATE = 'DATE'
)
SELECT
  top_products.rank,
  inventory.product_id,
  top_products.title AS product_title,
  top_products.brand,
  top_products.variant_gtins
FROM
  latest_top_products AS top_products
INNER JOIN
  latest_top_products_inventory AS inventory
USING (entity_id);

Moreover, if you want find the number of best selling products or brands in your inventory, run a query on the BestSellerProductClusterWeekly or BestSellerProductClusterMonthly tables using the product_inventory_status or brand_inventory_status columns. See the following example query:

SELECT
  *
FROM
  `DATASET.BestSellersProductClusterMonthly_MERCHANT_ID`
WHERE
  _PARTITIONDATE = 'DATE' AND
  product_inventory_status != 'NOT_IN_INVENTORY'
ORDER BY
  rank;

Example 3: Retrieve top brands for a given category and country

The following queries return a list of top brands for a given category and country.

Use the BestSellers_TopBrands table (old)

SELECT
  rank,
  previous_rank,
  brand
FROM
  `DATASET.BestSellers_TopBrands_MERCHANT_ID`
WHERE
  _PARTITIONDATE = 'DATE' AND
  ranking_category = 267 /*Smartphones*/ AND
  ranking_country = 'US'
ORDER BY
  rank;

Use the BestSellersTopBrandsWeekly or BestSellersTopBrandsMonthly table (new)

SELECT
  rank,
  previous_rank,
  brand
FROM
  `DATASET.BestSellersTopBrandsWeekly_MERCHANT_ID`
WHERE
  _PARTITIONDATE = 'DATE' AND
  report_category_id = 267 /*Smartphones*/ AND
  country_code = 'US'
ORDER BY
  rank;

Example 4: Retrieve products of top brands in your inventory

The following queries return a list of products of top brands in your inventory.

Use the BestSellers_TopBrands table (old)

WITH latest_top_brands AS
  (
    SELECT
      *
    FROM
      `DATASET.BestSellers_TopBrands_MERCHANT_ID`
    WHERE
      _PARTITIONDATE = 'DATE'
  ),
  latest_products AS
  (
    SELECT
      product.*,
      product_category_id
    FROM
      `DATASET.Products_MERCHANT_ID` AS product,
      UNNEST(product.google_product_category_ids) AS product_category_id,
      UNNEST(destinations) AS destination,
      UNNEST(destination.approved_countries) AS approved_country
    WHERE
      _PARTITIONDATE = 'DATE'
  )
SELECT
  top_brands.brand,
  (SELECT name FROM top_brands.ranking_category_path
  WHERE locale = 'en-US') AS ranking_category,
  top_brands.ranking_country,
  top_brands.rank,
  products.product_id,
  products.title
FROM
  latest_top_brands AS top_brands
INNER JOIN
  latest_products AS products
ON top_brands.google_brand_id = products.google_brand_id AND
   top_brands.ranking_category = product_category_id AND
   top_brands.ranking_country = products.approved_country;

Use the BestSellersTopBrandsWeekly or BestSellersTopBrandsMonthly table (new)

WITH latest_top_brands AS
  (
    SELECT
      *
    FROM
      `DATASET.BestSellersBrandMonthly_MERCHANT_ID`
    WHERE
      _PARTITIONDATE = 'DATE'
  ),
  latest_products AS
  (
    SELECT
      product.*,
      product_category_id
    FROM
      `DATASET.Products_MERCHANT_ID` AS product,
      UNNEST(product.google_product_category_ids) AS product_category_id,
      UNNEST(destinations) AS destination,
      UNNEST(destination.approved_countries) AS approved_country
    WHERE
      _PARTITIONDATE = 'DATE'
  )
SELECT
  top_brands.brand,
  –- The full category name is not supported in the new BestSellersTopBrands tables.
  –- (SELECT name FROM top_brands.ranking_category_path
  –- WHERE locale = 'en-US') AS ranking_category,
  top_brands.category_id,
  top_brands.rank,
  products.product_id,
  products.title
FROM
  latest_top_brands AS top_brands
INNER JOIN
  latest_products AS products
ON top_brands.brand = products.brand AND
   top_brands.category_id = product_category_id AND
   top_brands.country_code = products.approved_country;

In these queries, replace the following:

  • DATASET: the name of your dataset
  • MERCHANT_ID: the merchant account ID
  • DATE: the date in the YYYY-MM-DD format

What's next