Script Templates

Core Scripts

Page Views

Source: segment.com

This script creates a page view activity using data generated from Segment. A "page view" activity simply represents every time a customer views a page on your website.

SQL Template

SELECT
	 p.message_id AS  activity_id -- unique id of the page view
	 , p.timestamp AS  ts -- timestamp in UTC of when this activity occurred
	 , 'segment' AS  source -- name of the source of the customer identifier (source id)
	 , p.anonymous_id AS  source_id -- customer identifier from the data source
	 , p.email AS  customer -- email associated with the anonymous, if available
	 , 'page_view' AS  activity -- name of the activity
	 , p.context_page_path::varchar ( 250 ) AS  feature_1
	 , nullif ( p.referrer, '' ) ::varchar ( 250 ) AS  feature_2
	 , NULL ::VARCHAR(255) feature_3
	 , NULL ::FLOAT revenue_impact -- left null, because page view is not revenue generating
	 , p.context_page_url::varchar ( 250 ) AS  link -- full link to the page visited
FROM segment.pages AS p

Column Mapping for Page View

Source TableActivity StreamNotes
message_idactivity_idThis is a good unique identifier for the page view event
timestamptsThis is the time of when the activity was fired in UTC
'segment'sourceSet to the literal string "segment" so that Narrator knows where the source_id comes from
anonymous_idsource_idThe anonymous_id is the cookie for every browser that was set by Segment. This field will be used to identify the user as more data is captured with this id.
emailcustomerEmail is a good identifier since it is globally unique across all systems. If no email is available for that page view then it is NULL and the user mapping will handle is appropriately.
'page_view'activityThis is the name of the activity that is defined. It uses the literal string: "page_view".
context_page_pathfeature_1We chose to set the first feature of this activity to the path of the webpage that the customer visited.
referrerfeature_2We set the second feature as the referring url so it'll be easy to understand where each customer with a page_view activity was referred from. referrer is null if no referring url is available.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because page view is not a revenue generating activity
context_page_urllinkThis is the full link to the page the customer visited. This allows a user in Narrator to click on the activity in the customer journey and visit the exact page that is associated with this activity.

Session

Source: Page Views activity

It's useful to group a series of page views on a site into a single event -- this makes it easier to track visits. We'll call this a 'session' and define it as all the page views done by a user before being inactive for 30 minutes.

This is a synthetic event: it's computed entirely from the Page View activity.

To do this we simply subtract the time between page_view activities and define a new session if more than 30 minutes has elapsed.

You can set up the session definition to use any web activity (not just page view) if you want. For example, it is common to include clicks on the website in addition to page views. For simplicity, this example only uses page views.

We derive session using data that was generated prior to the last hour because the data is refreshed every 30 minutes (data update rate) so we don't want to count a customer as "inactive" if it was just the last data point.

SQL Template

SELECT
	  activity_id
	 , date_add ( 'minutes', -1, ts ) AS  ts -- set session ts as one second before the first web activity
	 ,  source
	 ,  source_id
	 ,  customer
	 , 'session' AS  activity -- name of activity
	 ,  feature_1
	 ,  feature_2
	 ,  feature_3
	 ,  revenue_impact
	 ,  link
FROM (
	SELECT
		  activity_id
		 , ts at time zone 'utc' AS  ts
		 ,  source
		 ,  source_id
		 ,  customer
		 ,  feature_1
		 ,  feature_2
		 ,  feature_3
		 ,  revenue_impact
		 ,  link

		 -- last_ts gets the timestamp of the customer's activity that occurred before this one
		 , lag ( s.ts at time zone 'utc' ) over ( partition by nvl ( customer, source_id ) order by ts ) AS  last_ts
	FROM dw.mv_activity_stream AS s
	WHERE activity in ( 'page_view' ) -- add more web events here if you would like them to be a part of the session definition
		AND ts < DATE_ADD ( 'hour', -1, SYSDATE::TIMESTAMP ) -- define session using data generated prior to the last hour to circumvent any complications from the data refresh cadence
) AS s
WHERE date_diff ( 'minutes', last_ts, s.ts ) >=30  -- define session when more than 30 minutes has elapsed between activities
	OR last_ts is null -- define new session when it's the first activity that occurred from the user

Column Mapping for Session

We use the same mapping as the pageview activity. The activityid is the same as page view because it's representing the same action in Segment.

Source TableActivity StreamNotes
message_idactivity_idThis is a good unique identifier for the session event because it is based off of the first page view.
timestamptsThis is the time of when the activity was fired in UTC
'segment'sourceSet to the literal string "segment" so that Narrator knows where the source_id comes from
anonymous_idsource_idThe anonymous_id is the cookie for every browser that was set by Segment. This field will be used to identify the user as more data is captured with this id.
emailcustomerEmail is a good identifier since it is globally unique across all systems. If no email is available for that page view then it is NULL and the user mapping will handle is appropriately.
'session'activityThis is the name of the activity that is defined. It uses the literal string: "session".
feature_1feature_1The session activity uses the same features as the page_view activity.
feature_2feature_2The session activity uses the same features as the page_view activity.
feature_3feature_3The session activity uses the same features as the page_view activity.
revenue_impactrevenue_impactThe session activity uses the same features as the page_view activity.
linklinkThe session activity uses the same features as the page_view activity.

First Touch Attribution

Source: Website

A "First Touch Lead Attribution" is an activity that attributes the lead to the first session of the customer. The attribution joins the first time a lead was created with that customer's first session. User mapping makes it easy to track users across their entire journey to make this possible. We reconcile the user mapping every night so this will backfill when new information is received.

SQL Template

SELECT
	  s.activity_id
	 , s.ts at time zone 'utc' AS  ts
	 , NULL ::VARCHAR(255) source
	 , NULL ::VARCHAR(255) source_id
	 ,  s.customer
	 , 'first_touch_lead_attribution' ::varchar ( 255 ) AS  activity
	 , p.session_source || ' ' || nvl ( p.domain, '' ) AS  feature_1
	 , p.utm_campaign AS  feature_2
	 , NULL ::VARCHAR(255) feature_3
	 , NULL ::FLOAT revenue_impact
	 , p.landing_url AS  link
FROM (
	SELECT
		  s.activity_id
		 ,  a.activity
		 ,  a.ts
		 ,  s.customer
	FROM (
		SELECT
			  *
		FROM dw.mv_activity_stream AS a
		WHERE a.activity in ( 'lead_created', )
			AND a.activity_occurrence = 1
			AND a.customer is not null
	) AS a
	JOIN (
		SELECT
			  *
		FROM dw.mv_activity_stream AS s
		WHERE s.activity = 'session'
			AND s.activity_occurrence = 1
			AND s.customer is not null
	) AS s
		ON ( s.ts < a.ts and s.customer = a.customer )
) AS s
JOIN dw.mv_enriched_pages AS p
	ON ( p.enriched_activity_id = s.activity_id )

Column Mapping for First Touch Attribution

