Total Sessions per Medium using BigQuery

Introduction

Google Analytics 4 (GA4) is a powerhouse, offering profound insights into user behavior and website performance. To harness GA4’s full potential, mastery of SQL queries is indispensable. Explore the landscape of user engagement with our guide on “Total Sessions per Medium.”

In this blog post, we will delve into the provided code that dissects the GA4 dataset, extracting insightful metrics to understand user behavior. Our focus is on the Total Sessions per Medium, a crucial aspect for businesses aiming to optimize user experiences and tailor marketing strategies. Join us on this journey as we break down the schema and explore the inner workings of the GA4 SQL query provided.

The SQL queries provided in this blog post are tailored for execution in BigQuery, Google’s enterprise data warehouse. BigQuery is a cloud-based platform designed for fast and efficient analysis of large datasets.This post is an invitation to wield GA4 and SQL queries as precision instruments, transforming raw data into actionable intelligence for digital success. Happy querying!

The SQL Query:

WITH SessionData AS (
  SELECT
    MAX(
      CASE
        WHEN (
          SELECT
            value.int_value
          FROM
            UNNEST (event_params)
          WHERE
            event_name = 'page_view'
            AND KEY = 'entrances'
        ) = 1
        AND (
          SELECT
            value.string_value
          FROM
            UNNEST (event_params)
          WHERE
            event_name = 'page_view'
            AND KEY = 'source'
        ) IS NULL THEN CONCAT(traffic_source.medium)
        ELSE (
          SELECT
            value.string_value
          FROM
            UNNEST (event_params)
          WHERE
            KEY = 'medium'
        )
      END
    ) AS session_medium,
    CONCAT(
      user_pseudo_id,
      (
        SELECT
          value.int_value
        FROM
          UNNEST (event_params)
        WHERE
          key = 'ga_session_id'
      )
    ) AS session_id,
    MAX(
      CASE
        WHEN event_name = 'first_visit' THEN user_pseudo_id
        ELSE NULL
      END
    ) AS new_users,
    user_pseudo_id,
    COUNT(event_name) AS event_count
  FROM
    `add_yor_dataset_name_here_2023*`
  WHERE
    event_date BETWEEN '20231001' AND '20231031'
  GROUP BY
    session_id,
    user_pseudo_id
)

SELECT
  session_medium AS Traffic_By_Medium,
  COUNT(DISTINCT session_id) AS Sessions,
  SUM(event_count) AS Event_Count,
  COUNT(DISTINCT new_users) AS New_Users,
  (COUNT(DISTINCT new_users) / COUNT(DISTINCT session_id)) * 100 AS Percentage_New_Users
FROM SessionData
GROUP BY session_medium

Breaking Down the Query:

1. Common Table Expression (CTE) – SessionData:

• This section establishes a CTE named SessionData to organize and preprocess the data for analysis.

2. Extracting session_medium:

• Utilizes conditional logic with CASE statements to extract session_medium based on various scenarios related to ‘page_view’ events and their parameters.
• Checks for ‘entrances’ and ‘source’ keys to determine the medium or falls back to the ‘medium’ key.

3. Creating a unique session_id:

• Concatenates user_pseudo_id with ga_session_id to generate a unique session_id for each user session.

4. Identifying new_users based on ‘first_visit’ events:

• Uses another CASE statement to identify new users based on ‘first_visit’ events.

5. Extracting user_pseudo_id and counting the number of events (event_count):

• Retrieves user_pseudo_id and counts the number of events for each user session.

6. Main Query using the CTE – SessionData:

• The main query uses the CTE SessionData to aggregate and calculate metrics based on the processed data.

7. Extracting session_medium for traffic analysis:

• Extracts session_medium for further traffic analysis, providing insights into user sessions based on different traffic sources.

8. Counting the number of unique sessions (Sessions):

• Counts the number of unique sessions using the session_id.

9. Summing up the total event counts (Event_Count):

• Aggregates the total event counts, providing an overview of user interactions across different mediums.

10. Counting the number of distinct new users (New_Uesrs): – Counts the number of distinct new users based on the ‘first_visit’ events.

11. Calculating the percentage of new users in each session (Percentage_New_Users): – Calculates the percentage of new users in each session, offering insights into user acquisition and retention.

12. Grouping by session_medium: – Groups the results by session_medium for a summarized view, enabling a detailed analysis of user sessions per medium.

Output Table:

Conclusion:

Finally, this GA4 SQL query reveals a detailed insight of user sessions per medium, enabling businesses to optimize strategy and improve user experiences. This query gives actionable data for educated decision-making by examining the complexities of traffic sources and user behavior. Utilize the power of GA4 analytics to transform raw data into a digital success path.

Discover a treasure trove of powerful GA4 queries in our primary blog post, “Top Queries within Google Analytics 4.” Go to our blog area here to read more intriguing blogs of this kind.