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