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.

Data Views in Salesforce Marketing Cloud

Data Views are a powerful feature of Salesforce Marketing Cloud. They store subscriber information and the last six months of tracking data for your account. While they cannot be accessed or viewed from the UI, they can be used like any other data extension: you can write a query against a data view in Automation Studio or in the new Query Studio and populate the results into a new data extension. You can join them with each other and with other data extensions. You can also reference them in AMPscript, for example in a Lookup function. Remember, that in order to be able to use Data Views, you first need to ask support to enable them in your account.

Data Views provide the data and metrics that can help you drive your analytics efforts to the top. Understanding how they relate to each other is key to getting the maximum value from your Marketing Cloud data. The official Data Views documentation provides details about (almost) all available Data Views, table names, fields names and data types. The below visualization shows how the most important of the tables are related to each other (click on the image to enlarge it or download a high-quality PDF here).

Data Views in SQL queries

If you’re looking for some example SQL queries for use with Data Views, visit the SQL Reference. You can basically join any of the email-related Data Views, as all of them have SubscriberKey, SubscriberID and JobID fields. Here’s an example:

Unengaged subscribers (no opens or clicks) in the last 7 days

Here’s an example of a very generic query, that will pull all the tracking data from Data Views into one table. All you need to do, is to create a Data Extension with the following fields:

SubscriberKey
EmailName
SentDate
OpenDate
ClickDate
BounceDate
BounceCategory
UnsubscribeDate

and run the following query:

In order to combine the data about your subscribers kept in the Profile Attributes with any of the Data Views, you will need to join them on SubscriberID with the EnterpriseAttribute data view. If you are running your query outside the parent business unit, it has to be used with the ENT. prefix. Here’s an example of a query that will pull all subscribers who opened an email in the last 7 days and their gender, stored in a Profile Attribute, is “Female”:

It’s a good idea to mirror Data Views in your account if you need access to more than the last 6 months of tracking data. This can be easily achieved in Automation Studio, where you can create a scheduled automation to regularly run SQL queries on Data Views and populate results into a data extension, here’s an example: Track Email Status logs using Data Views.

Data Views in AMPscript

If you want to use Data Views in AMPscript, you can reference them like any other data extension, for example:

Hidden Data Views

As mentioned in the beginning, almost all Data Views are documented, but there are at least four, which you won’t find in the official documentation. Two of them hold data related to Mobile Connect and you can access them by referencing _MobileAddress and _MobileSubscription. The other two hold data related to Mobile Push and their names are _PushAddress and _PushTag. Below is a full list of their attributes, courtesy of the Salesforce Stack Exchange community:

_MobileAddress

 Field Name       Data Type Required Data Default 
 _ContactID       Text                Y                            
 _MobileNumber    Text(15)            Y                            
 _Status          Text                N                            
 _Source          Text                N                            
 _SourceObjectId  Text(200)           N                            
 _Priority        Text                N              1
 _Channel         Text(20)            N                            
 _CarrierID       Text                Y              0
 _CountryCode     Text(2)             Y                            
 _CreatedDate     Date                Y               GETDATE()    
 _CreatedBy       Text                N                            
 _ModifiedDate    Date                Y               GETDATE()    
 _ModifiedBy      Text                N                            
 _City            Text(200)           N                            
 _State           Text(200)           N                            
 _ZipCode         Text(20)            N                            
 _FirstName       Text(100)           N                            
 _LastName        Text(100)           N                            
 _UTCOffset       Decimal(4,2)        N              0
 _IsHonorDST      Boolean             N               false        

_MobileSubscription

 Field Name                 Data Type RequiredData Default 
 _SubscriptionDefinitionID  Text(200)           Y                            
 _MobileNumber              Text(15)            Y                            
 _OptOutStatusID            Text                N                            
 _OptOutMethodID            Text                N                            
 _OptOutDate                Date                N                            
 _OptInStatusID             Text                Y                            
 _OptInMethodID             Text                N                            
 _OptInDate                 Date                N                            
 _Source                    Text                N                            
 _CreatedDate               Date                Y               GETDATE()    
 _CreatedBy                 Text                N                            
 _ModifiedDate              Date                Y               GETDATE()    
 _ModifiedBy                Text                N                            

