How To Export Email Tracking Data From ExactTarget Salesforce Marketing Cloud
Tapping into the services cloud email marketing platform for advanced analytics efforts
Salesforce Marketing Cloud is used for sales, customer service, digital marketing automation, loyalty, and CRM efforts. Data resident in Salesforce Marketing Cloud email marketing contains rich insights into digital marketing activities.
This data is part of a puzzle that allows you the ability to understand the customer journey. The better you can understand this journey the greater the opportunity to deepen customer engagement using data insights.
The Salesforce Marketing Cloud email data provides an insights backbone to the customer relationship management software. If you are a Salesforce Marketing Cloud Email customer and spending time with marketing automation then you really should be tapping into this data!
This article describes the steps to export tracking data from Salesforce Marketing Cloud: Email Studio, marketing software to an external data warehouse via SFTP.
You can also set up custom data extensions extracts in addition to the default tracking extracts. Why SFTP? There are limits to using the ExactTarget API for exporting bulk data from the ExactTarget marketing cloud. As a result, we suggest using the bulk export capability to support any external ExactTarget analytics efforts.
What Salesforce ExactTarget Extracts Are Available?
Here are the types of ExactTarget email extracts that are available:
- Attributes: The attributes tracking extract output file contains the attributes associated with subscribers in an account.
- Bounces: The bounce event extract file shows all the hard and soft bounces occurring within a specified date range. The events in this report relate to all sends for subscribers on a list or related to a particular send.
- ClickImpression: The click event extract file shows all the instances of a clicked link in an email occurring within the specified date range. The file also includes the impression region name associated with the click event. The events in this report can relate to all sends for subscribers on a list or related to a particular send.
- Clicks: The click event extract file shows all instances of a clicked link in an email occurring within the specified date range. The events in this report relate to all sends for subscribers on a list or related to a particular send.
- Conversions: The conversions file contains a row for each conversion event associated with a subscriber related to an email send job.
- ListMembershipChanges: The ListMembershipChanges file includes rows that contain information on the subscriber, list, and status data change for every event.
- Lists: The lists file contains rows including name, description, and other information for a unique list.
- NotSent: This list contains information on send jobs and messages that did not send.
- Opens: An Open event extract file shows open instances of an HTML email during the date range. The open events relate to all sends for subscribers on a list or to a particular send.
- SendImpression: The SendImpression event extract file shows instances of an email send occurring during a specified date range. The file also includes the impression region name associated with the click event. The send events relate to all sends for subscribers on a list or to a particular send.
- SendJobImpression: The SendJobImpression file represents all impression tracking information from sends with activity logged against them for the timeframe of the extract. This list reflects only jobs that already processed. Sends scheduled for the future do not appear in this list.
- SendJobs: The SendJobs file represents all sends with activity logged against them for the timeframe of the extract. This list reflects only jobs that already processed. Sends scheduled for the future do not appear in this list.
- Sent: The Sent event extract file shows instances of an email send occurring during a specified date range. The send events relate to all sends for subscribers on a list or to a particular send.
- Spam Complaints: The Extract Spam Complaints extract file shows subscriber, list, and domain data, including sendID and date, for subscribers who received an email and reported it as spam.
- StatusChanges: The StatusChanges file shows instances of change in a subscriber’s status.
- Subscribers: The ExactTarget “All” subscribers file contains subscribers with a tracking event occurring during a specified date range.
- Surveys: The Survey file contains information on responses to surveys included in email sends.
- Unsubs: The Unsubs file includes information on unsubs that occurred during a specified date range. The unsubscribe events can be related to all sends for subscribers on a list or to a particular send.
Step 1. Setup File Location in Email Studio
A file location is the SFTP location where you want to deliver your tracking export files. To do this you need to go into Salesforce Marketing Cloud Email Studio:
Once in Email Studio, you go to Admin -> Data Management -> File Locations
Within File Locations enter the setup details of your External SFTP Site delivery location:
You will need to set up a dedicated File Location for each Tracking extract. For example, in the URL of the setup, you enter the name of the tracking extract being sent. In this case, we are exporting sends so you would use pipeline-01.openbridge.io/salesforce_sends/
If you were export opens then you would create a new File Location for opens you would use pipeline-01.openbridge.io/salesforce_opens/
You would do the same for the Name field. Use something description like Openbridge SFTP Server Sent to help you identify which extract this location is set up for.
The Username and Password would be provided to you by Openbridge so check with your account team for that.
Here are a couple of examples that show a location for sent and open tracking extracts. Notice the different locations for URL:
Depending on the number of exports, you will likely have a collection of file locations for each tracking extract you want to deliver:
This structure ensures that the data is properly organized and can be processed, routed, and loaded correctly into your warehouse. If are dumping a “mix-n-match” set of data to single location schemas and data types will be different resulting in data failing to load.
Once complete with each location, select Save.
Step 2: Define Tracking Exports In Automation Studio
Step out of Email Studio and go to journey builder ExactTarget -> Automation Studio
The first step is in Automation Studio is to define extract and file transfer “Activities”. Think of them as the tasks you need to have performed.
In Automation Studio select Activities
Then select Create Activity
Create a Data Extract Activity
You will be presented with a few different options. The first step will be to define a data extract from Salesforce. Select Data Extract
You will be presented with a screen where you define the properties of the Data Extract activity.
Select Next
You will want to name this specific to the type of data being extracted. You will also want to set up the filename pattern in a manner that is unique to the export and the data:
%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%_send.zip
Select Next
You now get to define your extract
For Extract Type, we are using Tracking Extracts. Normally you will use a Rolling Range of 1 Day. This would reflect a daily push of all the prior days' transactions from Salesforce.
Make sure you set the character encoding to UTF-8 and the delimiter to the comma. The format should be set to CSV. Since we want the sent emails we need to select the Extract Sent option. To be safe, always select Quote Text. This will help ensure your comma delimiters are set for more complex use cases.
Select Next
Double-check your work. Look good? Great, you have just defined a Data Extract activity! Move onto the next step.
Select Finish
Create Data File Transfer Activity
The next step is to create a File Transfer activity. This describes the mechanics of transferring extracts to the File Location you defined in Step 1.
Click Create Activity
You will be presented with a modal similar to the last step. This time you will select File Transfer
Select Next
In this step you need to define the naming pattern of for the delivery similar to the name used in the extract:
%%Year%%%%Month%%%%Day%%%%Hour%%%%Minute%%_send.zip
Also, this is where you select the File Location for your sends that you created in Step 1.
Select Next
Everything looks good, right?
Select Finish
Step 3: Create Tracking Export Automation
Automation defines a process that executes one or more activities. This is the last step in configuring your exports.
Instead of clicking Activities, click Overview
Then click Automation
You will be presented with a modal. You will want to select Schedule
You will be presented with a workflow tool. This is where you configure the activities you created into a workflow Salesforce can execute
The two activities we are using are Data Extract and File Transfer
Drag one of each to the workflow window. The first should be the Data Extract and then the File Transfer. These will be empty templates. You need to choose the activity you want to run for each.
Select Choose
Select the Data Extract activity you defined for sends.
Select Done
Next, you link the File Transfer activity.
Select Choose
Select the File Transfer activity you defined for sends.
Select Done
Great, both are now configured!
The last step is to define a schedule. This describes when the Automation should be run. Since we want this to run daily we set the start date and tell it to repeat daily at 5 AM.
Once you complete the schedule, Success! Your extract will now be sent every day at 5 AM to be loaded into your warehouse.
Did you want to add more extracts? Repeat Steps 1 and 2 for each extract. Then add them to your automation.
That’s it! Your export data should be flowing. You can use the insights to create new customer experiences in a journey builder, refine social media strategies, or round out your cross-channel analytics efforts.
Code-free, fully automated Salesforce Marketing Cloud Email Data Pipelines
The Openbridge integrations provide a code-free, fully automated connector.
- The connector is designed for automating data feeds that fuel sales and BI tools like; Looker, Tableau, Power BI, Amazon Quicksight, and many others.
- The connector will route and load data into your private, secure data lake or cloud data warehouse like BigQuery, AWS Athena, Azure Data Lake, AWS Redshift, or Redshift Spectrum, and Snowflake.
No need to hire a developer or build your own integrations. By using a pre-built, fully-managed Salesforce connector, you can increase efficiency, reduce labor requirements, and improve response time to customers.
Get Started Today
D Want to discuss how Salesforce Marketing Cloud data for your organization? Need a platform and team of experts to kickstart your data and analytic efforts? We can help! Getting traction adopting new technologies, especially if it means your team is working in different and unfamiliar ways, can be a roadblock for success. This is especially true in a self-service only world. If you want to discuss a proof-of-concept, pilot, project or any other effort, the Openbridge platform and team of data experts are ready to help.
Reach out to us at hello@openbridge.com. Prefer to talk to someone? Set up a call with our team of data experts.
Visit us at www.openbridge.com to learn how we are helping other companies with their data efforts.
