How to Calculate GA4 Total Users and Active Users in BigQuery

In the previous post, I showed the SQL to calculate the “Sessions” metrics in BigQuery.

In this post, we will recreate the “Users” and “Active Users” metrics in BigQuery.

GA4 tracks users in three different ways.

1. By default, it uses an anonymous user ID for each user and stores it in the user_pseudo_id column.

2. If you have enabled known user_id tracking in GA4 for all the users then by default GA4 uses that ID to track users.

3. Finally, if you enabled user_id tracking in GA4 but not all the users have a User ID then GA4 uses user_id where it can find it else it uses anonymous_user_id

The first scenario is the most common one and the last one is a bit complicated. So I will cover the first two scenarios in this post and will cover the third one in the future.

Please note that GA4 also provides two different metrics for users: “Total Users:” and “Active Users”. The “Active Users” is also called “Users” in GA4. (This is confusing but there are a lot of things in GA4 that are confusing). Let’s take a look at both these metrics.

Total User Metrics

“Total Users” is the total number of people who visited your site or app in the specified date range. As mentioned above we will use two different methods to calculate the “Total Users” metrics.