Reply To: Aggregating GA4 Event Data by Session ID in BigQuery

[Jesse]

SQL queries can seem complex, but the trick is breaking them down into smaller and more manageable parts. Here’s an approach that I’ve found works quite well for understanding sequences of events. It might take a bit more time because it’s set up to look at other things as well, but it’ll get you what you need:

with _latest as (
  SELECT 
    --create unique id
            concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) as unique_session_id,
    
    --create event id
        concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'),event_name) as session_ids,
          event_name,
          event_date,
           
          TIMESTAMP_MICROS(event_timestamp) AS event_timestamp
        FROM  *******
       
        where
            -- change the date range by using static and/or dynamic dates
           _table_suffix between  '20221113' and '20221114'),
     Exit_count as (
        select *,
     row_number() over (partition by session_ids order by event_timestamp desc) as Event_order
        from _latest)
    
        select 
    Event_order,
    unique_session_id,
        event_date,
        event_name,
        
   FROM
        Exit_count
    
  group by
        Event_order,
        event_name,
        unique_session_id,
        --pagepath,
        event_date
        --Country_site
        order by
            unique_session_id, 
            Event_order 

Don’t worry if you’re new to SQL. Over time you’ll find it easier to write complex queries confidently.