When Merchandising Meets Advanced Analytics

In the retail space, Google Analytics 4 (GA4) and BigQuery conversations are missing a key person - the Site Trading Manager.

While analysts focus on implementation and marketers on acquisition campaigns, Trading Managers making critical product and merchandising decisions are left with often basic Looker Studio dashboards and limited insights through predefined query logic in the GA4 interface.

Requests for detailed analysis often sit in the analytics team's backlog for weeks if a query has not already been saved, and many Trading Managers (in my experience) aren't always comfortable writing their own SQL queries.

This guide cuts through the technical jargon to help Trading Managers leverage these powerful tools for what truly matters - driving contribution.

Let's get into what's possible in the GA4 interface versus BigQuery, and explore some ready-to-use queries that can level up your trading teams knowledge.

GA4 Interface vs BigQuery - What You Need to Know

Trading Managers often find themselves limited by the standard GA4 interface. Here's why getting BigQuery access to your team can be a game changer!

GA4 Interface

• GA4’s standard reports (e.g., Engagement, Acquisition, Monetisation) are rigid. Want to tweak a report to show revenue by product category alongside user demographics? You’re stuck with what Google gives you unless you jump into the Exploration tool — which still has its own constraints.
  ‍
• For sites with heavy traffic (think millions of events), GA4 applies sampling to keep things speedy. This means you’re looking at estimates, not the full picture - potentially skewing decisions on high-stakes trades, campaigns or promotions.
  ‍
• Limited data retention period of 14 months for standard properties, if you have a Google Marketing Platform 360 licsence then it's max 50 months. The two month retention period is always applied to age, gender, and interest data (if Google Signals is activated) regardless of your settings and service level agreement.
  ‍
• GA4’s interface limits how you can mix and match data points. Want to pair “Session Source” with “Item Revenue” and “User Lifetime Value”? You’ll hit a wall unless you export the data elsewhere.

BigQuery

• With BigQuery, you’re not confined to pre-built templates. Using SQL, you can craft queries to answer any question. Such as like which products drive the most repeat purchases in specific regions - without compromise.
  ‍
• Say goodbye to sampling guesswork. BigQuery delivers every event, every click, every conversion. For trading managers, this means trusting your data when deciding which products to push or pull, even on high-traffic peak seasons.
  ‍
• Full historical data access as long as you’ve been passing data into BigQuery. Unlike GA4’s retention caps, BigQuery stores your data indefinitely (as long as you keep it there). Set up the export AS SOON AS POSSIBLE, and in three years, you’ll have a goldmine of historical trends to analyse - no 14-month cutoff in sight.
  ‍
• Managing multiple sites or brands? BigQuery lets you merge data across GA4 properties, giving you a unified view of performance. Imagine spotting cross-sell opportunities between your apparel and accessories stores with one query.

Key Benefits for the Trading Team

Adopting to BigQuery rather than using the GA4 user interface isn't just about overcoming the limitations, it's purely to empower your team to work smarter. It truly does pay off.

• With unsampled data, you can assess every SKU’s performance, not just the top performers. Spot underperforming products dragging down your margins or hidden gems ripe for promotion.
  ‍
• GA4 might say “customers who bought a jacket also bought a scarf.” BigQuery goes further. “Customers who purchased a discounted coat in Q1 came back for a scarf in Q3 full price, and no discount code was applied.” That’s the kind of insight that fuels smarter bundles or targeted campaigns, whatever your product mix.
  ‍
• With years of data, you can compare this winter’s sales to last year’s - or beyond. Picture noticing “Coats always spike as we enter peak in early October” and planning your stock or promos accordingly. It’s forecasting that keeps you ahead of the curve, season after season.
  ‍
• BigQuery runs on SQL (Structured Query Language), which might look intimidating when glancing at the empty query terminal for the first time. It’s a skill your team can pick up with online courses, I personally use DataCamp which covers literally everything data from beginners to experts. Soon, you’ll be querying “Which products are the largest inpulse buys using Apple Pay” like it’s second nature. It’s truley resume booster that pays off.

8 BigQuery Recipes to Unlock Your Hidden GA4 Insights

1. Top-Performing Products by Revenue

SELECT 
  item.item_name,
  COUNT(*) AS units_sold,
  SUM(item.item_revenue) AS total_revenue,
  ROUND(SUM(item.item_revenue) / COUNT(*), 2) AS avg_price_per_unit
FROM 
  `your_project.your_dataset.events_*` -- Your project.dataset goes here
  , UNNEST(items) AS item
WHERE 
  event_name = 'purchase'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
GROUP BY 
  item.item_name
ORDER BY 
  total_revenue DESC
LIMIT 10;

What it does: Ranks products by revenue over the last 7 days.

2. Products with High Traffic but Low Conversion

SELECT 
  v.item_name,
  COUNTIF(event_name = 'view_item') AS views,
  COUNTIF(event_name = 'purchase') AS purchases,
  ROUND(COUNTIF(event_name = 'purchase') / COUNTIF(event_name = 'view_item') * 100, 2) AS conversion_rate
FROM 
  `your_project.your_dataset.events_*` -- Your project.dataset goes here
  , UNNEST(items) AS v
WHERE 
  event_name IN ('view_item', 'purchase')
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
GROUP BY 
  v.item_name
HAVING 
  views > 1000 AND conversion_rate < 2 -- Adjust thresholds
ORDER BY 
  views DESC;

What it does: Finds products with many views but few purchases in the last 7 days.

3. Product Affinity (Frequently Purchased Together)

SELECT 
  a.item_name AS product_a,
  b.item_name AS product_b,
  COUNT(DISTINCT event_timestamp) AS co_purchases
