Narrator Documentation

Viewed Page

Customer viewed a page on the website

Type: Activity Transformation

SELECT
     e.event_id || e.collector_tstamp AS "activity_id"
     , e.collector_tstamp AS "ts"
     
     , 'snowplow' AS "source"
     , e.domain_userid AS "source_id"
     
     , e.user_id  AS "customer"
     
     , 'viewed_page' AS "activity"
     
     , e.page_urlpath AS "feature_1"
     
     , nullif ( e.page_referrer, '' ) AS "feature_2"
     , e.os_name  AS "feature_3"
     
     , NULL AS "revenue_impact"
     , e.page_url AS "link"
     
FROM snowplow.event e
where e.event = 'pv'

WARNING: Snowplow doesn't actually guarantee that event_id is unique. If you capture and insert the event_id into another table, you will see that the same event_id is reused. We recommend that you concatenate the collector_tstamp with the event_id to create a unique id.

Processing Configuration:

  • Run After: Started Session
    Why? Setting this transformation to run after the session will ensure that page views will be used to define sessions only AFTER the identity resolution is applied.


Started Session

Started Session is derived from page views and it occurs one second before the first page view in that session.

A new session will be recorded if there are 30 minutes of inactivity between two page views.

Type: Activity Transformation

SELECT
     activity_id
     , date_add ( 'second' , -1 , ts ) AS "ts"
     , source
     , source_id
     , customer
     , 'started_session' AS "activity"
     , activity as feature_1
     , NULL AS feature_2
     , NULL AS feature_3
     , NULL AS revenue_impact
     , link
FROM (
    SELECT
         activity_id
         , ts
         , source
         , source_id
         , customer
         , activity
         , link
         , lag (s.ts) over ( partition by nvl ( customer , source_id ) order by ts ) AS "last_ts"
    FROM narrator.activity_stream AS s
  -- activity that starts a session
    WHERE activity in ( 'viewed_page' )
) AS s

WHERE date_diff ( 'minutes' , last_ts , s.ts ) >=30 
    OR last_ts is null

Processing Configuration:

  • Depends On: Viewed Page
    Why? Every time the viewed page activity's definition is updated or the transformation is resynced it will also update the started session activities


Enriched Pages

Enriched Pages transformation to generate an enrichment table for Viewed Page and Started Session activities.


Type: Enrichment Transformation

SELECT
     *
     , CASE 
        WHEN referring_domain in ( 'google' , 'yahoo' , 'bing' , 'ask' , 'aol' , 'baidu' , 'wolframalpha' , 'duckduckgo' , 'archive' , 'yandex' ) THEN 'Search' 
        WHEN referring_domain in ( 'facebook' , 'fb' , 'ig' , 'snap' , 'linkedin' , 't' , 'instagram' , 'youtube' , 'pinterest' , 'tumblr' , 'reddit' , 'flickr' ) THEN 'Social' 
        WHEN referring_domain is null or referring_domain ilike '%MYCOMPANYURL%' THEN 'Direct' 
        ELSE 'Referral'
     END AS "referral_kind"
     , CASE 
        WHEN gclid is not NULL or utm_medium is not null or (referring_domain =  'pinterest' and landing_url ilike '%epik=%') THEN 'Paid' 
        ELSE 'Organic'
     END AS "medium_kind"
     , CASE 
        WHEN gclid is not null or utm_source in ('google') or (nvl(utm_medium, utm_source) is not null and referring_domain = 'google') then 'adwords'
        WHEN utm_campaign ilike'%facebook%' or utm_source in ('fb', 'facebook') or  ( (nvl(utm_medium, utm_source) is not null and  referring_domain in ('fb', 'facebook', 'ig') ) )  then 'facebook'
        WHEN utm_source in ('pinterest') or landing_url ilike '%epik=%' then 'pinterest' 
      ELSE 'Organic'
      END as ad_source
FROM (
    SELECT
         e.event_id AS "enriched_activity_id"
         , e.collector_tstamp as enriched_ts
         , nullif ( e.refr_urlquery , '' ) ::varchar ( 2000 ) AS "search"
         , nullif ( e.page_referrer , '' ) ::varchar ( 2000 ) AS "referrer"
         , nullif ( reverse ( split_part ( reverse ( replace ( regexp_substr ( e.page_referrer , '//[^/\\\,[email protected]\\+]+\\.[^/:;,\\\\\(\\)]+' ) , '//' , '' ) ) , '.' , 2 ) ) , '' ) AS "referring_domain"
         , e.geo_country
         , e.geo_region
         , e.geo_city
         , e.geo_region_name
         , e.geo_timezone
         , e.os_name 
         , e.os_family
         , CASE WHEN e.dvce_ismobile THEN 'mobile' else 'desktop' end AS "device"
         , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'fbclid=[^&]*' ) , 8 ) , '' ) AS "fbclid"
         , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'gclid=[^&]*' ) , 7 ) , '' )  AS "gclid"
         , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_source=[^&]*' ) , 12 ) , '' )  AS "utm_source"
         , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "utm_medium"
         , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_campaign=[^&]*' ) , 14 ) , '' ) AS "utm_campaign"
         , nullif ( substring ( regexp_substr ( lower(e.page_url) , 'utm_content=[^&]*' ) , 13 ) , '' )::varchar ( 255 ) AS "utm_content"
         , e.page_url::varchar ( 255 ) AS "landing_url"
         , e.page_urlpath::varchar ( 255 ) AS "landing_page"
    FROM snowplow.event e 
    WHERE e.event = 'pv'
)



Identified Visitor

Customer was identified on the website.


This is a dedicated activity for identity resolution. Read more about generating dedicated activities to improve identity resolution.

Type: Activity Transformation

SELECT
     e.event_id AS "activity_id"
     
     , e.dvce_tstamp AS "ts"
     
     , 'snowplow' AS "source"
     , e.domain_userid AS "source_id"
     
     , e.se_label  AS "customer"
     
     , 'identified_visitor' AS "activity"
     
     , e.page_urlpath AS "feature_1"
     
     , NULL AS "feature_2"
     , NULL  AS "feature_3"
     
     , NULL AS "revenue_impact"
     , e.page_url AS "link"
     
FROM snowplow.event e
where e.event = 'se' 
        and se_action='identify'
    and e.se_label ilike '%@%'



šŸ‘

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-min meeting with our data team

Updated 2 months ago


What's Next

Don't see what you're looking for? Browse the Activity Index for more examples...

Activity Index

Snowplow


Suggested Edits are limited on API Reference Pages

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