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
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
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
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
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
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
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
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
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
Lists
Purpose: Enables you to capture all the different lists or segments in the platform
Table Name: uns.lists
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
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
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
Spams
Purpose: Enables you to identify what messages a contact reported as spam
Table Name: uns.spams
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
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
Last updated