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:

• List

• Settings

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:

• Data Exporter

• Event Stream

• 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

• Clients

• Lists

• Profiles

• Profile Lists

• Profile List Removals

• Profile Vars

• Templates

• Campaigns

• Campaign Sends

• Campaign Bounces

• Campaign Opens

• Campaign Clicks

• Campaign Unsubscribes

• Flows

• Triggered Sends

• Triggered Bounces

• Triggered Opens

• Triggered Clicks

• Triggered Unsubscribes

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
email	email
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