SELECT 
    
        CONCAT(event_timestamp, user_pseudo_id) as activity_id,
        TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL -1 second) as ts,
       
        user_pseudo_id as anonymous_customer_id,
        NULL as customer, 

        'started_session' activity,
        
        
        page_referrer AS feature_page_referrer,

            
        split(regexp_extract((t.page_location), '^https?://(?:[^/]*)(.*)'), '?')[SAFE_OFFSET(0)] as feature_path, 

        -- UTM Parameters
        nullif( substring ( regexp_substr ( lower(t.page_location), 'utm_medium=[^&]*' ) , 12 ) , '' )  AS feature_utm_medium,
        nullif( substring ( regexp_substr ( lower(t.page_location), 'utm_source=[^&]*' ) , 12 ) , '' )  AS feature_utm_source,
        nullif( substring ( regexp_substr ( lower(t.page_location), 'utm_term=[^&]*' ) , 10 ) , '' )  AS feature_utm_term,
        nullif( substring ( regexp_substr ( lower(t.page_location), 'utm_content=[^&]*' ) , 13 ) , '' )  AS feature_utm_content,
        nullif( substring ( regexp_substr ( lower(t.page_location), 'utm_campaign=[^&]*' ) , 14) , '' )  AS feature_utm_campaign,

        nullif( substring ( regexp_substr ( lower(t.page_location) , 'fbclid=[^&]*' ) , 8 ) , '' ) AS feature_fbclid,
        gclid AS feature_gclid,

        NULL as revenue_impact,

        t.page_location AS link -- url
 
FROM ( 
        
    -- extract features from GA4
    SELECT 
        user_pseudo_id, 
        event_timestamp,
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            -- Insert a parameter key
            key = 'page_referrer') AS referring_url,
    
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            -- Insert a parameter key
            key = 'page_title') AS page_title,

        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            -- Insert a parameter key
            key = 'page_location') AS page_location,
        
        (SELECT
            NET.REG_DOMAIN(value.string_value)
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_referrer') AS page_referrer,
        
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'gclid') AS gclid



    FROM `YOURSCHEMAHERE.events_20*`
    WHERE event_name in ('session_start')
    ) as t

SELECT 
    CONCAT(event_timestamp, user_pseudo_id) as activity_id,
    TIMESTAMP_MICROS(event_timestamp) as ts,
    
    user_pseudo_id as anonymous_customer_id,
    NULL as customer, 

    'viewed_page' activity,

    split(regexp_extract(
        (SELECT 
            value.string_value 
        FROM 
            UNNEST(event_params) 
        WHERE 
            key = 'page_location'
    ), '^https?://(?:[^/]*)(.*)'), '?')[SAFE_OFFSET(0)] as feature_1, -- page_path

    (SELECT
        value.string_value
    FROM
        UNNEST(event_params)
    WHERE
        key = 'page_referrer') AS feature_2, -- referrer

    NULL as feature_3,
        
    NULL as revenue_impact,
    
    (SELECT
        value.string_value
    FROM
        UNNEST(event_params)
    WHERE
        key = 'page_location') AS link -- url

FROM `YOURSCHEMAHERE.events_*`
WHERE event_name in ('page_view')

SELECT 
    CONCAT(t.event_timestamp, user_pseudo_id) as enriched_activity_id,
    TIMESTAMP_MICROS(t.event_timestamp) as enriched_ts,
    
    page_title,
    referring_url,
    IF(search is null, null, CONCAT('?', search)) as search,
    continent,
    country,
    region,
    city,
    device_category,
    gclid,
    fbclid,
    utm_source,
    utm_medium,
    utm_term,
    utm_campaign,
    utm_content,
    CASE WHEN (gclid IS NOT NULL OR (utm_source like '%google%' and utm_medium not in ('organic', 'referral') )) THEN 'adwords'
        WHEN ((utm_source like '%facebook%' OR utm_source like '%fb%' or utm_source like '%instagram%') and (utm_medium not in ('organic', 'referral') or utm_medium is null)) THEN 'facebook'
        WHEN (utm_source like '%linkedin%' and utm_medium not in ('organic', 'referral')) THEN 'linkedin'
        WHEN  (utm_source like '%pinterest%' and utm_medium not in ('organic', 'referral')) THEN 'pinterest'
    END AS ad_source
   