Source TableActivity StreamNotes
activity_idactivity_idThe activity_id is the same as session because it's representing the same action in Segment.
timestamptsTimestamp from the lead activity (in UTC) because this is the time that the lead gets attributed.
NULLsourceThis is left null because user mapping is not needed for activities already existing in the activity stream.
NULLsource_idThis is left null because user mapping is not needed for activities already existing in the activity stream.
customercustomerEmail is a good identifier since it is globally unique across all systems. If no email is available for that page view then it is NULL and the user mapping will handle is appropriately.
last_touch_lead_created_attributionactivityThis is the name of the activity that is defined.
session_source, domainfeature_1We chose to concatenate the session source and domain so that this column reads as "Paid Google" for easy interpretation.
utm_campaignfeature_2We set the second feature as the campaign that drove the first touch because it is often helpful to understand the campaign that drove the activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because page view is not a revenue generating activity
landing_urllinkThis is the full link to the page the customer visited during their first touch session. This allows a user in Narrator to click on the activity in the customer journey and visit the exact page that is associated with this activity.

Segment: Adding Last Touch Attribution

Source: Website

A "Last Touch lead Attribution" is an activity that attributes the lead to the last session of the customer. The attribution joins to the last session the customer had before the lead was generated. This is helpful in understanding what drove a sale, a lead, or a specific activity.

SQL Template

SELECT
	 sa.activity_id
	 , sa.ts at time zone 'utc'  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , sa.customer
	 , 'last_touch_lead_created_attribution' ::varchar ( 255 )  AS "activity"
	 , p.session_source || ' ' || nvl ( p.domain, '' )  AS "feature_1"
	 , p.utm_campaign  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , p.landing_url  AS "link"
FROM (
	SELECT
		 s.activity_id
		 , a.activity
		 , a.ts
		 , s.customer
		 , row_number ( ) over ( partition by s.customer, a.activity order by s.ts desc )  AS "row_num"
	FROM (
		SELECT
			 a.*
		FROM dw.mv_activity_stream AS a
		WHERE a.activity in ( 'lead_created')
			AND a.activity_occurrence = 1
			AND a.customer is not null
	) AS a
	JOIN (
		SELECT
			 ss.*
		FROM dw.mv_activity_stream AS ss
		WHERE ss.activity = 'session'
			AND ss.customer is not null
	) AS s
		ON ( s.ts < a.ts and s.customer = a.customer ) -- only include sessions that happened before the lead was generated
) AS sa
JOIN dw.mv_enriched_pages AS p
	ON ( p.enriched_activity_id = sa.activity_id )
WHERE sa.row_num = 1 -- select only the last activity before the lead

Column Mapping for Last Touch Attribution

Source TableActivity StreamNotes
activity_idactivity_idThe activity_id is the same as session because it's representing the same action in Segment.
timestamptsTimestamp from the lead activity (in UTC) because this is the time that the lead gets attributed.
NULLsourceThis is left null because user mapping is not needed for activities already existing in the activity stream.
NULLsource_idThis is left null because user mapping is not needed for activities already existing in the activity stream.
customercustomerEmail is a good identifier since it is globally unique across all systems. If no email is available for that page view then it is NULL and the user mapping will handle is appropriately.
'lasttouch\lead_attribution'activityThis is the name of the activity that is defined. It uses the literal string: "lasttouch\lead_attribution".
session_source, domainfeature_1We chose to concatenate the session source and domain so that this column reads as "Paid Google" for easy interpretation.
utm_campaignfeature_2We set the second feature as the campaign that drove the first touch because it is often helpful to understand the campaign that drove the activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because page view is not a revenue generating activity
landing_urllinkThis is the full link to the page the customer visited during their first touch session. This allows a user in Narrator to click on the activity in the customer journey and visit the exact page that is associated with this activity.

Segment: Adding Session Enrichment

Source: Website

For session activities you may want to add more features than the allotted three features in the activity definition. To include additional features you can define an enrichment table.

This query parses many features from the url. Some web tracking products will already do this for you.

SQL Template

SELECT
     *
     , CASE
        WHEN domain in ( 'google' , 'yahoo' , 'bing' , 'ask' , 'aol' , 'baidu' , 'wolframalpha' , 'duckduckgo' , 'archive' , 'yandex' ) THEN 'Search'
        WHEN domain in ( 'facebook' , 'fb' , 'ig' , 'snap' , 'linkedin' , 't' , 'instagram' , 'youtube' , 'pinterest' , 'tumblr' , 'reddit' , 'flickr' ) THEN 'Social'
        WHEN domain is null or domain ilike 'yourcompanydomain.com' THEN 'Direct'
        ELSE 'Referral'
     END AS "referral_kind"
     , CASE
        WHEN gclid is not NULL or utm_medium in ( 'cpm' , 'cpc' , 'web' ) THEN 'Paid'
        ELSE 'Organic'
     END AS "medium_kind"
FROM (
    SELECT
         p.message_id AS "enriched_activity_id"
         , p.timestamp AS "enriched_ts"
         , NULL ::VARCHAR(255) AS "session_source" -- placeholder for session source
         , NULL ::VARCHAR(255) AS "session_kind" --placeholder for session kind
         , NULL ::VARCHAR(255) AS "ad_source" --placeholder for ad source
         , nullif ( p.search , '' ) ::varchar ( 2000 ) AS "search"
         , nullif ( p.referrer , '' ) ::varchar ( 2000 ) AS "referrer"
         , nullif ( reverse ( split_part ( reverse ( replace ( regexp_substr ( p.referrer , '//[^/\\\,=@\\+]+\\.[^/:;,\\\\\(\\)]+' ) , '//' , '' ) ) , '.' , 2 ) ) , '' ) AS "domain"
         , regexp_substr ( context_user_agent , '[^/]*' ) AS "browser"
         , nullif ( substring ( regexp_substr ( context_user_agent , '\ [^;| |/]*' ) , 3 , 30 ) , '' ) AS "exact_device"
         , CASE
            WHEN nullif ( substring ( regexp_substr ( context_user_agent , '\ [^;| |/]*' ) , 3 , 30 ) , '' ) in ( 'iphone' , 'android' , 'blackberry' , 'ipad' ) then 'mobile'
            ELSE 'desktop'
         END AS "device"
         , nullif ( substring ( regexp_substr ( p.search , 'gclid=[^&]*' ) , 7 ) , '' ) AS "gclid"
         , nullif ( substring ( regexp_substr ( p.search , 'utm_source=[^&]*' ) , 12 ) , '' ) AS "utm_source"
         , nullif ( substring ( regexp_substr ( p.search , 'utm_medium=[^&]*' ) , 12 ) , '' ) AS "utm_medium"
         , nullif ( substring ( regexp_substr ( p.search , 'utm_campaign=[^&]*' ) , 14 ) , '' ) AS "utm_campaign"
         , nullif ( substring ( regexp_substr ( p.search , 'utm_content=[^&]*' ) , 13 ) , '' ) AS "utm_content"
         , p.url::varchar ( 255 ) AS "landing_url"
         , p.path::varchar ( 255 ) AS "landing_page"
    FROM segment.pages AS p
)

Column Mapping for Session Enrichment

