User: dimensions & metrics (GA4)
4 min read

User: dimensions & metrics (GA4)

The most important user dimensions and metrics for GA4 BigQuery export, like (new) users, user id, user pseudo id, user lifetime revenue and user properties key and values.
User: dimensions & metrics (GA4)

A lot of Google Analytics 4 data can be derived straight from the tables without performing calculations on the data. With the help of unnest and parse for dates or timestamps we can do the trick.

Other dimensions & metrics you are used to in your Google Analytics analysing and reporting are a bit harder to access. You’ll have to calculate them, using your own queries.

As I’ve tried to sort out the most used dimensions and metrics already, I hope I can be of help with the following example queries. There are plenty other, probably even better ways to achieve these results, but here is my two cents.

If you only need one dimension or metric, look at the -- comments in the example query for names and definitions and copy the part you need from the SELECT clause. Make sure that you also add any additional conditions (i.e. with, from, where, group by, having and order by) that are necessary to calculate the results correctly.

Default user dimensions

  • user_id
  • user_pseudo_id
  • user_first_touch_timestamp
  • user_properties.key
  • user_properties.value.string_value
  • user_properties.value.set_timestamp_micros
  • user_ltv.currency

Default user metrics

  • user_properties.value.int_value
  • user_properties.value.float_value
  • user_properties.value.double_value
  • user_ltv.revenue

Example query

select
    -- user_id (dimension | the user id set via the setUserId api)
    user_id,
    -- user_pseudo_id (dimension | the pseudonymous id (e.g., app instance id) for the user)
    user_pseudo_id,
    -- user_first_touch_timestamp (dimension | the time (in microseconds) at which the user first opened the app/website)
    timestamp_micros(user_first_touch_timestamp) as user_first_touch_timestamp,
    -- user_properties.key (dimension | the name of the user property | replace <insert key> with a parameter key or delete where clause to select all)
    (select key from unnest(user_properties) where key = '<insert key>') as user_properties_key,
    -- user_properties.value.string_value (dimension | the string value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
    (select value.string_value from unnest(user_properties) where key = '<insert key>') as user_string_value,
    -- user_properties.value.int_value (metric | the integer value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
    (select value.int_value from unnest(user_properties) where key = '<insert key>') as user_int_value,
    -- user_properties.value.float_value (metric | the float value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
    (select value.float_value from unnest(user_properties) where key = '<insert key>') as user_float_value,
    -- user_properties.value.double_value (metric | the double value of the user property | replace <insert key> with a parameter key or delete where clause to select all)
    (select value.double_value from unnest(user_properties) where key = '<insert key>') as user_double_value,
    -- user_properties.value.set_timestamp_micros (dimension | the time (in microseconds) at which the user property was last set | replace <insert key> with a parameter key or delete where clause to select all)
    timestamp_micros((select value.set_timestamp_micros from unnest(user_properties) where key = '<insert key>')) as user_set_timestamp_micros,
    -- user_ltv.revenue (metric | the lifetime value (revenue) of the user)
    user_ltv.revenue as user_ltv_revenue,
    -- user_ltv.currency (dimension | the lifetime value (currency) of the user)
    user_ltv.currency as user_ltv_currency
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20201101'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Calculated user dimensions

  • user type
  • count of sessions

Calculated user metrics

  • users
  • new users
  • % new sessions
  • number of sessions per user
  • event count per user (see separate query below)

Example query

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20201101' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),

-- subquery to prepare and calculate user data
user as (
select
    user_pseudo_id,
    case
        when (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') = 1 then 'new visitor'
        when (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') > 1 then 'returning visitor'
        else null end as user_type,
    (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') as count_of_sessions,
    count(distinct user_pseudo_id) as users,
    count(distinct case when (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') = 1 then user_pseudo_id else null end) as new_users,
    count(distinct case when event_name = 'session_start' and (select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number') = 1 then concat(user_pseudo_id, cast(event_timestamp as string)) end) / count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) as percentage_new_sessions,
    count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) / count(distinct user_pseudo_id) as number_of_sessions_per_user
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`,
    date_range
where
    _table_suffix between date_range.start_date and date_range.end_date
group by
    user_pseudo_id,
    user_type,
    count_of_sessions)

-- main query
select
    -- user type (dimension | a boolean, either new visitor or returning visitor, indicating if the users are new or returning)
    user_type,
    -- count of sessions (dimension | the session index for a user, each session from a unique user will get its own incremental index starting from 1 for the first session)
    count_of_sessions,
    -- users (metric | the total number of active users)
    sum(users) as users,
    -- new users (metric | the number of users who interacted with your site or launched your app for the first time)
    sum(new_users) as new_users,
    -- % new sessions (metric | the percentage of sessions by users who had never visited before)
    avg(percentage_new_sessions) as percentage_new_sessions,
    -- number of sessions per user (metric | the total number of sessions divided by the total number of users)
    avg(number_of_sessions_per_user) as number_of_sessions_per_user
from
    user
where
    user_type is not null
group by
    user_type,
    count_of_sessions
order by
    users desc

Event count per user

This user based metric is easier to get in a separate query, because we need to count all events with a certain event_name:

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20201101' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date)

select
    -- event count per user (metric | the number of times an individual event (change event_name 'page_view' to event that needs to be counted) was triggered divided by amount of users)
    count(distinct case when event_name = 'page_view' then concat(user_pseudo_id, cast(event_timestamp as string)) end) / count(distinct user_pseudo_id) as event_count_per_user
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`,
    date_range
where
    _table_suffix between date_range.start_date and date_range.end_date

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.