Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save krisjan-oldekamp/631e2f029d89538da45f4946f7d5be4f to your computer and use it in GitHub Desktop.
Save krisjan-oldekamp/631e2f029d89538da45f4946f7d5be4f to your computer and use it in GitHub Desktop.
Enrich your product feed with Google Shopping Insights, like price competitiveness or demand, using Merchant Center BigQuery exports. Full article on stacktonic.com.
-- Author: Krisjan Oldekamp
-- https://stacktonic.com/article/create-advanced-google-shopping-insights-using-merchant-center-big-query-exports
-- set variable to change the fetch date easily
declare gmc_fetch_date date default date('2021-09-05');
with
-- get productfeed uploaded in gmc for specific date
gmc_products as (
select
_partitiondate as date,
*
from
`dataset.products_<merchant-id>`
where
_partitiondate = gmc_fetch_date
and target_country in ('nl')
),
-- get mapping table to join rank (bestsellers) and productfeed
gmc_product_inventory as (
select
*
from
`dataset.bestsellers_topproducts_inventory_<merchant-id>`
where
_partitiondate = gmc_fetch_date
and (regexp_contains(rank_id, ':nl:'))
),
-- get price benchmark information
gmc_price_benchmarks as (
select
_partitiondate as date,
*
from
`dataset.products_pricebenchmarks_<merchant-id>`
where
_partitiondate = gmc_fetch_date
and country_of_sale in ('nl') -- specify the countrycodes, since dataset countains all countries
),
-- get bestseller information (if product ranks in bestseller list -> 10000 products per google product category and country).
gmc_bestsellers as (
select
_partitiondate as date,
inventory.product_id as product_id,
bestseller.rank_timestamp,
bestseller.rank_id,
bestseller.rank,
bestseller.previous_rank,
if(inventory.product_id is null,false,true) as product_in_inventory,
bestseller.ranking_country,
bestseller.ranking_category as ranking_category_id,
(select name from bestseller.ranking_category_path where locale = 'nl-nl') as ranking_category_path,
(select name from bestseller.product_title where locale = 'nl-nl') as product_title,
concat(gtins[safe_offset(0)],'|',gtins[safe_offset(1)],'|',gtins[safe_offset(2)],'|') as gtins,
bestseller.brand,
bestseller.google_brand_id,
bestseller.google_product_category as google_product_category_id,
(select name from bestseller.google_product_category_path where locale = 'nl-nl') as google_category_path,
bestseller.price_range.min as price_range_min,
bestseller.price_range.max as price_range_max,
bestseller.price_range.currency as price_range_currency,
bestseller.relative_demand.min as relative_demand_min,
bestseller.relative_demand.max as relative_demand_max,
bestseller.relative_demand.bucket as relative_demand_bucket,
bestseller.previous_relative_demand.min as previous_relative_demand_min,
bestseller.previous_relative_demand.max as previous_relative_demand_max,
bestseller.previous_relative_demand.bucket as previous_relative_demand_bucket
from
`dataset.bestsellers_topproducts_<merchant-id>` as bestseller
-- only join products when available in the productfeed and the bestsellers list
inner join gmc_product_inventory as inventory
on bestseller.rank_id = inventory.rank_id
where
_partitiondate = gmc_fetch_date
and ranking_country in ('nl')
)
-- join all the data on product level
select
products.date,
products.product_id as product_id_gms,
split(products.product_id, ':')[safe_offset(3)] as product_id,
products.target_country,
products.title,
products.price.value,
benchmarks.price_benchmark_value,
(cast(benchmarks.price_benchmark_value as float64) - cast(products.price.value as float64)) as price_benchmark_diff,
benchmarks.price_benchmark_currency,
bestsellers.rank,
bestsellers.previous_rank,
bestsellers.price_range_min,
bestsellers.price_range_max,
bestsellers.relative_demand_min,
bestsellers.relative_demand_max,
bestsellers.relative_demand_bucket,
bestsellers.previous_relative_demand_min,
bestsellers.previous_relative_demand_max,
bestsellers.previous_relative_demand_bucket
from gmc_products as products
-- join price benchmark data
left join gmc_price_benchmarks as benchmarks
on products.product_id = benchmarks.product_id
and products.date = benchmarks.date
-- join bestseller data
left join gmc_bestsellers as bestsellers
on products.product_id = bestsellers.product_id
and products.date = bestsellers.date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment