Event Parameters and Other Records in GA4 BigQuery Data

GA4 captures a wealth of data in event parameter columns, representing custom parameters associated with user interactions on your digital properties.

BigQuery uses a column (or field) of data type “record” to store the record of all the event parameters and their values.

The record type data is nested arrays or structs, making it challenging to analyze them directly.

Event parameters are not the only ones that are stored in that way, there are other fields in all three GA4 tables in BigQuery, that are stored in a similar format.

By mastering how to deal with event parameters in BigQuery, you empower yourself to unlock the full potential of GA4 event parameter data.

I am sure you are already familiar with event parameters in BigQuery but let us look at it again.

In the screenshot below, you can see that the “page_view” event has several event parameters stored in the “event_params” column. This column contains the record of all the event parameters. This record contains, the “event_params.key” column, which contains the names of all the event parameters, and the values of each paramater are stored in one of the next three columns, based on the data type of the value.

If the value of the parameter is an integer ( a number without decimals and fractions) then the value is stored in the “event_params.value.int_value” column, if the value of the paramater is a string (any character) then the value is stored in the “event_params.value.string_value” column, however, if it is a decimal value then it is stored in the “event_params.value.douable_value” column. There is also an “event_params.value.float_value” column in GA4 BigQuery export, but that is not currently used by GA4/BigQuery.

If you try to write a “SELECT” statement referring to any of the event_params.key values then you will get an error.

So how do you find all the records where page_title is “Learn BigQuery for GA4” or where the source is “m.facebook.com”?

That’s where you need to use a function called UNNEST in GA4.

UNNEST is a BigQuery function that helps flatten these nested structures, allowing for more straightforward analysis and querying.

Step-by-Step Guide to UNNESTing GA4 Event Parameters:

Here is a sample SQL to UNNEST the records type data:

SELECT
  event_name,
  key,
  value
FROM
`optizent-test-project.analytics_289314775.events_20231209`,
  UNNEST(event_params)
WHERE
  event_name = 'page_view'

Explanation

UNNEST expands the column specified in parenthesis, event_parms, in this case, and creates a “virtual table”.

Next, we are doing a join between the main table and this “virtual table” and selecting three columns.

You can select multiple columns or all the columns by just using a * if you want. However, for simplicity, I am only selecting three columns. These three columns are “event_name” from the main table and “key” and “value” from the UNNEST table. “key” and “value” columns are created by the UNNEST function by expanding (unnesting) the event_params column.

Here is the result that you will see:

Unnest expands each nested value in its own row and joins it back to the main row.

Each unnested value, i.e. event parameter, is now in its own row.

So now if I have to pull all the records where page_title is “Learn BigQuery for GA4” then I can simply use a “WHERE” clause to narrow down the result set.

Note: If you are not familiar with the “WHERE” clause then read this first, https://bigquery.beehiiv.com/p/narrowing-down-result-set-bigquery.

Go ahead and try it.

If you want to see my SQL and advanced tips then check the “Advanced” section below.

Advanced:

Subscribe to Premium Membership to read the rest.