"Know your channels", a wise man once said. Get actionable insights on your channel performance by building custom attribution models using Google Analytics 4 data in BigQuery. This end-to-end tutorial will show you how to prepare the GA4 data and build rule based and data driven (Markov) models.

medium_attribution_header.PNG

small_attribution_header.PNG

thumbnail_attribution_header.PNG

attribution_header.PNG

Build a Data Driven Attribution Model using Google Analytics 4, BigQuery and Python

** Price competitiveness and product benchmark data, already available in the Merchant Center interface, is made available in Google BigQuery. Leveraging these exports, you can create advanced insights into your product catalog that can be used for automation as well.**

- Export product and brand level market insight data (based on all the merchants on Google Shopping / Merchant center) to Google BigQuery
- Enrich your productfeed with the Merchant Center data to create valuable advanced insights and automations
- [BigQuery example query](#example-query)

## Connecting Merchant Center to Google BigQuery

First step, you will need a Google Cloud Project with an active billing account. If not present already, you can check out [these instructions](https://cloud.google.com/resource-manager/docs/creating-managing-projects).

The official [Google documentation](https://cloud.google.com/bigquery-transfer/docs/merchant-center-transfer#setting_up_a_google_merchant_center_transfer) provides all the info to set up the data transfer service between Merchant Center and Google BigQuery. When the connection is set up, Merchant Center will run a daily export of several reports.

![merchant-center-bigquery-export-flow.svg](https://static.stacktonic.com/merchant_center_bigquery_export_flow_dd1c547ff4.svg)
*Source: Google*

## Merchant Center exports

Several reports can be exported by the transfer service. You don't have to export all the reports (can be configured when setting up the export).

> Be aware that the Merchant Center exports are not that small in terms of GBs. For every available Google product category and country, it will export 10.000 top products for example. So it could generate some costs in your Google Cloud project. See the tips in the last chapter of this article.

The most important exports;

| Report | Table name | Description |
| - | - | - |
| Products and product issues | Product_\[ACCOUNT\] | Productdata provided to Merchant Center, enriched with information like status, item issues, Google product categories and (country) destinations |
| Price benchmarks | Products\_PriceBenchmarks\_\[ACCOUNT\] | Based on the **price competitiveness** report in the Merchant Center Interface. When available, it provides a price benchmark on product level based on data of other retailers (matching based on GTIN). |
| Best Sellers - Top Products | BestSellers\_TopProducts\_\[ACCOUNT\] | A ranked list of **top products** (max 10.000 products per country and Google product category). Based on data of all merchants (Shopping ads and unpaid listings). The same product can be featured multiple times in this table. Per ranking country + multiple times per Google product (sub) categories. |
| Best Sellers - Top Products Inventory | BestSellers\_TopProducts_Inventory\_\[ACCOUNT-ID\] | Mapping between your product feed and top products. The same product-ID can exist multiple times times (but has a unique ranking-ID). Per ranking country and in multiple Google product categories.  |
| Best Sellers - Top Brands | BestSellers\_TopBrands\_\[ACCOUNT\] | A ranked list of **top brands** (10.000 per country and Google product category). Based on data of all merchants (Shopping ads and unpaid listings). |

*Also see [this overview](https://cloud.google.com/bigquery-transfer/docs/merchant-center-transfer) in the official Google documentation.*

Some of the most interesting properties and definition;

- `price_benchmark_value`: The average (click-weighted) price of a specific product, based on all merchants who advertise that same product on Shopping ads. Matching products between merchants is based on GTIN. 
- `rank`: The popularity of the product on Shopping ads, on Google product category and country level. Popularity is based on the estimated number of products sold. The rank updates daily, but data in the exports can be delayed up to 2 days.
- `previous_rank`: The change in rank over the previous 7 days.
- `relative_demand.min / .max / .bucket`: A product's current estimated demand in relation to the product with the highest popularity rank in the same category and country. Also includes buckets (e.g. Very High)
- `previous_relative_demand.min / .max / .bucket`: Same as above, only calculated on the last 7 days.
- `price_range.min` / `price_range.max`: The lower and upper pricerange without decimals. Doesn't include shipping costs.

## Practical use cases & business value

Combining the different reports, you can think of the following use cases;

- **Product feed status**: Get Merchant Center and Shopping errors and notifications related to your product feeds.
- **Product inventory optimization**: Using the best seller reports, you can check which popular products are already in your inventory and what potential products could be added, based on the complete list of top products per Google product categories AND country. 
- **Price and bid optimization**: Using the price competitiveness benchmark information, you can check how you are doing in terms of price against competitors. 
- ** Trending products and brands**: Identify trending products / brands based on the changes in best seller rank over the last X days / weeks / months. Differentiate per country
- **Product feed enrichment**: Enrich you product feed with Google market insights data
- **Rule based automation**: Based on the product feed enrichment, pause and enable campaigns / products in channels (e.g. Google Shopping) or adjust bids. For example based on price competitiveness.

![Enrich product feed with Merchant Center data](https://static.stacktonic.com/enrich_productfeed_with_merchent_center_data_8477494f1d.PNG)

## Example query

To show you how the data can be combined, I've constructed a query that will join and combine data out of different Merchant Center tables on product level. Some remarks:

- **Google doesn't provide data for every product in your feed**. Either a product is not in the bestsellers list or Google just doesn't provide or calculate it (not enough data and probably some other reasons). Or you have unique products that other merchants don't offer.
- Use the `BestSellers_TopProducts_Inventory` as a mapping table to join the `BestSellers_TopProducts` data with your `Product` data by joining on `rank_id` (available in both tables)
- The "original" product-ID as known in your systems is not directly available in the different exports. It's available in the `product_id` field but you have te split the value and select the last part since Google added additional information to it, example: `online:nl:NL:<original-product-id>`
- The reports contain a lot of data. For example the best seller reports contains 10.000 best sellers in every Google product category and country. For efficiency reasons, try to select only the countries needed. Also important because the same product can be duplicated over different countries in the table Google provides.
- The reports contain nested and repeated data, make sure you understand the table structures. For example the product errors and notifications are nested.

**Example: Enrich product feed with benchmark and price competitiveness data **

The following query will result in this table that you could match on your original productfeed and could serve as input for automation rules (on product level)

![enrich-productfeed-with-merchent-center-data.PNG](https://static.stacktonic.com/enrich_productfeed_with_merchent_center_data_8477494f1d.PNG)

Make sure you replace the dataset / table names with the correct values in your situation.

*View code on [GitHub](https://gist.github.com/krisjan-oldekamp/631e2f029d89538da45f4946f7d5be4f)*

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

## Closing thoughts

Using the BigQuery Merchant Center exports, you can generate insights at scale and use it for automation. But keep in mind,  the exports hold a lot of data, so it could generate some costs in your Google Cloud project (and you probably won't need all the data). The tables are date partitioned, so you could write a Python script or use Airflow to delete partitions older than X days to keep the amount of data in BigQuery (probably after you've created some aggregate reports).






Using the BigQuery export functionality of Google Merchant Center, Google provided market insights like price competitiveness and benchmarks can be leveraged at scale. Combining this data with you product feed, valuable insights and optimizations can be delivered.

large_google-shopping-bigquery-advanced-analysis.jpg

medium_google-shopping-bigquery-advanced-analysis.jpg

small_google-shopping-bigquery-advanced-analysis.jpg

thumbnail_google-shopping-bigquery-advanced-analysis.jpg

google-shopping-bigquery-advanced-analysis.jpg

Create Advanced Google Shopping Insights using Merchant Center BigQuery exports

Cloud (data) platforms such as AWS, Google Cloud or Azure enable you to build a marketing data platform that doesn't only ingest and join data, but can also produce actionable insights and activate data in other tools and platforms. 

Building a cloud Marketing Data Platform

Google Analytics 4 is more than just a measurement and reporting tool. The event data exports to Google BigQuery (available in the free version) and the GA4 API brings a ton of new possibilities. Think of advanced analysis, joining data with other sources, building prediction models or integrate with other systems.

Google Analytics 4 Advanced

Increased privacy concerns, legislation and browser updates affecting (third-) party cookies is resulting in a new era where users regain control over their data and organizations should rethink their customer and marketing strategy. This will impact a lot of area's such as data collection, advertising, first-party data strategy, legal and UX. 