How to Track GA4 Purchase Revenue in BigQuery (Made Simple)

If you’ve set up your first GA4 export to BigQuery and want to dive deeper into your store’s purchase data, you’re in the right place. In this tutorial, I’ll walk you through how to query purchase revenue effectively using BigQuery with your GA4 data. Whether you run a small e-commerce store or a large website, understanding how to extract and analyze purchase events is crucial for marketing analytics and business insights.

Before we jump in, if you prefer a video tutorial, you can watch my detailed walkthrough here: Watch the BigQuery GA4 Purchase Revenue Tutorial.

Step 1: Understand Your GA4 BigQuery Export Structure

Once you have your GA4 data exported to BigQuery, your data is organized mainly into two parts: analytics folders and events tables. Every event on your website or app gets recorded here, including purchases if you have enhanced e-commerce tracking set up.

For example, under your dataset, you’ll find tables named like events_YYYYMMDD which contain daily event data. Inside these tables, the purchase events and their associated parameters like revenue are stored.

To get a grip on the schema and fields available, you can explore the event details within BigQuery. Look for the ecommerce object inside the event parameters schema. This object contains valuable fields such as purchase_revenue, which is the main metric we want to query.

Step 2: Writing a Simple Query to Extract Purchase Revenue

Now, let’s write a straightforward query to pull purchase revenue from your GA4 data. The key points are:

• Use the ecommerce.purchase_revenue field to get the revenue amount.
• Filter events where event_name = 'purchase' to focus solely on purchase events.
• Use event_date to segment your data by date.
• If your store operates in a currency other than USD, convert revenue to USD for consistency.
• Use the _TABLE_SUFFIX to limit the date range and reduce query size and cost.

Here’s an example query snippet that you can adapt:

SELECT
  event_date,
  event_name,
  ecommerce.purchase_revenue AS revenue,
  ecommerce.purchase_revenue_usd AS revenue_usd
FROM
  `your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'
  AND event_name = 'purchase'
ORDER BY
  event_date DESC

This query selects purchase events in May 2023, showing both the raw revenue and revenue converted to USD.

Step 3: Handle Large Datasets with Table Suffix Filtering

If your website is large or your dataset is extensive, querying the entire dataset at once can be costly and slow. To optimize your queries and reduce BigQuery costs, always use the _TABLE_SUFFIX clause to limit the time period you’re querying. This method slices your dataset to only the necessary daily tables.

For example, if you want to analyze purchases from the last week or month, specify the date range in the suffix:

WHERE _TABLE_SUFFIX BETWEEN '20230501' AND '20230507'

This practice will keep your processed data size manageable and your costs low.

Step 4: Troubleshooting No Data for Purchase Events

Sometimes, when you run your purchase revenue query, you might see no results even though you expect purchase data. This can happen due to:

• Custom event naming conventions (instead of the default purchase event).
• Plugins or tracking configurations that alter how purchase data is sent.

If you suspect this, try running a query to list all distinct event names in your dataset. This will help you identify what purchase-related events exist on your website:

SELECT DISTINCT event_name
FROM `your_dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'
ORDER BY event_name

This will output all unique event names so you can check if your purchase event uses a different name like UA_purchase or purchase_funnel.

Step 5: Alternative Method to Extract Revenue from Event Parameters

If the ecommerce.purchase_revenue field is empty or missing, you can extract the purchase revenue directly from the event parameters array. GA4 stores detailed event parameters in nested arrays, which we can query using BigQuery’s NEST and UNNEST functions.

The idea is to:

• Unnest the event_params array to access each key-value pair.
• Filter for the parameter key that holds the revenue value (usually value or similar).
• Extract the revenue as an integer or double value.

Here is an example query to check if the revenue data exists in event parameters for purchase events:

SELECT
  event_name,
  ep.key,
  ep.value.int_value,
  ep.value.double_value
FROM
  `your_dataset.events_*`,
  UNNEST(event_params) AS ep
WHERE
  _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'
  AND event_name = 'purchase'
  AND (ep.value.int_value IS NOT NULL OR ep.value.double_value IS NOT NULL)

This query will return all event parameters with values for purchase events, letting you identify which key holds your revenue.

Step 6: Constructing a Robust Query to Calculate Purchase Revenue

To ensure you capture purchase revenue reliably, you can build a query that:

1. Checks ecommerce.purchase_revenue first.
2. If that is null, falls back to the revenue value extracted from event parameters.

You can use BigQuery’s COALESCE() function or IFNULL() to handle this fallback logic. For example:

SELECT
  event_date,
  event_name,
  IFNULL(
    ecommerce.purchase_revenue,
    (SELECT
       CAST(ep.value.int_value AS FLOAT64)
     FROM UNNEST(event_params) ep
     WHERE ep.key = 'value' AND ep.value.int_value IS NOT NULL
     LIMIT 1)
  ) AS purchase_revenue
FROM
  `your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230501' AND '20230531'
  AND event_name = 'purchase'

This way, your query returns purchase revenue even if the ecommerce object is not populated, making your analysis more robust.

Step 7: Final Thoughts and Next Steps

With these approaches, you can confidently query your GA4 BigQuery export to analyze purchase revenue. Whether you use the straightforward ecommerce.purchase_revenue field or dive deeper into event parameters, you have flexible options to fit different GA4 setups.

Once you master these basics, you can extend your queries to include:

• Customer segmentation by purchase behavior.
• Product-level revenue analysis.
• Time series analysis of sales performance.

These insights will empower your marketing strategies and sales optimizations.

Explore More Tutorials

If you found this tutorial helpful, check out these additional step-by-step guides to deepen your BigQuery and GA4 skills:

• How to Calculate GA4 Sessions in BigQuery: A Simple Step-by-Step Guide
• How to Extract GA4 Event Parameters in BigQuery: A Step-by-Step Guide
• How to Get GA4 Event Count in BigQuery: A Simple Step-by-Step Tutorial

Feel free to reach out with questions about BigQuery, Google Data Studio, or Google Sheets.