Transform Performance Navigation data to wide format

transform_to_wide.sql

create or replace table `PROJECTNAME.DATASETNAME.performance_wide` partition by DATE(timestamp) as 
/*
	wide version of nested events table
*/



SELECT
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
  event_timestamp,
  user_pseudo_id as clientid,
  user_id,
  device.category as device_category,
  geo.country as country,

	max(case when e.key = 'type' then e.value.string_value else null end) as type,
	max(case when e.key = 'page_location' then e.value.string_value else null end) as url,
	max(case when e.key = 'page_location' then
		regexp_extract(e.value.string_value, 'https?://([^/]+)/.*') else null end) as hostname,
	max(case when e.key = 'page_location' then
		regexp_extract(e.value.string_value, 'https?://[^/]+(/[^?#]*).*') else null end) as page_path,
	max(case when e.key = 'page_title' then e.value.string_value else null end) as page_title,
	max(case when e.key = 'page_referrer' then e.value.string_value else null end) as page_referrer,

	max(case when e.key = 'transferSize' then e.value.int_value else null end) as transferSize,
	max(case when e.key = 'encodedBodySize' then e.value.int_value else null end) as encodedBodySize,
	max(case when e.key = 'decodedBodySize' then e.value.int_value else null end) as decodedBodySize,
		max(case when e.key = 'redirectCount' then e.value.int_value else null end) as redirectCount,
	max(case when e.key = 'redirectEnd' then e.value.int_value else null end) as redirectEnd,

	max(case when e.key = 'duration' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as duration,
	max(case when e.key = 'domComplete' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as domComplete,
	max(case when e.key = 'domContentLoadedEventStart' then 
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as domContentLoadedEventStart,
	max(case when e.key = 'domContentLoadedEventEnd' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as domContentLoadedEventEnd,
	max(case when e.key = 'domInteractive' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as domInteractive,
	max(case when e.key = 'loadEventEnd' then
					 case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as loadEventEnd,
	max(case when e.key = 'loadEventStart' then
							  case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as loadEventStart,
	max(case when e.key = 'requestStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as requestStart,
	max(case when e.key = 'responseStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as responseStart,
	max(case when e.key = 'responseEnd' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as responseEnd,
	max(case when e.key = 'unloadEventEnd' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as unloadEventEnd,
	max(case when e.key = 'unloadEventStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as unloadEventStart,
	max(case when e.key = 'redirectStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as redirectStart,
	max(case when e.key = 'connectStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as connectStart,
	max(case when e.key = 'fetchStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as fetchStart,
	max(case when e.key = 'domainLookupStart' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as domainLookupStart,
	max(case when e.key = 'first_contentful_paint' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as first_contentful_paint,
	max(case when e.key = 'first_paint' then
		       case when e.value.double_value is not null then e.value.double_value
		            when e.value.int_value is not null then e.value.int_value
								else null end else null end) as first_paint

FROM
  `DATASETNAME.events_2020*`, unnest(event_params) AS e
WHERE
  event_name = 'navigation_performance'
AND _table_suffix > '0400'
GROUP BY 1,2,3,4,5,6,7

See this guide on how to measure web performance data in bigquery for context