How to Calculate Unique Users in GA4 BigQuery.

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'.

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:

-- Calculate Unique users by counting the unique values of the user_pseudo_id

SELECT
  APPROX_COUNT_DISTINCT(user_pseudo_id) AS unique_users
FROM
  `<Enter your table id here>`
WHERE
  _TABLE_SUFFIX BETWEEN '20251001' AND '20251031';

Note: Use your table ID. Otherwise, the code would not work.

This figure is pretty close to the one reported in GA4 UI for the same time period. That's how you can test whether the SQL is correct.

======================

Learn the underlying logic for calculating various GA4 dimensions and metrics in BigQuery and leave the actual SQL generation to ChatGPT.

Your GA4 BigQuery data is only as good as the query logic you use. Once you understand the query logic, you can scale across 'N' SQL use cases.

That's what I teach in my GA4 BigQuery Course. The focus is entirely on teaching the underlying logic (instead of the actual SQL).

Calculating Unique Users by Traffic Source, Medium and Campaign Name in GA4 BigQuery.

In the GA4 BigQuery Export table, you can retrieve:

The traffic source from the 'traffic_source.source' field.
The traffic medium from the 'traffic_source.medium' field.
The campaign name from the 'traffic_source.name' field.

Calculate the values of the ‘Traffic Source’ column by extracting the values from the ‘traffic_source.source’ field.
Calculate the values of the ‘Traffic Medium’ column by extracting the values from the ‘traffic_source.medium’ field.
Calculate the values of the ‘Campaign Name’ column by extracting the values from the ‘traffic_source.name’ field.Calculate the values of the ‘Total Users’ column by counting the total number of unique 'user_pseudo_id'.

Once you understand the logic, calculating Unique Users by Traffic Source, Medium and Campaign Name 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:

-- Calculate the total number of unique users by traffic source, medium and campaign.

SELECT
  traffic_source.source AS traffic_source,
  traffic_source.medium AS traffic_medium,
  traffic_source.name AS campaign_name,
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM
  `<Enter your table id here>`
WHERE
  _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
GROUP BY
  traffic_source,
  traffic_medium,
  campaign_name
ORDER BY
  total_users DESC

Note: Use your table ID. Otherwise, the code would not work.

======================

Calculating Unique Users by Device Category in GA4 BigQuery.

BigQuery provides many fields through which you can access the device information of users by looking at the schema of your data table and searching for 'device':

Navigate to Google's documentation on GA4 BigQuery Export Schema to understand the meaning of different device fields.

You can calculate the values of the ‘Device Category’ column by extracting the values from the ‘device.category’ field.
You can calculate the values of the ‘Total Users’ column by counting the total number of unique 'user_pseudo_id'.

Once you understand the logic, calculating unique users by device category via text prompt is easy.

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

-- Calculate the total number of unique users by device Category

WITH
  prep AS (
    SELECT
      user_pseudo_id,
      NULLIF(device.category, '') AS device_category
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
  )
SELECT
  device_category AS device_category,
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM
  prep
GROUP BY
  device_category
ORDER BY
  total_users DESC;

Note: Use your table ID. Otherwise, the code would not work.

======================

Learn the underlying logic for calculating various GA4 dimensions and metrics in BigQuery and leave the actual SQL generation to ChatGPT.

Your GA4 BigQuery data is only as good as the query logic you use. Once you understand the query logic, you can scale across 'N' SQL use cases.

That's what I teach in my GA4 BigQuery Course. The focus is entirely on teaching the underlying logic (instead of the actual SQL).

Calculating Unique Users in GA4 BigQuery.

Important points about the 'user_pseudo_id'.

Calculating Unique Users by Traffic Source, Medium and Campaign Name in GA4 BigQuery.

Calculating Unique Users by Device Category in GA4 BigQuery.

Related Articles:

Himanshu Sharma