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:
- 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)
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
- Automations
- Bounces
- Campaigns
- Clicks
- Contact Attributes
- Contact Lists
- Contacts
- Events
- Impressions
- Lists
- Metrics
- Opens
- Sends
- Spams
- Unique Contacts
- 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 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 |
| 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 |