Sailthru Data Exporter

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

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 list memberships, var values, purchase data, and more. (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.

Setup

To receive daily and/or one-time historical exports from Sailthru, you must request one or both types of exports from Sailthru and configure a host where Sailthru will upload the data. You can either provide credentials for an SFTP server that you own and maintain or an Amazon S3 bucket that you own and maintain. In either case, you will need to permission Sailthru’s public credentials to be able to access your chosen host.

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. Note: depending on your server’s configuration, you may need to remove the comment field ` Sailthru Report Delivery` from the Sailthru public key.

  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 Data Exporter. For one-time historical exports, filenames will not contain the date.

Name

Filename & Description

Fields

User Profiles

<client_id>_profile<.YYYYMMDD>.json.gz

For one-time historical exports, this includes all profiles. For daily ‘update’ exports, this includes all user profiles that had a material event since midnight (Eastern Time) of the previous day, including on the current day prior to the export.

Material events include opens, clicks, pageviews, logins, purchases, natural list membership changes, optouts, bounce status changes, and var set.

Note: By default, all material events are included in your export, except for bounce status changes and var sets. Select the events you would like to include in your export to customize your data. Contact your CSM to make the changes to your export.

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”.

Provided only with daily exports, not one-time historical exports.

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”.

Provided only with daily exports, not one-time historical exports.

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_time Signup date for first list
lists_remove [] Array of names of lists for which the user was previously a member but has since been removed
login_time Time the user logged in.
bounce_time The last time a profile bounced.
bounce_message The bounce message from when the profile bounced.
bounce_status The profile’s current bounce status. If this is null, then the profile is not bounced.
var_time The last time a profile’s var was changed.
horizon For each tag, the aggregate number of times interest was recorded for that tag. Only included in historical exports.
horizon_month Count of interests on a tag for that month. For daily exports, contains the current month’s data. For historical exports, contains monthly data for at least 60 days.
horizon_times Every instance of recorded tag interest with its associated timestamp. Only provided in daily exports, not in historical exports.
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_type Optout status of the user. If the value does not exist or is null, the user is opted in (i.e. has not opted out of any messaging). Other statuses may be all, blast, or basic. For definitions, see User Opt-Out Levels.
optout_time Time user opted out
optout_reason Only set for some hosted email applications when the user clicks the Spam or Unsubscribe link (if you have set up Opt-out Reasons in your settings).

  • Unsubscribe-email: The user clicked the unsubscribe link provided by their ISP and the ISP sent the information back to Sailthru.
  • Update: A bulk update job opted out a mass group of users, or the user clicked the Spam button provided by Yahoo.
  • Spam: The user clicked the Spam button provided by other email applications.
  • API: The user clicked an Unsubscribe link on a Sailthru Hosted Page.
lists_signup {} Array of lists user is signed up for.
geo.count {} The total number of instances of geo location
geo.city {} Geo object of the cities the user has been identified to be located in and number of instances that the city was recorded for the user
geo.state {} Geo object of the states the user has been identified to be located in and number of instances that the state was recorded for the user
geo.country {} Geo object of the countries the user has been identified to be located in and number of instances that the country was recorded for the user
geo.zip {} Geo object of the ZIP codes the user has been identified to be located in and number of instances that the zip was recorded for the user
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
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
Device or browser recorded upon each email link click. If accessed via desktop, the browser is logged (e.g. chrome). Phones/tablets are logged, if used, and named accordingly.
lists {} Object displaying all active lists of which the user is a memberE.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

template

The template used in the campaign

list_name

List of users this campaign was sent to

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)

link_params

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

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 ID of the campaign. May be used in the blast API. Maps to the id value in the blast export.
opens [] An array of the email opens and open times
opens.ts Timestamp of each email open. Multiple ts values may be present, one for each open.
open_time Timestamp of the first open for this message. Matches the first opens.ts value (and the only opens.ts value, if only one is present).
clicks [] An array of in-email link-click data, including associated URLs and times.
clicks.ts Timestamp of each link click. Multiple ts values may be present, one for each click.
clicks.url URL of the link clicked. multiple URL may be present in the array, representing each click, with the potential for multiple clicks of the same URL (reflected as multiple instances of the same URL).
click_time Timestamp of the first link click for this message. Matches the first clicks.ts value (and the only clicks.ts value, if only one is present).
message_id For the given blast, a unique ID for the message sent to the specific user.
message_revenue Revenue attributed to the email based on the message_id sent with a Purchase API call
device Device which opened / viewed the message
delivery_status Unknown, scheduled, softbounce, hardbounce, or delivered
optout_time Time user opt-ed out from this list / messaging
template The template used in the campaign
list_name Name of the list of users this campaign was sent to
Derived Values
purchases Sent on profileCan 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 [] An array of the email opens and open times
opens.ts Timestamp of each email open. Multiple ts values may be present, one for each open.
open_time Timestamp of the first open for this message. Matches the first opens.ts value (and the only opens.ts value, if only one is present).
clicks [] An array of in-email link-click data, including associated URLs and times.
clicks.ts Timestamp of each link click. Multiple ts values may be present, one for each click.
clicks.url URL of the link clicked. multiple URL may be present in the array, representing each click, with the potential for multiple clicks of the same URL (reflected as multiple instances of the same URL).
click_time Timestamp of the first link click for this message. Matches the first clicks.ts value (and the only clicks.ts value, if only one is present).
message_revenue Revenue attributed to this message based on the message_id sent with a Purchase API call
device Device used to open message
delivery_status unknown, scheduled, softbounce, hardbounce, or delivered
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