Using SQL FIRST_VALUE() window function to replicate GA4 traffic source attribution behavior

ga4_traffic_source_session_level.sql

FIRST_VALUE(
  (SELECT 
     value.string_value
   FROM 
   	 UNNEST(event_params)
   WHERE 
   	 key = 'name_of_traffic_source_parameter' 
   	--source, medium, campaign, content, term, campaign_id  
  )
  IGNORE nulls 
) OVER (
	PARTITION BY
		user_pseudo_id,
  		(SELECT 
     		value.int_value
   		FROM 
   	 		UNNEST(event_params)
   		WHERE 
   	 		key = 'ga_session_id'
  		)
	ORDER BY 
		event_timestamp
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS my_utm_parameter_session_level