Total Daily Count of Users, New Users, Sessions and Events 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. In this exploration, our query becomes a guide through the labyrinth of data, unraveling the profound insights encapsulated within the realm of Total Daily Count of Users, New Users, Sessions and Events using BigQuery. As we dissect the code, we will uncover not just the sheer volume of user interactions ("total_events") but also the unique identities driving these engagements ("total_users"). Our quest extends to unveil the dynamics of "total_sessions" and "new_users," offering nuanced insights essential for refining user experiences and honing targeted 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:

‍

Breaking Down the Query:

1. SELECT DATE, ...:
   • The outer query selects the date field and aggregates metrics for each date.
2. COUNT(DISTINCT user_pseudo_id) AS total_users:
   • Counts the distinct user_pseudo_id, providing the total number of users.
3. COUNT(DISTINCT new_users) AS new_users:
   • Counts distinct new_users, identifying the number of new visitors.
4. COUNT(DISTINCT session_id) AS sessions:
   • Counts distinct session_id, representing the total number of sessions.
5. SUM(event_count) AS event_count:
   • Sums up the event_count, giving the total count of events for each date.
6. FROM ... (subquery):
   • The subquery aggregates data at a granular level, grouping by date, user_pseudo_id, and session_id.
7. MAX(CASE ...):
   • Within the subquery, the CASE statement identifies 'first_visit' events, attributing the user_pseudo_id for new_users.
8. CONCAT(user_pseudo_id, ...):
   • Concatenates user_pseudo_id with ga_session_id to create a unique session_id.
9. COUNT(event_name) AS event_count:
   • Counts the number of events for each user and session within the specified date range.
10. GROUP BY DATE:
    • Groups the results by date for the outer query.
11. ORDER BY DATE ASC:
    • Orders the final results in ascending order based on the date.

Output Table:

Conclusion:

By unveiling the metrics of Total Daily Users and Events, it not only quantifies user interactions but also unveils the unique identities shaping these engagements. The insights into total sessions and new users provide a nuanced perspective crucial for refining user experiences and tailoring marketing endeavors. With customization at its core, this query empowers businesses to harness the full potential of GA4, transforming data into actionable intelligence and steering digital strategies toward unparalleled success.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.