Tracking Google Analytics 4 Total Users in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

You can calculate the values of the Google Analytics 4 Total Users metric in BigQuery by counting the unique values of the ‘user_pseudo_id’ for users who generated at least one event within the date range.

Use the following SQL (automatically generated via a text prompt in ChatGPT) to track GA4 Total Users in BigQuery:

-- Calculate Total Users

SELECT
  COUNT(
    DISTINCT NULLIF(TRIM(user_pseudo_id), '')
  ) AS `Total Users`
FROM
  `dbrt-ga4.analytics_207472454.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
  AND user_pseudo_id IS NOT NULL;

I am not a fan of cookie-cutter reports. But if you want one, this one could be for you.

All you have to do is supply your table ID to generate SQL that works for you.

If you want to customize this report according to your unique data analysis requirements then you need to understand the “logic” used behind calculating the various metrics in this report.

Once you understand the logic, customizing this report is easy peasy.

I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.

The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.

Related Articles:

Himanshu Sharma