Ecommerce Analysis in BigQuery: Revenue, Products, and Customers

Ecommerce Data in GA4 BigQuery

When GA4 ecommerce tracking is implemented correctly, purchase events in the BigQuery export contain rich product data in the items array. This enables product-level analysis impossible in GA4's standard interface.

Query 1: Revenue by Product Category

SELECT
  item.item_category as category,
  SUM(item.quantity) as units_sold,
  SUM(item.price * item.quantity) as revenue,
  COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
    WHERE key = 'transaction_id')) as orders
FROM `project.analytics_PROPERTY.events_*`,
UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY category
ORDER BY revenue DESC

Query 2: Top Products by Revenue

SELECT
  item.item_id,
  item.item_name,
  SUM(item.quantity) as units_sold,
  SUM(item.price * item.quantity) as revenue,
  AVG(item.price) as avg_price
FROM `project.analytics_PROPERTY.events_*`,
UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY item.item_id, item.item_name
ORDER BY revenue DESC
LIMIT 20

Query 3: Purchase Frequency Analysis

WITH purchase_counts AS (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
      WHERE key = 'transaction_id')) as order_count
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260630'
    AND event_name = 'purchase'
  GROUP BY user_pseudo_id
)
SELECT
  order_count,
  COUNT(*) as customer_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct_of_customers
FROM purchase_counts
GROUP BY order_count
ORDER BY order_count

Query 4: Average Order Value Over Time

SELECT
  event_date,
  COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
    WHERE key = 'transaction_id')) as orders,
  SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) as revenue,
  ROUND(SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'))
    / COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
    WHERE key = 'transaction_id')), 2) as aov
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY event_date
ORDER BY event_date

Summary

BigQuery ecommerce analysis accesses product data via UNNEST(items) — the items array contains item_id, item_name, item_category, price, and quantity per product. This enables product-level revenue analysis, category performance, purchase frequency distribution, and AOV trends over time. All of these analyses are either impossible or require sampling in GA4's standard interface but run on 100% of data in BigQuery.

See our BigQuery Setup service for ecommerce analytics development.

Need ecommerce analysis in BigQuery? Contact Adslytics.