Build a GA4 Ecommerce Transactions Performance Report in BigQuery

The GA4 interface gives you ecommerce reporting, but the moment you want a metric defined your way, on a grain you control, fed into your own dashboard, you hit the ceiling of what the UI will do. The raw export in BigQuery removes that ceiling. This code-along builds a custom ecommerce transactions performance report directly from your GA4 export, with every metric calculated transparently so you always know exactly what a number means.

A word of expectation-setting before we start. This report is not designed to replicate any GA4 screen, and it will not match one exactly. That is not a flaw in the query, it is a consequence of how the export, the documentation, and the various UI reports each define things slightly differently. In building this, the choices were guided by what the export actually contains, how both the current GA4 and the legacy Universal Analytics reports framed these metrics, the documented definitions where they exist, the correct scope for each metric, whether user, session, event, or item, and plain common sense. The result is a report you can defend line by line, which is more valuable than one that happens to tie out to a screen whose own definitions are opaque.

Once built, this query is flexible. You can run it and open the result in Google Sheets or export a CSV, save it as a BigQuery table for deeper analysis, schedule it to refresh a table that feeds a Looker Studio dashboard, or simply treat it as a foundation you extend into your own reporting.

What the report contains

At the transaction level the report carries three descriptive fields: the date, the transaction id, and the transaction timestamp. From those transactions, and from the surrounding traffic, it computes a wide set of performance metrics: transactions, purchase revenue, purchasers, and three flavours of ecommerce conversion rate based on users, all sessions, and engaged sessions respectively. It also reports total items, unique items, refund value, tax value, and shipping value, and finishes with a family of averages: revenue per user, per purchaser, and per transaction, and items and unique items per purchaser and per transaction. Having all three conversion-rate denominators side by side is deliberate, because each answers a different question and the right one depends on what you are optimising.

Step one: a clean base layer

The first job is to read the export once and pull out the few things we need from the nested structures, namely the session identifier and engagement flag from the event parameters, along with the ready-made ecommerce record and tidy date and timestamp columns.

WITH base AS (
  SELECT
    user_pseudo_id,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id')   AS session_id,
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'session_engaged') AS session_engaged,
    event_name,
    ecommerce,
    PARSE_DATE('%Y%m%d', event_date)      AS event_dt,
    TIMESTAMP_MICROS(event_timestamp)     AS event_ts
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

This gives one row per event with everything later steps need. The session id and engagement flag come out of the parameter array, while the ecommerce record is already a structured field in the export, so its revenue, item, tax, shipping, and refund values are available without any unnesting. The date string is parsed into a real date and the microsecond timestamp into a real timestamp, which makes both grouping and display straightforward. The table suffix filter sets your reporting window and keeps the scan, and therefore the cost, contained.

Step two: the traffic denominators

Conversion rates need something to divide by, so before touching purchases we count the audience: distinct users, distinct sessions, and distinct engaged sessions across the whole window.

, traffic AS (
  SELECT
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING))) AS sessions,
    COUNT(DISTINCT IF(session_engaged = '1',
                      CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING)),
                      NULL)) AS engaged_sessions
  FROM base
)

A session is uniquely identified by the user and the session id together, which is why we concatenate the two before counting distinct values. Engaged sessions are the subset where the engagement flag was set, which the export stores as a string, hence the comparison to the text value. These three numbers become the denominators of the three conversion rates later.

Step three: one row per transaction

Now we isolate the purchases. The important subtlety here is deduplication, because GA4 can record the same purchase more than once, and counting those twice would inflate everything downstream. Grouping by transaction id and collapsing the duplicates fixes that, and this CTE is also where the report’s three descriptive fields live.

