If you want to work with the data output directly in BigQuery, getting started is straightforward. Below is an example query to analyse performance metrics from your Google universal bestseller list compared to your own inventory.
This query retrieves and summarises bestseller data, focusing on ranking, product titles, and inventory status. It groups the data by product ID and ranking country to provide a clear summary of performance metrics across different products and regions:
WITH latest_data AS ( SELECT *, RANK() OVER (PARTITION BY product_id ORDER BY data_date DESC) AS rank_date FROM `project.dataset.shopping_insight_bestseller_report_{table_suffix}` ) SELECT
product_id,
ranking_country,
product_title,
rank,
previous_rank,
is_in_inventory,
brand_is_in_inventory
FROM
latest_data
WHERE
rank_date = 1
ORDER BY
rank ASC LIMIT 10
Below is the detailed schema of the output tables in BigQuery:
Table: shopping_insight_bestseller_report_{table_suffix}
Full name | Type | Description |
brand | STRING | The brand of the product. |
ranking_country | STRING | The country where the ranking is applied. |
ranking_category_path | STRING | The path of the ranking category. |
is_brand_data | BOOLEAN | Indicates if the data is brand-specific. |
ranking_category | INTEGER | The ranking category. |
bucket | STRING | The bucket of the ranking. |
rank_id | STRING | The ID of the rank. |
rank | INTEGER | The rank of the product. |
previous_rank | INTEGER | The previous rank of the product. |
rank_id_nodate | STRING | The rank ID without the date. |
data_date | DATE | The date of the data. |
gtins | STRING | The Global Trade Item Numbers (GTINs) of the product. |
google_product_category_path | STRING | The path of the Google product category. |
google_product_category | INTEGER | The Google product category. |
min | NUMERIC | The minimum price of the product. |
max | NUMERIC | The maximum price of the product. |
currency | STRING | The currency of the product price. |
x_ranking_category_name_l1 | STRING | The first level name of the ranking category. |
x_ranking_category_name_l2 | STRING | The second level name of the ranking category. |
x_ranking_category_name_l3 | STRING | The third level name of the ranking category. |
x_ranking_category_name_l4 | STRING | The fourth level name of the ranking category. |
x_ranking_category_name_l5 | STRING | The fifth level name of the ranking category. |
x_ranking_category_name_l6 | STRING | The sixth level name of the ranking category. |
product_title | STRING | The title of the product. |
product_title_locale | STRING | The locale of the product title. |
product_id | STRING | The ID of the product. |
associated_brand_gtins | STRING | The associated brand GTINs. |
count_product_in_brand_cat | INTEGER | The count of products in the brand category. |
count_product_in_brand | INTEGER | The count of products in the brand. |
total_count_of_products_in_cat | INTEGER | The total count of products in the category. |
active_category | BOOLEAN | Indicates if the category is active. |
total_count_of_products_in_brand | INTEGER | The total count of products in the brand. |
active_brand | BOOLEAN | Indicates if the brand is active. |
total_count_of_products_in_brand_cat | INTEGER | The total count of products in the brand category. |
active_brand_cat | BOOLEAN | Indicates if the brand category is active. |
is_in_inventory | BOOLEAN | Indicates if the product is in inventory. |
brand_is_in_inventory | BOOLEAN | Indicates if the brand is in inventory. |