In previous lessons, we’ve explored the fundamentals of setting up the BigQuery export, dissected data structures, and learned how to navigate and extract meaningful information from events and event parameters. Now, we’re diving into the user_properties field, a powerful source of data that adds depth to the understanding of our users.
This is part 6 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
In addition to event parameters which we explored in the last tutorial, GA4 also allows you to define user properties, which serve as valuable context-enhancers for your website or app users.
It’s important to note though that GA4 doesn’t collect user properties by default; they must be explicitly implemented through Firebase or Google Tag Manager.
In this tutorial, we’ll uncover the nuances of working with user properties, where they’re stored in the GA4 BigQuery export, and how to extract meaningful data from them. Let’s dive in!
The user_properties RECORD
Within the GA4 BigQuery export, user properties are stored in the user_properties field. Just like the event_params field this is a REPEATED RECORD which can include numerous values nested inside.
From the screenshot below you can see that the structure of the user_properties field is almost exactly the same as event_params, the only difference being the column names and the inclusion of an additional timestamp field.
The below table provides a bit more detail on each field within the user_properties RECORD.
| Field Name | Data Type | Description | Example Values |
|---|---|---|---|
| user_properties.key | STRING | The name of the user property | user_type status first_open_time |
| user_properties.value.string_value | STRING | Property value will be populated in this field if represented by a string | premium logged_in |
| user_properties.value.int_value | INTEGER | Property value will be populated in this field if represented by an integer | 1, 2, etc. 1481243413060000 |
| user_properties.value.double_value | FLOAT | Property value will be populated in this field if represented by a double value | 1.5 |
| user_properties.value.float_value | FLOAT | Not currently in use | N/A |
| user_properties.value.set_timestamp_micros | INTEGER | Timestamp indicating when the user property was last set | 1481243342873000 |
Extracting data from the user_properties field
The queries below can be used to extract values from the user_properties RECORD.
Important Note: In all the queries provided below, make sure to replace the placeholders within the WHERE clause with the specific values for the table you are working with. For example, if you want to extract data for January 31st 2021 from the e-commerce sample dataset, use the following values:
- <project> should be replaced with bigquery-public-data
- <dataset> should be replaced with ga4_obfuscated_sample_ecommerce
- <table> should be replaced with events_20210131
Ensure that you substitute these placeholders with your actual project, dataset, and table names when executing the queries.
Count of occurrences by property
The below query will generate a list of properties along with a count of their respective occurrences. Please take note of the UNNEST function which is necessary for this task.
SELECT
up.key AS user_property,
COUNT(*) AS occurrences
FROM `<project>.<dataset>.<table>`, UNNEST(user_properties) AS up
GROUP BY 1
ORDER BY 2 DESCExtract property value
To extract a specific property value, we need to utilize a subquery within the main query.
Take a look at line 2 below, where we focus on selecting the value.string_value from the unnested user_properties field, filtering for cases where the key matches user_type. This approach enables you to extract only the specific property of interest.
SELECT
(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_type') AS user_type
FROM `<project>.<dataset>.<table>`
WHERE event_name = 'login'If the data is stored as an integer then we’d need to modify the query to pull from value.int_value field instead:
SELECT
(SELECT value.int_value FROM UNNEST(user_properties) WHERE key = 'first_open_time') AS first_open_time
FROM `<project>.<dataset>.<table>`Wrapping up
As we conclude this tutorial, you’ve gained a solid understanding of GA4 user properties within the BigQuery export. These properties offer a wealth of information about your users, and by learning how to work with them, you’ve unlocked the potential for deeper insights.
In the next tutorial, we’ll continue our journey through the GA4 BigQuery export as we learn how to extract data from multiple export tables in a single query.