[GA4] BigQuery Export: How to Extract GA4 User Properties and Metrics

In today's fast-paced digital landscape, where understanding user behavior can make or break a campaign, leveraging the right tools is not just advantageous – it's essential. Google Analytics 4 (GA4) combined with BigQuery offers a powerhouse solution for marketers, enabling deep dives into user data, uncovering trends, and driving smarter strategies.

Whether you're looking to refine your audience targeting, optimize conversions, or simply stay ahead of the competition, this guide will walk you through the process of exporting GA4 user properties and metrics to BigQuery, understanding their significance, and applying advanced querying techniques to maximize the value of your data.

Here are the key benefits of exporting GA4 data to BigQuery:

• Merge GA4 data with information from Google Ads, CRM systems, and external datasets to comprehensively view your business performance.
• BigQuery’s BI Engine caches frequently used data, speeding up SQL queries and improving performance in BI tools like Google Data Studio.
• Set up automated queries and reports in BigQuery to eliminate manual data extraction, saving time and resources.
• Use BigQuery for complex analyses, predictive modeling, and identifying trends that might be hidden in standard GA4 reports.
• Access raw, unsampled GA4 data in BigQuery, bypassing limitations like sampling, cardinality issues, and thresholding.

GA4 user properties and metrics are essential components that help understand user behavior and interactions on your site. These elements provide granular insights that can be used to tailor marketing strategies effectively.

User properties in GA4 are attributes associated with individual users, such as age, gender, location, or custom properties that you define based on specific needs. These properties help you understand who your users are and how they interact with your site or app, providing valuable insights for personalizing user experiences and tailoring marketing strategies.

In BigQuery, user-scope parameters are typically located in the events_YYYYMMDD tables, nested within the event data. These parameters provide a consistent reference for analyzing user behavior across multiple sessions and events, making them vital for longitudinal studies and targeted marketing efforts.

However, it's important to note that not all user-scope parameters can be exported directly from GA4 to BigQuery. Certain sensitive or personally identifiable information (PII), such as user email addresses or phone numbers, cannot be collected or exported in GA4 due to strict privacy regulations and data protection policies.

GA4 is designed to prevent the collection of such data, ensuring it remains absent both in the platform and any exports, including those to BigQuery.

Instead, GA4 focuses on exporting anonymized or pseudonymous data, which still allows for effective analysis without compromising user privacy. While you have access to a wealth of user-level data, some specific details will need to be calculated or inferred through other means.

You can often calculate those user-scope parameters that aren't directly exported using SQL queries within BigQuery.

For example:

• While GA4 might not export a direct count of sessions per user, you can calculate this by aggregating session IDs within the events_YYYYMMDD table.
• Similarly, you can create custom metrics and dimensions by combining and transforming existing data fields.

This flexibility allows you to tailor your analysis to your specific needs, ensuring you can extract maximum value from your GA4 data.

In GA4, dimensions and metrics work together to provide a complete picture of user interactions on your site or app. Dimensions, such as 'Page Title' or 'User Location,' describe the attributes of your data, while metrics like 'Page Views' or 'Event Count' measure the quantity of these interactions.

Alternatively, metrics like 'Page Views' or 'Session Duration' measure both the quantity and duration of user interactions.

By analyzing these together, you can gain deep insights into how users engage with your content, helping you to make informed decisions and optimize your marketing strategies.

Please check out this video to learn more about effectively using dimensions and metrics in your analysis.

Before extracting user properties and metrics from GA4, it’s important to ensure that they are set up correctly. Without properly configuring GA4 to capture the right user properties, your export data may be incomplete or inaccurate.

If you need a step-by-step guide on how to configure and export GA4 user properties to BigQuery, we’ve got you covered. Check out our comprehensive guide: How to Set Up and Export GA4 User Properties to BigQuery for detailed instructions.

This article explains why user properties are critical, how to configure them in GA4, and how to export them to BigQuery for advanced analysis. Once you’ve set everything up, you’ll be ready to extract and analyze these properties with advanced queries in BigQuery.

Mastering advanced querying techniques is essential to unlocking the full potential of your GA4 data in BigQuery. GA4 data is often nested and complex, requiring a solid understanding of how to work with nested fields and user-scope parameters.

GA4 exports data to BigQuery with a nested field schema, particularly for user-scope parameters. These parameters, which provide valuable insights at the user level, are typically nested within event data, making it necessary to use functions like UNNEST to access them. Understanding this schema is crucial for accurate data extraction and analysis, enabling you to maximize your user-level data.

