Segment

Suggest Edits

Viewed Page

Customer viewed a page on the website

Type: Activity Transformation

SELECT
    (pv.message_id || pv.timestamp) AS activity_id
    , pv.timestamp                  AS ts
    
    , pv.anonymous_id               AS "anonymous_customer_id"
    
    , pv.email_address              AS "customer"
    
    , 'viewed_page'                 AS activity
    
    , pv.path                       AS feature_1 -- page_path
    , pv.referrer                   AS feature_2 -- referrer
    , pv.device			                AS feature_3 -- device 
    
    , NULL                          AS revenue_impact
    , pv.url                        AS link

FROM 
    from segment.pages AS pv

SELECT
    (pv.id || pv.timestamp) AS activity_id
    , pv.timestamp                  AS ts

    , pv.anonymous_id               AS "anonymous_customer_id"
    
    , pv.USER_ID              AS "customer"
    
    , 'viewed_page'                 AS activity
    
    , pv.path                       AS feature_1 -- page_path
    , pv.referrer                   AS feature_2 -- referrer
    , CASE 
        -- derive ad source from the UTM parameters
        WHEN (pv.gclid IS NOT NULL OR (pv.utm_source like '%google%' and pv.utm_medium not in ('organic', 'referral') )) THEN 'adwords'
        WHEN (((pv.utm_source like '%facebook%' OR pv.utm_source like '%fb%') and pv.utm_medium not in ('organic', 'referral') )) THEN 'facebook'
        WHEN (pv.utm_source like '%linkedin%' and pv.utm_medium not in ('organic', 'referral')) THEN 'linkedin'
        WHEN  (pv.utm_source like '%pinterest%' and pv.utm_medium not in ('organic', 'referral')) THEN 'pinterest'
            END                     AS feature_3 -- ad_source 
    
    , NULL                          AS revenue_impact
    , pv.url                        AS link

