Skip to content

Instantly share code, notes, and snippets.

@zjuul
Created April 14, 2020 20:18
Show Gist options
  • Save zjuul/0ae677de25a7be4760813b45696b4934 to your computer and use it in GitHub Desktop.
Save zjuul/0ae677de25a7be4760813b45696b4934 to your computer and use it in GitHub Desktop.
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
Copy link
Author

zjuul commented Apr 20, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment