[GA4] BigQuery Export: How to UNNEST GA4 Event Parameters

Are you getting the most out of your Google Analytics 4 (GA4) data in BigQuery? Understanding how to UNNEST event parameters is crucial for deep data analysis. GA4 captures detailed user interactions, offering a wealth of insights about user behavior, but much of this data is nested within arrays.

This detailed guide explains the process of exporting GA4 data to BigQuery and effectively utilizing the UNNEST function to access and analyze complex nested data structures.

Whether you're a digital or data analyst or a BI expert, knowing how to pull actionable insights from GA4 event data is key. In BigQuery, one important skill is learning how to UNNEST GA4 event parameters.

This technique helps you get a clearer view of user behavior, allowing you to fine-tune your strategies confidently. Let’s dive into how you can turn raw data into actionable insights that drive your strategy forward.

These resources are designed to help you master the complexities of working with GA4 data in BigQuery.

Event parameters in Google Analytics 4 (GA4) are additional pieces of information that accompany each event, providing more context and details about user interactions. These parameters help you understand the specifics of each event, such as the page a user visited, the actions they took, or the items they interacted with.

As you explore your GA4 data, you’ll find that most events include valuable details within event parameters, such as page_location for a page_view or utm_source for events data. These insights are hidden in the event_params field.

GA4 event parameters are generally divided into two main types:

• Automatically Collected Parameters
• Custom Parameters

For example, if you click the banner below our Google Analytics property will collect the name of this specific banner in our system, the headline, the subheadline, the CTA button text & color as well as the image used in it.

While event parameters are powerful, there are some important restrictions to be aware of:

• Number of Parameters: GA4 allows you to add up to 25 custom parameters per event.
• Name Length: The name of each event parameter is limited to 40 characters.
• Value Length: The value for each parameter is capped at 100 characters.

Understanding these restrictions is crucial when designing your event-tracking strategy. It helps you prioritize the most important parameters and ensures your data collection is efficient and effective.

In Google Analytics 4 (GA4), certain events are automatically triggered by basic interactions with your app or website. If you use Google Analytics for Firebase SDK or gtag.js, these events are captured without additional coding.

These automatically collected events and their parameters are crucial when accessing and analyzing your raw event data in BigQuery. The parameters provide key insights into user interactions, such as the pages they visit, the devices they use, and how they engage with your content.

These automatically collected parameters include vital information such as the page URL, user engagement metrics, and device details. When accessing raw event data in BigQuery, these parameters can be useful for gaining insights into user interactions. Below is a list of the automatically collected event parameters:

• language
• page_location
• page_referrer
• page_title
• screen_resolution

Each parameter provides specific information that can help you better understand user activity. It's important to note that there are character limits for the values assigned to these parameters:

• language: 100 characters
• page_location: 1,000 characters
• page_referrer: 420 characters
• page_title: 300 characters

Here is a list of automatically collected events in Google Analytics 4 (GA4), along with their corresponding parameters

These automatically collected parameters are crucial for building a comprehensive view of user interactions, especially when analyzing data in BigQuery.

Custom events in Google Analytics 4 (GA4) allow you to track specific actions on your website or app that GA4 does not automatically collect. The name of a custom event is entirely up to you and should clearly describe what you intend to measure. For example, if you're tracking donations, you might name the event "donate."

Custom event parameters provide additional context and details about the custom event you've defined. While the custom event captures the specific action, the parameters help you understand the nuances of that action.

For instance, in a "donate" event, custom parameters might include the amount donated, the currency used, or whether the donation was successful.

Each parameter is a key-value pair:

• Parameter Name: Describes the type of information being collected, such as "amount" or "currency."
• Parameter Value: The specific data associated with that parameter, like "10.00" for a $10 donation.

These parameters allow you to capture more detailed information about each event, making your data more useful for analysis. It's important to remember that you can add up to 25 custom parameters per event, with each parameter name limited to 40 characters and each value limited to 100 characters.

You can expand event parameters and query specific data points using the UNNEST function. This approach allows you to filter, list, and analyze key metrics, such as page views, internal links, and user engagement with videos, offering a comprehensive view of user interactions.

Once your GA4 data is exported to BigQuery, extracting event parameters for analysis is straightforward. This section will guide you through the process, ensuring you can effectively analyze your data.

GA4 data stores event parameters in a nested structure within the event_params field. To effectively query this data, it's important to understand the structure of event parameters and how to reference them correctly in your SQL queries.

Structure of Event Parameters

In GA4, each event can have multiple parameters, which are stored in the event_params field as key-value pairs. The event_params field contains the following subfields:

• event_params.key (STRING): This field represents the name of the event parameter, such as page_location, firebase_screen, or ga_session_id.
• event_params.value.string_value (STRING): If the parameter value is a string, it will be stored in this field.
• event_params.value.int_value (INTEGER): This field stores parameter values represented as integers, such as session IDs (1690585146) or counts (1, 2, 3).
• event_params.value.double_value (FLOAT): If the parameter value is a double, it will be stored here, such as prices (22.99) or other decimal values (1.5).
• event_params.value.float_value (FLOAT): Although this field exists, it's currently inactive and not used in GA4.

