Activity Schema
A standard data model to make reusable analyses possible
Single source of truth
You don’t have to search through many tables or spend hours figuring out why numbers don’t match. All your company data is in one data model making it easy to find and always accurate.
Simple traceability
No web of dependencies. All reporting and analyses tables depend on a single table in your warehouse (modeled using an Activity Schema).
Minimal maintenance
Changes to BI tables (adding new columns, updating logic, etc.) can be done in a few clicks without adding any complexity to the definition.
Reinvented joins
Bridging data across systems doesn’t require custom engineering. You can bridge data from disparate systems easily and without defining foreign keys.
Activity Schema
How it's built
The Activity Schema is a data model designed for column-oriented data warehouses
Step 1
Define customer actions (activities) using simple SQL transformations
Logic is simple and easy to understand (~25 lines of code)
Activities are the building blocks that are used to assemble tables
All company definitions are managed in one place
Each activity follows the standard Activity Schema format. For more information on the 11 columns that make up each activity check out our docs.
Order
Transformations
SELECT
o.id AS activity.id
, o.created_at AS ts
, NULL AS source
, NULL AS source_id
, o.email AS customer
, 'completed_order' AS activity
, d.code AS feature_1 -- discount code
, NULL AS feature_2
, NULL AS feature_3
, (o.total_price - o.total_discounts) AS revenue_impact
, NULL AS link
FROM shopify.order AS o
LEFT JOIN shopify.order_discount_code d
ON (d.order_id = o.id)
WHERE
o.cancelled_at is NULL
and o.email is not NULL
SELECT
t.id AS activity_id
, t.created_at AS ts
, 'zendesk' AS source
, t.submitter_id AS source_id
, u.email AS customer
, 'submitted_ticket' AS activity
, t.subject AS feature_1 -- subject
, t.id AS feature_2 -- ticket_id
, t.description AS feature_3 -- description
, NULL AS revenue_impact
, 'https://{COMPANYNAME}.zendesk.com/agent/tickets/' || t.id AS link
FROM zendesk.ticket AS t
JOIN zendesk.user AS u
ON (u.id = t.submitter_id)
WHERE t.subject <> 'SCRUBBED'
SELECT
a.id AS activity_id
, a.timestamp AS ts
, null AS source
, null AS source_id
, m.email_address AS customer
, 'opened_email' AS activity
, c.title AS feature_1 -- Campaign Name
, l.name AS feature_2 -- list name
, c.type AS feature_3 -- campaign type
, NULL AS revenue_impact
, c.archive_url AS link
FROM mailchimp.campaign_recipient_activity a
JOIN mailchimp.member m
ON (m.id = a.member_id and m.list_id = a.list_id)
JOIN mailchimp.list l
ON (l.id = a.list_id)
JOIN mailchimp.campaign c
ON (c.id = a.campaign_id)
WHERE a.action = 'open'
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.context_page_path AS feature_1
, p.referrer AS feature_2
, NULL AS feature_3
, NULL AS revenue_impact
, p.context_page_url AS link
FROM segment.pages p
New Activity created
Completed Order
Narrator inserts the activity data into your Customer Stream table
Step 2
Narrator builds a single table using the SQL transformations
What Narrator does:
- Maintains an Activity Schema table called the Customer Stream
- Updates it with the SQL definitions created by your data team
- Applies robust identity resolution
- Runs nightly reconciliation
- Inserts and updates according to the schedule you set
- Runs automated testing to notify if your data changes dramatically
| TS | Activity | Customers | Features… |
|---|---|---|---|
| 2020-05-01 | Completed Order | justina.cummings89@yahoo.com | |
| 2020-05-01 | Viewed Page | justina.cummings89@yahoo.com | |
| 2020-05-01 | Submitted Ticket | mekhi60@hotmail.com | |
| 2020-05-01 | Viewed Page | justina.cummings89@yahoo.com | |
| 2020-05-01 | Viewed Page | justina.cummings89@yahoo.com | |
| 2020-05-01 | Completed Order | justina.cummings89@yahoo.com | |
| 2020-05-01 | Opened Email | justina.cummings89@yahoo.com | |
| 2020-04-30 | Started Session | joshuah_prosacco11@gmail.com | |
| 2020-04-30 | Recieved Email | justina.cummings89@yahoo.com | |
| 2020-04-30 | Viewed Page | joshuah_prosacco11@gmail.com | |
| 2020-04-30 | Viewed Page | joshuah_prosacco11@gmail.com | |
| 2020-04-30 | Shipped Product | justina.cummings89@yahoo.com |
Activity Schema
How it's used
A table modeled using an Activity Schema can be reassembled to generate any table for BI, reporting, and analysis
Narrator’s core innovation is that every data table can be constructed using a combination of activities and relationships between those activities.
Activity
Relationship
Activity
Using the Narrator platform, quickly assemble any table to:
Answer any question
Materialize any table for reporting or BI
Build the dataset for your next analytics or data science project
Example
Email Attribution
Q: How many orders are we driving through our emails?
Learn more about how these components generate queries in our docs.
"Narrator provides value by consolidating and organizing our activity data in such a way that's meaningful and flexible so that we can quickly create a dataset to answer relevant business questions."
Dylan Levan - Senior Marketing Analyst
Creative Market
Creative Market