Date and time dimensions & metrics (GA4)

A lot of Google Analytics 4 dimensions and metrics 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 and metrics that you might need are a bit harder to access. You will have to (re)calculate them, which can require some serious SQL-skills.

While designing the course Query GA4 Data In Google BigQuery I've learned a lot about best practices to calculate dimensions and metrics. To share this knowledge with you I will provide a combined query for default dimensions and metrics, and a single example query for every non-default dimension or metric.

As always, drop a line in the comments if you have any questions, feedback or suggestions related to this article.

Default date and time dimensions

• event_date
• event_timestamp
• event_previous_timestamp
• event_server_timestamp_offset
• user_first_touch_timestamp
• set_timestamp_micros

select
    -- dimension | the date on which the event was logged (YYYYMMDD format in the registered timezone of your app
    event_date,
    -- dimension | the time (in microseconds, UTC) at which the event was logged on the client
    event_timestamp,
    -- dimension | the time (in microseconds, UTC) at which the event was previously logged on the client.
    event_previous_timestamp,
    -- dimension | timestamp offset between collection time and upload time in micros
    event_server_timestamp_offset,
    -- dimension | the time (in microseconds) at which the user first opened the app or visited the site
    user_first_touch_timestamp,
    -- dimension | the time (in microseconds) at which the user property was last set
    (select value.set_timestamp_micros from unnest(user_properties) where key = '<insert key>') as user_set_timestamp_micros

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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Calculated date and time dimensions

• year (YYYY)

select
    -- year (dimension)
    format_date('%Y',parse_date("%Y%m%d",event_date)) as year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    year

• iso year (YYYY)

select
    -- iso year (dimension)
    format_date('%G',parse_date("%Y%m%d",event_date)) as iso_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    iso_year

• month of year (YYYYMM)

select
    -- month of year (dimension)
    format_date('%Y%m',parse_date("%Y%m%d",event_date)) as month_of_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    month_of_year

• month of the year (MM)

select
    -- month of the year (dimension)
    format_date('%m',parse_date("%Y%m%d",event_date)) as month_of_the_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    month_of_the_year

• week of year (YYYYWW)

select
    -- week of year (dimension)
    format_date('%Y%U',parse_date("%Y%m%d",event_date)) as week_of_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    week_of_year

• week of the year (WW)

select
    -- week of the year (dimension)
    format_date('%U',parse_date("%Y%m%d",event_date)) as week_of_the_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    week_of_the_year

• iso week of the year (WW)

select
    -- iso week of the year (dimension)
    format_date('%W',parse_date("%Y%m%d",event_date)) as iso_week_of_the_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    iso_week_of_the_year

• iso week of iso year (YYYYWW)

select
    -- iso week of iso year (dimension)
    format_date('%G%W',parse_date("%Y%m%d",event_date)) as iso_week_of_iso_year,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    iso_week_of_iso_year

• day of the month (DD)

select
    -- day of the month (dimension)
    format_date('%d',parse_date("%Y%m%d",event_date)) as day_of_the_month,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    day_of_the_month

• day of week (0-6)

select
    -- day of week, starting with Sunday (dimension)
    format_date('%w',parse_date("%Y%m%d",event_date)) as day_of_week,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    day_of_week

• day of week name (Sunday - Saturday)

select
    -- day of week name (dimension)
    format_date('%A',parse_date("%Y%m%d",event_date)) as day_of_week_name,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    day_of_week_name

• hour (HH)

select
    -- hour (dimension)
    format("%02d",extract(hour from timestamp_micros(event_timestamp))) as hour,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    hour

• hour of day (YYYYMMDDHH)

select
    -- hour of day(dimension)
    concat(event_date,cast(format("%02d",extract(hour from timestamp_micros(event_timestamp))) as string)) as hour_of_day,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    hour_of_day

• minute (MM)

select
    -- minute (dimension)
    format("%02d",extract(minute from timestamp_micros(event_timestamp))) as minute,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    minute

• date hour and minute (YYYYMMDDHHMM)

select
    -- date hour and minute (dimension)
    concat(concat(event_date,cast(format("%02d",extract(hour from timestamp_micros(event_timestamp))) as string)),format("%02d",extract(minute from timestamp_micros(event_timestamp)))) as date_hour_and_minute,
    count(distinct user_pseudo_id) as users
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 '20220901'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    date_hour_and_minute