UNNEST, the First BigQuery Function for GA4 E-commerce Tracking

Table of Contents

Introduction
Function Definition
Data Validation
UNNEST items
UNNEST event_params
UNNEST user_properties
Conclusion

#1 Introduction

At seeing a GA4 table in BigQuery we see that it does not look like what we are used to. This is because data is nested. To access data it is necessary to master the UNNEST function.

When it comes to E-commerce Tracking there are three important nested parameters: items, event_params and user_properties. In this article we’ll see how to unnest them and access to relevant values for the Online Business.

#2 Function Definition

In programming, NEST describes code that performs a particular function and is contained within other code that performs a broader function. Put it plain:

• To each event that GA4 collects (event_name) some parameters are added (event_params_) and each of these parameters has a value.

In the following table we see that event_params column has a drop-down menu

If we select the drop-down we see how the table expands and new values appear. The values are nested!

Note: See this article by Todd Kerpeelman for a more detailed explanation of the UNNEST function

#3 Data Validation

If you have implemented E-commcerce with Tag Manager, these are some of the events you will see when previewing the table in BigQuery:

• GA4 (by default): page_view, session_start, fist_visit, scroll and user-engagement
• GTM (customized): view_item, add_to_cart, purchase, etc.

Tag Manager
The parameters added in each GTM tag are the important ones to analyze, since without an E-commerce implementation we would not have access to them.

BigQuery
If the Tag Manager parameters match the BigQuery columns, we have done a good implementation.

BigQuery-Items
Let’s look for example at the add_to_cart event in the image. The items parameter has 1 nested row. If we click on the drop-down we can see what type of values they are and thus access them.

#4 UNNEST items

When using the UNNEST function we are modifying the table to have access to the nested values. And, in order to have access to the information of each parameter, it is necessary to indicate which values we want to access.

SELECT event_name, item_id, item_name, item_category, price
FROM `gelma-store.analytics_273980759.events_20220218`,
UNNEST(items) AS item

Note: We could name the UNNEST function with any name we want. Note that I have used orange as an extreme example.

SELECT event_name, orange.item_id, orange.item_name, orange.item_category, orange.price
FROM `gelma-store.analytics_273980759.events_20220218`,
UNNEST(items) AS orange

#5 UNNEST event_params

If we wanted to access the nested values of the event_params column, we would use the same structure of the previous function: Substitute items for params.

SELECT event_date, event_name, params.key, params.value
FROM `gelma-store.analytics_273980759.events_20220218`,
UNNEST(event_params) AS params

When applying this query, the result is a table with all the values of the params.value parameter. Some of these values are strings, integer or float.

Within the table, the value.string_value is the most relevant value to access, since it gives us the information we are interested in. Therefore, we will modify the query to return this more specific value.

SELECT event_date, event_name, params.key, value.string_value 
FROM `gelma-store.analytics_273980759.events_20220218`,
UNNEST(event_params) AS paramsWHERE value.string_value IS NOT NUL

From this point on, it is interesting to improve the query to access the most interesting values for analysis and adding aggregate functions such as COUNT, SUM, AVG, …

#6 UNNEST user_properties

To access these nested values we will also substitute the nomenclature as in the previous example. But, in order not to repeat ourselves, in this example we will see how two parameters can be unnested together.

SELECT user.value.string_value AS date, event_name, params.key, params.value.string_value AS value
FROM `gelma-store.analytics_273980759.events_*`,
UNNEST(user_properties) AS user, 
UNNEST(event_params) AS params
WHERE params.value.string_value IS NOT NULL
AND user.key ='user_r'

In this way we can create a table that joins user parameters (in particular user_r that shows a date) with the rest of parameters.

#7 Conclusion

To analyze a GA4 table in BigQuery it is necessary to unnest the rows. Although there are several columns with nested values, with respect to Online Business, the values items, user_properties and event_params are the important ones.

UNNEST is the first function to domain to access these values. From here, you have to improve the query to access deeper levels to access the relevant data for analysis.

And, if we wanted to be more specific in the analysis, we could join the three tables (items, user_properties and event_params) into one. To do this, we will use the WITH function that I explain in the following article.