Mailings
This table has 1 row per outbound mailing and per org id.
| Column | Type | Definition | Sample Value(s) | Sample Use |
|---|---|---|---|---|
| BENTO | Varchar | A collection of machines that power the Outreach app. Orgs live inside these. | This may distinguish orgs that are in different data centers. | |
| BOUNCED_AT | Timestamp_NTZ | The UTC datetime for the first time this mailing failed to deliver. | ||
| CALENDAR_EVENT_ID | Varchar | If the mailing included an invite to a Meeting, this will be populated if a prospect accepts and a meeting is created. | Maps to the CALENDAR_EVENTS table | |
| CALENDAR_ID | Varchar | If a calendar event was registered to a mailing, This is the user's calendar's ID. | Maps to the CALENDARS table | |
| CLICKED_AT | Timestamp_NTZ | The UTC datetime for the first time a link was clicked in a mailing. | ||
| CLICK_COUNT | Number | The cumulative # of times anyone (not just the desired prospect) clicked a link in this mailing. Requires that the mailing have click tracking enabled. | ||
| CREATED_AT | Timestamp_NTZ | The UTC datetime for when the mailing was created. This may be notably longer than its delivery date if scheduled in advance. | ||
| DELIVERED_AT | Timestamp_NTZ | The UTC datetime for when the mailing was successfully delivered originally. | ||
| DML_AT | Timestamp_NTZ | A datetime (local to server) in which the row was last modified. dml_at can be used to identify updated and new records, but as tables may not see changed records every day, it is not authoritative on when the table was last synced. | 2019-01-01 12:14:01:10 | Where dml_at::date >= current_date-7 This will return all rows modified in the last 7 days. |
| DML_TYPE | Varchar | The type of manipulation that last occurred to a row. | 'update', 'delete', 'insert', 'backfill', 'backfill-delete' | Where dml_type <> 'delete' Excludes deleted rows. |
| ERROR_CATEGORY | Varchar | If there was an error (such as a bounce or lockout) a broad category will appear here. | 'throttle', 'other' | |
| ERROR_REASON | Varchar | A more detailed description of the error category appears here. It could be a detailed message about a rate limit, or an error code returned by your mailbox provider. | 5.1.0, "You have reached the 10% daily bounce limit" | |
| ID | Varchar | Represents an individual row of data in a table. Fields in other tables that match to this ID will be called thisTable_id. e.g. ID in the calls table will appear as the call_id in another table. | Count(1), count(id), and count(distinct id) are equivalent in a table without joins or groupings for a given bento & o_id. | |
| IN_REPLY_TO | Varchar | This is the message ID (various by mailbox provider) that the mailing is a reply to. | ||
| IS_DELETED_IN_APP | Boolean | Is the latest dml_type "delete" or "backfill-delete". | Where is_deleted_in_app = false Only keeps undeleted rows. | |
| MAILBOX_ID | Varchar | The mailbox that orchestrated the mailing. This is how users are associated with mailings. | Maps to the MAILBOXES table | |
| MAILING_TYPE | Varchar | A category of mailings based on if it was sent in sequence or not. This can be better replicated with "case when sequenced_id is not null then 'sent in sequence' end" | 'single', 'sequence', 'campaign' | |
| MARKED_AS_SPAM_AT | Timestamp_NTZ | If the mailing server returns spam notifications, the mailing will have a UTC datetime for when the server was notified of spam. | ||
| MESSAGE_ID | Varchar | This is the message ID (various by mailbox provider) of this mailing. | ||
| OPENED_AT | Timestamp_NTZ | If open tracking is enabled, this field includes the UTC datetime of when the mailing was first opened by a prospect. | ||
| OPEN_COUNT | Number | The cumulative # of times anyone (not just the desired prospect) opened this mailing. Requires that open tracking be enabled. | ||
| OPPORTUNITY_ID | Varchar | If a mailing was associated with an opportunity, its ID will be here. | Maps to the OPPORTUNITIES table | |
| O_ID | Varchar | Organization ID, also called instance ID. This is the ID for your unique Outreach account. | Most commonly used on joins alongside other primary keys. where a.o_id = b.o_id and a.id = b.a_id | |
| PARENT_MESSAGE_ID | Varchar | The parent_message_id is the original message ID that began an email thread. | ||
| PROSPECT_ID | Varchar | The prospect ID associated with a mailing. A prospect may be contacted via multiple email domains and retain the same prospect ID so long as all emails associate to the same ID. | Maps to the PROSPECTS table | |
| REPLIED_AT | Timestamp_NTZ | This is the UTC datetime that a prospect replied to the mailing. | ||
| REPLIER_MESSAGE_ID | Varchar | If a reply was received, this is the message ID (varies by mailbox provider) that was received. | ||
| SEQUENCE_ID | Varchar | If a mailing was sent in sequence, this field maps it to that sequence. | Maps to the SEQUENCES table | |
| SEQUENCE_STATE_ID | Varchar | The associated sequence state of the prospect that triggered the mailing. | Maps to the SEQUENCE_STATES table | |
| SEQUENCE_STEP_ID | Varchar | The sequence step of a prospect at the time of mailing. | Maps to the SEQUENCE_STEP_ID | |
| SOURCE | Varchar | A categorical field that identifies where the mailing was sent from. Mailings using the extension are flagged as 'app'. | 'app', 'gmail', 'outlook' | |
| STATE | Varchar | This is the current status of the mailing. It is state based and changes as actions are taken against the mailing. | 'delivered', 'bounced', 'opened', 'clicked', 'replied' | |
| STATE_CHANGED_AT | Timestamp_NTZ | The UTC datetime for when the state field was last updated. | ||
| SUBJECT | Varchar | The subject line of mailing. | ||
| SUBJECT2 | Varchar | The secondary subject line of the mailing; the fields are redundant and can be NVL() to get non null values. | ||
| SURROGATE_ID | Varchar | An md5 hash of bento, o_id and ID for a table. | This can be used to get distinct counts of records if an account has more than 1 org instance. Two orgs will both have a prospect with ID of "1" but each will have a different surrogate ID. | |
| TASK_ID | Varchar | A corresponding task ID if a mailing had a task assigned to it. | Maps to the TASKS table | |
| TEMPLATE_ID | Varchar | Maps a mailing to the template used for its construction. | Maps to the TEMPLATES table | |
| TRACK_LINKS | Boolean | A Boolean field for if link clicking is tracked on the mailing. | ||
| TRACK_OPENS | Boolean | A Boolean field for if email opens are tracked on the mailing. | ||
| UNSUBSCRIBED_AT | Timestamp_NTZ | The time when the mailing was unsubscribed to. | ||
| UPDATED_AT | Timestamp_NTZ | The UTC datetime for the last time this record was updated. | 2019-01-01 20:14:01:10 | Almost equivalent to dml_at (but adjusted for time zones), but doesn't include record creation or deletion. |