Google Analytics 4 Traffic Acquisition Report

Introduction

Google Analytics 4 (GA4) is a powerhouse, offering profound insights into user behavior and website performance. Explore the intricacies of user traffic with our GA4 SQL query designed to illuminate the Traffic Acquisition Report.

In this comprehensive guide, we decipher user sessions, engagement patterns, and key metrics to empower businesses with actionable insights. Whether you’re tracking active users, analyzing session engagement, or evaluating events per session, this SQL query provides a robust toolset. Dive into the intricacies of source and medium data, examining user behavior to optimize marketing strategies.

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:

SELECT
 IFNULL(
   CONCAT(session_source, ' / ', session_medium),
   CONCAT(first_user_source, ' / ', first_user_medium)
 ) AS session_source_medium,
 COUNT(DISTINCT active_users) AS active_users,
 COUNT(DISTINCT session_id) AS sessions,
 SUM(CAST(engaged_sessions AS INT)) AS engaged_sessions,
 SAFE_DIVIDE(
   SUM(engagement_time_seconds),
   COUNT(DISTINCT session_id)
 ) AS average_engagement_time_per_session,
 SAFE_DIVIDE(
   SUM(event_count_without_session_start),
   COUNT(DISTINCT session_id)
 ) AS events_per_session,
 SAFE_DIVIDE(
   SUM(CAST(engaged_sessions AS INT)),
   COUNT(DISTINCT session_id)
 ) AS engagement_rate,
 SUM(event_count) AS event_count
FROM
 (
   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.source)
         ELSE (
           SELECT
             value.string_value
           FROM
             UNNEST (event_params)
           WHERE
             KEY = 'source'
         )
       END
     ) AS session_source,
     traffic_source.source AS first_user_source,
     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,
     traffic_source.medium AS first_user_medium,
     MAX(
       CASE
         WHEN (
           SELECT
             value.int_value
           FROM
             UNNEST (event_params)
           WHERE
             key = 'engagement_time_msec'
         ) > 0
         OR (
           SELECT
             value.string_value
           FROM
             UNNEST (event_params)
           WHERE
             key = 'session_engaged'
         ) = '1' THEN user_pseudo_id
         ELSE NULL
       END
     ) AS active_users,
     user_pseudo_id,
     CONCAT(
       user_pseudo_id,
       (
         SELECT
           value.int_value
         FROM
           UNNEST (event_params)
         WHERE
           key = 'ga_session_id'
       )
     ) AS session_id,
     MAX(
       (
         SELECT
           value.string_value
         FROM
           UNNEST (event_params)
         WHERE
           key = 'session_engaged'
       )
     ) AS engaged_sessions,
     SAFE_DIVIDE(
       SUM(
         (
           SELECT
             value.int_value
           FROM
             UNNEST (event_params)
           WHERE
             key = 'engagement_time_msec'
         )
       ),
       1000
     ) AS engagement_time_seconds,
     COUNTIF(event_name NOT IN ('session_start')) AS event_count_without_session_start,
     COUNT(event_name) AS event_count
   FROM
     `add_yor_dataset_name_here_202310*`
   WHERE
     event_date BETWEEN '20231001' AND '20231031'
   GROUP BY
     first_user_source,
     first_user_medium,
     user_pseudo_id,
     session_id
 )
GROUP BY
 session_source_medium

Breaking Down the Query:

1. SELECT Statement:
   • session_source_medium: Concatenation of session source and medium for both session and first user.
   • active_users: Count of distinct active users.
   • sessions: Count of distinct session IDs.
   • engaged_sessions: Sum of engaged sessions.
   • average_engagement_time_per_session: Average engagement time per session.
   • events_per_session: Events per session.
   • engagement_rate: Engagement rate calculated as the ratio of engaged sessions to total sessions.
   • event_count: Total count of events.
2. FROM Statement (Subquery):
   • Subquery fetching relevant data for the main query, including session source, medium, engaged sessions, and event details.
3. GROUP BY Clause (Main Query):
   • Grouping by session_source_medium for aggregating metrics.
4. Result Set (Main Query):
   • The final result set provides insights into user acquisition by presenting aggregated metrics for different traffic sources and mediums.

Output Table:

Conclusion:

In conclusion, our GA4 SQL query for the Traffic Acquisition Report provides a powerful tool for extracting actionable insights from your GA4 data. By understanding user traffic sources, engagement patterns, and key metrics, businesses can refine their marketing strategies and enhance user experiences. Embrace the potential of GA4 analytics with this comprehensive guide, turning data into a strategic advantage for your digital endeavors.

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.