Narrator Docs

Viewed app screen

Customer viewed a page on the website

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'viewed_app_screen' as activity
    
    , e.os as feature_1
    , e.referrer as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'Viewed Screen'

WARNING: Mixpanel 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 time with a 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

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

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_app_screen' )
) AS s

WHERE date_diff ( 'minutes' , last_ts , s.ts ) >=30 
    OR 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.


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
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'logged_in' as activity
    
    , e.os as feature_1
    , e.referrer as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'Logged In'



Opened App

Customer viewed a paywall alert

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'opened_app' as activity
    
    , NULL as feature_1
    , NULL as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'Opened App'



Started App Session

User started a new session on the app (after 30 minutes of inactivity)

Type: Activity Transformation

SELECT
     activity_id
     , date_add ( 'second' , -1 , ts ) AS "ts"
     , source
     , source_id
     , customer
     , 'started__app_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_app_screen', 'opened_app' )
) AS s

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



Viewed Paywall

Customer viewed a paywall alert

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'viewed_paywall' as activity
    
    , JSON_EXTRACT_PATH_TEXT(e.properties, 'name') as feature_1 -- paywall name
    , JSON_EXTRACT_PATH_TEXT(e.properties, 'reason') as feature_2 -- paywall reason
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'Launched Paywall'



Added to Cart

Customer viewed a paywall alert

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'addd_to_cart' as activity 
    
    , JSON_EXTRACT_PATH_TEXT(e.properties, 'sku') as feature_1 -- SKU
    , NULL as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name =  'Added To Cart'



Submitted Search

Customer submitted a search via the search bar

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'submitted_search' as activity 
    
    , JSON_EXTRACT_PATH_TEXT(e.properties, 'term') as feature_1 -- search term
    , NULL as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'Submitted Search'



Viewed Cart

Customer viewed the cart page

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'viewed_cart' as activity
    
    , e.os as feature_1
    , e.referrer as feature_2
    , NULL as feature_3
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'View Page' and e.SPLIT_PART(e.current_path, '/', 3) = 'cart'



Viewed Product

Customer viewed a product

Type: Activity Transformation

SELECT
    e.event_id || e.time  as activity_id

    , e.time as ts

    , 'mixpanel' AS "source"
     , e.distinct_id_before_identity AS "source_id"
    
    , e.email AS "customer"
    
    , 'viewed_product' as activity
    
    , p.current_path as feature_1
    , SPLIT_PART(e.current_path, '/', 4) as feature_2 -- category
    , SPLIT_PART(p.current_path, '/', 5) as feature_3 -- sku
    
    , null as revenue_impact
    , e.current_url as link

FROM mp.event e
where e.name = 'View Page' and e.SPLIT_PART(e.current_path, '/', 3) = 'products'
SELECT
    p.message_id as activity_id
    , p.timestamp as ts
    , 'segment' AS source
     , p.anonymous_id AS source_id
    
    , p.user_id AS customer
    
    , 'viewed_page' as activity
    
    , p.path as feature_1
    , p.referrer as feature_2
    , p.context_os_name as feature_3
    
    , null as revenue_impact
    , p.url as link
FROM segment.pages AS p
SELECT
    p.message_id as activity_id
    , p.timestamp as ts
    , 'segment' AS source
    , p.anonymous_id AS source_id
    
    , p.user_id AS customer
    
    , 'viewed_page' as activity
    
    , p.path as feature_1
    , p.referrer as feature_2
    , p.context_os_name as feature_3
    
    , null as revenue_impact
    , p.url as link
FROM segment.pages AS p



šŸ‘

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

Mixpanel


Suggested Edits are limited on API Reference Pages

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