FROM (
    select
    p.ID
    , p.TIMESTAMP
    , p.ANONYMOUS_ID
    , p.USER_ID
    , p.PATH
    , p.REFERRER
    , p.URL
    , nullif ( substring ( regexp_substr ( lower(p.URL) , 'gclid=[^&]*' ) , 7 ) , '' )  AS "GCLID"
	, nullif ( substring ( regexp_substr ( lower(p.URL) , 'utm_source=[^&]*' ) , 12 ) , '' )  AS "UTM_SOURCE"
	, nullif ( substring ( regexp_substr ( lower(p.URL) , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "UTM_MEDIUM"
    from SEGMENT.PAGES AS p
) pv

WARNING: Segment doesn't actually guarantee that message_id is unique. If you capture and insert the message_id into another table, you will see that the same message_id is reused. We recommend that you concatenate the timestamp with a message_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

A customer Started a session in your product. A session defined by google is 30 minutes of inactivity

Type: Activity Transformation

SELECT
	 (pv.message_id || pv.timestamp) AS activity_id
	 	, date_add ( 'second' , -1 , pv.timestamp ) AS "ts"

        , pv.anonymous_id               AS "anonymous_customer_id"

        , pv.user_id                    AS "customer"

        , 'started_session'             AS activity

        , pv.path                       AS feature_1 -- page_path
        , pv.referrer                   AS feature_2 -- referrer
	 			, CASE 
            -- derive ad source from the UTM parameters
            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%') and utm_medium not in ('organic', 'referral') )) 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 feature_3 -- ad_source 
	 , NULL AS revenue_impact
	 , url as link
FROM (

    SELECT
        p.message_id
        , p.timestamp
        , p.anonymous_id
        , p.user_id
        , p.path
        , p.referrer
        , p.url
        , nullif ( substring ( regexp_substr ( lower(p.url) , 'gclid=[^&]*' ) , 7 ) , '' )  AS "gclid"
      , nullif ( substring ( regexp_substr ( lower(p.url) , 'utm_source=[^&]*' ) , 12 ) , '' )  AS "utm_source"
      , nullif ( substring ( regexp_substr ( lower(p.url) , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "utm_medium"
  		 , lag (p.timestamp) over ( partition by NVL(p.anonymous_id, p.user_id) order by p.timestamp ) AS "last_ts"
        from segment.pages AS p

) AS pv

WHERE date_diff ( 'minutes' , pv.last_ts , pv.timestamp ) >=30 
	OR pv.last_ts is null

SELECT
     activity_id
     , DATEADD( 'second' , -1 , ts ) AS ts
     , anonymous_customer_id
     , customer
     , 'started_session' AS activity
     , activity as feature_1
     , NULL AS feature_2
     , feature_3 -- ad_source
     , NULL AS revenue_impact
     , link
FROM (
    SELECT
         activity_id
         , ts
         , anonymous_customer_id
         , customer
         , activity
         , link
     , feature_3
         , lag (s.ts) over ( partition by nvl ( customer , anonoymous_customer_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 DATEDIFF(minutes, last_ts , s.ts) >=30 
    OR s.last_ts is null

Why do we subtract 1 second from the session?

We are using the first page_view to represent a session but technically a session starts right before that. We subtract 1 second to handle that case. This ensures that saying give me all page views after a session returns the first page view too.

Learn More about Creating an Activity Using Another Activity


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




Logged In

Customer logged into the platform

Type: Activity Transformation

SELECT 

    t.message_id ||t.timestamp  as activity_id
    , t.timestamp as ts

     , t.anonymous_id AS "anonymous_customer_id"
    
    , u.email AS "customer"
    
    , 'logged_in' as activity
    
    , t.context_os_name as feature_1
    , t.referrer as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , t.url as link

FROM segment.tracks t 
join internal_db.users u 
	on (u.id = p.user_id)
where t.name  = 'Logged In'



Viewed Paywall

Customer viewed a paywall alert

Type: Activity Transformation

SELECT
    t.message_id as activity_id
    , t.timestamp as ts
    
	 , NULL AS "anonymous_customer_id"
    
    , u.email AS "customer"

    , 'viewed_paywall' as activity 
    
    , d.name as feature_1 -- paywall name
    , d.reason as feature_2 -- shown reason 
    , NULL as feature_3
    
    , NULL as revenue_impact
    
    , t.context_page_url as link

FROM segment.tracks t 
join segment.paywall d
    on t.message_id = d.message_id
join internal_db.users u 
	on (u.id = p.user_id)
where label = 'Viewed Paywall'



Added to Cart

Customer viewed a paywall alert

Type: Activity Transformation

SELECT
    t.message_id as activity_id
    , t.timestamp as ts

	 , t.anonymous_id AS "anonymous_customer_id"
    
    , u.email AS "customer"

    , 'addd_to_cart' as activity 
    
    , c.sku as feature_1 -- Item SKU
    , NULL as feature_2  
    , NULL as feature_3
    
    , c.amount as revenue_impact
    
    , t.context_page_url as link

FROM segment.tracks t 
join segment.cart c
	on (c.message_id = t.message_id)

where t.label = 'Added To Cart'



Submitted Search

Customer submitted a search via the search bar

Type: Activity Transformation

SELECT
    t.message_id as activity_id
    , t.timestamp as ts

	 , t.anonymous_id AS "anonymous_customer_id"
    
    , u.email AS "customer"

    , 'submitted_search' as activity 
    
    , s.term as feature_1 -- Search Term
    , NULL as feature_2  
    , NULL as feature_3
    
    , c.amount as revenue_impact
    
    , t.context_page_url as link

FROM segment.tracks t 
join segment.search s
	on (s.message_id = t.message_id)

where t.label = 'Submitted Search'



Viewed Cart

Customer viewed the cart page

Type: Activity Transformation

SELECT
    p.message_id ||p.timestamp  as activity_id
    , p.timestamp as ts
  
     , p.anonymous_id AS "anonymous_customer_id"
    
    , p.email AS "customer"
    
    , 'viewed_cart' as activity
    
    , p.path as feature_1
    , NULL as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , p.url as link
    
FROM segment.pages AS p
where SPLIT_PART(p.path, '/', 3) = 'cart'



Viewed Product

Customer viewed a product

Type: Activity Transformation

SELECT
    p.message_id ||p.timestamp  as activity_id
    , p.timestamp as ts
    
     , p.anonymous_id AS "anonymous_customer_id"
    
    , p.email AS "customer"
    
    , 'viewed_product' as activity
    
    , p.path as feature_1
    , SPLIT_PART(p.path, '/', 4) as feature_2 -- category
    , SPLIT_PART(p.path, '/', 5) as feature_3 -- sku
    
    , null as revenue_impact
    , p.url as link
    
FROM segment.pages AS p
where SPLIT_PART(p.path, '/', 3) = 'products'



Enriched Pages

Additional features for the viewed_page, viewed_product, and started_session activities


Type: Enrichment Transformation

SELECT
    p.message_id || p.timestamp AS enriched_activity_id
    , p.timestamp AS enriched_ts
    , (p.context_ip)::varchar(255) as ip_address 
    , (p.context_device_type)::varchar(255) as device
    , (p.context_page_path)::varchar(255) as page_path 
    , (p.context_referrer_link)::varchar(255) as referral_url
    , (p.context_page_title)::varchar(255) as page_title
    , nullif ( substring ( regexp_substr ( lower(p.url) ,  'fbclid=[^&]*' ) , 8 ) , '' )::varchar(255) 
        AS fbclid

	, nullif ( substring ( regexp_substr ( lower(p.url) , 'gclid=[^&]*' ) , 7 ) , '' )::varchar(255) 
         AS gclid

	, nullif ( substring ( regexp_substr ( lower(p.url) , 'utm_source=[^&]*' ) , 12 ) , '' )::varchar(255) 
         AS utm_source

	, nullif ( substring ( regexp_substr ( lower(p.url) , 'utm_medium=[^&]*' ) , 12 ) , '' )::varchar(255) 
         AS utm_medium

	, nullif ( substring ( regexp_substr ( lower(p.url) , 'utm_campaign=[^&]*' ) , 14 ) , '' )::varchar(255) 
         AS utm_campaign

	, nullif ( substring ( regexp_substr ( lower(p.url) , 'utm_content=[^&]*' ) , 13 ) , '' )::varchar ( 255 ) 
         AS utm_content
FROM segment.pages AS p

Processing Configuration:
Enriched Activities: viewed_page, viewed_product, and started_session
Why?
All of these activities have the same activity_id because they are based on page views.



👍

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 2 years ago

What’s Next

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