Create a GA4 Country Report in BigQuery

Generate a geo country report from your GA4 data in BigQuery, counting users, sessions and engagement metrics for each country in the dataset.

Query overview

This query will generate a list of countries along with a count of total users, active users, new users, sessions, engaged sessions, engagement rate and engaged sessions per user.

Dimensions:

• Country

Metrics:

• Total Users
• Active Users
• New Users
• Sessions
• Engaged Sessions
• Engagement Rate
• Engaged Sessions per User

Time Period:

• Last 7 days (you can modify this in the query)

Note: refer to the comments inline below for an explanation of the code.

WITH flattened_data AS (
  SELECT
    event_name,
    user_pseudo_id,
    geo.country,
    -- extract the ga_session_id from event_params and concatenate with the user_pseudo_id to create a unique value,
    CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS ga_session_id,
    -- extract the engagement_time_msec value from event_params 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
    -- extract the session_engaged value from event_params 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged
  -- update with your project and dataset names here
  FROM `<project>.<dataset>.events_*`
  -- update timeframe here (currently set to last 7 days)
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 day))
    AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 day))
),

metrics AS (
  SELECT
    country,
    -- total users: count the no. of unique user_pseudo_id values
    COUNT(DISTINCT user_pseudo_id) AS total_users,
    -- active users: count the no. of unique user_pseudo_id values where engagement_time_msec is greater than zero or session_engaged = 1
    COUNT(DISTINCT CASE WHEN engagement_time_msec > 0 OR session_engaged = '1' THEN user_pseudo_id END) AS active_users,
    -- new users: count the no. of unique user_pseudo_id values where event_name = first_visit or first_open
    COUNT(DISTINCT CASE WHEN event_name IN ('first_visit', 'first_open') THEN user_pseudo_id END) AS new_users,
    -- sessions: count the no. of unique ga_session_id values
    COUNT(DISTINCT ga_session_id) AS sessions,
    -- engaged sessions: count the no. of unique ga_session_id values where session_engaged = 1
    COUNT(DISTINCT CASE WHEN session_engaged = '1' THEN ga_session_id END) AS engaged_sessions
  FROM flattened_data
  -- aggregate results by the 1st column (country)
  GROUP BY 1
)

SELECT
  country,
  total_users,
  active_users,
  new_users,
  sessions,
  engaged_sessions,
  -- engagement rate: engaged sessions divided by sessions, rounded to 2 decimal places
  ROUND(engaged_sessions / sessions, 2) AS engagement_rate,
  -- engaged sessions per user: engaged sessions divided by total users, rounded to 2 decimal places
  ROUND(engaged_sessions / total_users, 2) AS engaged_sessions_per_user,
FROM metrics
-- order by the 2nd column (total_users) in descending order
ORDER BY 2 DESC