FROM ( 
    SELECT 
        user_pseudo_id,
        geo.continent,
        geo.country,
        geo.region,
        geo.city,
        event_name,
        event_timestamp,
        device.category as device_category,

        split(regexp_extract((
            select value.string_value 
            from unnest(event_params) 
            where key = 'page_location'
        ), '^https?://(?:[^/]*)(.*)'), '?')[SAFE_OFFSET(1)] as search, -- query/search params

    (SELECT
        nullif ( substring ( regexp_substr ( lower(value.string_value) , 'fbclid=[^&]*' ) , 8 ) , '' ) 
    FROM
        UNNEST(event_params)
    WHERE
        -- Insert a parameter key
        key = 'page_location') AS fbclid,
    
    (SELECT
        value.string_value
    FROM
        UNNEST(event_params)
    WHERE
        -- Insert a parameter key
        key = 'gclid') AS gclid,

    (SELECT
        value.string_value
    FROM
        UNNEST(event_params)
    WHERE
        -- Insert a parameter key
        key = 'page_location') AS page_location,

    nullif ( substring ( regexp_substr ( lower(
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_location')
    ), 'utm_medium=[^&]*' ) , 12 ) , '' )  AS utm_medium,

    nullif ( substring ( regexp_substr ( lower(
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_location')
    ), 'utm_source=[^&]*' ) , 12 ) , '' )  AS utm_source,

    nullif ( substring ( regexp_substr ( lower(
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_location')
    ), 'utm_term=[^&]*' ) , 10 ) , '' )  AS utm_term,

    nullif ( substring ( regexp_substr ( lower(
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_location')
    ), 'utm_content=[^&]*' ) , 13 ) , '' )  AS utm_content,

    nullif ( substring ( regexp_substr ( lower(
        (SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_location')
    ), 'utm_campaign=[^&]*' ) , 14) , '' )  AS utm_campaign,
    
    (SELECT
        value.string_value
    FROM
        UNNEST(event_params)
    WHERE
        -- Insert a parameter key
        key = 'page_referrer') AS referring_url,
    
    (SELECT
        value.string_value
    FROM
        UNNEST(event_params)
    WHERE
        -- Insert a parameter key
        key = 'page_title') AS page_title,

    FROM `YOURSCHEMAHERE.events_*`
    WHERE event_name in ('page_view')
) t

SELECT *
from 
    (SELECT 
        CONCAT(event_timestamp, user_pseudo_id) as activity_id,
        TIMESTAMP_MICROS(event_timestamp) as ts,
        
        user_pseudo_id as anonymous_customer_id,
        NULL as customer, 

        'viewed_product' activity,

        split(regexp_extract((
            select value.string_value 
            from unnest(event_params) 
            where key = 'page_location'
        ), '^https?://(?:[^/]*)(.*)'), '?')[SAFE_OFFSET(0)] as feature_1, -- page_path

        ARRAY_REVERSE(split(split(regexp_extract((
            select value.string_value 
            from unnest(event_params) 
            where key = 'page_location'
        ), '^https?://(?:[^/]*)(.*)'), '?')[SAFE_OFFSET(0)], '/'))[SAFE_OFFSET(0)] as feature_2, -- product from page_path

        NULL as feature_3,
            
        NULL as revenue_impact,
        (
        SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = 'page_location') AS link , -- url
    FROM `YOURSCHEMAHERE.events_*`
    WHERE event_name in ('page_view')
    )
WHERE lower(link) like '%/products/%'