(Enhanced) Ecommerce transactions: dimensions & metrics
The most important (enhanced) ecommerce transactions dimensions and metrics for GA360 BigQuery export, like transaction id, transactions, revenue, ecommerce conversion rate and avg. order value.
💡
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