遷移暢銷商品報表

這份文件可協助你從舊版暢銷商品報表遷移至新版。舊版報表會匯出 BestSellers_TopBrands_、BestSellers_TopProducts_ 和 BestSellers_TopProducts_Inventory_ 表格，並將於 2025 年 9 月 1 日淘汰。

新版暢銷商品報表提供下列功能：

舊版和新版報表匯出的表格

下表比較新舊版報表匯出的資料表：

舊報表 新增報表
BestSellers_TopBrands BestSellersBrandWeekly 和 BestSellersBrandMonthly
BestSellers_TopProducts BestSellersProductClusterWeekly 和 BestSellersProductClusterMonthly
BestSellers_TopProducts_Inventory BestSellersEntityProductMapping

舊版報表只會匯總一段未指定時間範圍內的暢銷商品資料。這份新報表會提供要求時的最新每週和每月匯總資料。

比較「BestSellers_TopBrands」和「BestSellersBrandWeekly」與「BestSellersBrandMonthly」

下表有助於找出 BestSellers_TopBrands 表格中，在 BestSellersBrandWeekly 和 BestSellersBrandMonthly 表格中具有對應替代項的欄位。舊版表格的部分欄位沒有替代欄位。

BestSellers_TopBrands (舊) BestSellersBrandWeekly 和 BestSellersBrandMonthly (新)
rank_timestamp _PARTITIONDATE 和 _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

比較「BestSellers_TopProducts」和「BestSellersProductClusterWeekly」與「BestSellersProductClusterMonthly」

下表有助於找出 BestSellers_TopProducts 表格中，在 BestSellersProductClusterWeekly 和 BestSellersProductClusterMonthly 表格中具有對應替代項的欄位。舊版表格的部分欄位沒有替代欄位。

BestSellers_TopProducts (舊) BestSellersProductClusterWeekly 和 BestSellersProductClusterMonthly (新)
rank_timestamp _PARTITIONDATE 和 _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 (每個語言代碼的陣列改為單一標題)
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

暢銷商品資料的商品目錄對應

在舊版暢銷商品報表中，系統會使用TopProducts 表格中的 rank_id 欄，將暢銷商品資料對應至新產生的表格中的商家商品目錄資料。

在新的暢銷商品報表中，entity_id 欄會匯出至 BestSellersProductCluster 表格，並對應至 BestSellersEntityProductMapping 表格中商家商品型錄的所有產品 ID。

BestSellers_TopProductsInventory (舊) BestSellersEntityProductMapping (新)
rank_id (位於 BestSellers_TopProducts) entity_id (位於 BestSellersProductClustersWeekly 和 BestSellersProductClustersMonthly 資料表中)
product_id product_id
merchant_id
aggregator_id

查詢範例

本節重點說明用於擷取暢銷產品資料的範例查詢變更。

範例 1：擷取特定類別和國家/地區的熱銷產品

下列查詢會傳回特定類別和國家/地區的熱銷產品。

使用 BestSellers_TopProducts 表格 (舊版)

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;

使用 BestSellersProductClusterWeekly 或 BestSellersProductClusterMonthly 表格 (新版)

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;

範例 2：擷取商品目錄中的熱門產品

下列查詢會傳回商品目錄資料中的熱門產品清單。

使用 BestSellers_TopProducts 表格 (舊版)

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);

使用 BestSellersProductClusterWeekly 或 BestSellersProductClusterMonthly 表格 (新版)

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);

此外，如要找出庫存中熱銷產品或品牌的數量，請使用 product_inventory_status 或 brand_inventory_status 欄，在 BestSellerProductClusterWeekly 或 BestSellerProductClusterMonthly 表格上執行查詢。請參閱以下查詢範例：

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

範例 3：擷取特定類別和國家/地區的頂尖品牌

下列查詢會傳回特定類別和國家/地區的熱銷品牌清單。

使用 BestSellers_TopBrands 表格 (舊版)

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;

使用 BestSellersTopBrandsWeekly 或 BestSellersTopBrandsMonthly 表格 (新版)

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;

範例 4：擷取商品目錄中熱門品牌的產品

下列查詢會傳回商品目錄中熱門品牌的產品清單。

使用 BestSellers_TopBrands 表格 (舊版)

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;

使用 BestSellersTopBrandsWeekly 或 BestSellersTopBrandsMonthly 表格 (新版)

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;

在這些查詢中，請取代下列項目：

  • DATASET：資料集名稱
  • MERCHANT_ID：商家帳戶 ID
  • DATE：日期，格式為 YYYY-MM-DD

後續步驟