Create a GA4 Hourly Traffic Report in BigQuery

Calculate a view of hourly users and sessions on your website for a specific day using the data in your GA4 BigQuery export.

Query overview

This query will provide you with a breakdown of web traffic by hour, for a specific day.

Dimensions:

• Hour

Metrics:

• Total Users
• Sessions

Time Period:

• A specific day

Note: refer to the comments inline below for an explanation of the code.

SELECT
  -- extract the hour from event_timestamp and then concatenate with ":00" for display purposes
  -- converts event_timestamp to London time, so ensure you update the timezone to match your needs
  CONCAT(FORMAT('%02d',EXTRACT(hour FROM TIME(TIMESTAMP_MICROS(event_timestamp), 'Europe/London'))), ":00") AS hour,
  -- count the no. of unique users
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  -- concatenate user_pseudo_id and ga_session_id and then count the no. of unique instances to get sessions
  COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions
-- update with your project, dataset and table here
FROM `<project>.<dataset>.<table>`
-- filter to only web traffic (other options are 'IOS' or 'ANDROID')
WHERE platform = 'WEB'
-- aggregate results by the 1st column (hour)
GROUP BY 1
-- order by the 1st column (hour) in ascending order
ORDER BY 1 ASC