select  
user_pseudo_id,
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,
event_date as date,
(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,
traffic_source.name AS First_Campaign,
traffic_source.medium AS FIRST_medium,
traffic_source.source AS FIRST_source,
device.web_info.hostname AS Hostname,
device.category as device_category,

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.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,
   
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,
First_Campaign,
FIRST_medium,
FIRST_source,
Hostname,
device_category