BigQuery tables behind Looker studio dashboard for the Shopping insights report v2 | Learning Center

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 shopping product report.

This query retrieves and summarises Google shopping data, focusing on total clicks, impressions, conversions, conversion value, cost, and ROAS (Return on Ad Spend) for each product, starting from 1 January 2023. It groups the data by product ID and product country to provide a clear summary of performance metrics across different products and regions:

SELECT 
  product_id, 
  product_country, 
  SUM(Clicks) AS total_clicks, 
  SUM(Impressions) AS total_impressions, 
  SUM(Conversions) AS total_conversions, 
  SUM(ConversionValue) AS total_conversion_value, 
  SUM(Cost) AS total_cost, 
  SAFE_DIVIDE(SUM(ConversionValue), 
  SUM(Cost)) AS roas, 
  AVG(AverageCpc) AS avg_cpc, 
  AVG(ConversionRate) AS avg_conversion_rate, 
  SUM(CrossDeviceConversions) AS total_cross_device_conversions, 
  AVG(Ctr) AS avg_ctr 
FROM `project.dataset.shopping_insight_report_v2_{table_suffix}` 
WHERE date >= '2023-01-01' 
GROUP BY 
  product_id, 
  product_country 
ORDER BY 
  total_conversions DESC LIMIT 10

Below is the detailed schema of the output tables in BigQuery:

Table 1: shopping_insight_report_v2_{table_suffix}

Full name	Type	Description
OfferId	STRING	The ID of the offer.
AccountDescriptiveName	STRING	The descriptive name of the account.
MerchantId	INTEGER	The ID of the merchant.
product_country	STRING	The country where the product is sold.
channel	STRING	The channel where the product is advertised.
date	DATE	The date of the record.
AggregatorId	STRING	The ID of the aggregator.
CountryCriteriaId	STRING	The ID of the country criteria.
campaign	STRING	The name of the campaign.
campaign_id	STRING	The ID of the campaign.
custom_currency	STRING	The custom currency used for unified currency calculations.
customer__currency_code	STRING	The currency code used by the customer.
ExternalCustomerId	STRING	The external customer ID.
LanguageCriteriaId	STRING	The ID of the language criteria.
mc_ids_match_perfb	BOOLEAN	Indicates if the MC IDs match.
AverageCpc	FLOAT	The average cost per click (CPC).
Clicks	FLOAT	The number of clicks.
ConversionRate	FLOAT	The conversion rate.
Conversions	FLOAT	The number of conversions.
ConversionValue	FLOAT	The value of conversions.
Cost	FLOAT	The cost of the ads.
CostPerConversion	FLOAT	The cost per conversion.
CrossDeviceConversions	FLOAT	The number of cross-device conversions.
Ctr	FLOAT	The click-through rate (CTR).
Impressions	FLOAT	The number of impressions.
SearchAbsoluteTopImpressionShare	FLOAT	The search absolute top impression share.
SearchClickShare	FLOAT	The search click share.
SearchImpressionShare	FLOAT	The search impression share.
ValuePerConversion	FLOAT	The value per conversion.
productROAS	FLOAT	The return on ad spend (ROAS) for the product.
product_data_timestamp_date	DATE	The timestamp date of the product data.
feed_label	STRING	The label of the feed.
product_country_status	STRING	The status of the product in the country.
availability	STRING	The availability of the product.
historical_servability	STRING	The historical servability of the product.
historical_CustomAttribute0	STRING	The historical custom attribute 0.
historical_CustomAttribute1	STRING	The historical custom attribute 1.
historical_CustomAttribute2	STRING	The historical custom attribute 2.
historical_CustomAttribute3	STRING	The historical custom attribute 3.
historical_CustomAttribute4	STRING	The historical custom attribute 4.
Price	FLOAT	The price of the product.
salePrice	FLOAT	The sale price of the product.
price_original_value	NUMERIC	The original price value of the product.
price_original_currency	STRING	The original currency of the product price.
sale_price_original_value	NUMERIC	The original sale price value of the product.
sale_price_original_currency	STRING	The original currency of the product sale price.
ActualProductPrice	FLOAT	The actual product price.
productIsOnSale	BOOLEAN	Indicates if the product is on sale.
is_disapproved	BOOLEAN	Indicates if the product is disapproved.
ActualProductPrice_current	FLOAT	The current actual product price.
product_id	STRING	The ID of the product.
has_benchmark	BOOLEAN	Indicates if the product has a benchmark.
price_benchmark_aggregator_id	INTEGER	The ID of the price benchmark aggregator.
price_benchmark_country_of_sale	STRING	The country of sale for the price benchmark.
price_benchmark_value	FLOAT	The value of the price benchmark.
price_benchmark_value_local_currency	FLOAT	The value of the price benchmark in local currency.
price_benchmark_local_currency	STRING	The local currency of the price benchmark.
price_benchmark_timestamp	STRING	The timestamp of the price benchmark.
max_date	DATE	The maximum date of the record.
account_TotalProducts	INTEGER	The total number of products in the account.
Account_TotalROAS	FLOAT	The total return on ad spend (ROAS) for the account.
account_TotalCost	FLOAT	The total cost for the account.
account_TotalConversionValue	FLOAT	The total conversion value for the account.
account_AvgCostPerProduct	FLOAT	The average cost per product for the account.
account_AvgConvValuePerProduct	FLOAT	The average conversion value per product for the account.
title	STRING	The title of the product.
image_link	STRING	The link to the product image.
link	STRING	The link to the product page.
mpn	STRING	The manufacturer's part number (MPN) of the product.
gender	STRING	The gender for which the product is intended.
gtin	STRING	The global trade item number (GTIN) of the product.
material	STRING	The material of the product.
adult	BOOLEAN	Indicates if the product is intended for adults.
pattern	STRING	The pattern of the product.
color	STRING	The color of the product.
Brand	STRING	The brand of the product.
age_group	STRING	The age group for which the product is intended.
condition	STRING	The condition of the product (e.g., new, used).
ProductTypeL1	STRING	The first level of product type classification.
ProductTypeL2	STRING	The second level of product type classification.
ProductTypeL3	STRING	The third level of product type classification.
ProductTypeL4	STRING	The fourth level of product type classification.
ProductTypeL5	STRING	The fifth level of product type classification.
CustomAttribute0	STRING	The custom attribute 0.
CustomAttribute1	STRING	The custom attribute 1.
CustomAttribute2	STRING	The custom attribute 2.
CustomAttribute3	STRING	The custom attribute 3.
CustomAttribute4	STRING	The custom attribute 4.
google_product_category_pathL1	STRING	The first level of Google product category path.
google_product_category_pathL2	STRING	The second level of Google product category path.
google_product_category_pathL3	STRING	The third level of Google product category path.
google_product_category_pathL4	STRING	The fourth level of Google product category path.
google_product_category_pathL5	STRING	The fifth level of Google product category path.
google_product_category_path	STRING	The full Google product category path.
product_type	STRING	The type of the product.
producttype_lastsegment	STRING	The last segment of the product type.
ProducttypeLastSegmentInTitle	INTEGER	Indicates if the last segment of the product type is in the title.
feedTitleContainsBrand	INTEGER	Indicates if the feed title contains the brand.
feedTitleContainsProductType	INTEGER	Indicates if the feed title contains the product type.
product_stats_clicks	FLOAT	The number of product stats clicks.
product_stats_impressions	FLOAT	The number of product stats impressions.
product_stats_ConversionValue	FLOAT	The conversion value from product stats.
product_stats_Cost	FLOAT	The cost from product stats.
product_stats_ValuePerConversion	FLOAT	The value per conversion from product stats.
product_stats_productROAS	FLOAT	The return on ad spend (ROAS) from product stats.
expected_value_per_day	FLOAT	The expected value per day when product had impressions. Used among other to calculate Lost Revenue.
count_days_with_impressions	INTEGER	The count of days with impressions.
price_difference	FLOAT	The price difference of the product.
price_difference_above_below	STRING	Indicates if the price difference is above or below the benchmark.
HigherThanAverageCost	BOOLEAN	Indicates if the product cost is higher than average.
HigherThanAverageRoas	BOOLEAN	Indicates if the product ROAS is higher than average.
HigherThanAverageConvValue	BOOLEAN	Indicates if the product conversion value is higher than average.
availability_current	STRING	The current availability status of the product.

Table 2: shopping_insight_report_v2_issues_{table_suffix}

Full name	Type	Description
OfferId	STRING	The ID of the offer.
channel	STRING	The channel where the product is advertised.
MerchantId	INTEGER	The ID of the merchant.
product_country	STRING	The country where the product is sold.
product_data_timestamp	TIMESTAMP	The timestamp of the product data.
date	DATE	The date of the record.
feed_label	STRING	The label of the feed.
destination	STRING	The destination of the product.
title	STRING	The title of the product.
image_link	STRING	The link to the product image.
link	STRING	The link to the product page.
gtin	STRING	The global trade item number (GTIN) of the product.
Brand	STRING	The brand of the product.
ProductTypeL1	STRING	The first level of product type classification.
ProductTypeL2	STRING	The second level of product type classification.
ProductTypeL3	STRING	The third level of product type classification.
ProductTypeL4	STRING	The fourth level of product type classification.
ProductTypeL5	STRING	The fifth level of product type classification.
CustomAttribute0	STRING	The custom attribute 0.
CustomAttribute1	STRING	The custom attribute 1.
CustomAttribute2	STRING	The custom attribute 2.
CustomAttribute3	STRING	The custom attribute 3.
CustomAttribute4	STRING	The custom attribute 4.
google_product_category_pathL1	STRING	The first level of Google product category path.
google_product_category_pathL2	STRING	The second level of Google product category path.
google_product_category_pathL3	STRING	The third level of Google product category path.
google_product_category_pathL4	STRING	The fourth level of Google product category path.
google_product_category_pathL5	STRING	The fifth level of Google product category path.
google_product_category_path	STRING	The full Google product category path.
product_type	STRING	The type of the product.
product_country_status	STRING	The status of the product in the country.
code	STRING	The issue code.
attribute_name	STRING	The name of the attribute causing the issue.
short_description	STRING	A short description of the issue.
detailed_description	STRING	A detailed description of the issue.
servability	STRING	The servability status of the product.
documentation	STRING	Documentation related to the issue.
resolution	STRING	The resolution for the issue.
product_stats_clicks	FLOAT	The number of clicks for the product.
product_stats_impressions	FLOAT	The number of impressions for the product.
product_stats_ConversionValue	FLOAT	The conversion value for the product.
product_stats_Conversions	FLOAT	The number of conversions for the product.

BigQuery tables behind Looker studio dashboard for the Channel grouping performance report v2

BigQuery tables behind Looker studio for the Creative insights report

BigQuery tables behind Looker studio dashboard for the Performance Max breakdown report v2

BigQuery tables behind Looker studio dashboard for the Shopping insights bestseller report

BigQuery tables behind Looker studio dashboard for the Precis starter report