What is an activity schema?

An activity schema models all data in the warehouse as a single time series table.

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.

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.

Building your activity schema with Narrator

Each row is an action taken by your customer in time (customer can be a client, company, product, vehicle, etc…)

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 a standard format. For more information on the 10 columns that make up each activity check out the activity schema spec.

Example
Transformations

SELECT
  o.id              AS activity.id
  , o.created_at    AS ts
  , NULL            AS anonymous_customer_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
  , t.submitter_id     AS anonymous_customer_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://{ZENDESKURL}/' || 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 anonymous_customer_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
  , p.anonymous_id      AS anonymous_customer_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