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:
- 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.
- FROM Statement (Subquery):
- Subquery fetching relevant data for the main query, including session source, medium, engaged sessions, and event details.
- GROUP BY Clause (Main Query):
- Grouping by session_source_medium for aggregating metrics.
- 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.