Retrieve tracking data since account inception using WSProxy

Whether you’re building a data warehouse or are dealing with a legal compliance issue, access to historical tracking data from your Salesforce Marketing Cloud account using the out-of-the-box features is limited.

Data Views are powerful, but only allow you to query for up to six months of subscriber and tracking information.

Tracking extracts provide granular tracking data regarding several different aspects of email send jobs, such as clicks, bounces and unsubscribes. They are not time-restricted like Data Views, but if you want to get tracking data from a specific time range, the start and end dates can only be up to 30 days apart.

The third option to access historical data is to reach out to Salesforce support and ask them for a data export for a specific time range, but this service will come at a hefty price.

The method of retrieving tracking data using SOAP API and WSProxy also has some limitations, mostly related to performance when working with big data volumes, but it can be very useful in many cases where using Data Views or Tracking Extracts is not enough.

Retrieve Tracking Data with SOAP API

To retrieve tracking data using the SOAP API, we will call the SentEvent, OpenEvent, ClickEvent, BounceEvent and UnsubEvent objects, which will allow us to create a structure similar to the one we know from Data Views. Each of those objects has a persistent set of properties, which include: BatchID, ClientID, EventDate, SendID, SubscriberKey and TriggeredSendDefinitionObjectID.

Here is an example SOAP envelope that you could use in Postman to retrieve data from any of the tracking event objects:

Interact with SOAP API using WSProxy

To retrieve data from the tracking event objects using WSProxy, we will have to specify the properties to retrieve and perform the retrieve on each of the objects we are interested in. Here’s an example of retrieving data from the SentEvent object:

We can also add a filter to narrow down the results. You can filter the results by JobID, SubscriberKey or any other object property. Here, we are going to filter by EventDate, which will show us all the events that took place in the given timeframe:

If you run the above script in an account that sends large volumes of emails, you will notice that it only pulls a part of the results and displays a status message: MoreDataAvailable. That’s because the SOAP API returns up to 2500 records at a time per retrieve call. To retrieve all available data for a given timeframe, we need to include pagination of retrieves in our script. Let’s also add an HTML table, so that data is displayed in a more accessible way:

You can do the same with the remaining objects, here are the scripts:

Write retrieved data into a Data Extension

Although this will increase the processing time of the call, it will allow you to have the data ready for further processing without any additional preparations. Below script will create a new Data Extension called 01_SentEvent in your main Data Extensions folder and insert all the retrieved data.

Here are the scripts that will do the same for the remaining objects:

Performance of the retrieve calls

The official Marketing Cloud SOAP API documentation states the following:

In most production implementations, data volume in the account requires you to include specific JobIDs in the filter criteria of the request. If requests that don’t specify a JobID time out during processing, add a JobID in the filter. If specifying a JobID is not possible, or if your implementation requires a broad range of JobIDs, use a data extract-based procedure instead.

Above is true if you are sending heavy volumes of emails each month, but you are not limited to filtering by JobID. You can include any of the object properties in filter criteria. You can, for example, retrieve all send and tracking history for a specific subscriber if you use the SubscriberKey in the filter: filter = {Property: "SubscriberKey", SimpleOperator: "equals", Value: "{{SubscriberKey}}"}

If you’re experiencing time outs when working with the script, try narrowing down the amount of retrieved data by using filters.

Security

Last but not least, always remember to put security measures into practice when setting up this kind of functionality on a CloudPage to prevent your data from being exposed in a breach. For the script to run and either return results onscreen or insert them into a Data Extension, you don’t have to actually publish the CloudPage – just press “Publish”, without further saving it. Or, to be completely safe – create a Script Activity in Automation Studio and run it from there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s