WITH, Create Unnested Tables in a GA4 E-commerce Project in BigQuery

4 min readMar 1, 2022

Table of Contents

Introduction
Function Definition
Beyond UNNEST
WITH clause
Example Resolution
Conclusion

#1 Introduction

In the previous article we saw how the UNNEST function gives access to the nested values of a BigQuery table.

Today we are going to put the parameters together in a single table that combines unnested string and numeric values so as to have a complete overview of what is going on in the Online Business. To do this, we will use the WITH function

Note: In a GA4 E-commerce project customized values from Tag Manager are the most relevant to analyze: event_params, items and user_properties.

#2 Function Definition

The WITHclause defines a temporary dataset whose result is available to be referenced in subsequent queries.

In practical terms, the WITH function allows you to go from a nested table that is difficult to interpret, to a nested table that combines different parameters in a way that is understandable for analysis.

Switching from a nested table to an unnested table

#3 Beyond UNNEST

With the UNNEST function we can unest the values of two parameters together. For example: event_params and user_properties.

SELECT user.value.string_value AS date, event_name, params.key, params.value.string_value AS valueFROM `gelma-store.analytics_273980759.events_*`,
UNNEST(user_properties) AS user,
UNNEST(event_params) AS params

If we wanted to add items, the logical thing to do would be to add a line of code with the parameters we wanted to access + the UNNEST function.

SELECT user.value.string_value AS date, event_name, params.key, params.value.string_value AS value, item_name, item_id, price,FROM `gelma-store.analytics_273980759.events_*`,
UNNEST(user_properties) AS user,
UNNEST(event_params) AS params,
UNNEST (items) AS item

Although the query is well constructed and BigQuery processes it, the result gives an error. We must find an alternative to access the third group of parameters items.

#4 WITH clause

By applying WITH we can add the three parameters user_properties, items and event_params in a single table. We want to get a table that shows:

user_properties: user_r
event_params: ga_session_number, page_title
items: item_id, item_name, item_category, price

Note: The table is correct and returns the unnested parameters for the add_to_cart event. To make it more visually appealing, in this article I explain the ARRAY_AGG(EXPR) function.

#5 Example Resolution

Step 1
A temporary table_1 has been created and returns the unnested values of event_params and user_properties for the add_to_cart event of E-commerce. The date format has also been modified with the PARSE_DATE function to make it more recognizable.

By using WHERE statement we can filter the name of the event as well as the name of the page where it occurs: page_title which is nested inside event_params.

Note: The end of the GROUP BY statement is followed by a comma , which indicates that the first temporary table ends and the second begins.

Step 2
A temporary table_2 has been created and returns the unnested values of items for the add_to_cart event of the same dataStream. The end of the statement GROUP BY ends without a comma.

Note: If we look at the SELECT command we are calling the event_timestamp value in both tables. This is so that both table have a join point.

Step 3
To join the two tables with the common value event_timestampit is necessary to use the LEFT JOIN function (although INNER JOIN, FULL OUTER JOIN would also work). This function joins all rows that have this common value.

#1 Conclusion

The WITHclause creates temporary tables that can be used by the main SQL statement, so that the code can be broken down into smaller, easier to understand chunks. The WITH clause can be used to create one or more SELECT statements, give them each a name and then reference them in the main query.

In today’s article we have seen how to apply it in a Digital Analytics Project for E-commerce, creating a temporary table with the values event_params and user_properties and another table with the values of items.

At the end of the statement we have used the LEFT JOINfunction to join both temporary tables and create a single permanent one that shows the data of these parameters together.