Extracting Geolocations in GA4 BigQuery

These are the Geolocations fields in GA4 BigQuery:

In the GA4 BigQuery Export table, you can retrieve:

• The value of the Continent that a user belongs to from the 'geo.continent' field.
• The value of the Sub Continent that a user belongs to from the 'geo.sub_continent' field.
• The value of the Country that a user belongs to from the 'geo.country' field.
• The value of the Region that a user belongs to from the 'geo.region' field.
• The value of the City that a user belongs to  from the 'geo.city' field.
• The value of the Metro that a user belongs to from the 'geo.metro' field.

Once you understand the logic, using Geolocation fields 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 in GA4 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
  `<Enter your table id here>`
WHERE
  _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
GROUP BY
  continent,
  sub_continent,
  country,
  region,
  city
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).

Related Articles:

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.