Follow me on LinkedIn - AI, GA4, BigQuery

You can track the values of the GA4 Geolocations metrics in BigQuery via the Geolocation fields:


Use the following SQL (automatically generated via a text prompt in ChatGPT) to track the total number of unique users by geo locations in BigQuery:

-- Calculate the total number of unique users by geo locations (continent, sub_continent, country, region and city) 

SELECT
  geo.continent AS continent,
  geo.sub_continent AS sub_continent,
  geo.country AS country,
  geo.region AS region,
  geo.city AS city,
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM
  `dbrt-ga4.analytics_207472454.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
GROUP BY
  continent,
  sub_continent,
  country,
  region,
  city
ORDER BY
  total_users DESC

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.
  1. GA4 to BigQuery Mapping Tutorial.
  2. Understanding the BigQuery User Interface.
  3. GA4 BigQuery Query Optimization.
  4. How to access a nested field in GA4 BigQuery data table.
  5. How to Calculate Unique Users in GA4 BigQuery.
  6. GA4 BigQuery Export Schema Tutorial.
  7. Calculating First Time Users in GA4 BigQuery.
  8. Extracting Geolocations in GA4 BigQuery.
  9. GA4 BigQuery SQL Optimization Consultant.
  10. Tracking Pages With No Traffic in GA4 BigQuery.
  11. First User Primary Channel Group in GA4 BigQuery
  12. How to handle empty fields in GA4 BigQuery.
  13. Extracting GA4 User Properties in BigQuery.
  14. Calculating New vs Returning GA4 Users in BigQuery.
  15. How to access BigQuery Public Data Sets.
  16. How to access GA4 Sample Data in BigQuery.