How to Extract GA4 Event Parameters in BigQuery: A Step-by-Step Guide

If you’ve been diving into your Google Analytics 4 (GA4) data in BigQuery, you might have noticed that key metrics like purchase revenue seem elusive — hidden away inside nested event parameters. Don’t worry, you’re not alone! This guide will walk you through how to extract those important GA4 event parameters in BigQuery, especially focusing on purchase-related data, so you can unlock valuable insights for your marketing analytics.

If you prefer learning visually, feel free to watch the full video tutorial here: Watch the GA4 BigQuery Event Parameters Tutorial.

Step 1: Understanding the GA4 Event Parameters Structure in BigQuery

When GA4 exports your analytics data to BigQuery, it structures event data in a way that stores most important values — such as revenue, transaction IDs, and page locations — inside an array called event_parameters. This array contains key-value pairs where each key represents the name of the parameter (like “currency” or “transaction_id”) and the value can be a string, integer, or float.

To access these nested parameters, you need to use BigQuery’s UNNEST function. This function allows you to flatten the array so that you can query the individual parameters as if they were columns in a table.

Step 2: Query Your GA4 Events Table

Begin by querying your GA4 BigQuery events table. The first thing is to identify your GA4 dataset and events table. In my case, the dataset is named j_four, and inside it, there’s a table containing event data.

Here’s a basic query to select all events from your GA4 table:

SELECT * FROM `your_project.your_dataset.events_*`

Replace your_project and your_dataset with your actual project and dataset names.

You will notice a column called event_name which tells you the type of event (e.g., purchase, page_view, session_start).

Step 3: Filter for Purchase Events

To focus on purchase events, you can filter your query like this:

SELECT * FROM `your_project.your_dataset.events_*`
WHERE event_name = 'purchase'

This will return only the rows where a purchase event occurred.

Step 4: Explore Available Event Parameters

Now, the question is: what event parameters are available for purchase events? To find out, you need to UNNEST the event_params array and pull out the distinct keys.

Here’s a query that lists all unique event parameter keys for purchase events:

SELECT DISTINCT ep.key
FROM `your_project.your_dataset.events_*`,
UNNEST(event_params) AS ep
WHERE event_name = 'purchase'

This query will return a list of parameter keys like transaction_id, currency, value, and many others.

Step 5: Extract Specific Event Parameter Values

Once you know the keys you want, for example, currency or transaction_id, you can extract their values by filtering the unnested array.

Here’s how to extract the currency value from the event parameters:

SELECT
  (SELECT ep.value.string_value
   FROM UNNEST(event_params) ep
   WHERE ep.key = 'currency') AS currency
FROM `your_project.your_dataset.events_*`
WHERE event_name = 'purchase'

This subquery inside the SELECT statement fetches the string value for the key currency from the event parameters array.

Step 6: Extract Purchase Revenue and Other Key Metrics

Similarly, you can extract the purchase revenue, transaction ID, and page location by adjusting the key in the subquery. For example, to get the revenue (usually stored under the key value or value_in_usd), you might write:

SELECT
  (SELECT ep.value.int_value
   FROM UNNEST(event_params) ep
   WHERE ep.key = 'value') AS revenue
FROM `your_project.your_dataset.events_*`
WHERE event_name = 'purchase'

Note that revenue could be stored as an integer or float, so check the data type in your schema.

To get multiple parameters in one query, you can extract them as separate columns:

SELECT
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'transaction_id') AS transaction_id,
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'currency') AS currency,
  (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'value') AS revenue
FROM `your_project.your_dataset.events_*`
WHERE event_name = 'purchase'

Step 7: Build Your Custom Revenue Dashboard

With these extracted parameters, you can now build a revenue dashboard in Looker Studio or any other BI tool. By joining this data with other marketing data sources, you can analyze performance by campaigns, user segments, or channels.

Querying GA4 event parameters in BigQuery gives you the flexibility to create tailored marketing dashboards that match your reporting needs.

Summary and Final Tips

Extracting GA4 event parameters in BigQuery might seem tricky at first due to the nested data structure, but by using the UNNEST function and filtering for the right keys, you can access valuable purchase data like revenue, currency, and transaction IDs.

• Start by querying your GA4 events table and filtering for purchase events.
• Use SELECT DISTINCT with UNNEST to explore available event parameter keys.
• Extract specific parameter values by filtering the unnested array for keys like currency and value.
• Combine these extractions in one query to get a comprehensive view of purchase data.
• Use this data to build custom dashboards and reports in Looker Studio or other BI tools.

If you want to deepen your skills with Google Sheets, Looker Studio, and BigQuery, check out these helpful articles:

• Learn Google Sheets: Master the OR Function Quickly
• What’s New in the Looker Studio Update
• Google Sheets to BigQuery Made Easy (No Coding Required)

Mastering the extraction of GA4 event parameters in BigQuery empowers you to get the most out of your analytics data and create impactful marketing insights. Happy querying!