Messages Schema

Overview

Campaign Intelligence for messages, such as emails and SMS, leverages three types of models:

1. Users are the parent model.
2. Interactions are events that connect users to an asset, such as email clicks or opens.
3. Emails are a type of asset.

Schema

In the entity relationship diagram (ERD) below, we show a schema for messages using emails as an example. The ERD shows expected model columns and their relationships, with necessary primary (PK) and foreign keys (FK).

In the ERD above, Users are connected to different interactions -- such as email opens and clicks -- through a unique user_id. Interactions are events, and so require a timestamp. They can have additional columns based on the type of interaction, such as a clicked_url for email click events.

Each interaction is also linked to a message asset, either email or SMS, by way of a message_id. The message can have a variety of columns, including date columns such as start_at and ended_at to indicate the dates that email asset was in use.