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.

Make sure you are aware of the timezone your GA4 data export is using. The date field is in YYYYMMDD format in the registered timezone of your GA4 property, while the event_timestamp field is stored in microseconds based on UTC time. If necessary, you can convert dates and timestamps to your own time zone.

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

    -- 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
    -- change this to your google analytics 4 export location in bigquery
    -- 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))