Extract GA4 Event Parameters in BigQuery

Welcome to the next installment of our GA4 BigQuery export series. In the previous tutorials, we’ve covered essential topics like setup, accessing data, and querying events. Now, we’re diving deeper into the world of the GA4 BigQuery export by learning how to extract event parameters.

This is part 5 of 10 in our series, “Get Started with the GA4 BigQuery Export”. Check out the course overview for a full list of all the topics covered.

Introduction

As you delve deeper into your GA4 data, you’ll discover that most events come with additional layers of context and detail, thanks to event parameters. For instance, a page_view event might carry crucial information like page_location and page_title, while a purchase event could reveal currency and value.

These invaluable insights are neatly tucked away within the event_params field, and in this tutorial, we’ll unlock their potential, showing you how to extract and utilize them effectively.

The event_params RECORD

In tutorial 3 we introduced the concept of RECORD fields within the GA4 events export. Some of these fields are REPEATED, including the event_params field. This means that accessing the valuable data within it isn’t as straightforward as a simple SELECT statement. However, fear not, because working with this data is not as daunting as it may initially seem.

Take a look below at an example of what the event_params field looks like. Nested within the first row, you’ll find nine different parameters, such as session_engaged, engagement_time_msec, and page_title. One thing to note is that the values are stored in different columns, depending on the type of data, whether it’s a STRING, INT, or DOUBLE (please note that the FLOAT field is currently inactive).

It’s not uncommon to get caught up trying to extract data from the string_value field when it’s actually stored in one of the other columns. So, when constructing your queries, double-check that you’re referencing the correct field to avoid any pitfalls along the way.

Let’s take a closer look at each field within the event_params RECORD to gain a deeper understanding of their roles and significance.

Field Name	Data Type	Description	Example Values
event_params.key	STRING	The name of the event parameter	page_location firebase_screen ga_session_id
event_params.value.string_value	STRING	Parameter value will be populated in this field if represented by a string	https://mysite.com/ home
event_params.value.int_value	INTEGER	Parameter value will be populated in this field if represented by an integer	1, 2, 3, 1690585146
event_params.value.double_value	FLOAT	Parameter value will be populated in this field if represented by a double value	1.5, 22.99
event_params.value.float_value	FLOAT	Not currently in use	N/A

Querying the event_params RECORD

Below, we’ve compiled a set of queries that are your key to unlocking the valuable insights hidden within the event_params RECORD.

Count of occurrences by parameter

This query will provide you with a list of parameters and the count of occurrences. It’s a valuable way to gauge the prevalence of different parameters in your GA4 event data:

SELECT
  ep.key AS event_parameter,
  COUNT(*) AS occurrences
FROM `<project>.<dataset>.<table>`, UNNEST(event_params) AS ep
GROUP BY 1
ORDER BY 2 DESC

Extract parameter value

To pinpoint and extract a specific parameter value, we’ll employ a subquery technique within our query.

Take a look at line 2 below, where we focus on selecting the value.string_value from the unnested event_params field, filtering for cases where the key matches page_location. This approach enables you to extract only the specific parameters of interest.

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `<project>.<dataset>.<table>`
WHERE event_name = 'page_view'

If your data is stored as an integer, such as the ga_session_number, you’ll need to make a slight adjustment to the query. In this case, we’ll pull from value.int_value instead of value.string_value. This modification ensures that we correctly retrieve integer-based data where applicable.

SELECT
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number
FROM `<project>.<dataset>.<table>`
WHERE event_name = 'session_start'
ORDER BY 1 DESC

Wrapping up

Bravo! You’ve successfully navigated the intricacies of event parameter extraction in the GA4 BigQuery export. In this tutorial, we’ve equipped you with the essential queries and techniques needed to tap into the full potential of the event_params RECORD.

In the next instalment, we’ll shift our focus to user properties, learning how to extract granular information about users from the user_properties field.

Next >> 6. Extract GA4 user properties in BigQuery