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

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

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

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

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

Last updated