Documentation

Google Analytics 4

Suggest Edits

Started Session

Customer started a session on the website

Type: Activity Transformation


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



Viewed Page


Type: Activity Transformation

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')



Enriched Pages


Type: Enrichment Transformation
Enriches both: Started Session and Viewed Page

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



Viewed Product

Viewed a product on an e-commerce website like Shopify for example

Type: Activity Transformation

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/%'



šŸ‘

Contribute to the Narrator Community

Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.




Have questions?

Our data team is here to help! Here are a couple ways to get in touch...

šŸ’¬ Chat us from within Narrator

šŸ’Œ Email us at [email protected]

šŸ—“ Or schedule a 15 minute meeting with our data team

Updated over 1 year ago