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:

Contact: who the communication is being sent to
Campaign: what the communication is messaging
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

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"

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

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

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

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

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

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 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

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

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

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

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

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

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

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

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

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

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

PreviousUnified Order Schema (UOS)NextUnified Subscription Schema (USS)

Last updated 5 months ago