Daasity Data Model: Unified Notification Schema (UNS)

This article provides a detailed description of the Unified Notification Schema (UNS) within the Daasity Data Model. This article will help you understand the purpose of each table and column in this data model.

Overview

The Unified Order Schema (UNS) is a core data model within the Daasity transformation module that helps accelerate development of analytical capability by normalizing all notifications data: email, SMS, push notifications and app notifications. The normalized schema was designed to support the four types of targeted communication and enable cross-platform analytics when multiple platforms are used.

The schema is designed around the core concept of any communication having three key elements:

  1. Contact: who the communication is being sent to
  2. Campaign: what the communication is messaging
  3. Events: how the contact interacted with the communication

We add an additional core table [uns.unique_contacts] to enable house-holding across platforms so that we can understand contact behavior across platforms and create a true cross-channel personalized experience.

Entity Relationship Diagram (ERD)

The embedded diagram houses the ERD for the Unified Order Schema (UNS) integration illustrating the different tables and keys to join across tables.  Hover over the embedded diagram to reveal controls to zoom and scroll.

Unified Notification Schema Tables

TIP: Many of the tables contain the following fields which can be used to track the data flow from the source system to the integration schema within the database and then to UOS, our normalized order schema

  • __loaded_at: defines when the record was last loaded into this table
  • __synced_at: defines when the record was last replicated from the source system into the integration tables
  • __uns_integration_id: the ID of the integration within the Daasity platform
  • __uns_source: the general source of the data (Klaviyo, Attentive, Emarsys, Retention Science, etc.)

Automations

Purpose: Enables you to link campaigns to an automation for platforms that enable development of a series of triggered messages (automations, flows, journeys) and thus roll-up performance to an automation and compare one series against another.

Table Name: uns.automations

Column Description
AUTOMATION_ID Unique Daasity identifier for the automation - comprised of the source system and the automation id from the source system
CREATED_AT Date the automation was created
NAME Name of the automation
SOURCE_AUTOMATION_ID Identifier for the automation from the source system
STATUS Indicates the status of the automation (ex: live, draft, etc.)
UPDATED_AT Date the automation was last modified

Bounces

Purpose: Enables you to identify what messages bounced (i.e. were not accepted by the contact) and why the message bounced

Table Name: uns.bounces

Column Description
BOUNCE_ID Unique Daasity identifier for the bounce - comprised of the source system and the bounce id from the source system
DETAIL Detail information on the bounce
EVENT_AT Date the bounce occurred
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
SEND_ID Send ID from the sends table enabling a bounce to reference back to the send
TYPE The type of bounce - "Hard" or "Soft"

Campaigns

Purpose: Enables you to track high-level information related to the messages that are sent to contacts and build data models around campaign performance or messaged that might be structured as a series of messages

Table Name: uns.campaigns

Column Description
AUTOMATION_ID Automation ID from the automations table  linking a campaign to an automation
AUTOMATION_POSITION The position of the campaign in an automation
CAMPAIGN_ID Unique Daasity identifier for a campaign - comprised of the source system and the campaign id from the source system
CAMPAIGN_NAME The name of the campaign
CAMPAIGN_STATUS The status of a campaign (ex: sent, scheduled, draft, etc.)
CAMPAIGN_TYPE The type of campaign (ex: regular, A/B, etc.)
CREATED_AT Date the campaign was created
FROM_EMAIL The email address that the send shows as the "from"
FROM_NAME The name of the email address the send shows as the "from"
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
SEND_DATE Date the message was sent
SEND_TYPE Type of send (ex: automation, campaign, etc.)
SOURCE_CAMPAIGN_ID Identifier of the campaign from the source system
SUBJECT_LINE Subject Line for the campaign
UPDATED_AT Date the campaign was last modified

Clicks

Purpose: Enables you to identify what messages a contact engaged with by clicking on the content and which specific content (if multiple) the contact engaged with

Table Name: uns.clicks

Column Description
CLICK_ID Unique Daasity identifier for a click - comprised of the source system and the click id from the source system
EVENT_AT Date the click occurred
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
SEND_ID Send ID from the sends table enabling a click to reference back to the send
URL URL that was clicked
URL_NAME Friendly name for the URL

Contact Attributes

