Mixpanel
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
, e.distinct_id_before_identity AS "anonymous_customer_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"
, anonymous_customer_id
, customer
, 'started_session' AS "activity"
, event_name as feature_1
, NULL AS feature_2
, NULL AS feature_3
, NULL AS revenue_impact
, link
FROM (
SELECT
e.event_id || e.time as activity_id
, e.time as ts
, e.distinct_id_before_identity AS "anonymous_customer_id"
, e.email AS "customer"
, e.os as feature_1
, e.referrer as feature_2
, NULL as feature_3
, null as revenue_impact
, e.current_url as link
, e.name as event_name
, lag (e.time) over ( partition by NVL(e.distinct_id_before_identity, e.email) order by e.time ) AS "last_ts"
FROM mp.event e
where e.name = 'Viewed 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
, e.distinct_id_before_identity AS "anonymous_customer_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
, e.distinct_id_before_identity AS "anonymous_customer_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"
, s.event_name as feature_1
, NULL AS feature_2
, NULL AS feature_3
, NULL AS revenue_impact
, link
FROM (
SELECT
e.event_id || e.time as activity_id
, e.time as ts
, e.distinct_id_before_identity AS "anonymous_customer_id"
, e.email AS "customer"
, e.os as feature_1
, e.referrer as feature_2
, NULL as feature_3
, null as revenue_impact
, e.current_url as link
, e.name as event_name
FROM mp.event e
where e.name in ('Viewed 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
, e.distinct_id_before_identity AS "anonymous_customer_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
, e.distinct_id_before_identity AS "anonymous_customer_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
, e.distinct_id_before_identity AS "anonymous_customer_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
, e.distinct_id_before_identity AS "anonymous_customer_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
, e.distinct_id_before_identity AS "anonymous_customer_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 minute meeting with our data team
Updated almost 3 years ago
Don't see what you're looking for? Browse the Activity Index for more examples...