Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Transform Performance Navigation data to wide format
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
@zjuul

This comment has been minimized.

Copy link
Owner Author

@zjuul zjuul commented Apr 20, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.