What's important to know about message database tables is that this information hits Frakture not as a single flat table, but as several related tables gathered into optimized views that roll up raw tables. As usual, the whole complex of tables/views will use our naming syntax ...

<platform>_<bot_id>_<data_type>

And you'll find the various component parts of a single message stream carrying different <data_type> suffixes.

Usually your focus for reporting purposes, will be the summary tables totaling up into a one-row-per-message format the label and time of that message plus its all-time performance metrics, such as

engaging_abd_email_summary

Where possible, we also create a similar per-day view with one row per message per day. Systems don't commonly offer such per-day figures for mass email; it's more typical for social media and advertising, e.g.:

facebook_bizman_abe_ad_summary

facebook_bizman_abe_ad_summary_by_date

Underlying (and incorporated into) these summary views are hard tables, which is where the Messages streams directly stack their data.

<platform>_<bot_id>_message

<platform>_<bot_id>_stats

<platform>_<bot_id>_stats_by_date

message captures the setup information about the message: when it was first sent, its label or subject, its content; message_id — which is Frakture's ID number for the message, not the external system's — is the primary key.

stats (and where available, stats_by_date ) is a simple table indexed by two fields, message_id + name, with a third field, value, reporting a single metric. A typical message will generate multiple named metrics, some of which are unique to the channel (e.g., YouTube video plays) and others of which are common across virtually all messages (e.g., impressions/views and clicks). Our stats table creates one row for each metric, for each message — so if the email blast you sent last Tuesday throws off figures for emails sent, opened, clicked, and bounced, the stats table will have four different rows for that one email.

The core standard message definition fields are:

Messages table (from external platform)

In addition to these fields that are captured from the source system, there are several appended by Frakture:

Messages table appended by Frakture

Individual Message Statistics are also pulled into the Warehouse and supplied alongside these definition fields.

The standard message stat fields are described below, along with the standard definition for each*: