Sailthru Data Exporter

The Sailthru Data Exporter allows for a one-time or daily recurring export to a client-managed secure SFTP site or Amazon S3 bucket.

Included in the export is campaign, transactional, and user data that includes var, purchase, and interest data. (A full list of fields and collections are included on this page.)

  • Profile data is exported for users that have updated profiles from activity since the previous day.
  • Campaign data includes basic information about campaigns sent on the previous day
  • Campaign message data includes metadata on all emails sent as part of each campaign sent on the previous day, and all campaign emails sent over the past 7 days that generated user activity.
  • Transactional message data includes metadata on all emails sent as part of each transactional email sent on the previous day, and all transactional emails sent over the past 7 days that generated user activity.

Once you prepare your upload site using the Sailthru public key, the initial upload begins the following morning.

Note that you can alternatively perform an export of user data for a given list on the My Sailthru “Lists” page (see Export Lists and User Profile Data) or using the list_export_data job in the Sailthru API.

Sailthru Data Exporter is an individually priced product offering from Sailthru.

Setup

To receive the daily export, you must configure an SFTP server that you own and maintain or an S3 bucket that you own and maintain. In both cases, you will need to permission Sailthru’s public credentials to access those resources.

SFTP

  1. Create a user with write permission and allow the Sailthru public key (below) to log in as that user.
    Please consult your SFTP server documentation to determine how this is done.
  2. Send the username and hostname to Sailthru, along with an optional path and folder to export to, otherwise the root directory is used.

Sailthru public SFTP key:

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAgEAtwmLED75SHzCWTm6HGheO6HAmhDYGsDEVj6lrlGqdDNaObqqOI3wWuuBS6VKx1ZovYjNiQyDRWXfru0IGFu4EoeWo2lyQuHGYOPShTuy0tLtkTMljx1XnlwxX2p4cRZ/ZGQsPAjJmIpQttGpT5VyvPYxKPuF3H3ldPxjffmSpt4bUqbOyGsXI6riA1jvTVt46hf/UN2Or23FwpoXp6gdKZJfvVThXNwXk2+tpKrnzltt2wiSyMui7AXCcU4o9fvk8iG3JN7IKIkg0Fp0IxVpbJi14h/2YbtPuK7tbORneC2VnWYAvE5INfzNejEJcEtHyR32nXEiLXt3QNgikNgjPqBNiuwQ9QsILD+GfAZWpN8xp5NmEogTpoOkYRXdlkBZBpmIlZmC7H+LsrzZ+XYWccwPOd5hpGsSyr5ijBkZfd3TIgEkc/kK76Eg2HOsBBMjcmUG7F7+JAs617P5r+vJsPMYi0xj4dXsqTgGjBJleUC+S6xIOojCZzQ7eGIKaXVUFHZFE6Q7D0RPYZw3R/9XqZwMQIhsR6zgwcT2Phd206nnLsq8Y2BgRsmdU3EcL4CdWUfTWjqssP7nHvjf6o6xsBWPKtl4+YJaFom0P68ZNEuvJDcLmKMd4a5oe+CyvhYdYUVJ1t5aJ2ZRNzUe2EHuQG1g4IKo0tfaj9/gosdvhx0= Sailthru Report Delivery
(Triple-click to highlight, then copy.)

Amazon S3

  1. Permission the Sailthru user account to be able to write to a bucket that is owned by your company as per this AWS documentation. You will use the Sailthru public S3 account principalarn:aws:iam::728023223659:user/integ.
    Click to view and customize the S3 account bucket policy
    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "bucket permissions",
                "Effect": "Allow",
                "Principal": {
                    "AWS": "arn:aws:iam::728023223659:user/integ"
                },
                "Action": [
                    "s3:GetBucketLocation",
                    "s3:ListBucket"
                ],
                "Resource": "arn:aws:s3:::yourcompanyname-sailthru-data"
            },
            {
                "Sid": "key permissions",
                "Effect": "Allow",
                "Principal": {
                    "AWS": "arn:aws:iam::728023223659:user/integ"
                },
                "Action": [
                    "s3:PutObject",
                    "s3:GetObject",
                    "s3:DeleteObject",
                    "s3:PutObjectAcl",
                    "s3:GetObjectAcl"
                ],
                "Resource": "arn:aws:s3:::yourcompanyname-sailthru-data/*"
            }
        ]
    }
  1. Provide your Sailthru representative with the bucket name and a path to export to.

Files

The following files are exported by the Data Exporter on a daily basis.

Name

Filename & Description

Fields

User Profiles

<client_id>.profile.<YYYYMMDD>.json.gz

Contains all user profiles that had a ‘material event’ since midnight (Eastern Time) of the previous day, including on the current day until the export. These events include the following:

  • open (daily_open)
  • pv (daily_pv)
  • purchase (purchase_time)
  • login (daily_login)
  • optout

Reference

Campaigns

<client_id>.blast.<YYYYMMDD>.json.gz

Contains basic information about the campaigns that were sent on the previous day.

Reference

Campaign Messages

<client_id>.message_blast.<YYYYMMDD>.json.gz

Contains metadata about all campaign messages sent on the previous day. These represent the individual emails that were sent to end users and some basic stats about what was done with each message.

