GA4 E-commerce Schema in BigQuery

GA4’s e-commerce data in BigQuery splits across two structures: a transaction-level ecommerce RECORD and a product-level items REPEATED RECORD. Understanding both — and their relationship — is the starting point for any purchase analysis from raw GA4 data.

The ecommerce RECORD

The ecommerce RECORD stores transaction-level data. It’s a simple nested RECORD (not a repeated array), so accessing fields requires dot notation rather than UNNEST:

The ecommerce RECORD is only meaningful for e-commerce events — purchase, refund, begin_checkout, add_payment_info. For other events, these fields are null.

SELECT
  event_name,
  ecommerce.transaction_id,
  ecommerce.purchase_revenue,
  ecommerce.purchase_revenue_in_usd,
  ecommerce.total_item_quantity
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'

The items REPEATED RECORD

The items field is a REPEATED RECORD — an array — where each element represents one product in the transaction. A purchase with three distinct products produces one purchase event row with three elements in items.

Because items is a REPEATED RECORD, accessing product-level data requires UNNEST:

SELECT
  event_name,
  ecommerce.transaction_id,
  item.item_id,
  item.item_name,
  item.item_category,
  item.price,
  item.quantity,
  item.price * item.quantity AS line_revenue
FROM `project.analytics_123456789.events_*`,
  UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'

UNNEST on items expands each event into multiple rows — one per product. A purchase with 3 products becomes 3 rows. Account for this in aggregations: summing ecommerce.purchase_revenue across the unnested rows would triple-count the transaction revenue. Always aggregate revenue from the ecommerce RECORD before unnesting, or deduplicate by transaction_id after.

-- Safe revenue calculation from unnested items
SELECT
  ecommerce.transaction_id,
  -- Sum from the items-level (correct for item-grain analysis)
  SUM(item.price * item.quantity) AS calculated_revenue,
  -- From the ecommerce record (authoritative transaction revenue)
  MAX(ecommerce.purchase_revenue) AS transaction_revenue
FROM `project.analytics_123456789.events_*`,
  UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY ecommerce.transaction_id

items.item_params: Nested Custom Dimensions

Added in October 2023, item_params is itself a REPEATED RECORD within items. This enables custom product dimensions — arbitrary key-value pairs attached to each product, mirroring how event_params works for events.

Accessing item_params requires UNNEST twice: once for items, once for item_params:

SELECT
  item.item_id,
  item.item_name,
  (
    SELECT value.string_value
    FROM UNNEST(item.item_params)
    WHERE key = 'product_color'
  ) AS product_color,
  (
    SELECT value.int_value
    FROM UNNEST(item.item_params)
    WHERE key = 'warehouse_stock'
  ) AS warehouse_stock
FROM `project.analytics_123456789.events_*`,
  UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'

The same type detection considerations that apply to event_params apply here — see GA4 Event Params Type Detection for the COALESCE defensive pattern when type is uncertain.

Note that item_params only exists in tables after October 2023. Historical analysis spanning this date needs conditional handling if your queries depend on item_params fields.

Event Coverage for E-commerce Fields

Not every e-commerce event populates all fields. The GA4 recommended events spec defines which fields are expected for each event:

Filter to the appropriate event name before accessing e-commerce fields. Querying ecommerce.transaction_id across all events will return mostly nulls — only purchase and refund events carry a meaningful transaction_id.

Building a Products Table from GA4

A common mart pattern extracts the items UNNEST into a dedicated product-purchase table:

SELECT
  event_date,
  ecommerce.transaction_id,
  item.item_id,
  item.item_name,
  item.item_brand,
  item.item_category,
  item.item_category2,
  item.price,
  item.quantity,
  item.price * item.quantity AS line_revenue,
  item.coupon,
  item.discount
FROM {{ ref('base__ga4__events') }},
  UNNEST(items) AS item
WHERE event_name = 'purchase'

Paired with a session-grain purchase summary (one row per transaction from the ecommerce RECORD), these two tables handle most e-commerce reporting needs: revenue by product, category performance, coupon usage analysis, and refund tracking.