_PushAddress

 Field Name        Data Type Required  Data Default 
 _ContactID        Text                Y                            
 _DeviceID         Text(200)           Y                            
 _APID             Text(38)            Y                            
 _Status           Text                N                            
 _Source           Text                N                            
 _SourceObjectId   Text(200)           N                            
 _Platform         Text(100)           N                            
 _PlatformVersion  Text(100)           N                            
 _Alias            Text(100)           N                            
 _OptOutStatusID   Text                N                            
 _OptOutMethodID   Text                N                            
 _OptOutDate       Date                N                            
 _OptInStatusID    Text                Y              0
 _OptInMethodID    Text                N                            
 _OptInDate        Date                N                            
 _Channel          Text(20)            N                            
 _CreatedDate      Date                Y               GETDATE()    
 _CreatedBy        Text                N                            
 _ModifiedDate     Date                Y               GETDATE()    
 _ModifiedBy       Text                N                            
 _City             Text(200)           N                            
 _State            Text(200)           N                            
 _ZipCode          Text(20)            N                            
 _FirstName        Text(100)           N                            
 _LastName         Text(100)           N                            
 _UTCOffset        Decimal(4,2)        N              0
 _IsHonorDST       Boolean             N               false        
 _SystemToken      Text(4000)          N                            
 _ProviderToken    Text(200)           N                            
 _Badge            Number              N                            
 _LocationEnabled  Boolean             N                            
 _TimeZone         Text(50)            N                            
 _Device           Text(100)           N                            
 _HardwareId       Text(100)           N                            
 _DeviceType       Text(20)            N                            

_PushTag

 Field Name     Data Type Required Data Default 
 _DeviceID      Text(200)           Y                            
 _APID          Text(38)            Y                            
 _Value         Text(128)           N                            
 _CreatedDate   Date                Y               GETDATE()    
 _CreatedBy     Text                N                            
 _ModifiedDate  Date                Y               GETDATE()    
 _ModifiedBy    Text                N                            

Sources: https://salesforce.stackexchange.com/questions/73127/how-to-bulk-export-mobileconnect-sms-subscriptions, https://salesforce.stackexchange.com/questions/174179/attributes-in-pushaddress-and-pushtag-data-views/174180#174180

If you’re looking for more information about Data Views and SQL queries in Marketing Cloud, check out the official documentation, topics related to Data Views on Salesforce Stack Exchange and Adam’s post about Troubleshooting Queries in SFMC.

Query Studio for Salesforce Marketing Cloud

The ability to write and automate SQL queries in Automation Studio is an indispensable feature when it comes to working with data in Salesforce Marketing Cloud. The current SQL query editor in Automation Studio is very rudimentary and makes it almost impossible to debug SQL queries. It can be very time consuming and frustrating to work on more complicated queries as each time your run your query, you need to go to Data Extensions to check the results.

Salesforce has decided to make things a bit easier for Marketing Cloud users and launched a new app last month called Query Studio for Marketing Cloud. The app allows you to quickly run queries with a similar experience to SQL Server Studio or MySQL workbench.

You can get Query Studio for Marketing Cloud for free from Salesforce AppExchange and install in a couple of clicks. After you install the app, you need to log out and log back in, to be able to access it from the App Exchange menu:

The user interface of Query Studio is quite simple:

  • From the Query search box at the top, you can choose an existing query that you wish to run, or you can write your own query in the query editor window.
  • Click the Run button or use the keyboard shortcut CTRL + Enter to run your query – you will see a timer next to the Run button, showing you how long the query takes to execute.
  • You can also validate your query to check if query results can be inserted into target data extension – once you execute your query, choose a target data extension from the Data Extension search box, choose Data Action and click on Validate Data.

The validation function, according to Salesforce, will cross check for required fields, field lengths, and mismatched field data types. I ran a few tests and made a few mistakes on purpose to see how the validation feature works. It’s nice to see a description of the validation error in the results, but if you have more than one error, you will have to correct them one by one, as the validation tool only displays the first error it encounters:

Unfortunately, the Query Studio is still missing a debugging feature, which would show syntax errors in your query:

It is worth mentioning that Query Studio creates a new folder in your Data Extensions folder, called “QueryStudioResults”:

QueryStudioResults data extension folder will be created in your account to hold temporary data extensions, which have data retention set to auto-delete after 24 hours. Each time you run a query in Query Studio, a new Data Extension is created.

Every Query Studio user will also have a query activity created in Automation Studio for them, which is used for running one-off queries. It will contain ‘InteractiveQuery’ in the name:

Overall, the new Query Studio is great for developing new queries, when you want to see the results on-screen and be able to test and amend your queries quickly. I would say that this is a satisfying minimum viable product, which will hopefully evolve into a full-on SQL Console with proper debugging support in the future.

Read more and install here: https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FP3yFUAT

Related article: Data Views in Salesforce Marketing Cloud