Templates
This table has 1 row per template and per org id.
| Column | Type | Definition | Sample Value(s) | Sample Use |
|---|---|---|---|---|
| ARCHIVED_AT | Timestamp_NTZ | The UTC datetime that the template was archived and removed from selection for users. | ||
| 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. | |
| BODY_HTML | Varchar | The HTML used to populate the body of an email. Includes variables. | '<div>Hello {first_name}</div>' | |
| BODY_TEXT | Varchar | The plain text used to populate the body of an email. Includes variables. | 'Hello {first_name}' | |
| BOUNCE_COUNT | Number | The cumulative sum of email bounces. | ||
| CLICK_COUNT | Number | The cumulative sum of email clicks if click tracking was enabled. | ||
| CLONED_FROM_ID | Varchar | The ID of a template if it was copied from one. | Maps to the TEMPLATES table | |
| CREATED_AT | Timestamp_NTZ | The datetime when the template was created. | ||
| CREATOR_ID | Varchar | The ID for whomever created a record in Outreach, or who updated it if records are overwritten when changed. Maps to different tables based on the "creator_type" field. | ||
| DELIVER_COUNT | Number | The cumulative sum of email deliveries. | ||
| 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. |
| FAILURE_COUNT | Number | The cumulative sum of email failures, typically from bad variables, or bad mailbox configurations. | ||
| 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. | |
| IS_DELETED_IN_APP | Boolean | Is the latest dml_type "delete" or "backfill-delete". | Where is_deleted_in_app = false Only keeps undeleted rows. | |
| LAST_USED_AT | Timestamp_NTZ | The UTC datetime for when this template was last applied to a mailing. | ||
| NAME | Varchar | The name of a template if available. | ||
| OPEN_COUNT | Number | The cumulative sum of email opens if open tracking was enabled. | ||
| OPT_OUT_COUNT | Number | The cumulative sum of emails with opt out requests. | ||
| OWNER_ID | Varchar | The user that has full rights to the template ID. | Maps to the USERS 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 | |
| REPLY_COUNT | Number | The cumulative sum of email replies. | ||
| SHARE_TYPE | Varchar | A categorical flag for the privacy of a template. | 'private', 'shared' | |
| SUBJECT | Varchar | The default subject line of a template. Includes variables. | '{mycompany}/{account_name} Initial Call' | |
| 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. |
| UPDATER_ID | Varchar | Same as creator_id above, but refers to who last did the update of the record. |