with prep as (select --key session data user_pseudo_id, event_date as date, device.web_info.hostname AS Hostname, device.category as device_category, (select value.string_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id, concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) as sessions, (case when event_name = "first_visit" then user_pseudo_id end) as new_user, --page data regexp_replace(regexp_replace((select p.value.string_value from unnest(event_params) as p where p.key = 'page_location'),r'^https?://[^/]+',''),r'[\?].*','') as page_path, regexp_replace((select value.string_value from unnest(event_params) where key = 'page_location'), r'\?.*', '') as page_location, (select value.string_value from unnest(event_params) where key = 'page_title') as page_title, --FIrst traffic data traffic_source.name AS First_Campaign, traffic_source.medium AS FIRST_medium, traffic_source.source AS FIRST_source, --Engagement metrics max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged, sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec'))/1000 as engagement_time_seconds, max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec'AND user_pseudo_id is not null)) as USER_engagement_time_msec, max((select value.string_value from unnest(event_params) where key = 'variant_id')) as variant_id, max((select value.string_value from unnest(event_params) where key = 'experiment_id')) as experiment_id, max((select item_name from unnest(items) )) as itemName, count(distinct case when event_name = "page_view" then concat(user_pseudo_id,event_timestamp) end) as page_views from `bigquery-341716.analytics_278788286.events_*` WHERE _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 2 day)) and format_date('%Y%m%d',date_sub(current_date(), interval 2 day)) group by user_pseudo_id, page_path, page_location, page_title, date, ga_session_id, sessions, new_user, First_Campaign, FIRST_medium, FIRST_source, Hostname, device_category) select Hostname, date, First_medium as medium, FIRST_source as source, First_Campaign as Campaign, itemName as Campaign_code, page_path, page_location, page_title, case when First_Campaign is null and (First_medium = '(not set)' or First_medium is null) then 'Direct' when First_medium = '(none)' then 'Direct' when First_medium = 'null' then 'Direct' when First_medium = 'organic' then 'Organic Search' when regexp_contains(First_medium, r'^(facebook|twitter|youtube|social|social-network|social-media|sm|social network|social media)$') then 'Social' when regexp_contains(First_medium, r'^(share|shared)$') then 'Shared' when regexp_contains(First_medium, r'^(email|emai|email-|ciwf)$') then 'Email' when First_medium = 'affiliate' then 'Affiliates' when First_medium = 'referral' then 'Referral' when First_medium = 'QR-code' then 'QR-code' when regexp_contains(First_medium, r'^(cpc|ppc|paidsearch)$') then 'Lead Gen' when regexp_contains(First_medium, r' ^(cpv|cpa|cpp|content-text)$') then 'Other Advertising' when regexp_contains(First_medium, r'^(display|cpm|banner)$') then 'Display' else '(Other)' end as session_default_medium_grouping, count(distinct sessions) as sessions, --count(distinct concat(user_pseudo_id,ga_session_id)) as sessions, count(distinct case when session_engaged = '1' then concat(user_pseudo_id)END) as Engaged_Sessions, COUNT (DISTINCT user_pseudo_id) AS Users, COUNT (DISTINCT new_user) AS near_Users, sum(page_views) as page_view, --(sum(engagement_time_seconds),count(distinct case when session_engaged = '1' then sessions end)) as engagement_time, (safe_divide(sum(USER_engagement_time_msec),count(distinct case when session_engaged = '1' then sessions end)))/1000 as USER_engagement_time from prep group by Hostname, Campaign, itemName, date, First_medium, FIRST_source, session_default_medium_grouping, device_category, page_path, page_location, page_title
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter