Enrich your product feed with Google Shopping Insights, like price competitiveness or demand, using Merchant Center BigQuery exports. Full article on stacktonic.com.

google_bigquery_merchant_center_export_feed_enrichment.sql

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