, transactions AS (
  SELECT
    event_dt                          AS date,
    ecommerce.transaction_id          AS transaction_id,
    MIN(event_ts)                     AS transaction_timestamp,
    ANY_VALUE(user_pseudo_id)         AS user_pseudo_id,
    MAX(ecommerce.purchase_revenue)   AS purchase_revenue,
    MAX(ecommerce.total_item_quantity) AS total_items,
    MAX(ecommerce.unique_items)       AS unique_items,
    MAX(ecommerce.refund_value)       AS refund_value,
    MAX(ecommerce.tax_value)          AS tax_value,
    MAX(ecommerce.shipping_value)     AS shipping_value
  FROM base
  WHERE event_name = 'purchase'
    AND ecommerce.transaction_id IS NOT NULL
  GROUP BY date, transaction_id
)

Filtering to purchase events with a real transaction id, then grouping by that id, guarantees one row per genuine transaction. Taking the earliest timestamp gives a stable transaction time, and because the monetary and item values are identical across any duplicate purchase rows, taking the maximum simply selects that single value safely. This CTE is useful in its own right: output it directly and you have a clean transaction-level detail table.

Step four: the performance report

Finally we aggregate the transactions into the headline figures and combine them with the traffic denominators to produce the rates and averages.

, summary AS (
  SELECT
    COUNT(*)                       AS transactions,
    COUNT(DISTINCT user_pseudo_id) AS purchasers,
    SUM(purchase_revenue)          AS purchase_revenue,
    SUM(total_items)               AS total_items,
    SUM(unique_items)              AS unique_items,
    SUM(refund_value)              AS refund_value,
    SUM(tax_value)                 AS tax_value,
    SUM(shipping_value)            AS shipping_value
  FROM transactions
)
SELECT
  s.transactions,
  s.purchase_revenue,
  s.purchasers,
  ROUND(s.transactions / t.users,            4) AS ecom_cr_users,
  ROUND(s.transactions / t.sessions,         4) AS ecom_cr_all_sessions,
  ROUND(s.transactions / t.engaged_sessions, 4) AS ecom_cr_engaged_sessions,
  s.total_items,
  s.unique_items,
  s.refund_value,
  s.tax_value,
  s.shipping_value,
  ROUND(s.purchase_revenue / t.users,        2) AS avg_revenue_per_user,
  ROUND(s.purchase_revenue / s.purchasers,   2) AS avg_revenue_per_purchaser,
  ROUND(s.purchase_revenue / s.transactions, 2) AS avg_revenue_per_transaction,
  ROUND(s.total_items / s.purchasers,        2) AS avg_items_per_purchaser,
  ROUND(s.total_items / s.transactions,      2) AS avg_items_per_transaction,
  ROUND(s.unique_items / s.purchasers,       2) AS avg_unique_items_per_purchaser,
  ROUND(s.unique_items / s.transactions,     2) AS avg_unique_items_per_transaction
FROM summary s
CROSS JOIN traffic t

The summary counts transactions as rows, counts purchasers as the distinct buyers behind them, and sums the money and item figures. The final select then cross joins that single summary row against the single traffic row, which is the clean way to bring the conversion denominators alongside the conversion numerators. Each ecommerce conversion rate is transactions divided by one of the three denominators, stated explicitly so there is no ambiguity about what it measures. The averages follow the same transparent pattern, dividing revenue or item counts by users, purchasers, or transactions as the metric name says.

Notes worth knowing

A few practical points will keep your numbers honest. The revenue, tax, shipping, and refund fields are reported in the property’s configured currency, and the export also offers parallel fields converted to US dollars if you would rather standardise, so swap those in when you report across currencies. The deduplication step matters more than it looks, since duplicate purchase events are a common cause of inflated revenue, and grouping by transaction id is what neutralises it. As stated at the outset, expect this report not to match the GA4 interface exactly, and treat that as a sign you now control the definitions rather than a problem to chase. And if you want a daily trend instead of a single period total, add the date to the grouping in the summary, carry it through, and you have a time series ready to drop into Looker Studio.

The strength of building it this way is ownership. Every metric is defined where you can see it, the transaction grain is clean and deduplicated, and the same query serves a spreadsheet, a saved table, or a live dashboard. Once it runs, extending it is easy, whether you want to break revenue down by channel, add a device split, or join in product detail from the items array.

See you soon.