[GA4] BigQuery Export: How to UNNEST GA4 Event Parameters
Alyona Samovar, Senior Digital Analyst @ OWOX
Get in-depth insights
Get in-depth insights
[GA4] BigQuery Export SQL Queries Library
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.
This article is Part 4 in our series, "[GA4] BigQuery Export."
If you're following along, check out the previous articles in the series:
These resources are designed to help you master the complexities of working with GA4 data in BigQuery.
What Are Event Parameters in GA4 Data?
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.
Explore BigQuery Data in Google Sheets
Bridge the gap between corporate BigQuery data and business decisions. Simplify reporting in Google Sheets without manual data blending and relying on digital analyst resources availability
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.
Overview of the Automatically Collected Parameters
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
Event Name | Description | Platform | Parameters | Collection Method |
ad_reward | Triggered when a reward is granted by a rewarded ad served by the Mobile Ads SDK. | App | ad_unit_code, reward_type, reward_value | - |
app_clear_data | Triggered when the user resets/clears the app data, removing all settings and sign-in data. | Android only | - | - |
app_exception | Triggered when the app crashes or throws an exception. | App | fatal, timestamp, engagement_time_msec | - |
app_remove | Triggered when an application package is removed (uninstalled) from an Android device. | Android only | - | - |
app_update | Triggered when the app is updated to a new version and launched again. | App | previous_app_version | - |
click | Each time a user clicks a link that leads away from the current domain is triggered. | Web | link_classes, link_domain, link_id, link_url, outbound (boolean) | Collected via enhanced measurement |
file_download | Triggered when a user clicks a link leading to a file (e.g., documents, videos, audio files). | Web | file_extension, file_name, link_classes, link_id, link_text, link_url | Collected via enhanced measurement |
firebase_campaign | Triggered when the app is launched with campaign parameters. | App | source, medium, campaign, term, content, gclid, aclid, cp1, anid, click_timestamp, campaign_info_source | - |
firebase_in_app_message_action | Triggered when a user takes action on a Firebase In-App Message. | App | message_name, message_device_time, message_id | - |
firebase_in_app_message_dismiss | Triggered when a user dismisses a Firebase In-App Message. | App | message_name, message_device_time, message_id | - |
firebase_in_app_message_impression | Triggered when a user sees a Firebase In-App Message. | App | message_name, message_device_time, message_id | - |
first_open | Triggered the first time a user launches an app after installing or re-installing it. | App | previous_gmp_app_id, updated_with_analytics, previous_first_open_count, system_app, system_app_update, engagement_time_msec | - |
first_visit | Triggered the first time a user visits a website or launches an Android instant app with Analytics enabled. | App, Web | - | - |
form_start | Triggered the first time a user interacts with a form in a session. | Web | form_id, form_name, form_destination | Collected via enhanced measurement |
form_submit | Triggered when the user submits a form. | Web | form_id, form_name, form_destination, form_submit_text | Collected via enhanced measurement |
in_app_purchase | Triggered when a user completes an in-app purchase, including an initial subscription, processed by the App Store or Google Play Store. | App | product_id, price, value, currency, quantity, subscription, free_trial, introductory_price | - |
notification_dismiss | Triggered when a user dismisses a notification sent by Firebase Cloud Messaging (FCM). | Android only | message_name, message_time, message_device_time, message_id, topic, label, message_channel | - |
notification_foreground | Triggered when a notification sent by FCM is received while the app is in the foreground. | App | message_name, message_time, message_device_time, message_id, topic, label, message_channel, message_type | - |
notification_open | Triggered when a user opens a notification sent by FCM. | App | message_name, message_time, message_device_time, message_id, topic, label, message_channel | - |
notification_receive | Triggered when a notification sent by FCM is received by a device when the app is in the background. | Android only | message_name, message_time, message_device_time, message_id, topic, label, message_channel, message_type | - |
os_update | Triggered when the device operating system is updated to a new version. | App | previous_os_version | - |
page_view | Triggered each time the page loads or the browser history state is changed by the active site. | Web | page_location (page URL), page_referrer (previous page URL), engagement_time_msec | Collected via enhanced measurement |
screen_view | Triggered when a screen transition occurs, and any of the following criteria are met: No screen was previously set, the new screen name differs from the previous screen name, the new screen-class name differs from the previous screen-class name, or the new screen ID differs from the previous screen ID. | App | firebase_screen, firebase_screen_class, firebase_screen_id, firebase_previous_screen, firebase_previous_class, firebase_previous_id, engagement_time_msec | - |
scroll | Triggered the first time a user reaches the bottom of each page (i.e., when a 90% vertical depth becomes visible). | Web | engagement_time_msec | Collected via enhanced measurement |
session_start | Triggered when a user engages the app or website. | App, Web | - | - |
user_engagement | Triggered when the app is in the foreground, or the webpage is in focus for at least one second. | App, Web | engagement_time_msec | - |
video_complete | Triggered when the video ends. | Web | video_current_time, video_duration, video_percent, video_provider, video_title, video_url, visible (boolean) | Collected via enhanced measurement |
video_progress | Triggered when the video progresses past 10%, 25%, 50%, and 75% duration time. | Web | video_current_time, video_duration, video_percent, video_provider, video_title, video_url, visible (boolean) | Collected via enhanced measurement |
video_start | Triggered when the video starts playing. | Web | video_current_time, video_duration, video_percent, video_provider, video_title, video_url, visible (boolean) | Collected via enhanced measurement |
view_search_results | Triggered each time a user performs a site search, indicated by the presence of a URL query parameter. | Web | search_term, q_<additional key> (optional) | Collected via enhanced measurement |
These automatically collected parameters are crucial for building a comprehensive view of user interactions, especially when analyzing data in BigQuery.
Custom Event Parameters
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.
Make Your Corporate BigQuery Data Smarter in Sheets
Transform Google Sheets into a dynamic data powerhouse for BigQuery. Visualize your data for wise, efficient, and automated reporting
How to Extract Event Parameters from GA4 Data in BigQuery
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.
Techniques for Expanding Event Parameters for Analysis in BigQuery
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.
Field Name | Data Type | Description | Example Values |
event_params.key | STRING | The name of the event parameter | page_location, firebase_screen, ga_session_id |
event_params.value.string_value | STRING | Parameter value stored as a string | https://mysite.com/, home |
event_params.value.int_value | INTEGER | Parameter value stored as an integer | 1, 2, 3, 1690585146 |
event_params.value.double_value | FLOAT | Parameter value stored as a double | 1.5, 22.99 |
event_params.value.float_value | FLOAT | Not currently in use | N/A |
Using UNNEST Function
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:
SELECT event_name, param.key, param.value.string_value, param.value.int_value, param.value.double_value
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS param
WHERE event_name = "page_view"
AND (param.key = "engagement_time_msec" OR param.key = "page_title")
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.
Seamless BigQuery Integration in Sheets
Get real-time, automated, and deeply insightful reporting at your fingertips. Connect BigQuery, run reports, and prepare dashboards in your favorite Google Sheets
Listing All Event Parameters
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.
SELECT
ep.key AS event_parameter,
COUNT(*) AS occurrences
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS ep
GROUP BY 1
ORDER BY 2 DESC
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.
How to Query Specific Event Parameters
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:
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'page_view'
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:
SELECT
user_pseudo_id,
event_date,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'session_start'
ORDER BY 1, 3 DESC
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.
Uncover in-depth insights
[GA4] BigQuery Export SQL Queries Library
Download nowBonus for readers
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.
SQL Query Example of Page Views for Analysis
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.
*/* Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS (
(SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name)
);
/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS (
(SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name)
);
SELECT
user_pseudo_id,
event_timestamp,
GetParamInt(event_params, 'ga_session_id') as ga_session_id,
GetParamString(event_params, 'page_location') as page_location,
GetParamString(event_params, 'page_title') as page_title
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name = 'page_view'
AND _TABLE_SUFFIX BETWEEN '20210120' AND '20210131'
ORDER BY
user_pseudo_id,
event_timestamp
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.
/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS (
(SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name)
);
/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS (
(SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name)
);
SELECT
user_pseudo_id,
event_timestamp
GetParamInt(event_params, 'ga_session_id') as ga_session_id,
GetParamString(event_params, 'page_location') as page_location,
GetParamString(event_params, 'page_title') as page_title
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name = 'page_view'
AND _TABLE_SUFFIX BETWEEN '20210120' AND '20210131'
AND ( SELECT value.string_value FROM UNNEST(event_params)
WHERE KEY = 'page_title') = 'Google Online Store'
ORDER BY
user_pseudo_id, event_timestamp
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.
Simplify BigQuery Reporting in Sheets
Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs
Filtering Event Data with Conditions
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.
SELECT
event_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
COUNT(*) AS event_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'page_view'
AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') > 10
AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') = 'Home
GROUP BY event_date, page_title, session_number
ORDER BY event_date DESC, event_count DESC
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.
SQL Query Example of Internal Links for Analysis
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.
SELECT
event_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS current_page,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_url') AS internal_link_clicked,
COUNT(*) AS link_click_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'internal_click'
AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_url') LIKE '%shop.googlemerchandisestore.com%'
GROUP BY event_date, current_page, internal_link_clicked
ORDER BY link_click_count DESC
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.
SQL Query Example of Counting Page Views by Source/Medium
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.
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
COUNT(*) AS page_view_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'page_view'
GROUP BY source, medium
ORDER BY page_view_count DESC
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.
SQL Query Example of Users Watching a Launched Video to the End
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'.
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'video_title') AS video_title,
COUNT(IF(event_name = 'video_start', event_timestamp, null)) AS start_views,
COUNT(IF(event_name = 'video_complete', event_timestamp, null)) AS complete_views
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'video_title') = 'Launched Video Title'
GROUP BY 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.
Using OWOX Reports Extension for Google Sheets
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.
Unlock BigQuery Insights in Google Sheets
Report on what matters to you. Integrate corporate BigQuery data into a familiar spreadsheet interface. Get insightful, up-to-date reports with just a few clicks
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.
FAQ
-
What is the UNNEST function in BigQuery, and how is it used with GA4 data?
The UNNEST function in BigQuery is used to convert arrays into individual rows. This is particularly useful for working with GA4 event parameters, as it allows you to break down and analyze the nested data more effectively.
-
What Are Event Parameters in GA4 Data?
Event parameters in GA4 are specific details or metrics collected alongside events to provide more context and insights. These parameters help in understanding user interactions on a deeper level by capturing additional information about the events.
-
How can I extract event parameters from GA4 data in BigQuery?
To extract event parameters from GA4 data in BigQuery, you can use the UNNEST function. This function helps in expanding the nested event parameters within your dataset, making it easier to analyze specific details.
-
How can I query specific event parameters in BigQuery?
You can query specific event parameters in BigQuery by using SQL commands that filter and select the desired parameters. This allows you to focus on particular aspects of user interactions for deeper analysis.
-
What are some examples of SQL queries to analyze GA4 data in BigQuery?
Examples of SQL queries include filtering event data with conditions, counting page views by source/medium, analyzing internal link clicks, and determining how many users watched a video to the end. These queries help in gaining actionable insights from your GA4 data.
Get in-depth insights
Get in-depth insights
[GA4] BigQuery Export SQL Queries Library