Reference

Updated Campaign Messages

<client_id>.message_blast_update.<YYYYMMDD>.json.gz

Contains metadata about all campaign messages sent in the past 7 days that have had any activity: an open, a click, revenue attributed, or an opt-out. Data field names match those included in “message_blast”.

Reference

Transactional Messages

<client_id>.message_transactional.<YYYYMMDD>.json.gz

Contains metadata about transactional messages. These represent individual emails that were sent to end users, along with some basic statistics about actions taken on them.

Reference

Updated Transactional Messages

<client_id>.message_transactional_update.<YYYYMMDD>.json.gz

Contains metadata about all transactional messages sent in the past 7 days that have had any activity: an open, a click, revenue attributed, or an opt-out. Data field names match those included in “message_transactional”.

Reference

Data Links

This table identifies the key values that join each dataset together.

file.field

matches

file.field

profile.id

message_blast.profile_id

profile.id

message_blast_update.profile_id

profile.id

message_transactional.profile_id

profile.id

message_transactional_update.profile_id

blast.id

message_blast.blast_id

blast.id

message_blast_update.blast_id

Field Reference

Note: Fields that have no value for a given export will not be included in the output file. Date/time fields conform to the ISO 8601 standard.

User Profiles

(<client_id>.profile.<YYYYMMDD>.json.gz)

Field

Description

id

user profile ID

email

user email address

create_date

Sailthru profile create date

signup_date

signup date for first list

total_opens

total number of open emails (campaign, transactional)

total_clicks

total number of email clicks (campaign, transactional)

total_unique_opens

total number of unique email opens, so opening the same email multiple times only counts as a single open

total_unique_clicks

total number of unique clicks, so clicking from the same email multiple times only counts as a single click

total_pageviews

total number of site pageviews

last_open

date the user last opened an email

last_click

date the user last clicked an email

last_pageview

date the user last viewed a page on your site

optout

optout status of user

  • optout is NULL or does not exist: user is opted in
  • optout ‘blast’ : user is opted out of campaigns (e.g. newsletters), but still available for individual transactional emails
  • optout ‘all’ : user is unavailable for all email messaging

optout_time

time user opted out

lists_signup {}

array of lists user is signed up for

geo.city {}

geo object of the cities the user has been identified to be located in

geo.state {}

geo object of the states the user has been identified to be located in

geo.country {}

geo object of the countries the user has been identified to be located in

geo.zip {}

geo object of the zipcodes the user has been identified to be located in

total_messages

total number of email messages a user has received, both campaign and transactional

first_purchase_time

date/time of a user’s first purchase

purchase_count

total number of purchases a user has made

total_revenue

monetary value of all purchases by the user

purchase_incomplete {}

abandoned cart items

last_purchase_time

date/time of a user’s last purchase

purchase []

array of all purchases and purchased items by a user

browser {}

Object displaying browsers used, with a count of instances for each

E.g. {“Chrome” : 2, “Internet Explorer”: 3}

lists {}

Object displaying all active lists of which the user is a member

E.g. {“Main”, “Master List”}

Derived Values

Engagement

derived value based on Sailthru rules

Largest Purchase Item Price

Can be found in the purchase array

Top Device

Derived from browser data

Email Status

derived value, if optout == “all”, status Optout (All), otherwise, valid

optout_templates

Templates from which the user opted out. Derived from optout_time on the message and template

Campaigns

<client_id>.blast.<YYYYMMDD>.json.gz

Field

Description

name

newsletter name

day

date newsletter sent

id

the blast id

can be used to map the message to the campaign (joined from message.blast by blast id)

Campaign Messages

<client_id>.message_blast.<YYYYMMDD>.json.gz &
<client_id>.message_blast_updated.<YYYYMMDD>.json.gz

Field

Description

profile_id

id of message recipient

send_time

send time of the email

blast_id

message.blast

opens []

an array of the opens and open times

message_revenue

revenue attributed to the email

device

device which opened / viewed the message

delivery_status

ex. softbounce, hardbounce, etc

optout_time

time user opt-ed out from this list / messaging

template

the template used in the campaign

list_name

list of users this campaign was sent to

link_params

an object containing all of the querystring parameters added to the message

Derived Values

purchases

- sent on profile

- can use profile id and message date + purchase date to arrive at same data

- purchases outside the 24hrs + till export time will not be sent with this approach

purchase time

date time of purchase

Transactional Messages

<client_id>.message_transactional.<YYYYMMDD>.json.gz &
<client_id>.message_transactional_updated.<YYYYMMDD>.json.gz

Field

Description

send_time

time message was sent

profile_id

unique identifier of the email recipient

opens []

array of the opens and open times

[] = url, timestamp

message_revenue

revenue attributed to this message

device

device used to open message

delivery_status

ex. softbounce, hardbounce, etc

template

message template sent

optout_time

time user opted out of messaging attributed to this email

link_params

an object containing all of the querystring parameters added to the message

Derived Values

purchases

- sent on profile
– can use profile id and message date + purchase date to arrive at same data
– purchases outside the 24hrs + till export time will not be sent with this approach

purchase time

date time of purchase

Top