Exception handling in Salesforce Marketing Cloud emails

“Hope for the best and prepare for the worst” should be every marketer’s motto when it comes to creating contextual personalized content. Your company’s reputation could be easily tarnished if you send out buggy emails, that’s why exception handling should be an essential element of your email strategy.

In this article, you will learn about some of Salesforce Marketing Cloud’s build-in features and best practices that will help you maintain high quality of content that goes out to your subscribers.

Fallback values in emails

When it comes to creating personalized emails, we often hear Salesforce Marketing Cloud users assume that the data that comes through will be correct and that they are not going to include fallback values for their variables. The assumption should be exactly the opposite and you should always plan for bad or missing data!

If you’re using a Profile Attribute or a Data Extension column for personalizing an email, the best practice is to always leverage the Empty() AMPscript function to check whether there is a value available.

Here’s an example: it’s much easier to use a simple personalization string such as Dear %%FirstName%%, in your email, but if for some reason the data is missing, your subscriber would see Dear , in the email they receive. This can be easily prevented by using the Empty() function and adding a fallback value: [see code snippet]

In the above example, a Profile Attribute or a Data Extension column will be used if there’s a value available, and if not, the greeting in the email will be set to Dear Valued Customer.

Whether you’re using Lists or Data Extensions to segment the data in Salesforce Marketing Cloud, you can also leverage the “Default Value” feature to ensure that required data is always available:

Profile Attribute with a default value
Data Extension field with a default value

Using the RaiseError function in emails

The RaiseError() function, used along with a conditional statement, ensures that the email does not get sent if there is bad or missing data in the dataset used for personalizing the email. Depending on how you set the function, it can either skip the send for a current subscriber and move on to next subscriber, or it can stop the remaining send job. Here’s a summary of the function’s main features in Eliot Harper’s video:

RaiseError AMPscript Function in Salesforce Marketing Cloud

Let’s take a look at the function syntax and properties:

RaiseError(1, 2, 3, 4, 5)

OrdinalTypeRequiredDescription
1stringNError message to display
2booleanYIndicates whether function skips send for current subscriber and continues or stops. A value of true skips the send for current subscriber and moves to next subscriber. A value of false stops the send and returns an error. Function defaults to false.
3stringNAPI error code (set by the user)
4stringNAPI error number (set by the user)
5booleanNIndicates whether the function records information to data extensions before error occurs, even if the process skips the subscriber. A value of 1 retains information written to data extensions before the error occurs, even if the subscriber is skipped. A value of 0 does not retain information recorded before the error. This parameter refers to inserted, updated, upserted, or deleted information via AMPscript.

Let’s use the previous script example with the FirstName personalization, but instead of setting a fallback value in case the FirstName is missing, we will use the RaiseError() function to skip the send for that particular subscriber: [see code snippet]

You will notice, that upon previewing the email, the error message that you set will be displayed if the FirstName value is missing:

In a scenario where you would try to send this email to a subscriber with a missing FirstName value, the function would prevent from sending an email to the current subscriber and would move on to the next one.

This function should not be misused for data segmentation. Even though a subscriber is skipped or the whole job is cancelled, it still counts towards your totals as a sent message, meaning that you will pay for each suppressed email as you would for a sent one. This is a small price to pay for handling exceptions and sustaining a positive reputation with your subscribers, but it should not replace regular data cleansing and maintenance processes.

An additional thing to keep in mind is that AMPscript is interpreted top-down. This means, that if there are any other AMPscript functions before the RaiseError() function, they will all be executed, even if the send for a particular subscriber is skipped in the end.

On the other hand, if you have set the RaiseError() function to cancel the remaining send job, then any Update, Insert, and Delete AMPscript functions, which are processed in bulk during send time, will not be executed, unless you specify otherwise by setting the last of the RaiseError() properties to 1.

Bear in mind, that the second property of the RaiseError() function, which indicates whether function skips send for the current subscriber and continues or stops, will default to false if not specified. This means, that unless set to true, it will always stop the remaining send job.

Logging RaiseError results

By default, the emails and jobs suppressed by the RaiseError() function will only be visible in the NotSent tracking extract. If you’re using this function in Journey Builder emails or Triggered Sends, you will also be able to see how many emails were suppressed by checking the “Errored” column in Email Studio’s Tracking tab, under Journey Builder Sends/Triggered Sends. Unfortunately, you won’t be able to view any details of the suppressed sends in the UI.

In order to log all suppressed sends, you can create a Data Extension and add an InsertDE function to the email. Make sure to set the last property of the RaiseError() function to 1 and to include the InsertDE() function before the RaiseError() function, as everything placed after won’t get executed. This way, each email suppressed by the RaiseError() function, will also be logged in the Data Extension: [see code snippet]

Analyzing the log will help you investigate why some of the values are missing and improve your company’s data hygiene.


Questions? Comments?

Leave a comment below or email me at zuzanna@sfmarketing.cloud.

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