In a previous post, we discussed our Unified Order Schema (UOS), which centralizes all your order data in a single schema, enabling deep analytics. In this piece, we’ll discuss our Unified Notification Schema (UNS).
The reason for designing the UNS was simple: we wanted to make it easy to centralize and analyze data from multiple communication platforms, such as:
- Email via an Email Service Provider (ESP)
- SMS via SMS platforms
- Push notifications or in-app notifications via relevant providers
Have you had to deal with the struggle of changing your ESP? If so, I know your pain: I was responsible for our email infrastructure at ProFlowers, and in my 8 years there, we used 4 different ESPs. At Daasity, we’ve seen many of our merchants change their ESPs or use a different provider for email versus SMS.
There is no standard format in which ESPs and SMS platforms deliver data, and knowing that your marketing team will likely change platforms at some point (or at least add new platforms as your business grows), leveraging a standard data model for notifications will enable you to maintain a common set of reporting and analytics even when the underlying platforms change.
So what does a standard UNS data model look like?
It needs to include a few core concepts:
- Contact: Who you are sending a notification to
- Campaign: What is being sent
- Events: When a Contact subscribed or unsubscribed to receiving notifications, as well as other actions
There is usually a lot of other data around notifications that we include in UNS, but it is built from these core concepts.
ERD for Daasity’s UNS data model
Here is the entity relationship diagram for our UNS data model:
Contact Tables
Contact tables help us understand how we might interact with customers and potential customers (hence the name contact, as not everyone we communicate with will be a customer).
Contacts: This is the core table in the Contact section of the data model. It contains basic information that we would expect to have in most notification platforms–email, phone, or id that enables us to identify that contact–and if we are able to contact that individual based on being “subscribed” to notifications.
Contact Attributes: This was built to make it easy to incorporate any other type of information that we might store about contacts. We built this as a key value type table, as source data is in JSON, but we want our analytics to be available in a traditional SQL-type database.
Unique Contacts: Each notification platform will have their own concept of a contact (also called members, profiles, etc.). Ultimately, we want to be able to understand an individual’s behavior across the different platforms we use to communicate with them. Therefore, householding unique contacts across different platforms is critical.
Lists: Not all platforms have the concept of lists or being able to have a contact subscribe or unsubscribe from a list. This table enables us to derive the concept of a list and track subscription status at the list level instead of at the general platform level.
Contact Lists: This is how we tie the concept of a List to the concept of a Contact and tie a Contact’s subscription status to that List.
Campaign Tables
Campaign tables enable us to track high-level information related to the messages we are sending to our contacts. Additionally, they help us build data models around the campaigns that might be structured as a series of messages.
Campaigns: This is the core table in the Campaign section of the data model. It enables us to capture key information about campaigns so that we can build standard reporting on email, SMS, and other notification strategies.
Automations: Many platforms allow users to deploy a series of triggered messages (automations, flows, journeys). This table is designed to link to the Campaigns table. This way, we can roll up performance to an automation and develop analytics that will allow us to compare one automation against another.
Impressions: This table was designed to help develop analytics around the effectiveness of sign-up capture by tracking impression volume by day and around types of sign-up pages.
Event Tables
Event tables enable us to track how a contact interacts with notifications. Event tables link back to both the Contact and the Campaign. Because there is no standard way for notification platforms to deliver this information, we often have to derive this Event data from what the platform provides.
Sends: This is the core table for the Event section of the data model. It helps us understand who received the message, what the message was, and if the message was sent as part of a list.
Metrics: For platforms like Klaviyo, which feeds all interaction data as a single event stream, this is an important table: it outlines what metric is being sent to what table as part of the source to unified schema transformation.
Bounces: This table incorporates bounce type and bounce details so that brands can understand why messages bounce. Additionally, iit ties the data back to Sends to help maintain a healthy email list.
Opens: Since the release of iOS 14.5, it has become more important to incorporate information on the email client used, as you will want to identify segments that are pre-fetching all images. Additionally, SMS messages don’t have the concept of an open, so we generate opens for SMS platforms as part of this table.
Clicks: Users will often click on more than one link in an email. Although many ESPs and SMS platforms don’t share details on clicks, it is extremely useful to capture them when they are available. This way, we can construct the concept of clicks and unique clicks on the analytics side.
Spams: This table is used to track data around when email recipients report an email sent as spam. This is important for a couple primary reasons:
- This is data that helps brands understand the impact(s) of poor segmentation.
- When contacts report your messages as spam, it will negatively impact your ability to send messages.
Unsubscribes: We use this table to tie unsubscribes to messages by tying this table to the Sends table. This allows brands to understand how email or SMS frequency may impact unsubscribe rates.
- Unsubscribes via other locations (e.g., an account preferences page) are updated to the Contact Lists table directly.
Events: This table is used to capture metrics not listed above for platforms that provide data in an event stream.
