How to replicate the 'Conversions | (Standard) Ecommerce | Transactions' report (UA)
1 min read

How to replicate the 'Conversions | (Standard) Ecommerce | Transactions' report (UA)

A query to generate the Google Analytics Transactions report in BigQuery. In this report you'll find data about the performance of your (standard) ecommerce activities, segmented by transaction id.
How to replicate the 'Conversions | (Standard) Ecommerce | Transactions' report (UA)

As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.

Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.

Transactions report

In the Conversions | (Standard) Ecommerce | Transactions report you'll find data about the performance of your ecommerce activities, segmented by transaction id.

Let's query!

-- this query will not return quantity data as there is no standard ecommerce product data available in the sample data set

select
  hits.transaction.transactionid,
  ifnull(sum(hits.transaction.transactionrevenue)/1000000,0) as revenue,
  ifnull(sum(hits.transaction.transactiontax)/1000000,0) as tax,
  ifnull(sum(hits.transaction.transactionshipping)/1000000,0) as shipping,
  ifnull(sum(hits.item.itemquantity),0) as quantity
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits
where
  totals.visits = 1
  and hits.transaction.transactionid is not null
group by
  transactionid
order by
  revenue desc

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.