Skip to main content

GA4 | dimensions & metrics

Ecommerce dimensions & metrics (GA4)

How to query the most important ecommerce (transaction) dimensions and metrics from the GA4 BigQuery export, like transaction id, total item quantity, purchase revenue, refund value, shipping value and tax value.

A lot of Google Analytics 4 dimensions and metrics 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.

Other dimensions and metrics that you might need are a bit harder to access. You will have to (re)calculate them, which can require some serious SQL-skills.

While designing the course Query GA4 Data In Google BigQuery I've learned a lot about best practices to calculate dimensions and metrics. To share this knowledge with you I will provide a combined query for default dimensions and metrics, and a single example query for every non-default dimension or metric.

As always, drop a line in the comments if you have any questions, feedback or suggestions related to this article.

💡
Reclaim a significant amount of your time by subscribing to our premium membership. You'll gain immediate access to a ready-to-go GA4 ecommerce transactions performance report query.

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
💡
If you only need one default dimension or metric, look at the -- comments in the example query below 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. E.g, the query below is ungrouped, so every row corresponds to an event and may contain duplicate rows.

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
    event_name = 'purchase'
    -- define static and/or dynamic start and end date
    and _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    transaction_id