In the event_YYYYMMDD tables, user-scope parameters are nested within the event data. These parameters track user-specific attributes across various sessions and events, providing a consistent view of user behavior over time. Querying these parameters allows you to analyze user interactions in detail and draw meaningful conclusions about user engagement.

The pseudonymous_users_YYYYMMDD table contains user-scope parameters associated with anonymized or pseudonymous user identifiers. These parameters help you track user behavior without exposing personally identifiable information (PII), ensuring data privacy while allowing for comprehensive user activity analysis across sessions.

When working with GA4 data in BigQuery, you’ll often encounter nested structures like arrays and structs, which organize data into complex, multi-level formats.

Arrays hold multiple values within a single field, such as a list of event parameters for an event, while structs group related fields into a single, composite data type. To analyze these nested structures effectively, you must know how to use BigQuery functions like UNNEST and STRUCT.

For example, let's say you want to extract the value of a specific event parameter, such as page_location, from the event_params array within the event_YYYYMMDD table.

(And by the way, you can run this query yourself - it takes the public data available for everybody to play with)

Here’s how you might write the query:

Here:

• user_pseudo_id: This field represents the unique identifier for each user, allowing you to track user behavior across different events and sessions.
• event_name: This field captures the name of the event (e.g., 'page_view'). It is used in the query to filter and focus on specific events of interest.
• param.value.string_value AS page_location: This part of the query extracts the value associated with the page_location key from the event_params array.
• FROM Clause: Specifies the GA4 event table being queried. The format event_YYYYMMDD indicates a daily partitioned table, where YYYYMMDD represents the specific date.
• UNNEST(event_params) AS param: The UNNEST function is used to flatten the event_params array, turning each element of the array into a separate row.
• WHERE Clause: The WHERE clause filters the data to include only rows where event_name is 'page_view'. Additionally, it filters the unnested parameters to include only those with the key 'page_location'.

This query demonstrates how to navigate and extract data from nested arrays and structs in GA4 using BigQuery. By mastering these techniques, you can perform detailed analyses and gain deeper insights into your data.

💡To dive deeper into how to work with arrays in BigQuery, including using functions like UNNEST, be sure to read our detailed article on BigQuery Array Functions. It’s a must-read for mastering advanced data analysis techniques!

Extracting user dimensions and metrics from GA4 data in BigQuery is essential for in-depth analysis and reporting. User dimensions describe various attributes of your users, such as location or device type, while metrics quantify user interactions, like session counts or conversions.

In GA4, these data points are stored in the events_YYYYMMDD tables, which hold daily event data with nested user properties. By querying these tables effectively, you can unlock detailed insights into user behavior, segment audiences more precisely, and enhance your marketing strategies.

Let's explore how to extract both default and calculated user dimensions and metrics using specific SQL queries in BigQuery.

GA4 provides several default user dimensions that help describe your users' attributes, such as their ID, activity status, and properties associated with them.

These dimensions are crucial for understanding user behavior and can be extracted from the events_YYYYMMDD tables in BigQuery. Below, each dimension is presented with a sample query to help you understand how to retrieve it.

This dimension represents the user ID set via the setUserId API. It's a unique identifier for users who have logged in or been assigned a specific ID.

This dimension is the pseudonymous ID (e.g., app instance ID) assigned to users. It's used to track users anonymously across sessions.

This dimension indicates whether the user was active (true) or inactive (false) at any point during the calendar day. This is available from 2023-07-17 onward.

This dimension records the time (in microseconds) when the user first opened the app or website. It helps to understand when the user first interacted with your site or app.

This dimension represents the name of the user property. Replace <insert key> with the specific property key you are interested in, or delete the WHERE clause to select all properties.

This dimension extracts the string value of a specific user property. Replace <insert key> with the specific property key, or delete the WHERE clause to select all.

This dimension indicates the time (in microseconds) when the user property was last set. Replace <insert key> with the specific property key, or delete the WHERE clause to select all.

This dimension represents the currency of the user's lifetime value, helping to understand the financial contribution of users in different currencies.

You can create more targeted analyses and improve your overall data-driven strategies by leveraging these dimensions, such as user IDs, activity status, and custom user properties.

Default user metrics in GA4 are vital for quantifying user interactions and behavior on your website or app. These metrics help you measure and analyze aspects such as user engagement, lifetime value, and custom properties that provide valuable insights into the effectiveness of your marketing strategies.

Extracting these metrics from the events_YYYYMMDD tables in BigQuery allows you to perform detailed analysis and reporting.