Purpose: Enables you to easily store additional information that might come from the source platform and is not in the standard list of data in the [uns.contacts] table.  Stored in a key value table to make it very easy to normalize and flatten in a downstream process

Table Name: uns.contact_attributes

Column Description
ATTRIBUTE_NAME Name of the contact attribute
ATTRIBUTE_VALUE Value of the contact attribute
CONTACT_ATTRIBUTE_ID Unique Daasity identifier for the contact attribute - comprised of the source system and the contact id and attribute name from the source system
CONTACT_ID Contact ID from the contact table linking the contact attribute to the contact
CREATED_AT Date the contact attribute was created
UPDATED_AT Date the contact attribute was last modified

Contact Lists

Purpose: Enables you to link a contact to a list and track the subscription status of the contact for that list.  A key design for platforms that allow you to have opt-in/opt-out at the list level.

Table Name: uns.contact_lists

Column Description
CONTACT_ID Contact ID from the contact table linking the contact attribute to the contact
CONTACT_LIST_ID Unique Daasity identifier for the contact list - comprised of the source system, contact id from the source system and list id from the source system
LIST_ID List ID from the lists table indicating which contact is part of which lists
SOURCE_CONTACT_ID Contact ID from the source system
SOURCE_LIST_ID List ID from the source system
STATUS Status - subscribed or unsubscribed
SUBSCRIBED_AT Date the contact was first subscribed
SUBSCRIBED_FLAG Flag indicating if the contact is subscribed to the list
UNSUBCRIBED_AT Date the contact was last unsubscribed

Contacts

Purpose: Enables you to keep basic information from each notification platform that can be used to household across platforms and create a unique contact

Table Name: uns.contacts

Column Description
CONTACT_ID Unique Daasity identifier of the contact - comprised of the source system and the contact id from the source system
CREATED_AT Date the contact was created
EMAIL Email address for the contact
FIRST_NAME First name of the contact
LAST_NAME Last name of the contact
PHONE_NUMBER Phone number of the contact
SOURCE_CONTACT_ID Contact ID from the source system
SUBSCRIBED_AT Date the contact was subscribed
SUBSCRIBED_FLAG Flag indicating if the contact is subscribed to the marketing platform
TIMEZONE Timezone of the contact
UNIQUE_CONTACT_ID ID from the unique contact table
UNS_SOURCE_RANK Rank of the source system for use in contact householding
UNSUBCRIBED_AT Date the contact was unsubscribed
UPDATED_AT Date the contact was last modified

Events

Purpose: Enables you to capture additional events not stored in other tables and link back to the contact for that event

Table Name: uns.events

Column Description
CONTACT_ID Contact ID from the contact table linking an event to a contact
EVENT_AT Date of the event
EVENT_ID Unique Daasity identifier for the event - comprised of the source system and the event id from the source system
METRIC_ID ID from the metric table indicating what type of event
NAME Name of the event

Impressions

Purpose: Enables you to build additional analytics around sign-up or information capture by tracking impression volume by day and campaign

Table Name: uns.impressions

Column Description
CAMPAIGN_ID ID from the campaign table indicating what campaign drove the impressions
IMPRESSION_DATE Date of the impressions
IMPRESSION_ID Unique Daasity identifier for the impressions - comprised of the source system, date of the impressions and campaign
IMPRESSION_VOLUME Number of impressions

Lists

Purpose: Enables you to capture all the different lists or segments in the platform

Table Name: uns.lists

Column Description
CREATED_AT Date the list was created
LIST_ID Unique Daasity identifier for the list - comprised of the source system and the list id from the source system
NAME Name of the list
RATING Rating for the list
SOURCE_LIST_ID ID of the list from the source system
STATUS Status for the list (ex: active, deleted, etc.)
TYPE Type of list (ex: list, segment)
UPDATED_AT Date the list was last modified

Metrics

Purpose: Enables you to track all the different metrics that are stored in the schema and determine where the data is stored

Table Name: uns.metrics

Column Description
DESTINATION For platforms that use an event feed, indicates the table where the metric is stored
METRIC_ID Unique Daasity identifier for the metric - comprised of the source system and the metric id from the source system
NAME Name of the metric
SOURCE How the metric data is added to the notification platform (ex: internal, API, etc.)
SOURCE_METRIC_ID ID of the metric from the source system

Opens

