Count various event types for a GA4 session, whether user is returning and whether it's a bounced session and/or converting session

ga4_session_event_counts.sql

WITH events as (SELECT 
    user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
    replace(lower(event_name),' ','_') event_name, 
    count(*) events
  FROM `ra-development.analytics_277223877.events_*`
  GROUP BY 
    1, 2, 3, 4),
session_events_pivoted as (
SELECT * FROM
    (SELECT * FROM events)
    PIVOT (SUM(events) for event_name IN('session_start', 'page_view', 'podcast_played', 'pricing_viewed', 'view_case_studies', 'file_download', 'contact_us', 'contact_us_clicked','cta_pressed','about_us_viewed'))
ORDER BY
 1,2),
session_event_counts as (
select 
  * except (session_number),
  case when page_view = 1 then true else false end as is_bounced_session,
  case when session_number > 1 then true else false end as is_returning_user,
  case when contact_us > 0 or contact_us_clicked > 0 or cta_pressed > 0 then true else false end as is_converting_session
from
  session_events_pivoted)
select * from session_event_counts