How to Query Key eCommerce Dimensions and Metrics from GA4 BigQuery Export

GA4 export data is the raw event data from Google Analytics 4 properties (including subproperties and roll-up properties). We can use export data to unlock powerful new features for tracking user interactions across your digital properties.

To get your GA4 export data in Big query you will need to link GA4 property to BQ 

We can use BigQuery to query raw event data directly to perform analysis or create a dashboard. GA4 export data consists of a large amount of dimensions and metrics hence the data is in a nested format         

NOTE: Sharing the GA4 schema to gain a comprehensive understanding of the data.

This blog will guide you through querying essential eCommerce dimensions and metrics such as total item quantity, total transactions, shipping value, purchase revenue, refund and tax value from your GA4 BigQuery export.

Queries

• Total Item Quantity

The total item quantity represents the number of items purchased in a transaction.

Query

SELECT

SUM(ecommerce.total_item_quantity) AS Total_item_quantity

FROM `projectid.dataset.events_*`

WHERE  event_name = 'purchase'

• Total transactions

Total number of transactions

Query

SELECT

COUNT( DISTINCT ecommerce.transaction_id) AS Total_transactions

FROM `projectid.dataset.events_*`

• Shipping value

Shipping value is the cost of shipping for a transaction.

Query

SELECT

SUM(ecommerce.total_item_quantity) AS Total_item_quantity

FROM `projectid.dataset.events_*`

WHERE  event_name = 'purchase'

• Purchase revenue

Purchase revenue is the total amount of revenue generated from a transaction.

Query 

SELECT

SUM(ecommerce.purchase_revenue) AS Purchase_revenue,

FROM `projectid.dataset.events_*`

WHERE  event_name = 'purchase'

• Refund Value

Refund value represents the amount refunded for a transaction.

Query

SELECT

SUM(ecommerce.refund_value) AS Refund_value,

FROM `projectid.dataset.events_*`

WHERE  event_name = 'purchase'

• Tax value

Tax value is the amount of tax applied to a transaction.

Query

SELECT

SUM(ecommerce.tax_value) AS Tax_value ,

FROM `projectid.dataset.events_*`

WHERE  event_name = 'purchase'

There are many more nested values present in ‘ecommerce’ and using them you can make data-driven decisions to enhance your business strategies.