nicobrx

select
    -- day of week, starting with Sunday (dimension)
    format_date('%w',parse_date("%Y%m%d",event_date)) as day_of_week,
    -- hour (dimension)
    format("%02d",extract(hour from timestamp_micros(event_timestamp))) as hour,
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_path,
    count(event_name) as sessions
from
    -- change this to your google analytics 4 export location in bigquery
    `yourproject.analytics_1234567.events_*`

nicobrx

-- This is intended to be run as a Looker Studio BigQuery custom query. To run it directly in BigQuery, change @DS_START_DATE
-- and @DS_END_DATE to 'YYYYMMDD' string values. To use it in BigQuery, you will also need to enable date range parameters
-- in the custom query. Be wary of using this query for high-volume GA4 properties or long date ranges, as it could incur 
-- BigQuery analysis costs. This github repo has a Google Dataform repository that is designed to update a table with path data
-- on a daily basis, which is a much more efficient and cost-effective approach: https://github.com/nicobrx/ga4-path-analysis
-- This query references an 'extract_path' UDF, the original version of which can be found here: 
-- https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/cw_url_extract_path.sqlx
-- You will need to update the reference in this query to the location of the UDF you create in your BigQuery project
-- You will also need to update the FROM references in the source_mediums