Below are some key default user metrics and example queries to help you retrieve and analyze them in BigQuery.

This metric captures the integer value of a specific user property. Replace <insert key> with the specific property key you wish to analyze, or remove the WHERE clause to select all.

This metric represents the float value of a user property. Replace <insert key> with the desired property key, or delete the WHERE clause to access all float values.

This metric captures the double value of a user property, which is useful for more precise measurements. Replace <insert key> with the specific property key, or remove the WHERE clause to retrieve all double values.

This metric measures the lifetime value (revenue) generated by the user. It's essential for understanding users' financial contributions over time.

These queries provide a foundation for extracting and analyzing key user metrics from GA4 data in BigQuery. By tailoring these queries to your specific needs, you can gain deeper insights into user behavior, helping you make data-driven decisions that improve your marketing efforts and overall business performance.

Calculated user dimensions in GA4 allow you to create custom dimensions by combining or transforming existing data. These dimensions help you gain deeper insights into user behavior by categorizing users in ways that standard dimensions may not cover.

For instance, you might want to distinguish between new and returning visitors or calculate the total number of sessions for each user. These calculated dimensions are essential for creating more tailored reports and understanding user patterns at a granular level.

Here are some examples of calculated user dimensions, along with the queries used to generate them:

The user type dimension categorizes users as either new or returning visitors. This is based on the ga_session_number parameter, which tracks the session count for each user.

The count of sessions dimension calculates the total number of sessions each user has had. This helps in understanding how often users return to your site or app.

Calculated user dimensions help tailor your analysis to understand your audience better and optimize your marketing strategies, ensuring you make data-driven decisions that align with your business goals.

Calculated user metrics in GA4 allow you to measure user behavior and engagement more precisely by applying custom logic to your data. These metrics are essential for understanding how different user segments interact with your site or app, and they provide insights that go beyond standard metrics.

Below are some key-calculated user metrics, along with queries and explanations.

This metric counts the total number of unique users based on the user_pseudo_id.

This metric identifies active users based on engagement criteria, such as time spent on the site or specific interactions.

This metric counts the number of users who interacted with your site or app for the first time.

This metric calculates the percentage of users who are new, compared to the total user base.

This metric counts the number of sessions started by new users, providing insights into how effectively your site or app attracts first-time visitors.

This metric calculates the percentage of sessions that were initiated by new users.

This metric calculates the average number of sessions per user, offering a clear view of user engagement over time.

This metric counts how many times a specific event, like page_view, was triggered per user.

This metric calculates the average number of engaged sessions per user.

Calculated user metrics in GA4 allow you to analyze user behavior and engagement more meaningfully. Whether you're measuring total users, active users, new users, or the number of sessions per user, these metrics provide deeper insights into how users interact with your site or app.

Session metrics in GA4 provide crucial insights into how users interact with your site or app during a specific visit or session. These metrics help you understand user engagement, session frequency, and overall behavior patterns.

By extracting and analyzing session metrics from GA4 data in BigQuery, you can gain a deeper understanding of the effectiveness of your content, the user experience, and the impact of your marketing efforts.

Session metrics in GA4 provide a comprehensive view of how users interact with your website or app during individual sessions. These metrics, including session counts, engagement rates, bounce rates, and session duration, are crucial for understanding user behavior and evaluating the effectiveness of your content and marketing strategies.

Below, you'll find examples of both straightforward and calculated session metrics and the corresponding queries to extract them.

This metric counts the total number of unique sessions users initiate, providing a baseline understanding of overall user engagement. Tracking sessions help assess how frequently users interact with your site or app.

This metric counts the number of "engaged" sessions based on user interaction or engagement time. Engaged sessions indicate the quality of user interactions during visits.

This metric calculates the percentage of sessions that were engaged compared to all sessions, offering insight into how effective your site or app is at keeping users engaged.

This metric calculates the average length of time in seconds that the app was in the foreground or the website had focus during a session.

This metric counts the number of sessions that resulted in a bounce, meaning the session ended without significant engagement. Monitoring bounces helps identify areas where user experience might need improvement.

This metric calculates the percentage of sessions that resulted in a bounce, providing insight into how well your site or app retains user interest. A high bounce rate may indicate issues with content relevance or user experience.

This metric calculates the number of times an individual event (e.g., 'page_view') was triggered per session, offering a measure of user activity during each visit.

This metric calculates the average duration of users' sessions in seconds, reflecting how long users stay engaged during a visit. Longer session durations often suggest better content or user experience.

