Date and time: dimensions & metrics (GA4)
2 min read

Date and time: dimensions & metrics (GA4)

The most important date and time dimensions and metrics for GA4 BigQuery export, like date, year, month, iso week, day of week, hour and minute.
Date and time: 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.

Calculated date and time dimensions

  • date
  • year
  • iso year
  • month of year
  • month of the year
  • week of year
  • week of the year
  • iso week of the year
  • iso week of iso year
  • day of the month
  • day of week
  • day of week name
  • hour
  • minute
  • hour of day
  • date hour and minute

Example query

select
    -- date (dimension)
    event_date as date,
    -- year (dimension)
    format_date('%Y',parse_date("%Y%m%d",event_date)) as year,
    -- iso year (dimension)
    format_date('%G',parse_date("%Y%m%d",event_date)) as iso_year,
    -- month of year (dimension)
    format_date('%Y%m',parse_date("%Y%m%d",event_date)) as month_of_year,
    -- month of the year (dimension)
    format_date('%m',parse_date("%Y%m%d",event_date)) as month_of_the_year,
    -- week of year (dimension)
    format_date('%Y%U',parse_date("%Y%m%d",event_date)) as week_of_year,
    -- week of the year (dimension)
    format_date('%U',parse_date("%Y%m%d",event_date)) as week_of_the_year,
    -- iso week of the year (dimension)
    format_date('%W',parse_date("%Y%m%d",event_date)) as iso_week_of_the_year,
    -- iso week of iso year (dimension)
    format_date('%G%W',parse_date("%Y%m%d",event_date)) as iso_week_of_iso_year,
    -- day of the month (dimension)
    format_date('%d',parse_date("%Y%m%d",event_date)) as day_of_the_month,
    -- day of week (dimension)
    format_date('%w',parse_date("%Y%m%d",event_date)) as day_of_week,
    -- day of week name (dimension)
    format_date('%A',parse_date("%Y%m%d",event_date)) as day_of_week_name,
    -- hour of day(dimension)
    concat(event_date,cast(format("%02d",extract(hour from timestamp_micros(event_timestamp))) as string)) as hour_of_day,
    -- hour (dimension)
    format("%02d",extract(hour from timestamp_micros(event_timestamp))) as hour,
    -- minute (dimension)
    format("%02d",extract(minute from timestamp_micros(event_timestamp))) as minute,
    -- 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
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))

Remark: Timezone formatting

BigQuery displays data usually in UTC. That leads to problems when using date formatting functions because dates and times can be off. Converting the datetimes prior formatting into the correct timezone solves those issues.
(source & how to)

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

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