Source TableActivity StreamNotes
message_idenriched_activity_idThis is the id that is associated with the session activity. With enrichment tables, we need to use the same identifier that is used for the activity we want to enrich (session in this case).
timestampenriched_tsTimestamp from the session activity (in UTC).
NULLsession_sourceThis is a placeholder if you would like to define session_source.
NULLsession_kindThis is a placeholder if you would like to define a session_kind.
NULLad_sourceThis is a placeholder if you would like to define a ad_source.
searchsearchSearch term associated with the session.
referrerreferrerReferring URL of the session.
referrerdomainParse out the domain from the referring URL.
contextuseragentbrowserExtract the session's browser from the contextuseragent.
contextuseragentexact_deviceExtract the session's exact device ('iphone' , 'android' , 'blackberry', etc) from the contextuseragent.
contextuseragentdeviceExtract the session's device type ('desktop', 'mobile') from the contextuseragent.
searchgclidExtract the google click id from the search parameters.
searchutm_sourceExtract the session source from the search parameters.
searchutm_mediumExtract the utm medium from the search parameters.
searchutm_campaignExtract the utm campaign from the search parameters.
searchutm_contentExtract the utm content from the search parameters.
urllanding_urlFull url of the session landing page.
pathlanding_pagePath of the session landing page.
domainreferral_kindClassify the referral url into 'Search', 'Social', 'Direct', or 'Referral'.
gclid, utm_mediummedium_kindClassify the session as 'Paid' if it is associated with a google click id or paid utm medium, otherwise classify the session as "Organic".

Segment User Mapping with Identify Activities

Source: Website