Purpose: Enables you to identify what messages a contact engaged with by opening the message.  For platforms (ex: SMS) where the concept of an open does not exist we create an open to make it easier to preserve a standard downstream analytics model

Table Name: uns.opens

Column Description
EMAIL_CLIENT_NAME Name of the email platform where the message was opened (ex: gmail, safari, etc.)
EMAIL_CLIENT_OS Operating system of the device where the message was opened
EMAIL_CLIENT_TYPE Type of application that was used to open the message (ex: browser, app, etc.)
EVENT_AT Date the message was opened
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
OPEN_ID Unique Daasity identifier for an open - comprised of the source system and the open id from the source system
SEND_ID Send ID from the sends table enabling an open to reference back to the send

Sends

Purpose: Enables you to store who received a message, what the message was and if the message was sent as part of a list

Table Name: uns.sends

Column Description
AUTOMATION_ID Automation ID from the automations table enabling the send to reference the automation that resulted in the send
CAMPAIGN_ID Campaign ID from the campaign table enabling the send to reference the campaign that resulted in the send
CONTACT_ID Contact ID from the contact table enabling a send to reference the contact that was sent the message
EVENT_AT Date of the send
LIST_ID List ID from the lists table enabling the send to reference the list for the send
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
SEND_ID Unique Daasity identifier for a send - comprised of the source system and the send id from the source system
SEND_TO The information where the message was send (ex: email address, phone number, app id, etc.)
SOURCE_SEND_ID ID of the send from the source system

Spams

Purpose: Enables you to identify what messages a  contact reported as spam

Table Name: uns.spams

Column Description
EVENT_AT Date of the spam
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
SEND_ID Send ID from the sends table enabling a spam to reference back to the send
SPAM_ID Unique Daasity identifier for a spam - comprised of the source system and the spam id from the source system

Unique Contacts

Purpose: Enables you to implement householding logic for contacts and indicate how contacts where householded and the different platforms where the householded contact exists

Table Name: uns.unique_contacts

Column Description
CREATED_AT_APP Date the householded contact was created  in the app notification platform
CREATED_AT_EMAIL Date the householded contact was created  in the email platform
CREATED_AT_PUSH Date the householded contact was created  in the push notification
CREATED_AT_SMS Date the householded contact was created  in the SMS platform
EMAIL Householded email address
FIRST_NAME Householded first name
LAST_NAME Householded last name
MATCH_TYPE What platforms were used to create the householded contact
PHONE Householded phone number
SUBSCRIBED_FLAG_APP Date the householded contact was first subscribed to app notifications
SUBSCRIBED_FLAG_EMAIL Date the householded contact was first subscribed to email
SUBSCRIBED_FLAG_PUSH Date the householded contact was first subscribed to push notifications
SUBSCRIBED_FLAG_SMS Date the householded contact was first subscribed to SMS notifications
UNIQUE_CONTACT_ID Unique identifier for the householded contact
UNSUBSCRIBED_AT_APP Date the householded contact was last unsubscribed to app notifications
UNSUBSCRIBED_AT_EMAIL Date the householded contact was last unsubscribed to email
UNSUBSCRIBED_AT_PUSH Date the householded contact was last unsubscribed to push notifications
UNSUBSCRIBED_AT_SMS Date the householded contact was last unsubscribed to SMS notifications
UPDATED_AT_APP Date the householded contact was last updated  in the app notification platform
UPDATED_AT_EMAIL Date the householded contact was last updated in the email platform
UPDATED_AT_PUSH Date the householded contact was last updated in the push notification
UPDATED_AT_SMS Date the householded contact was last updated in the sms platform

Unsubscribes

Purpose: Enables you to link unsubscribes that are a result of a specific message back to the message.  For unsubscribes that occur as part of account preferences or other general non-message related actions the unsubscribe will be updated in the contact list directly

Table Name: uns.unsubscribes

Column Description
CONTACT_ID Contact ID from the contact table enabling an unsubscribe to reference the contact that unsubscribed
EVENT_AT Date of the unsubscribe
MESSAGE_TYPE The type of message: Email, SMS, Push or In-App
SEND_ID Send ID from the sends table enabling an unsubscribe to reference back to the send
UNSUBSCRIBE_ID Unique Daasity identifier for an unsubscribe - comprised of the source system and the unsubscribe id from the source system