FROM 
  `your_project.your_dataset.events_*` -- Your project.dataset goes here
  , UNNEST(items) AS a,
  UNNEST(items) AS b
WHERE 
  event_name = 'purchase'
  AND a.item_name < b.item_name
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
GROUP BY 
  a.item_name, b.item_name
ORDER BY 
  co_purchases DESC
LIMIT 10;

What it does: Identifies product pairs that have been purchased together in the last 7 days.

4. Seasonal Sales Trends by Product Category

SELECT 
  EXTRACT(DAY FROM TIMESTAMP_MICROS(event_timestamp)) AS day,
  item.item_category,
  SUM(item.item_revenue) AS total_revenue
FROM 
  `your_project.your_dataset.events_*` -- Your project.dataset goes here
  , UNNEST(items) AS item
WHERE 
  event_name = 'purchase'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
GROUP BY 
  day, item.item_category
ORDER BY 
  day, total_revenue DESC;

What it does: Shows daily revenue by category over the last 7 days.

5. Customer Repeat Purchase Rate by Product

SELECT 
  item.item_name,
  COUNT(DISTINCT user_pseudo_id) AS unique_buyers,
  COUNT(DISTINCT CASE WHEN purchase_count > 1 THEN user_pseudo_id END) AS repeat_buyers,
  ROUND(COUNT(DISTINCT CASE WHEN purchase_count > 1 THEN user_pseudo_id END) / COUNT(DISTINCT user_pseudo_id) * 100, 2) AS repeat_rate
FROM (
  SELECT 
    user_pseudo_id,
    item.item_name,
    COUNT(DISTINCT event_timestamp) AS purchase_count
  FROM 
    `your_project.your_dataset.events_*` -- Your project.dataset goes here
    , UNNEST(items) AS item
  WHERE 
    event_name = 'purchase'
    AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  GROUP BY 
    user_pseudo_id, item.item_name
)
GROUP BY 
  item.item_name
ORDER BY 
  repeat_rate DESC;

What it does: Measures repeat purchase per product in the last 7 days.

6. Average Session Duration by Traffic Source (Single Day)

SELECT 
  source,
  medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS total_sessions,
  ROUND(AVG(session_duration) / 1000000, 2) AS avg_session_duration_seconds
FROM (
  SELECT 
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    traffic_source.source AS source,  -- Explicitly select source
    traffic_source.medium AS medium,  -- Explicitly select medium
    MAX(event_timestamp) - MIN(event_timestamp) AS session_duration
  FROM 
    `your_project.your_dataset.events_20250324` -- Your project.dataset goes here; replace with your latest date
  WHERE 
    event_name IN ('page_view', 'user_engagement', 'purchase')
  GROUP BY 
    user_pseudo_id, session_id, traffic_source.source, traffic_source.medium
)
GROUP BY 
  source, medium
HAVING 
  total_sessions > 10
ORDER BY 
  avg_session_duration_seconds DESC;

What it does: Calculates the average session duration (in seconds) for each traffic source on a single day. It's also a sneaky way to check if your digital marketing team is actually bringing engaged and relevant customers onto the site! 

7. Slow-Moving Products with High Views

SELECT 
  item.item_name AS product_name,
  SUM(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS total_views,
  SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS total_purchases,
  ROUND(
    (SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) / 
     SUM(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END)) * 100, 
    2
  ) AS sell_through_rate
FROM 
  `your_project.your_dataset.events_20250324` -- Your project.dataset goes here; replace with your latest date
  , UNNEST(items) AS item
WHERE 
  event_name IN ('view_item', 'purchase')
GROUP BY 
  item.item_name
HAVING 
  total_views > 500 
  AND sell_through_rate < 5
ORDER BY 
  total_views DESC;

What it does: Identifies products with high amount of views but fewer purchases on a single day (it can be altered over any period of time).

8. Purchase Behaviour by Age & Gender

SELECT
  COALESCE(
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'age'),
    'Unknown'
  ) AS age_group,
  COALESCE(
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'gender'),
    'Unknown'
  ) AS gender,
  COUNT(DISTINCT user_pseudo_id) AS unique_buyers,
  SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS total_purchases,
  SUM(CASE WHEN event_name = 'purchase' THEN (SELECT SUM(item.item_revenue) FROM UNNEST(items) AS item WHERE items is not null) ELSE 0 END) AS total_revenue
FROM (
  SELECT
    user_pseudo_id,
    user_properties,
    items,
    event_name
  FROM
    `your_project.your_dataset.events_*` -- Replace with your project and dataset
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
)
GROUP BY
  age_group,
  gender
ORDER BY
  total_revenue DESC;

What it does: Breaks down total purchases and revenue by age and gender over the last 7 days, showing which demographics are spending the most.

Empowering Your Trading Team with Data

The queries and insights we've covered are just the beginning of what's possible when Trading Managers embrace GA4 and BigQuery. By bringing these powerful analytics capabilities directly to your trading team, you can:

• Make merchandising decisions based on complete data instead of sampled estimates
• Identify opportunities and issues before they impact your bottom line
• Respond to market changes and customer behavior shifts in real-time
• Reduce dependency on analytics teams for basic performance insights
• Build a data-driven trading culture that drives continuous improvement

While there may be a learning curve with SQL, the competitive advantage gained from having direct access to your customer and product data is immeasurable. The days of flying blind with basic reports or waiting weeks for analytics support are over.

I hope these queries and insights help your trading team unlock the full potential of your GA4 data.

If you would like more information on how we can help with your Google Analytics 4 setup, feel free to contact us.

‍

‍