GA4BigQuery

Yesterday’s post: How to Measure and Analyze Cart Abandonment with GA4 and BigQuery
Today, we’ll go beyond a single funnel view and break down performance by campaign and by product category — so you can quickly see what’s driving the best conversion rates.

Why This Matters

Not all campaigns or product lines pull their weight.
If you’re only looking at aggregate funnel metrics, underperforming campaigns can hide in the averages.
With GA4 + BigQuery, you can isolate funnel performance by:

• Campaign source/medium
• Google Ads campaign name
• Product category or item name
• Any other marketing dimension

SQL Walkthrough

We’ll calculate step-by-step conversion rates for each campaign and product category.

Step 1: Get Funnel Events with Campaign & Product Info

WITH funnel_events AS (
  SELECT
    user_pseudo_id,
    campaign,
    source,
    medium,
    item_category,
    event_name,
    event_timestamp
  FROM
    `your_project.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
    AND event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
)

How this SQL works:

• Pulls all relevant funnel events in the given date range.
• Includes marketing attribution (campaign, source, medium) and product info (item_category).
• Uses event_name filtering so we only analyze funnel-related actions.

📄 Docs: GA4 Event Parameters Reference

Step 2: Calculate Conversion Counts by Campaign & Category

, funnel_counts AS (
  SELECT
    campaign,
    source,
    medium,
    item_category,
    COUNT(DISTINCT CASE WHEN event_name = 'view_item' THEN user_pseudo_id END) AS view_item_users,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id END) AS add_to_cart_users,
    COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id END) AS begin_checkout_users,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS purchase_users
  FROM funnel_events
  GROUP BY campaign, source, medium, item_category
)

How this SQL works:

• Groups data by campaign + product category.
• Uses COUNT(DISTINCT user_pseudo_id) to avoid counting the same user twice for the same step.
• Prepares a clear table for conversion rate calculations.

Step 3: Compute Step-by-Step Conversion Rates

SELECT
  campaign,
  source,
  medium,
  item_category,
  SAFE_DIVIDE(add_to_cart_users, view_item_users) AS view_to_cart_rate,
  SAFE_DIVIDE(begin_checkout_users, add_to_cart_users) AS cart_to_checkout_rate,
  SAFE_DIVIDE(purchase_users, begin_checkout_users) AS checkout_to_purchase_rate,
  SAFE_DIVIDE(purchase_users, view_item_users) AS overall_purchase_rate
FROM funnel_counts
ORDER BY overall_purchase_rate DESC

How this SQL works:

• SAFE_DIVIDE avoids division-by-zero errors.
• Calculates conversion rate for each funnel step plus the overall conversion rate.
• Orders results to highlight top-performing campaigns/categories first.

How to Use These Insights

• High view-to-cart but low checkout-to-purchase → checkout process might be a bottleneck.
• Low view-to-cart across campaigns → consider improving product descriptions or ad targeting.
• Compare campaigns head-to-head to see which brings high-intent traffic.

TL;DR

• This analysis lets you compare funnel conversion rates across campaigns and product categories.
• Use it to identify winning campaigns, spot underperformers, and focus your optimization efforts where they’ll have the biggest ROI.