This metric calculates the number of web pages viewed per session, indicating user engagement and interest. Higher views per session generally indicate that users find the content engaging and relevant.

These session metrics provide a detailed view of how users interact with your site or app during their sessions. By leveraging these queries, you can gain valuable insights into user engagement, session quality, and overall user behavior.

When working with GA4 data in BigQuery, extracting user properties is essential for gaining insights into user behavior and characteristics. In GA4, user properties are often stored in the pseudonymous_users_YYYYMMDD and users_YYYYMMDD tables.

• pseudonymous_users_YYYYMMDD Table

The pseudonymous_users_YYYYMMDD table holds data associated with anonymized or pseudonymous user identifiers. By querying this table, you can analyze user engagement, segment users based on properties like device type or location, and ensure data privacy while gaining meaningful insights.

• users_YYYYMMDD Table

The users_YYYYMMDD table contains user properties linked to identified users, such as those who have logged in or provided specific identifiers. Queries to this table enable you to track user journeys, analyze lifetime value, and create personalized experiences based on user-specific information.

💡For a deeper dive into understanding the GA4 BigQuery export, including event table schemas and handling dates, check out this article on our blog. It’s essential for mastering your GA4 data analysis! Read the article here.

Counting the occurrences of each user property in GA4 data is essential for understanding the distribution and importance of various user attributes. Using the UNNEST function in BigQuery, you can break down nested fields, such as user properties, and count how often specific attributes—like device type or subscription status – appear across your user base.

Let's walk through an example of counting the occurrences of different user properties to gain insights into user preferences.

Example:

Suppose you manage an e-commerce platform and want to understand the distribution of user properties such as "preferred_device" and "subscription_status" among your customers.

Here:

• UNNEST Function: The UNNEST function is used to flatten the user_properties array, turning each element within the array into a separate row.
• SELECT Clause: The query selects the key of each user property (e.g., "preferred_device", "subscription_status") and renames it as user_property.
• FROM Clause: The query is run against your specific BigQuery table, denoted as ... The UNNEST function is applied directly within the FROM clause to expand the user_properties array.
• GROUP BY and ORDER BY: The results are grouped by the user_property key, and the counts are ordered in descending order to show the most common properties first.

Using the UNNEST function in this query, you can efficiently count the occurrences of various user properties in your GA4 data.

Extracting specific user property values in GA4 data allows you to focus on particular attributes most relevant to your analysis. By filtering the key field in your query, you can target and extract only the specific property you’re interested in, such as a user’s device type or the time of their first interaction with your app.

Example:

Imagine you manage a mobile app and want to analyze the "user_type" property to understand whether users logging into your app are new or returning customers.

To extract a specific property value, use a subquery within the main query to filter for the desired property. Here’s how you can extract the "user_type" property:

If the property you want to extract is stored as an integer, you would modify the query to pull from the value.int_value field. For example, to extract the "first_open_time" property:

Here:

• Subquery within SELECT Clause: The subquery is used to extract the string_value associated with the user_type property. This targets only the specific property of interest.
• UNNEST Function: The UNNEST function is used to break down the user_properties array so that each property can be accessed and filtered individually.
• WHERE Clause: The WHERE clause filters the events to focus on a specific event, such as 'login', which is relevant to the analysis.
• String vs. Integer Values: Depending on the data type of the property, you might use value.string_value or value.int_value to extract the information.

Using targeted queries to extract specific user property values in GA4 data can provide precise insights into user behavior and characteristics.

Executing queries in BigQuery allows you to extract, analyze, and visualize data from your GA4 to BigQuery data export. You can dive into specific user behaviors by crafting precise SQL queries like the above, segment data by various properties, and uncover valuable insights that drive business decisions.

Whether you're counting occurrences, extracting specific values, or analyzing session metrics, running queries effectively in BigQuery is essential for leveraging the full potential of your GA4 data.

The OWOX: Reports, Charts & Pivots Extension is a powerful tool that allows you to query GA4 data directly from Google Sheets seamlessly.

This integration simplifies analyzing your GA4 data, enabling you to perform complex queries without leaving the familiar Google Sheets environment.

With just a few clicks, you can pull data from BigQuery into your sheets, making it easier to work with and share insights across your team.

Beyond querying data, the OWOX BI Reports Extension also helps you automatically build pivot tables and charts, transforming raw GA4 data into meaningful visualizations. This feature saves time and enhances your ability to interpret and act on the data.

Whether tracking key metrics or exploring user behavior trends, this tool provides a streamlined approach to making your GA4 data more accessible and actionable.