Unified Notification Schema (UNS)

This page provides a detailed description of the Unified Notification Schema (UNS) within the Daasity Data Model and defines each table and column in this schema

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)

Click on this link to view the ERD for the Unified Order Schema (UNS) integration illustrating the different tables and keys to join across tables.

Unified Notification Schema Tables

• Automations [uns.automations]

• Bounces [uns.bounces]

• Campaigns [uns.campaigns]

• Clicks [uns.clicks]

• Contact Attributes [uns.contact_attributes]

• Contact Lists [uns.contact_lists]

• Contacts [uns.contacts]

• Events [uns.events]

• Impressions [uns.impressions]

• Lists [uns.lists]

• Metrics [uns.metrics]

• Opens [uns.opens]

• Sends [uns.sends]

• Spams [uns.spams]

• Unique Contacts [uns.unique_contacts]

• Unsubscribes [uns.unsubscribes]

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 UNS, our normalized notification 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