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

GA4 Google BigQuery

icon Get in-depth insights

[GA4] BigQuery Export SQL Queries Library

icon 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.

table

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

Simplify Analytics Now

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.

report-v2

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

Transform Your 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.

    pipeline

    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

    Enhance Your Sheets Now

    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 now

    Bonus for readers

    [GA4] BigQuery Export SQL Queries Library

    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.
    table

    Simplify BigQuery Reporting in Sheets

    Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs

    Simplify Reporting Now

    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.

          report-v2

          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

          Unlock Data Insights Now

          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

          Expand all Close all
          • 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.

          icon Get in-depth insights

          [GA4] BigQuery Export SQL Queries Library

          icon Get in-depth insights

          [GA4] BigQuery Export SQL Queries Library