Take a look at the event_params field below. Nested within the first row, you’ll find nine different parameters, such as session_engaged, engagement_time_msec, and page_title. One thing to note is that the values are stored in different columns, depending on the data type, whether it’s a STRING, INT, or DOUBLE.

Let’s examine each field within the event_params RECORD more closely to understand their roles and significance better.

The UNNEST function is essential for breaking down nested data structures, such as event parameters, into a flat format that can be easily queried and analyzed.

Example: Flattening Event Parameters

Let's consider a business scenario where you want to analyze user interactions by examining specific event parameters, such as the time spent on a page (engagement_time_msec) or the type of page viewed (page_title).

These parameters are stored in a nested structure within the event_params field, and to make sense of them, we need to flatten this structure using the UNNEST function.

Here's an example SQL query (that you can run yourself - it’s based on public data) that demonstrates how to transform these parameters into a flat structure for easy analysis:

Here:

• UNNEST(event_params) AS param: This part of the query expands the event_params array into individual rows, where each event parameter is treated as a separate record.
• param.key: Represents the name of the event parameter (e.g., engagement_time_msec, page_title).
• param.value.string_value, param.value.int_value, param.value.double_value: These fields store the actual values of the parameters, depending on their data types (string, integer, or double).

With this process, you can perform more advanced queries, making it easier to conduct detailed analyses, such as calculating average engagement time or identifying the most frequently viewed pages to derive insights.

Note: Always check which data type your parameter is stored in (STRING, INT, DOUBLE) to ensure accurate analysis and avoid common pitfalls in querying GA4 data.

When working with GA4 data in BigQuery, it's often important to understand the various event parameters being captured and their frequency of occurrence. This knowledge allows you to identify which parameters are most prevalent and, therefore, most valuable for further analysis.

The following SQL query provides a comprehensive list of all event parameters and a count of their occurrences, helping you gauge the significance of each parameter in your dataset.

Here:

• SELECT ep.key AS event_parameter: This part of the query selects the key from the event_params array and labels it as event_parameter.
• COUNT(*) AS occurrences: Here, the query counts the number of times each event parameter appears in the dataset. This count gives you an idea of how frequently each parameter is recorded.
• FROM .., UNNEST(event_params) AS ep: The UNNEST function is used to expand the nested event_params array into individual rows, allowing each event parameter to be counted separately.
• GROUP BY 1: This groups the results by the event_parameter, ensuring that each parameter's occurrences are aggregated.
• ORDER BY 2 DESC: Finally, the query orders the results by the count of occurrences in descending order, so the most frequent event parameters appear at the top of the list.

When analyzing GA4 data in BigQuery, it is often necessary to extract specific event parameters based on their data types, such as strings, integers, or doubles.

To do this effectively, you need to use the appropriate fields within nested queries, ensuring that you pull the relevant data for your analysis. This method allows you to precisely target parameters like page views or session numbers, providing accurate and actionable insights.

SQL Queries for Specific Event Parameters

To extract a specific string parameter, such as page_location, you can use the following query:

Here:

• (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location: This subquery extracts the string_value associated with the page_location key from the event_params array. The UNNEST function is used to break down the nested structure, allowing the query to focus on specific parameters.
• WHERE event_name = 'page_view': This clause filters the results to include only events related to page views, ensuring the query retrieves relevant data.

For integer-based parameters, such as ga_session_number, you would modify the query to target the int_value field:

Here:

• (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number: This query extracts the integer value associated with the ga_session_number key, making it ideal for analyzing session-based metrics.
• ORDER BY 1, 3 DESC: The results are ordered in descending order, which is particularly useful when analyzing session numbers to identify the most recent sessions per user.

By using targeted subqueries to extract specific event parameters, you can tailor your BigQuery analysis to focus on the most relevant data for your business needs.

To assist you further, we’ve compiled a collection of SQL queries designed to help you analyze GA4 data in BigQuery. These queries cover various use cases, from page views to user interactions.

Analyzing page views is common when working with GA4 data in BigQuery. You can gain insights into user behavior, popular content, and overall website performance by querying page views.

Example 1: Basic Page View Sequence Analysis

This query counts page views, groups them by date, page title, and page location, and then orders the results to show the most viewed pages by date.

Here:

• GetParamString: A function to extract string values (like page titles) from the event_params array.
• GetParamInt: A function to extract integer values (like session IDs) from the event_params array.
• user_pseudo_id: Tracks the anonymous user ID.
• event_timestamp: Tracks timestamp of events.
• GetParamInt for ga_session_id: Retrieves the session ID for grouping events.
• GetParamString for page_location and page_title: Extracts the URL and title of the page viewed.
• FROM Clause: Replace your_table_name_here with the name of your table.
• WHERE Clause event_name = 'page_view': Filters the data to include only page view events.
• _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD': Filters the date range.
• ORDER BY Clause: Orders the results by user and event sequence.

This query provides a comprehensive view of page views, allowing you to analyze which pages are most popular on specific dates.

Example 2: Page View Sequence Analysis with a Filter on page_title

This query adds a filter to focus only on page views for a specific page title.

Here:

• FROM Clause: Replace your_table_name_here with the name of your table.
• event_name = 'page_view': Filters the data to include only page view events.
• GetParamString(event_params, 'page_title') = 'Google Online Store': Adds a filter to focus only on page views where the page title is 'Google Online Store'.
• _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD': Filters the date range.
• ORDER BY Clause: Orders the results by user and event sequence.

Filtering event data in BigQuery allows you to extract specific insights by applying conditions to your queries. Using event parameters in the WHERE clause, you can target and analyze data based on precise criteria, such as session numbers, page titles, or any other event attributes.

Example:

This query filters page view events to include only those from sessions with a session number greater than 10 and where the page title is 'Home' page.

Here:

• event_date: This field extracts the date of the event, providing a time reference for your analysis.
• (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title: This subquery retrieves the page title for each event, focusing on the page_title key in the event_params field.
• (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number: This subquery extracts the session number from the event_params field, allowing you to analyze events based on specific sessions.
• COUNT(*) AS event_count: This function counts the number of events that meet the specified conditions, providing a tally of relevant events.
• WHERE event_name = 'page_view': This clause filters the data to include only page view events, ensuring that the query focuses on relevant interactions.
• AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') > 10: This condition further filters the data to include only sessions where the session number is greater than 10, targeting more engaged users.
• AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') = 'Home': This condition filters the data to include only page views where the page title is 'Home' page, focusing the analysis on a specific page.
• GROUP BY event_date, page_title, session_number: The results are grouped by event date, page title, and session number, organizing the data for clear analysis.
• ORDER BY event_date DESC, event_count DESC: The results are ordered by the most recent events and the highest event counts, ensuring that the most relevant data appears first.

This query enables you to filter GA4 event data based on specific criteria, such as session numbers or page titles, by applying conditions in the WHERE clause.

Internal link analysis is crucial for understanding user navigation patterns within your website. By tracking interactions with internal links, you can determine which pages drive traffic to other parts of your site and how users move through your content.

Example:

This query tracks the number of internal link clicks and identifies the pages where users interacted with internal links.

Here:

• event_date: Retrieves the date of each click event, which is important for tracking link performance over time.
• current_page: Extracts the URL of the page where the internal link was clicked, helping you understand the starting point of user navigation.
• internal_link_clicked: Identifies the specific internal link that was clicked, providing insight into the destination pages.
• WHERE (link_url) LIKE '%yourdomain.com%': Filters the query to include only clicks on internal links within your domain, ensuring the analysis is focused on internal navigation.
• link_click_count: Counts the number of times each internal link was clicked, revealing the most popular internal navigation paths.

This query helps you understand how users navigate within your site by identifying which internal links are most frequently clicked.

Understanding the source and medium that drive page views helps in evaluating the effectiveness of your marketing channels. This query tracks page views categorized by the source (e.g., Google, direct) and medium (e.g., organic, referral).

Example:

This query counts the number of page views and categorizes them by their traffic source and medium.

Here:

• source: Extracts the source of the traffic (e.g., Google, direct), which is crucial for understanding where your website visitors are coming from.
• medium: Extracts the medium of the traffic (e.g., organic, referral), providing additional context on the type of traffic.
• page_view_count: Counts the number of page views attributed to each source and medium, allowing you to assess the effectiveness of different traffic channels.
• GROUP BY source, medium: Groups the results by source and medium to aggregate the page views for each combination, making it easier to analyze traffic performance.

This query provides valuable insights into the effectiveness of your marketing channels by showing which sources and mediums drive the most page views.

Analyzing video completion rates is essential for understanding user engagement with video content. This query identifies how many users watched a specific video from start to finish.

Example:

This query counts the number of users who watched the entire video titled 'Launched Video Title'.

Here:

• video_title: Identifies the specific video being analyzed, allowing you to focus on the performance of a particular piece of content.
• start_views: Counts the number of times the video is started to be viewed.
• complete_views: Counts the number of times the video was watched to completion, which is a key metric for measuring user engagement with video content.
• WHERE (video_title) = 'Launched Video Title': Filters the query to focus on the specific video of interest, ensuring that the analysis is targeted and relevant.

This query helps you measure the engagement level of your video content by determining how many users watched a specific video from start to finish.

For those looking to streamline their reporting process, OWOX Reports Extension for Google Sheets automates the creation of reports, charts, and pivots. This extension is designed to make your data analysis more efficient by eliminating the need for manual data manipulation.

With OWOX Reports, you can set up automated reports that update in real time, ensuring you always have the latest insights at your fingertips.

The tool also simplifies the process of visualizing and analyzing your GA4 data directly within Google Sheets. By integrating seamlessly with Google Sheets, it enables you to create charts and pivots that provide actionable insights with minimal effort. This makes it easier to focus on interpreting data rather than getting bogged down in the technicalities of data preparation.