Ecommerce: dimensions & metrics (GA4)
2 min read

Ecommerce: dimensions & metrics (GA4)

The most important ecommerce (transaction) dimensions and metrics for GA4 BigQuery export, like transaction id, total item quantity, purchase revenue, refund value, shipping value and tax value.
Ecommerce: dimensions & metrics (GA4)

A lot of Google Analytics 4 data can be derived straight from the tables without performing calculations on the data. With the help of unnest and parse for dates or timestamps we can do the trick.

If you only need one dimension or metric, look at the -- comments in the example query for names and definitions and copy the part you need from the select clause. Make sure that you also add any additional conditions (i.e. with, from, where, group by, having and order by) that are necessary to calculate the results correctly.

Default ecommerce dimensions

  • ecommerce.transaction_id

Default ecommerce metrics

  • ecommerce.total_item_quantity
  • ecommerce.purchase_revenue_in_usd
  • ecommerce.purchase_revenue
  • ecommerce.refund_value_in_usd
  • ecommerce.refund_value
  • ecommerce.shipping_value_in_usd
  • ecommerce.shipping_value
  • ecommerce.tax_value_in_usd
  • ecommerce.tax_value
  • ecommerce.unique_items

Example query

select
    -- transaction id (dimension | the transaction id of the ecommerce transaction)
    ecommerce.transaction_id,
    -- total item quantity (metric | total number of items in this event, which is the sum of items.quantity)
    sum(ecommerce.total_item_quantity) as total_item_quantity,
    -- purchase revenue in usd (metric | purchase revenue of this event, represented in usd with standard unit, populated for purchase event only)
    sum(ecommerce.purchase_revenue_in_usd) as purchase_revenue_in_usd,
    -- purchase revenue (metric | purchase revenue of this event, represented in local currency with standard unit, populated for purchase event only)
    sum(ecommerce.purchase_revenue) as purchase_revenue,
    -- refund value in usd (metric | the amount of refund in this event, represented in usd with standard unit, populated for refund event only)
    sum(ecommerce.refund_value_in_usd) as refund_value_in_usd,
    -- refund value (metric | the amount of refund in this event, represented in local currency with standard unit, populated for refund event only)
    sum(ecommerce.refund_value) as refund_value,
    -- shipping value in usd (metric | the shipping cost in this event, represented in usd with standard unit)
    sum(ecommerce.shipping_value_in_usd) as shipping_value_in_usd,
    -- shipping value (metric | the shipping cost in this event, represented in local currency)
    sum(ecommerce.shipping_value) as shipping_value,
    -- tax value in usd (metric | the tax value in this event, represented in usd with standard unit)
    sum(ecommerce.tax_value_in_usd) as tax_value_in_usd,
    -- tax value (metric | the tax value in this event, represented in local currency with standard unit)
    sum(ecommerce.tax_value) as tax_value,
    -- unique items (metric | the number of unique items in this event, based on item_id, item_name, and item_brand)
    sum(ecommerce.unique_items) as unique_items
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20201101' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
    and ecommerce.transaction_id is not null
group by
    transaction_id

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

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