This table has 1 row per email message and per org id. Each row contains instances of email-message and email-participant.
| 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. | |
| CREATED_AT | Timestamp_NTZ | The time this email record was first created in Outreach. | ||
| 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. |
| 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. | Count(1), count(id), and count(distinct id) are equivalent in a table without joins or groupings for a given bento & o_id. | |
| IS_DELETED_IN_APP | Boolean | Is the latest dml_type "delete" or "backfill-delete". | Where is_deleted_in_app = false Only keeps undeleted rows. | |
| MESSAGE | Variant | JSON Object containing the email message data synced from the mailbox. Contains email-message-participant and email-message objects | ||
| MAILBOX_EMAIL_HASH | Varchar | The hashed associated email address of a mailbox. | ||
| 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 | |
| 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. | |
| 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. |
This object is contained once per Email Message Synced Event and is stored in JSON format in the MESSAGE field. Each Email Message contains instances of Email Message Participant.
| Column | Type | Definition | Sample Value(s) | Sample Use |
|---|---|---|---|---|
| ID | Varchar | This is the message ID (various by mailbox provider) of this mailing. | ||
| DATE | Timestamp_NTZ | A datetime (local to server) in which the row was last modified. date 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 date::date >= current_date-7 This will return all rows modified in the last 7 days. |
| DIRECTION | Varchar | A string describing the direction type. | 'EMAIL_MESSAGE_DIRECTION_UNKNOWN', 'DIRECTION_OUTBOUND', 'DIRECTION_INBOUND' | |
| FROM | Variant | This is the email participant associated with `from` email message field. | ||
| TO | Array | This is a list of email participants associated with `to` email participants. | ||
| REPLY_TO | Variant | This is a email participant associated with the `reply_to` email participant. | ||
| CC | Array | This is a list of email participants associated with carbon copy email participants. | ||
| BCC | Array | This is a list of email participants associated with blind carbon copy email participants. |
This object is stored in JSON format within the MESSAGE field and is referenced by the Email Message FROM, TO, REPLY_TO, CC, and BCC fields.
| Column | Type | Definition | Sample Value(s) | Sample Use |
|---|---|---|---|---|
| Varchar | the email of the email message participant. | |||
| EMAIL_HASH | Varchar | the hashed email of the participant. | ||
| NAME | Varchar | the name of the participant. | ||
| TYPE | Varchar | A string describing the participant type. | 'UNRESOLVED', 'USER', 'PROSPECT' | |
| PROSPECT_ID | Varchar | the id of the participant if the participant is a prospect. | this will be null for non prospect participants. maps to prospects table. | |
| USER_ID | Varchar | the id of the participant if the participant is an outreach user. | this will be null for non outreach user participants. maps to users table. |