A "Identify" activity is used to help with user mapping internally. It associates the activity_id (i.e. segment's anonymous id) with the given email. This allows Narrator to backfill earlier (anonymous) page view activities and associate them with the identified user.

The sole purpose of this event is to enable user mapping so many of the columns unrelated to user mapping are left as NULL.

SQL Template

SELECT
	 u.message_id AS  activity_id
	 , u.timestamp AS  ts -- timestamp of when user became identified
	 , 'segment' AS  source
	 , u.anonymous_id AS  source_id -- cookie id from segment
	 , lower ( u.email ) AS  customer -- identified user email
	 , 'segment_identify' AS  activity
	 , NULL ::VARCHAR(255) feature_1
	 , NULL ::VARCHAR(255) feature_2
	 , NULL ::VARCHAR(255) feature_3
	 , NULL ::FLOAT revenue_impact
	 , NULL ::VARCHAR(255) link
FROM segment.users AS u
WHERE u.email is not NULL

Column Mapping for Segment Identify Activities

Source TableActivity StreamNotes
message_idactivity_idUnique identifier of this event. We will use segment message_id.
timestamptsTimestamp (in UTC) of when the user became identified.
'segment'sourceSet to the literal string name of the user_identifier so that Narrator knows where the source user id comes from
anonymous_idsource_idThe user_id from the data source. This is the cookie id assigned by Segment. This field will be used to process user mapping.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'segment_identify'activityName of the activity. We use the literal string to define the name.
NULLfeature_1We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Source: Website

A "leadsegmentcapture" activity is used to help with the user mapping.

We recommend that when a user submits a form with their email/customer identified, then you should save the cookie(anonymous id) in your internal DB.
This is very important because front end events sometimes don't fire correctly and mapping an identified user to their prior anonymous activity becomes difficult.

By adding a user mapping event like 'leadsegmentcapture' when users submit a form (or otherwise identify themselves), Narrator will be able to map this user to all their (previously anonymous) activities in the data.

SQL Template

SELECT
	 l.id AS  activity_id
	 , l.updated AS  ts
	 , 'segment' AS  source
	 , l.segment_id AS  source_id
	 , l.email AS  customer
	 , 'lead_segment_capture' AS  activity
	 , l.lead_source AS  feature_1
	 , NULL ::VARCHAR(255) feature_2
	 , NULL ::VARCHAR(255) feature_3
	 , NULL ::FLOAT revenue_impact
	 , NULL ::VARCHAR(255) link

FROM postgres_public.leads l
where l.segment_id is not NULL
Source TableActivity StreamNotes
message_idactivity_idUnique identifier of this event. We will use lead id.
updated_attsTimestamp (in UTC) of when the user became identified. This is when the lead was created.
'segment'sourceSet to the literal string name of the user_identifier so that Narrator knows where the source user id comes from.
segment_idsource_idThe anonymous user id from the data source. This is the cookie id assigned by Segment. This field will be used to apply the user mapping.
emailcustomerThis is the email associated with the lead.
'leadsegmentcapture'activityName of the activity. We use the literal string to define the name.
NULLfeature_1We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Segment User Mapping with IP Addresses

Source: Website

A "ip_mapping" activity is used to help with the user mapping by using the same activity fired in the same two seconds with the same IP. This approach is great because the same IP ties to multiple users at a time but it is rare that two people on the same IP will start your form at the same time.

This script looks for activities that fire on the front end via track and are also stored in the database at the same time.

SQL Template

SELECT
	  f.id as activity_id
	 ,  f.started_at as ts
	 , 'segment' AS  source
	 , t.anonymous_id AS  source_id
	 , l.email as customer
	 , 'ip_mapping' AS  activity
	 , NULL ::VARCHAR(255) feature_1
	 , NULL ::VARCHAR(255) feature_2
	 , NULL ::VARCHAR(255) feature_3
	 , NULL ::FLOAT revenue_impact
	 , NULL ::VARCHAR(255) link
FROM postgres_public.forms_captured AS f
JOIN postgres_public.lead as l
	on (l.id = f.lead_id)
JOIN (
	SELECT
		  t.anonymous_id
		 ,  t.timestamp
		 ,  t.context_ip
	FROM segment.tracks AS t
	WHERE t.event = 'form_step_1_started'
) AS t
	ON ( t.context_ip = l.ip_address and ABS ( DATE_DIFF ( 'seconds', f.started_at ,t.timestamp ) ) <= 1 )
WHERE l.ip_address is not NULL

Column Mapping for IP User Mapping

Source TableActivity StreamNotes
idactivity_idUnique identifier from the form captured event
started_attsTimestamp (in UTC) that the form was started.
'segment'sourceSet to the literal string name of the user_identifier so that Narrator knows where the source user id comes from.
anonymous_idsource_idThe anonymous user id from the data source. This is segment's cookie id from the track event. This field will be used to process user mapping.
emailcustomerThis is the email associated with the lead when the person becomes identified.
'ip_mapping'activityName of the activity. We use the literal string to define the name.
NULLfeature_1We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context for a user mapping activity.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Segment: Adding Track events

Source: Website

With Segment, it is common to set up unique track events that are fired when a user completes an action. Use the script below to add those events to your activity stream.

SQL Template

SELECT
	 t.message_id  AS "activity_id"
	 , t.timestamp  AS "ts"
	 , 'segment'  AS "source"
	 , t.anonymous_id  AS "source_id"
	 , t.email AS "customer"
	 , 'event_name'  AS "activity" -- replace with the name of the event you want to include
	 , v.event_feature_1  AS "feature_1"
	 , v.event_feature_2  AS "feature_2"
	 , v.event_feature_3  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , t.context_page_url::varchar ( 250 )  AS "link" -- link to where the activity occurred
FROM segment.tracks AS t
JOIN segment.event_name AS v
	ON ( t.message_id = v.message_id )
WHERE t.event ilike 'event_name' -- replace with he name of the event you want to include

Column Mapping for Segment Track Events

Source TableActivity StreamNotes
message_idactivity_idUnique identifier of this event in Segment.
message_idtsTimestamp (in UTC) that the track event occurred.
'segment'sourceSet to the literal string name of the user_identifier so that Narrator knows where the source user id comes from.
anonymous_idsource_idThis is the segment cookie set for every browser (from the segment.track). This field will be used to process user mapping.
emailcustomerInclude the identified user email when available. This will be used for user mapping.
'event_name'activityReplace this with the string name of the event you want to include in the activity stream.
eventfeature1feature_1A feature column associated with this event type.
eventfeature2feature_2A feature column associated with this event type.
eventfeature3feature_3A feature column associated with this event type.
NULLrevenue_impactLeave this blank if it is not a revenue generating activity
contextpageurllinkThe link associated with the page this event occurred on. By including the full link, Narrator users will be able to click and visit this page from the activity stream views.

Database: Historical Email Tracking

Source: Internal Database / DB

Sometimes a given customer will update their email address but the product tracking doesn't track and migrate the historical email changes. For this reason, Narrator can keep a local cache in the activity stream and you can use this cache to migrate old emails in the activity stream with an "Aliasing Script".

The first step is to ensure you have a historical record of the emails added. This script will help you set that up.

IMPORTANT: This script should use an immutable processing type in order to preserve a historical record of all identity changes (email changes).

SQL Template

SELECT
	 u.id  AS "activity_id"
	 , u.created_at  AS "ts"
	 , NULL  AS "source"
	 , NULL  AS "source_id"
	 , u.email  AS "customer"
	 , 'user_created'  AS "activity"
	 , NULL ::VARCHAR(255)  AS "feature_1"
	 , NULL ::VARCHAR(255)  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.users AS u

Column Mapping for Historical Email Tracking

Source TableActivity StreamNotes
idactivity_idUse the id of the user. We do this so it can be used for a self-join if you choose to migrate all historical emails to a new email with an 'aliasing script' later on.
created_attsTimestamp (in UTC) that the user was created.
NULLsourceLeave as NULL because this script will not be used for user mapping. (See database aliasing activity to understand how user mapping can be applied once this activity is created.)
NULLsource_idLeave as NULL because this script is not used for user mapping.
emailcustomerThis is the email associated with the user when they are created.
'user_created'activityName of the activity. We use the literal string to define the name.
NULLfeature_1We chose to leave this feature blank because we didn't have an obvious need to include more context for a user created activity.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context for a user created activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context for a user created activity.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Database: Migrating Historical Emails Using an Email Alias Script

Source: Internal Database / DB

Once you've created a historical reference of when each user id (usually email address) was created, you can use an aliasing script to apply user mapping and replace all historical instances of the old email address with the new email address.

This will ensure that all user activities can be referenced using a single (up-to-date) email identifier.

See Database: Historical Email Tracking to set up the historical reference.

This script uses a special activity called an "alias" activity. See here for more information on how this works.

This script migrates all user emails to the most up to date email by self joining to the activity stream and checking if the same userid now has a different email. Aliasing activity scripts use user mapping to map the existing `sourceidtocustomer` in all activities.

SQL Template

SELECT
	 u.id  AS "activity_id" -- customer id
	 , u.updated_at  AS "ts" -- date the email was updated (script will only be applied for users when their emails are newly updated)
	 , 'customer'  AS "source" -- indicates special aliasing activity
	 , s.customer  AS "source_id" -- old customer identifier
	 , u.email  AS "customer" -- new customer identifier
	 , 'email_updated'  AS "activity"
	 , NULL ::VARCHAR(255)  AS "feature_1"
	 , NULL ::VARCHAR(255)  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.app_user AS u
JOIN dw.mv_activity_stream AS s -- get historical reference of all email updates
	ON ( s.activity = 'user_created' and s.activity_id = u.id )
WHERE lower ( u.email ) <> s.customer -- only apply where email changes

Column Mapping for Migrating Historical Emails with Aliasing

Source TableActivity StreamNotes
idactivity_idUse the id of the user. We do this so it can be used to identify all the historical emails of a single user.
updated_attsTimestamp (in UTC) that the user was updated. This script will be applied each time a user id is updated.
'customer'source'customer' indicates this is an aliasing activity and all 'source_id' fields should be replaced with the values in the 'customer' field
customersource_idAll historical customer identifiers.
emailcustomerThis is the new email address that the old activities should be migrated to.
'email_updated'activityName of the activity. We use the literal string to define the name.
NULLfeature_1We chose to leave this feature blank because we didn't have an obvious need to include more context for an email updated activity.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context for an email updated activity.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context for an email updated activity.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Mailchimp: Adding email activity

Source: Mailchimp

Use this script to add email activity from Mailchimp. This is useful when your company needs to understand how emails affect the customer.

This only tracks opens, clicks, bounces. We chose not to track email sends because it is usually not indicative of customer behavior. You can adapt this script to include email sends if you choose.

SQL Template

SELECT
	 a.automation_email_id  AS "activity_id"
	 , a.timestamp  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , m.email_address  AS "customer"
	 , 'email_'|| a.action  AS "activity" --dynamic activity name
	 , ae.title  AS "feature_1" --campaign title
	 , ae.subject_line  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , ae.archive_url  AS "link" --link to preview the email
FROM mailchimp.automation_recipient_activity AS a
JOIN mailchimp.member AS m
	ON ( m.id= a.member_id )
JOIN mailchimp.automation_email AS ae
	ON ( ae.id = a.automation_email_id )

Column Mapping for Mailchimp Emails

Source TableActivity StreamNotes
automation_email_idactivity_idUnique identifier of this email activities.
timestamptsTimestamp (in UTC) that the email activity occurred.
NULLsourceLeave blank. User is identified for email activities so we don't need user mapping.
NULLsource_idLeave blank. User is identified for email activities so we don't need user mapping.
email_addresscustomerEmail address of the customer associated with the activity.
'email_' + actionactivityWe create a dynamic name of the activity to define multiple email activities at once. It's helpful to differentiate emailclick from emailopen, etc.
titlefeature_1Title of the email campaign.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity
archive_urllinkURL that links to the the email associated with this activity. In the Narrator UI, a user will be able to click and see the email that was sent using this link.

ActiveCampaign: Adding Email Activity

Source: ActiveCampaign

Use this script to add email activity from ActiveCampaign. This is useful when your company needs to understand how emails affect the customer.

You can set up ActiveCampagin data collection with a webhook and track events.

SQL Template

SELECT
	 e._id  AS "activity_id"
	 , e._created  AS "ts"
	 , NULL AS "source"
	 , NULL AS "source_id"
	 , e.contact_email_ AS "customer"
	 , 'email_' || e.type AS activity
	 ,  e.campaign_name_  AS "feature_1"
	 , e.campaign_message_subject_  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , e.type in ( 'open', 'click' ) then 'https://yourcompany.activehosted.com/report/#/campaign/'|| e.campaign_message_id_ || '/message' ::varchar ( 255 )  AS "link"
FROM activecampaign.activities AS e
WHERE e.contact_email_ is not null
	AND e.type in ( 'open', 'click') -- only tracking open and click activities

Column Mapping for ActiveCampagin Emails

Source TableActivity StreamNotes
_idactivity_idUnique identifier of this email activities.
_created_attsTimestamp (in UTC) that the email activity occurred.
NULLsourceLeave blank. User is identified for email activities so we don't need user mapping.
NULLsource_idLeave blank. User is identified for email activities so we don't need user mapping.
contact_emailcustomerEmail address of the customer associated with the email.
'email_' + typeactivityWe create a dynamic name of the activity to define multiple email activities at once. It's helpful to differentiate emailclick from emailopen, etc.
campaign_name_feature_1Name of the email campaign.
campaign_message_subject_feature_2Subject of the email.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity
type, campaign_message_id_linkUse a combination of email types and campaign message id to create the link to the email preview.

ActiveCampaign: Adding Tag Activity (A/B test tracking)

Source: ActiveCampaign

If your emails from ActiveCampaign include a tags, you can track those as separate activities in the activity stream. This can come in handy for A/B testing email campaigns to understand which version of the email your customer received.

SQL Template

SELECT
	 e._id  AS "activity_id"
	 , e._created  AS "ts"
	 , NULL AS "source"
	 , NULL AS "source_id"
	 , e.contact_email_ AS "customer"
	 , 'tag_added' AS activity
	 ,  e."tag"  AS "feature_1"
	 , e.contact_tags_  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , 'https://yourcompany.activehosted.com/app/contacts/' || e.contact_id_ ::varchar ( 255 )  AS "link"
FROM activecampaign.activities AS e
WHERE e.contact_email_ is not null
	AND e.type in ( 'contact_tag_added')

Column Mapping for ActiveCampaign Tags

Source TableActivity StreamNotes
_idactivity_idUnique identifier of this email activities.
_created_attsTimestamp (in UTC) that the email activity occurred.
NULLsourceLeave blank. User is identified for email activities so we don't need user mapping.
NULLsource_idLeave blank. User is identified for email activities so we don't need user mapping.
contact_email_customerEmail address of the customer associated with the email.
'tag_added'activityName of the activity. We use the literal string to define this field.
tagfeature_1Associated tag
contact_tags_feature_2Contact tags.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity
contactidlinkLink to the contact who viewed the email.

ActiveCampaign: Adding SMS Activity

Source: ActiveCampaign

Use this to track SMS activities from ActiveCampaign.

You can set up ActiveCampagin data collection with a webhook and track events.

These activities use phone number as the source_id but map to the universal customer identifier (email).

SQL Template

SELECT
	 e._id  AS "activity_id"
	 , e._created  AS "ts"
	 , 'phone_number' AS "source"
	 , nullif ( regexp_replace ( e.contact_phone_, '([^0-9.])' ) , '' )   AS "source_id"
	 , e.contact_email_ AS "customer"
	 , e.type AS activity
	 ,  e.campaign_name_  AS "feature_1"
	 , e.sms_successful_  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , 'https://yourcompany.activehosted.com/app/contacts/' || e.contact_id_ ::varchar ( 255 )  AS "link"
FROM activecampaign.activities AS e
WHERE e.contact_email_ is not null
	AND e.type in ( 'sms_sent', 'sms_reply')

Column Mapping for ActiveCampaign SMS Activity

Source TableActivity StreamNotes
_idactivity_idUnique identifier of this SMS activities.
_created_attsTimestamp (in UTC) that the activity occurred.
'phone_number'sourcePhone number is used by the source system to identify the user.
contact_phone_source_idThe phone number of the customer.
contact_email_customerEmail address of the customer associated with the SMS.
typeactivityUse the type of SMS to define a dynamic activity name. This will create two types of activities ('smssent', 'smsreply').
campaign_name_feature_1Name of the SMS campaign.
sms_successful_feature_2Outcome of the SMS.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity
contactidlinkLink to the contact who received the SMS.

Shopify: Adding Item Purchase Activities

Source: Shopify

This script defines a purchase activity for an individual item. This will allow your team to understand purchase behavior down to the item level.

SQL Template for Shopify Item Purchases

SELECT
	 l.id  AS "activity_id"
	 , o.processed_at  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , o.email  AS "customer"
	 , 'purchased_item'  AS "activity"
	 , p.title  AS "feature_1"
	 , l.vendor  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , l.price  AS "revenue_impact"
	 , 'https://your_company_here.shopify.com/admin/orders/' || o.id  AS "link"
FROM shopify.order_line AS l
JOIN shopify.product AS p
	ON ( p.id = l.product_id )
JOIN shopify.order AS o
	ON ( o.id = l.order_id )

Column Mapping for Purchase Activities

Source TableActivity StreamNotes
idactivity_idUnique identifier of this item purchase.
processed_attsTimestamp (in UTC) that the purchase occurred.
NULLsourceLeave blank because user is identified by email in the customer field. No user mapping necessary.
NULLsource_idLeave blank because user is identified by email in the customer field. No user mapping necessary.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'purchased_item'activityName of the activity. We use the literal string to define the name.
titlefeature_1We chose to use product title since it is consistent and searchable.
vendorfeature_2Include item vendor.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
pricerevenue_impactSet to the revenue associated from the purchase of the item (in USD).
idlinkUse the shopify order id to create a link to the order so that a Narrator user can click into the order if they want to understand more.

Shopify: Adding completed orders

Source: Shopify

Used to track the completion of an order from Shopify.

SQL Template

SELECT
	 o.id  AS "activity_id"
	 , o.processed_at  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , o.email  AS "customer"
	 , 'completed_order'  AS "activity"
	 , o.shipping_address_city  AS "feature_1"
	 , o.shipping_address_province  AS "feature_2"
	 , l.total_items ::VARCHAR ( 255 )  AS "feature_3"
	 , o.total_price  AS "revenue_impact"
	 , 'https://your_company_here.shopify.com/admin/orders/' || o.id  AS "link"
FROM shopify.order AS o
JOIN (
	SELECT
		 o.order_id
		 , count ( * )  AS "total_items"
	FROM shopify.order_line
	GROUP BY 1
) AS l
	ON ( l.order_id = o.id )

Column Mapping for Shopify Completed Orders

Source TableActivity StreamNotes
idactivity_idUnique identifier of this order.
processed_attsTimestamp (in UTC) that the order occurred.
NULLsourceLeave blank because user is identified by email in the customer field. No user mapping necessary.
NULLsource_idLeave blank because user is identified by email in the customer field. No user mapping necessary.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'completed_order'activityName of the activity. We use the literal string to define the name.
shippingaddresscityfeature_1City of shipping address.
shippingaddressprovincefeature_2Province of shipping address.
total_itemsfeature_3Total items associated with the order.
total_pricerevenue_impactSet to the revenue associated from the purchase of the order (in USD).
idlinkUse the shopify order id to create a link to the order so that a Narrator user can click into the order if they want to understand more.

Stripe: Adding charges

Source: Stripe

Adding the charges from Stripe is very simple because the data is already modeled similar to the activity stream format.

SQL Template

SELECT
	 c.id  AS "activity_id"
	 , c.created  AS "ts"
	 , c.source_object  AS "source"
	 , c.source_id  AS "source_id"
	 , c.metadata_email  AS "customer"
	 , 'stripe_charged'  AS "activity"
	 , c.description  AS "feature_1"
	 , c.shipping_address_city  AS "feature_2"
	 , c.shipping_address_state ::VARCHAR ( 255 )  AS "feature_3"
	 , c.amount  AS "revenue_impact"
	 , 'https://your_company_here.stripe.com/charges/' || c.id  AS "link"
FROM stripe.charges AS c
WHERE c.status = 'succeeded'

Column Mapping for Shopify Charges

Source TableActivity StreamNotes
idactivity_idUnique identifier of this charge.
createdtsTimestamp (in UTC) that the charge occurred.
source_objectsourceName of the customer identifier used by Shopify.
source_idsource_idValue of the customer identifier used by Shopify.
metadata_emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'stripe_charged'activityName of the activity. We use the literal string to define the name.
descriptionfeature_1Description of the charge.
shippingaddresscityfeature_2City of shipping address.
shippingaddressstatefeature_3State of shipping address.
amountrevenue_impactSet to the revenue associated from the charge (in USD).
idlinkUse the Shopify id to create a link to the order so that a Narrator user can click into the order if they want to understand more.

Stripe: Adding refunds

Source: Stripe

This script can be used to record refunds from Stripe in the activity stream.

SQL Template

SELECT
	 c.id  AS "activity_id"
	 , c.created  AS "ts"
	 , c.source_object  AS "source"
	 , c.source_id  AS "source_id"
	 , c.metadata_email  AS "customer"
	 , 'stripe_charged'  AS "activity"
	 , c.description  AS "feature_1"
	 , c.shipping_address_city  AS "feature_2"
	 , c.shipping_address_state ::VARCHAR ( 255 )  AS "feature_3"
	 , c.amount  AS "revenue_impact" -- revenue amount of the refund
	 , 'https://your_company_here.stripe.com/charges/' || c.id  AS "link"

-- get all stripe charges that are also associated with a refund
FROM stripe.charges AS c
JOIN stripe.refunds r
	on ( r.charge_id = c.id )
WHERE c.status = 'succeeded'

Column Mapping for Stripe Refunds

Source TableActivity StreamNotes
idactivity_idUnique identifier of this charge.
createdtsTimestamp (in UTC) that the charge occurred.
source_objectsourceName of the customer identifier used by Shopify.
source_idsource_idValue of the customer identifier used by Shopify.
metadata_emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'stripe_charged'activityName of the activity. We use the literal string to define the name.
descriptionfeature_1Description of the charge.
shippingaddresscityfeature_2City of shipping address.
shippingaddressstatefeature_3State of shipping address.
amountrevenue_impactRevenue associated with the refund (in USD).
idlinkUse the Shopify id to create a link to the order so that a Narrator user can click into the order if they want to understand more.

Affirm: Adding charges

Source: Affirm

This script creates activities for each charge from Affirm.

SQL Template

SELECT
	 c.id  AS "activity_id"
	 , c.created  AS "ts"
	 , 'affirm'  AS "source"
	 , c.user_id  AS "source_id"
	 , c.billing_email  AS "customer"
	 , 'affirm_charge'  AS "activity"
	 , c.financing_program  AS "feature_1"
	 , c.discount_code  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , c.amount/100  AS "revenue_impact"
	 , 'https://www.affirm.com/dashboard/#/details/' || c.id  AS "link"
FROM affirm.charges AS c

Column Mapping for Affirm Charges

Source TableActivity StreamNotes
idactivity_idUnique identifier of this charge.
createdtsTimestamp (in UTC) that the charge occurred.
'affirm'sourceName the source of the customer identifier.
user_idsource_idAffirm's customer identifier.
billing_emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'affirm_charge'activityName of the activity. We use the literal string to define the name.
financing_programfeature_1Column for Affirm's financing program.
discount_codefeature_2Discount code used with the charge.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
amount/100revenue_impactSet to the revenue associated from the charge (in USD). Divide by 100 because amount is recorded in cents.
idlinkUse the Affirm id to create a link to the order so that a Narrator user can click into the order if they want to understand more.

Zendesk: Adding ticket created, closed, or solved

Source: Zendesk

For Zendesk, we can use the field history to track the status of the ticket. Every time the field history is updated, a new record will be added in the activity stream.

SQL Template

SELECT
	 t.id  AS "activity_id"
	 , fh.updated  AS "ts"
	 , 'zendesk'  AS "source"
	 , t.submitter_id::varchar ( 255 )  AS "source_id"
	 , u.email  AS "customer"
	 , 'ticket_' || fh.value  AS "activity"
	 , t.type  AS "feature_1"
	 , t.subject  AS "feature_2"
	 , a.email  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , 'https://narrator.zendesk.com/agent/tickets/' || t.id  AS "link"
FROM zendesk.ticket_field_history AS fh
JOIN zendesk.ticket AS t
	ON ( fh.ticket_id = t.id )
JOIN zendesk.user AS u
	ON ( u.id = t.submitter_id )
LEFT JOIN zendesk.user AS a
	ON ( a.id = t.assignee_id )
WHERE fh.field_name = 'status'
	AND fh.value IN ('new', 'solved', 'closed')

Column Mapping for Zendesk Tickets

Source TableActivity StreamNotes
idactivity_idUnique identifier of for the ticket.
updatedtsTimestamp (in UTC) that the ticket update occurred.
'zendesk'sourceSet to the literal string name of the user_identifier so that Narrator knows where the source user id comes from.
submitter_idsource_idThe Zendesk ID associated with the person who submitted the ticket. This field will be used to process user mapping.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'ticket_' + valueactivityName of the activity. We use a dynamic string to define the name. Names will take the form ticketnew, ticketsolved, or ticket_closed.
typefeature_1Ticket Type
subjectfeature_2Ticket Subject
emailfeature_3Email associated with the ticket assignee.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
idlinkUse the ticket id to create a link so that a Narrator user can click to see the full ticket information.

Internal: Adding New Customer Subscriptions

Source: Internal

For subscription based businesses, it is helpful to understand when a customer first became a subscriber and track them as a "new subscription" at that date. To identify new subscriptions, we self join to the subscription history to see if this is the first subscription that occurred for that user.

SQL Template

SELECT
	 ss.id  AS "activity_id"
	 , ss.created_at  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , ss.email  AS "customer"
	 , 'new_subscription'  AS "activity"
	 , ss.name  AS "feature_1"
	 , ss.desks ::VARCHAR ( 255 )  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , ss.amount/100.0::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.subscriptions AS ss
INNER JOIN (
	SELECT
		 ss.email
		 , min ( id )  AS "min_id"
	FROM postgres_public.subscriptions AS ss
	GROUP BY 1
) AS sm
	ON ( sm.min_id = ss.id )

Column Mapping for New Customer Subscriptions

Source TableActivity StreamNotes
idactivity_idUnique identifier of the subscription.
created_attsTimestamp (in UTC) that the subscription occurred.
NULLsourceNot needed because users are identified via email when they are creating a subscription. User mapping not required.
NULLsource_idNot needed because users are identified via email when they are creating a subscription. User mapping not required.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'new_subscription'activityName of the activity. We use the literal string to define the name.
namefeature_1Name of the product subscribed to.
desksfeature_2Number of desks associated with the subscription.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
amountrevenue_impactRevenue associated with the subscription. Divided by 100 to convert to USD.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Internal: Adding Subscription Started

Source: Internal

For subscription based businesses, it is helpful to understand when a customer started each subscription (for every subscription period). This script creates those activities.

SQL Template

SELECT
	 ss.id  AS "activity_id"
	 , ss.started_at AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , ss.email  AS "customer"
	 , 'started_subscription'  AS "activity"
	 , ss.name  AS "feature_1"
	 , ss.desks ::VARCHAR ( 255 )  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , ss.amount/100.0::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.subscriptions AS ss

Column Mapping for Subscription Started

Source TableActivity StreamNotes
idactivity_idUnique identifier of the subscription.
started_attsTimestamp (in UTC) that the subscription started.
NULLsourceNot needed because users are identified via email when they are creating a subscription. User mapping not required.
NULLsource_idNot needed because users are identified via email when they are creating a subscription. User mapping not required.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'started_subscription'activityName of the activity. We use the literal string to define the name.
namefeature_1Name of the product subscribed to.
desksfeature_2Number of desks associated with the subscription.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
amountrevenue_impactRevenue associated with the subscription. Divided by 100 to convert to USD.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Internal: Adding subscription ended

Source: Internal

For subscription based businesses, it can be helpful to understand when each subscription ended for each customer. This script looks for subscription records that have an existing end date to create an 'ended_subscription' activity.

SQL Template

SELECT
	 ss.id  AS "activity_id"
	 , ss.ended_at AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , ss.email  AS "customer"
	 , 'ended_subscription'  AS "activity"
	 , ss.name  AS "feature_1"
	 , ss.desks ::VARCHAR ( 255 )  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , ss.amount/100.0::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.subscriptions AS ss
-- non null ended_at indicates the subscription will end / has ended
where ss.ended_at IS NOT NULL

Column Mapping for Subscription Ended

Source TableActivity StreamNotes
idactivity_idUnique identifier of the subscription.
ended_attsTimestamp (in UTC) that the subscription ended (or is scheduled to end).
NULLsourceNot needed because users are identified via email when they have a subscription. User mapping not required.
NULLsource_idNot needed because users are identified via email when they have a subscription. User mapping not required.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'ended_subscription'activityName of the activity. We use the literal string to define the name.
namefeature_1Name of the product subscribed to.
desksfeature_2Number of desks associated with the subscription.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
amountrevenue_impactRevenue associated with the subscription. Divided by 100 to convert to USD.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Internal: Adding Subscription Upgraded/Downgraded

Source: Internal

This script adds an activity when a subscription is upgraded or downgraded.

SQL Template

SELECT
	 ss.id  AS "activity_id"
	 , ss.created_at  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , ss.email  AS "customer"
	 , CASE
		WHEN ss.kind = 'plan_1' -- old subscription is lower tier plan
			and ns.kind= 'plan_2' -- new subscription is upper tier plan
		THEN 'upgraded_subscription'
		ELSE 'downgraded_subscription'
	 END  AS "activity"
	 , ss.name  AS "feature_1"
	 , ss.desks ::VARCHAR ( 255 )  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , ss.amount/100.0::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.subscriptions AS ss -- old subscription
JOIN (
	SELECT
		 *
		 , LAG ( ss.id ) over ( partition by ss.email order by ss.created_at )  AS "last_id"
	FROM postgres_public.subscriptions AS ss
) AS ns -- new subscription
	ON ( ss.id = nl.last_id )

Internal: Adding Subscription Churned

Source: Internal

This script creates an activity when a customer churns out of their subscription.

This query looks for the subscription that a customer ended on and joining back to the subscription details to create a 'churned_customer' activity.

SQL Template

SELECT
	 ss.id  AS "activity_id"
	 , ss.updated_at  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , ss.email  AS "customer"
	 , 'churned_customer'  AS "activity"
	 , ss.name  AS "feature_1"
	 , ss.desks ::VARCHAR ( 255 )  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , ss.amount/100.0::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM postgres_public.subscriptions AS ss
JOIN (
	-- find subscription that the customer churned on
	SELECT
		 ss.email
		 , max ( id )  AS "max_id"
	FROM postgres_public.subscriptions AS ss
	GROUP BY 1
) AS sm
	ON ( sm.max_id = ss.id )
WHERE ss.ended_at is not NULL

Column Mapping for Subscription Churned

Source TableActivity StreamNotes
idactivity_idUnique identifier of the subscription.
updated_attsTimestamp (in UTC) that the subscription was updated (and churned).
NULLsourceNot needed because users are identified via email when they have a subscription. User mapping not required.
NULLsource_idNot needed because users are identified via email when they have a subscription. User mapping not required.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'churned_customer'activityName of the activity. We use the literal string to define the name.
namefeature_1Name of the product subscribed to.
desksfeature_2Number of desks associated with the subscription.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
amountrevenue_impactRevenue associated with the subscription. Divided by 100 to convert to USD.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Twilio: Adding SMS Messages

Source: Twilio

Twilio allows you to connect a webhook to the events so we use that data to create the activities.

SQL Template

SELECT
	 s._id  AS "activity_id"
	 , s._created  AS "ts"
	 , 'phone_number'  AS "source"
	 , nullif ( regexp_replace ( right ( u.phone, 16 ) , '([^0-9.])', '' ) , '' )  AS "source_id"
	 , u.email  AS "customer"
	 , 'sent_text'  AS "activity"
	 , s.kind  AS "feature_1"
	 , s.message::varchar ( 255 )  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM twilio.sms AS s
LEFT JOIN api_db_public.users AS u
	ON ( u.phone = s.to_number )

Column Mapping for Twilio SMS Activity

Source TableActivity StreamNotes
_idactivity_idUnique identifier of this SMS activities.
_createdtsTimestamp (in UTC) that the activity occurred.
'phone_number'sourcePhone number is used by the source system to identify the user.
contact_phone_source_idThe phone number of the customer.
emailcustomerEmail address of the customer associated with the SMS.
'send_text'activityName of the activity.
kindfeature_1Kind of message sent.
messagefeature_2Text of message sent.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity
NULLlinkNo relevant link to add.

Mandrill: Adding Email Opens

Source: Mandrill

Mandrill allows you to connect a webhook to the events so we use that data to create the activities for email open.

SQL Template

SELECT
	 a._fivetran_id  AS "activity_id"
	 , a._fivetran_synced  AS "ts"
	 , NULL ::VARCHAR(255)  AS "source"
	 , NULL ::VARCHAR(255)  AS "source_id"
	 , a.email_address  AS "customer"
	 , 'email_opened'  AS "activity"
	 , t."tag"  AS "feature_1"
	 , a.subject  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM mandrill.activity AS a
LEFT JOIN mandrill.activity_tag AS t
	ON ( t._fivetran_id = a._fivetran_id )
WHERE a.opens > 0

Column Mapping for Email Open

Source TableActivity StreamNotes
_fivetran_idactivity_idEmail open event ID from the webhook (set up via Fivetran).
_fivetran_syncedtsTimestamp (in UTC) that the activity occurred.
NULLsourceLeave blank. The customer is identified by email (our universal identifier) for email open activities so user mapping is not needed.
NULLsource_idLeave blank. The customer is identified by email (our universal identifier) for email open activities so user mapping is not needed.
email_addresscustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'email_opened'activityName of the activity. We use the literal string to define the name.
tagfeature_1Tag associated with the email.
subjectfeature_2Subject of the email.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Postmark: Adding Email Delivered

Source: Postmark

Postmark allows you to connect a webhook to the events so we use that data to create the activities for email delivered.

SQL Template

SELECT
	 e.message_id  AS "activity_id"
	 , e.delivered_at  AS "ts"
	 , NULL  AS "source"
	 , NULL::VARCHAR(255)  AS "source_id"
	 , e.recipient AS "customer"
	 , 'email_delivered'  AS "activity"
	 , e."tag" ::varchar ( 255 )  AS "feature_1"
	 , e.subject  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , 'https://account.postmarkapp.com/servers/' ||e.server_id || '/messages/' || e.message_id AS "link"
FROM postmark.events AS e
WHERE e.record_type = 'Delivery'

Column Mapping for Email Delivered

Source TableActivity StreamNotes
message_idactivity_idEmail delivered event ID.
delivered_attsTimestamp (in UTC) that the activity occurred.
NULLsourceLeave blank. The customer is identified by email (our universal identifier) for email activities so user mapping is not needed.
NULLsource_idLeave blank. The customer is identified by email (our universal identifier) for email activities so user mapping is not needed.
recipientcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'email_delivered'activityName of the activity. We use the literal string to define the name.
tagfeature_1Tag associated with the email.
subjectfeature_2Subject of the email.
NULLfeature_3We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
serverid, messageidlinkUse serverid and messageid to create a link to the email so that a Narrator user can click directly to the email that was sent.

Postmark: Adding Email Opened or Clicked

Source: Postmark

Postmark allows you to connect a webhook to the events so we use that data to create the activities for email opened and clicked.

SQL Template

SELECT
	 e.message_id  AS "activity_id"
	 , e.received_at  AS "ts"
	 , NULL  AS "source"
	 , NULL::VARCHAR(255)  AS "source_id"
	 , e.recipient AS "customer"
	 , 'email_' || lower(e.record_type)  AS "activity"
	 , e."tag" ::varchar ( 255 )  AS "feature_1"
	 , e.subject  AS "feature_2"
	 , e.platform ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , 'https://account.postmarkapp.com/servers/' ||e.server_id || '/messages/' || e.message_id AS "link"
FROM postmark.events AS e
WHERE e.record_type in ('Click', 'Open')

Column Mapping for Email Click or Open

Source TableActivity StreamNotes
message_idactivity_idEmail event ID.
received_attsTimestamp (in UTC) that the activity occurred.
NULLsourceLeave blank. The customer is identified by email (our universal identifier) for email activities so user mapping is not needed.
NULLsource_idLeave blank. The customer is identified by email (our universal identifier) for email activities so user mapping is not needed.
recipientcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'email' + recordtypeactivityDynamic name of the email activity. The name depends on the record type and will take the form 'emailclick' or 'emailopen'.
tagfeature_1Tag associated with the email.
subjectfeature_2Subject of the email.
platformfeature_3Platform where the activity occurred.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
serverid, messageidlinkUse serverid and messageid to create a link to the email so that a Narrator user can click directly to the email that was clicked or opened.

Postmark: Adding Email Bounced

Source: Postmark

Postmark allows you to connect a webhook to the events so we use that data to create the activities for email bounce.

SQL Template

SELECT
	 e.message_id  AS "activity_id"
	 , e.bounced_at  AS "ts"
	 , NULL  AS "source"
	 , NULL::VARCHAR(255)  AS "source_id"
	 , e.email AS "customer"
	 , 'email_bounced'  AS "activity"
	 , e."tag" ::varchar ( 255 )  AS "feature_1"
	 , e.subject  AS "feature_2"
	 , e.type ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , 'https://account.postmarkapp.com/servers/' ||e.server_id || '/messages/' || e.message_id AS "link"
FROM postmark.events AS e
WHERE e.record_type = 'Bounce'

Column Mapping for Email Bounce

Source TableActivity StreamNotes
message_idactivity_idEmail event ID.
bounced_attsTimestamp (in UTC) that the activity occurred.
NULLsourceLeave blank. The customer is identified by email (our universal identifier) for email activities so user mapping is not needed.
NULLsource_idLeave blank. The customer is identified by email (our universal identifier) for email activities so user mapping is not needed.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'email_bounced'activityName of the activity. We use the literal string to define the activity name.
tagfeature_1Tag associated with the email.
subjectfeature_2Subject of the email.
typefeature_3Email Type.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
serverid, messageidlinkUse serverid and messageid to create a link to the email so that a Narrator user can click directly to the email that bounced.

Salesforce: Adding Opportunity Activity

Source: Salesforce

Use this script to create an activity whenever a new salesforce opportunity is created.

This example is based off of a trial based offering and an account based activity stream. If you want to define an activity for a user based activity stream we recommend using email as the "customer".

SQL Template

SELECT
	 o.id  AS "activity_id"
	 , o.trial_start_date_c  AS "ts"
	 , 'account'  AS "source"
	 , a.id  AS "source_id"
	 , a.name  AS "customer"
	 , 'trial_started'  AS "activity"
	 , NULL ::VARCHAR(255)  AS "feature_1"
	 , NULL ::VARCHAR(255)  AS "feature_2"
	 , u.email  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , NULL ::VARCHAR(255)  AS "link"
FROM salesforce.opportunity AS o
JOIN salesforce.account AS a
	ON ( a.id = o.account_id )
JOIN salesforce.user AS u
	ON ( u.id = o.owner_id )
WHERE o.type = 'New Business'

Column Mapping for Salesforce Opportunity

Source TableActivity StreamNotes
idactivity_idUnique identifier of the opportunity.
trialstartdate_ctsTimestamp (in UTC) that the trial was started.
'account'sourceSet to the literal string name of the customer_identifier so that Narrator knows where the source user id comes from.
idsource_idThe user_id from the Salesforce. This field will be used to process user mapping.
namecustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'trial_started'activityName of the activity. We use the literal string to define the name.
NULLfeature_1We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
emailfeature_3Email of the person who signed up for the trial.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
NULLlinkLeave this blank because there is not an associated URL for this type of activity.

Salesforce: Adding Lead Activity

Source: Salesforce

Use this script to add activities when a customer lead is created in Salesforce.

SQL Template

SELECT
	 l.id  AS "activity_id"
	 , l.created_date  AS "ts"
	 , CASE
		WHEN l.phone is not null then 'phone_number'
		ELSE null
	 END  AS "source"
	 , nullif ( regexp_replace ( l.phone , '([^0-9.])' ) , '' )  AS "source_id"
	 , l.email  AS "customer"
	 , 'lead_created'  AS "activity"
	 , l.company::varchar ( 250 )  AS "feature_1"
	 , NULL ::VARCHAR(255)  AS "feature_2"
	 , NULL ::VARCHAR(255)  AS "feature_3"
	 , NULL ::FLOAT  AS "revenue_impact"
	 , l.website  AS "link"
FROM salesforce.lead AS l

Column Mapping for Salesforce Lead Activity

Source TableActivity StreamNotes
idactivity_idUnique identifier of the opportunity.
created_attsTimestamp (in UTC) that the lead was created.
'phone_number'sourceName of the source_id field when it is available. Used for User Mapping.
phonesource_idPhone number of the customer. Used in User Mapping.
emailcustomerAdd your company's customer identifier here. Email is a good identifier since it is globally unique across all systems.
'lead_created'activityName of the activity. We use the literal string to define the name.
companyfeature_1Name of the company associated with this lead.
NULLfeature_2We chose to leave this feature blank because we didn't have an obvious need to include more context. You can add an additional feature here if you wish.
emailfeature_3Email of the person who signed up for the trial.
NULLrevenue_impactLeave this blank because this is not a revenue generating activity.
websitelinkWebsite of the company associated with the lead.