Events: 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.

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 event dimensions

• event_date
• event_timestamp
• event_name
• event_params.key
• event_previous_timestamp
• event_bundle_sequence_id
• event_server_timestamp_offset
• event_dimensions.hostname

Default event metrics

• event_params.value.int_value
• event_params.value.float_value
• event_params.value.double_value
• event_value_in_usd

Example query

select
    -- event_date (dimension | the date on which the event was logged)
    parse_date('%Y%m%d',event_date) as event_date,
    -- event_timestamp (dimension | the time (in microseconds, utc) at which the event was logged on the client)
    timestamp_micros(event_timestamp) as event_timestamp,
    -- event_name (dimension | the name of the event)
    event_name,
    -- event_key (dimension | the event parameter's key | change key to select another parameter)
    (select key from unnest(event_params) where key = 'page_location') as event_key,
    -- event_string_value (dimension | the string value of the event parameter | change key to select another parameter)
    (select value.string_value from unnest(event_params) where key = 'page_location') as event_string_value,
    -- event_int_value (metric | the integer value of the event parameter | change key to select another parameter)
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as event_int_value,
    -- event_float_value (metric | the float value of the event parameter | change key to select another parameter)
    (select value.float_value from unnest(event_params) where key = 'page_location') as event_float_value,
    -- event_double_value (metric | the double value of the event parameter | change key to select another parameter)
    (select value.double_value from unnest(event_params) where key = 'page_location') as event_double_value,
    -- event_previous_timestamp (dimension | the time (in microseconds, utc) at which the event was previously logged on the client)
    timestamp_micros(event_previous_timestamp) as event_previous_timestamp,
    -- event_value_in_usd (metric | the currency-converted value (in usd) of the event's "value" parameter)
    event_value_in_usd,
    -- event_bundle_sequence_id (dimension | the sequential id of the bundle in which these events were uploaded)
    event_bundle_sequence_id,
    -- event_server_timestamp_offset (dimension | timestamp offset between collection time and upload time in micros)
    event_server_timestamp_offset,
    -- event_dimensions.hostname (dimension | hostname)
    event_dimensions.hostname
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))
    -- change event_name to select another event
    and event_name = 'page_view'