top of page

How to measure the number of users in Google Analytics: UA v.s. GA4 UI v.s. BigQuery

Last July marked a significant shift in the Analytics landscape. Google officially discontinued Universal Analytics (UA) in favor of Google Analytics 4 (GA4), the newest version of their analytics tool. This upgrade represents more than only a change in name; it is a fundamental turning point in the tracking methodology of Analytics. 


Amidst the several changes that occurred between the two versions of Analytics, the majority of our clients encountered a big challenge: reconciling their UA metrics with the new GA4 ones. The latter led to rough transitions.


In this article, we will focus on User metrics as they provide essential insights on how your users interact, engage, and navigate within your website or app. Firstly, we will see the main differences between UA and GA4 User metrics. Then, we will see how to get these metrics in the GA4 UI and in BigQuery using the export of GA4 data. 


Users in Google Analytics 4 v.s. Users in Universal Analytic


As you may all know, since 1st July 2023, standard UA properties stopped processing new data. You can still access previously processed data in your UA property and API until July 1, 2024. As a side note, you may want to take into account that the timeline is slightly different for 360 customers. In any case, if you want to still use the measurement solution of Google, you will need to move to/start with Google Analytics 4 (GA4).


Between UA and GA4, there are a plethora of fundamental changes. From the data models (resp. Sessions and pageviews based v.s. event based) to how metrics are calculated. Our focus of the day, User metrics, is not an exception.


There were two main metrics to count users in UA, namely Total Users and New Users. In GA4, we can find three metrics, namely Total Users, Active Users, and New Users. Despite GA4 being the newer version of UA and the two tools are sharing two identically named User metrics (Total and New Users), they are not calculated the same way. Hence, keep in mind that you can not compare the metrics between tools as an apples-to-apples scenario.


Total users


In UA, the metric represents the total count of distinct users who have accessed your website or app within a specified time period. 


In GA4, the metric is based on an event-driven data model that focuses on user interactions and events rather than sessions. 


New Users


In UA, the metric represents the count of unique users who visit your website or app for the first time within the specified reporting period. It is based on the presence of a first-party cookie. 


In GA4, the metric also refers to the count of unique users who visit your website or app for the first time within the reporting period. However, the latter is measured by the number of new unique user IDs that logged the first_open or first_visit event.


Active users


This metric is only available in GA4. It refers to the number of distinct users who visited your website or application. More precisely, an user is considered as active if this user an active user has an engaged session or when Analytics collects:



The clear statement of Google with GA4 is that it will track user metrics in a more efficient way than UA. This is possible by relying on a more sophisticated approach (event-based tracking, ML algorithms, etc.).


Alongside, with this shift in the tracking methodology, we can see that User metrics are not exactly computed the same way between UA and GA4. Hence, there is no sense to perform a one to one comparison. 


If you want to have more information about the differences between UA and GA4 metrics as a whole, you can refer to the official documentation


Users in Google Analytics 4 v.s. Users in BigQuery


Here, it is worth noticing that there are two main ways to get the number of GA4 users. Either directly using the UI or using BigQuery if you exported your data. 


GA4 UI


All users metrics are automatically populated by GA4 when tracking your website or app and available among the different pre-built reports and usable in the Explore section for custom reports. As mentioned above, GA4 focuses on Active Users which is also shown as Users.


Source: Google Analytics 4 UI - Custom report


BigQuery


If you import your GA4 data into BigQuery, you might want to replicate these metrics for several purposes.


Here, it is a bit more tricky as these aggregated metrics are not directly imported into BigQuery which means that you will have to calculate it yourself using SQL. Another tricky point is that the different users' count metrics are not based on the same metrics. 


Below, we will go through SQL code samples to count users based on GA4 data export to BQ. 


Total Users


SELECT
    COUNT(DISTINCT user_pseudo_id) AS users
FROM `<your-project>.<analytics-dataset>.<events-table>`

Similarly, you could compute the Total Users metric by using the field user_id as follows:

WITH MAPPING AS ( 
	SELECT DISTINCT 
			user_id, 
			user_pseudo_id 

	FROM `<your-project>.<analytics-dataset>.<events-table>` 
	WHERE USER_ID IS NOT NULL 
	QUALIFY ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY EVENT_DATE DESC) = 1  ), 

DATA AS (   
	SELECT DISTINCT
			user_pseudo_id 
	FROM `<your-project>.<analytics-dataset>.<events-table>`
	LEFT JOIN MAPPING USING(user_pseudo_id) 

) 

SELECT

	COUNT(user_pseudo_id) AS total_users 

FROM DATA

The first window function (MAPPING) extracts all unique combinations of user_pseudo_id and user_id from your GA4 raw data export in BigQuery. The second window function (DATA) extracts all unique user_pseudo_id from the raw data export. The LEFT JOIN operation enables the inclusion of user_id whenever it is available. 


  • This methodology allows a comprehensive exploration of a user's journey even when the latter is disconnected. For example,  let’s say that a given user triggered 5 page_view events and then logged in on your website. You will be able to associate all events before and after authentication to the same user. 


Below, you can find a table showing a preview of the data after the second window function (*This is sample data).


Finally, we can count user_pseudo_id to know the number of users. 


Active users

SELECT

count(distinct 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

FROM `<your-project>.<analytics-dataset>.<events-table>`

An alternative query will be to use the field is_active_user available in the BQ export since beginning of July:

SELECT


	count(distinct case when is_active_user is true then user_pseudo_id end) as active_users


FROM `<your-project>.<analytics-dataset>.<events-table>`

However, keep in mind that this new field is not retroactive. It means that for data processed before, this field will not be available. 


New Users

select

-- new users (metric | the number of users who interacted with your site or launched your app for the first time)

    count(distinct case when (select value.int_value from unnest(event_params) where key = 'ga_session_number') = 1 then user_pseudo_id else null end) as new_users

from

    -- change this to your google analytics 4 export location in bigquery

    `<your-project>.<analytics-dataset>.<events-table>`

You know how we can compute different Users count metrics in the GA4 UI directly or through BigQuery using GA4 data. However, you might want to know which approach is the most interesting for you. 


The choice between both depends mainly on your specific needs and objectives.


  • As a whole, the GA4 UI is well-suited for quick insights and basic analytics. Hence, it will be  particularly beneficial if you prefer a straightforward approach to monitoring and understanding your User metrics.


  • On the other hand, BigQuery provides a more advanced and flexible environment to leverage GA4 data. In this context, it will be a more suited tool if you need the ability to perform custom analyses tailored to your business requirements.


  • One last point to take into consideration: You should not expect to have exactly the same numbers when comparing the GA4 UI and the export on BigQuery. The main reason is that GA4 uses sampling, aggregation, and filtering techniques to process data for reporting. The BigQuery export provides you the raw data.


At the end, you may find yourself using both approaches, but in different contexts. The most important is being able to choose the most suited one for your current needs. I hope I gave the right foundations to make that choice.


conclusion


Let’s now take a step back and see what we can conclude from this article through 2 main questions.


  1. Should I compare my UA User metrics to my GA4 User metrics ?


  • Main User metrics in UA and GA4 are not computed the same despite having the same name ;

  • As a whole, there are significant differences between GA4 and UA, the main one being the new GA4 event-driven data collection ;

  • GA4 comes with several advancements aimed at providing a more comprehensive understanding of user interactions and avoiding some data loss scenarios possible with UA.


In short, you should not unless you want to see if your GA4 metrics are in the same order of magnitude than your UA metrics. In any other cases, you should always expect that there will be a difference. Hence, there is no sense to perform a 1 to 1 comparison.


  1. Should I use the GA4 UI or the export of data in BigQuery ? 


  • GA4 UI is suited for quick insights, standard reporting, and rapid iteration on tracking configurations ;

  • The export of GA4 data in BigQuery is extremely beneficial for advanced analysis, custom data manipulation, large-scale data analysis, and integration with other tools and systems.


When choosing between the two approaches, take into account your specific analytical requirements and the level of flexibility and scalability needed for your analysis.


Need help ? Feel free to reach out !



Comentários


bottom of page