Narrator Documentation

Google Analytics 4

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,
        
        'google_analytics' as source,
        user_pseudo_id as source_id,
        NULL as customer, 

        'started_session' 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
            NET.REG_DOMAIN(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 ('session_start')



Viewed Page


Type: Activity Transformation

SELECT 
    CONCAT(event_timestamp, user_pseudo_id) as activity_id,
    TIMESTAMP_MICROS(event_timestamp) as ts,
    
    'google_analytics' as source,
    user_pseudo_id as source_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,
        
        'google_analytics' as source,
        user_pseudo_id as source_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 about a month ago


Google Analytics 4


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.