Follow me on LinkedIn - AI, GA4, BigQuery

Calculating Unique Users in GA4 BigQuery.

You can calculate Unique users by counting the unique values of the 'user_pseudo_id' or 'user_id'.
Article content

Important points about the 'user_pseudo_id'.

  • The 'user_pseudo_id' is essentially the same as the client ID or app instance ID.
  • The values of the 'user_pseudo_id'  is automatically generated by GA4.

  • Every event in the GA4 BigQuery export table has a 'user_pseudo_id' associated with it, and multiple events can have the same 'user_pseudo_id'. So when you just count 'user_pseudo_id', it counts all occurrences of each 'user_pseudo_id' i.e. all events.
  • To count unique users, you will need to count the total number of unique 'user_pseudo_id'.

Once you understand the logic, calculating unique users is easy.

The SQL below is automatically generated via a text prompt in GA4 BigQuery Composer (a custom chatGPT) that calculates unique users in GA4 BigQuery: