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

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

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

Last updated