Integration Specifications
This article will help you learn about how Daasity replicates data from Sailthru, limitations to the data we can extract and where the data is stored in the Sailthru schema
Integration Overview
Sailthru is an email marketing platform that allows businesses to send outbound emails, build and personalize email campaigns and manage email lists to acquire, grow and retain customers.
Integration Availability
This integration is available for:
Enterprise
API Endpoints
The Daasity Sailthru extractor is built based on this Sailthru API documentation. The following endpoints are used by Daasity to replicate data from Sailthru:
S3 Feed
The Daasity Sailthru extractor is built based on this Sailthru Connect documentation. The following is used by Daasity to replicate data from Sailthru:
Daasity provides the option to export Mobile Events via the Mobile Event Stream in this integration
Read more about the Sailthru Mobile Integration to learn how Daasity extracts Mobile Event Stream data
Entity Relationship Diagram (ERD)
Click here to view the ERD for the Daasity Sailthru integration illustrating the different tables and keys to join across tables.
Sailthru Schema
The Daasity Sailthru extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint or S3 feed to the table based on the mapping logic outlined in each table.
Daasity Sailthru V1 Integration:
Settings
Endpoint: Settings
Update Method: UPSERT
Table Name: [
sailthru.settings
]
JSON Element | Database Column |
---|---|
id | id |
domains | domain |
domain::site_domain | site_domain |
timezone | timezone |
id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Clients
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.clients
]
CSV Header | Database Column |
---|---|
client_id | client_id |
account_id | account_id |
name | account_name |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + client_id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Lists
Endpoint: List
Update Method: UPSERT
Table Name: [
sailthru.lists
]
JSON Element | Database Column |
---|---|
list_id | list_id |
name | list_name |
type | list_type |
email_count | email_count |
valid_count | valid_count |
primary | primary_lists |
created_time | created_at |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + list_id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Profiles
Endpoint: Data Exporter
Update Method: UPSERT
Table Name: [
sailthru.profiles
]
CSV Header | Database Column |
---|---|
id | profile_id |
created_date | created_at |
signup_time | signup_at |
login_time | login_at |
bounce_time | bounce_at |
bounce_message | bounce_message |
total_opens | total_opens |
total_clicks | total_clicks |
total_unique_opens | total_unique_opens |
total_unique_clicks | total_unique_clicks |
total_pageviews | total_pageviews |
total_messages | total_messages |
last_open | last_open |
last_click | last_click |
last_pageview | last_pageview |
optout_type | optout_type |
optout_time | optout_at |
optout_reason | optout_reason |
var_time | updated_at |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Profile Lists
Endpoint: Data Exporter
Update Method: UPSERT
Table Name: [
sailthru.profile_lists
]
CSV Header | Database Column |
---|---|
MD5(name + profile_id) | profile_list_id |
id | profile_id |
name | list_name |
signup_time | list_signup_at |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + name + id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Profile List Removals
Endpoint: Data Exporter
Update Method: UPSERT
Table Name: [
sailthru.profile_list_removals
]
CSV Header | Database Column |
---|---|
MD5(lists_remove.first + profile_id) | profile_list_id |
id | profile_id |
lists_remove.first | list_name |
lists_remove.last | list_removed_at |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + lists_remove.first) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Profile Vars
Endpoint: Data Exporter
Update Method: UPSERT
Table Name: [
sailthru.profile_vars
]
CSV Header | Database Column |
---|---|
MD5(id + vars[0]) | profile_var_id |
id | profile_id |
vars[0] | key |
vars[1] | value |
vars[1]::ktile | ktile |
vars[1]::num | num |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + id + vars[0] + vars[1]) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Templates
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.templates
]
CSV Header | Database Column |
---|---|
client_id | client_id |
template_id | template_id |
name | template_name |
create_time | create_time |
modify_time | modify_time |
create_user | create_user |
modify_user | modify_user |
from_name | from_name |
from_email | from_email |
replyto_email | replyto_email |
subject | subject |
preheader | preheader |
is_basic | is_basic |
data_feed_url | data_feed_url |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + template_id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Campaigns
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.campaigns
]
CSV Header | Database Column |
---|---|
client_id | client_id |
campaign_id | campaign_id |
name | campaign_name |
from_name | from_name |
from_email | from_email |
replyto_email | replyto_email |
subject | subject |
list_name | list_name |
data_feed_url | data_feed_url |
preheader | preheader |
template_id | template_id |
schedule_time | schedule_time |
report_email | report_email |
suppress_list | suppress_list |
email_hour_range | email_hour_range |
create_time | create_time |
start_time | start_time |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + campaign_id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Campaign Sends
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.campaign_sends
]
CSV Header | Database Column |
---|---|
MD5(campaign_id + profile_id + event_time) | send_id |
'campaign' | type |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
campaign_id | campaign_id |
list_name | list_name |
list_id | list_id |
template_id | template_id |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + campaign_id + profile_id + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Campaign Bounces
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.campaign_bounces
]
CSV Header | Database Column |
---|---|
MD5(campaign_id + profile_id + send_time + event_time) | bounce_id |
MD5(campaign_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
campaign_id | campaign_id |
list_id | list_id |
reason_log | reason_log |
message_type | message_type |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + campaign_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Campaign Opens
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.campaign_opens
]
CSV Header | Database Column |
---|---|
MD5(campaign_id + profile_id + send_time + event_time) | open_id |
MD5(campaign_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
campaign_id | campaign_id |
list_id | list_id |
browser | browser |
count | open_count |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + campaign_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Campaign Clicks
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.campaign_clicks
]
CSV Header | Database Column |
---|---|
MD5(campaign_id + profile_id + send_time + event_time) | click_id |
MD5(campaign_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
campaign_id | campaign_id |
list_id | list_id |
browser | browser |
url | url |
count | click_count |
url_count | url_count |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + campaign_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Campaign Unsubscribes
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.campaign_unsubscribes
]
CSV Header | Database Column |
---|---|
MD5(campaign_id + profile_id + send_time + event_time) | unsubscribe_id |
MD5(campaign_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
campaign_id | campaign_id |
list_id | list_id |
message_type | message_type |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + campaign_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Flows
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.flows
]
CSV Header | Database Column |
---|---|
flow_id | flow_id |
name | flow_name |
create_time | create_time |
modify_time | modify_time |
version | version |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + flow_id) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Triggered Sends
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.triggered_sends
]
CSV Header | Database Column |
---|---|
MD5(flow_id + profile_id + event_time) | send_id |
'triggered' | type |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
template_id | template_id |
is_basic | is_basic |
flow_id | flow_id |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + flow_id + profile_id + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Triggered Bounces
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.triggered_bounces
]
CSV Header | Database Column |
---|---|
MD5(profile_id + send_time + event_time) | bounce_id |
MD5(profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
template_id | template_id |
is_basic | is_basic |
reason_log | reason_log |
message_type | message_type |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Triggered Opens
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.triggered_opens
]
CSV Header | Database Column |
---|---|
MD5(flow_id + profile_id + send_time + event_time) | open_id |
MD5(flow_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
template_id | template_id |
is_basic | is_basic |
flow_id | flow_id |
browser | browser |
count | open_count |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + flow_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Triggered Clicks
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.triggered_clicks
]
CSV Header | Database Column |
---|---|
MD5(flow_id + profile_id + send_time + event_time) | click_id |
MD5(flow_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
template_id | template_id |
is_basic | is_basic |
flow_id | flow_id |
browser | browser |
url | url |
index | link_index |
count | click_count |
url_count | url_count |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + flow_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Triggered Unsubscribes
Endpoint: Event Stream
Update Method: UPSERT
Table Name: [
sailthru.triggered_unsubscribes
]
CSV Header | Database Column |
---|---|
MD5(flow_id + profile_id + send_time + event_time) | unsubscribe_id |
MD5(flow_id + profile_id + send_time) | send_id |
client_id | client_id |
event_time | event_time |
send_time | send_time |
message_id | message_id |
profile_id | profile_id |
email_sha256 | email_sha256 |
email_md5 | email_md5 |
engagement | engagement |
signup_time | signup_time |
template_id | template_id |
is_basic | is_basic |
message_type | message_type |
Daasity: source_id | __source_id |
Daasity: account_id | _account_id |
Daasity: MD5(source_id + flow_id + profile_id + send_time + event_time) | __sync_key |
Daasity: timestamp when loaded into DB | __synced_at |
Last updated