Learn business growth with Google Analytics 4 Forums Google Analytics 4 Aggregating GA4 Event Data by Session ID in BigQuery Reply To: Aggregating GA4 Event Data by Session ID in BigQuery

  • Jesse

    Member
    5 June 2023 at 9:50 am

    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.