You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

(Enhanced) Ecommerce transactions: dimensions & metrics

Join 2,600+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in
💡
This article is about GA3 - Universal Analytics

This example query contains all following Google Analytics ecommerce transactions dimensions and metrics. If you only need one dimension or metric, look at the -- comments in the example query and copy the part you need from the select clause. Make sure that you also add any additional conditions (in the from, where, group by and order by) that are necessary to calculate the results correctly.

(Enhanced) Ecommerce dimensions

  • transaction id

(Enhanced) Ecommerce metrics

  • transactions
  • ecommerce conversion rate
  • revenue
  • avg. order value
  • per session value
  • shipping
  • tax
  • revenue per user
  • transactions per user

Example query

select
  -- transaction id (dimension)
  hits.transaction.transactionid as transaction_id,
  -- transactions (metric)
  count(distinct hits.transaction.transactionid) as transactions,
  -- revenue (metric)
  sum(hits.transaction.transactionrevenue)/1000000 as revenue,
  -- ecommerce conversion rate
  count(distinct hits.transaction.transactionid) / count(distinct concat(cast(fullvisitorid as string), cast(visitstarttime as string))) as ecommerce_conversion_rate,
  -- avg. order value
  (sum(hits.transaction.transactionrevenue)/1000000)/count(distinct hits.transaction.transactionid) as avg_order_value,
  -- per session value
  (sum(hits.transaction.transactionrevenue)/1000000) / count(distinct concat(cast(fullvisitorid as string), cast(visitstarttime as string))) as per_session_value,
  -- shipping
  ifnull(sum(hits.transaction.transactionshipping)/1000000,0) as shipping,
  -- tax
  ifnull(sum(hits.transaction.transactiontax)/1000000,0) as tax,
  -- revenue per user
  (sum(hits.transaction.transactionrevenue)/1000000) / count(distinct fullvisitorid) as revenue_per_user,
  -- transactions per user
  count(distinct hits.transaction.transactionid) / count(distinct fullvisitorid) as transactions_per_user
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits
where
  totals.visits = 1
group by
  transaction_id
having
  hits.transaction.transactionid is not null
order by
  revenue desc