Subscribe to Automation Studio notifications via SOAP API

If you have used Automation Studio to build workflows on autopilot, you are certainly aware of the importance of monitoring. Checking manually the execution status of every automation in your account is tedious and exhausting. Sometimes, you need to be notified in real time so you can fix eventual issues. That is why having email notifications enabled on your automations is necessary.

There are two types of notifications to which you can subscribe on automation settings:

  • Error or skipped run: you will receive an email if the automation’s execution fails or is skipped
  • Run Completion: you will receive an email once the automation’s run completes.

Keep in mind that at this moment, the only official way of interacting with Automation Studio via API is through SOAP. I’m saying “official” because there are actually some undocumented, but very limited, REST endpoints that can be used to play with Automation Studio. Let me know in the comments if you are interested.

Therefore, if we check the official SOAP documentation, we will see that creating an automation along with activities and email notifications in its settings is pretty easy and straightforward. However, if we look closer, we will find a comment on the notification tag saying: DOES NOT WORK. But we are not going to accept this for an answer, aren’t we?

This is the SOAP request sample from the documentation to create an automation: [see code snippet on Github]

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<soapenv:Header>
<wsse:Security soapenv:mustUnderstand="1">
<wsse:UsernameToken>
<wsse:Username>username</wsse:Username>
<wsse:Password>password</wsse:Password>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body>
<CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
<Options/>
<Objects xsi:type="Automation">
<Client>
<ID>1234567</ID>
</Client>
<Name>AUT_ONEOFF</Name>
<CustomerKey>AUT_ONEOFF</CustomerKey>
<Description>AUT_ONEOFF</Description>
<!–<CategoryID>74052</CategoryID>–>
<!–<Notifications> DOES NOT WORK
<Notification>
<Address>swhitmore@salesforce.com</Address>
<NotificationType>-1</NotificationType>
</Notification>
</Notifications>–>
<AutomationTasks>
<AutomationTask>
<PartnerKey xsi:nil="true"/>
<ObjectID xsi:nil="true"/>
<Name>Task 1</Name>
<Activities>
<Activity>
<PartnerKey xsi:nil="true"/>
<ObjectID>c7ccca24-4567-4317-b8d0-3874d69c130c</ObjectID><!– ObjectID of Query –>
<Name>Query 1</Name><!– Name of Activity –>
<!–<Definition>
<PartnerKey xsi:nil="true"/>
<ObjectID xsi:nil="true"/>
</Definition>–>
<ActivityObject xsi:type="QueryDefinition">
<PartnerKey xsi:nil="true"/>
<ObjectID>c7ccca24-4567-4317-b8d0-3874d69c130c</ObjectID><!– ObjectID of Query –>
<CustomerKey>546012e7-4438-4526-9e6c-5ec73939848b</CustomerKey><!– CustomerKey of Query –>
<Name>BrowseAbandon_US_Unica_Sendable_Audience_Approved</Name><!– Name of Query –>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
</AutomationTasks>
<AutomationType>scheduled</AutomationType>
</Objects>
</CreateRequest>
</soapenv:Body>
</soapenv:Envelope>

To find the source of the problem, we should get into the tags responsible for adding email notifications to the settings. In this example, they are using a wrong value for the NotificationType tag in addition to a missing ChannelType tag.

NotificationType should be either “Complete” OR “Error” and definitely not “-1”. And ChannelType should be equal to 1.

The correct SOAP request to create an automation along with a “Complete” notification email should look like this: [see code snippet on Github]

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<s:Header>
<fueloauth xmlns="http://exacttarget.com">{{access_token}}</fueloauth>
</s:Header>
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
<Options/>
<Objects xsi:type="Automation">
<Client>
<ID>50000xxxx</ID>
</Client>
<Name>AUT_ONEOFF4</Name>
<CustomerKey>AUT_ONEOFF4</CustomerKey>
<Description>AUT_ONEOFF4</Description>
<!–<CategoryID>74052</CategoryID>–>
<Notifications>
<Notification>
<ChannelType>1</ChannelType>
<NotificationType>Complete</NotificationType>
<Address>rachid.mamai@xxx.com</Address>
</Notification>
</Notifications>
<AutomationType>scheduled</AutomationType>
</Objects>
</CreateRequest>
</s:Body>
</s:Envelope>

For the curious ones, you may be asking how I found the working values. I invite you to go to Automation Studio, open the Chrome Developer Tools (F12) and click on the Network tab.

Open an automation, add your email to the settings and click done. Now go check out the Network tab. There is a REST call to the endpoint below.

Given the fact that we can only interact with Automation Studio using SOAP calls for now, we can understand why the endpoint is beta and not officially documented:

https://mc.s50.exacttarget.com/rest/beta/automations/notifications/AUTOMATION_ID/

Now let us look at the request payload:

We can see that the values used for NotificationType and ChannelType are as described above. That was worth a try and it worked: [see code snippet on Github]

{
"programId": "TnJZZGdzNjVjRW1zX0lhVjhrNkxPxxxxxx",
"workers": [
{
"body": "",
"channelType": "1",
"programId": "TnJZZGdzNjVjRW1zX0lhVjhrNkxPxxxxxx",
"definition": "rachid.mamai@xxxx.com",
"notificationType": "Complete"
},
{
"channelType": "1",
"programId": "TnJZZGdzNjVjRW1zX0lhVjhrNkxPxxxxxx",
"definition": "rachid.mamai@xxxx.com",
"notificationType": "Error"
}
]
}

I invite you to check the network tab every time you need to interact with Salesforce Marketing Cloud APIs. It can be very interesting and full of undocumented information.

If you know someone that can correct this on the official documentation, please get in touch through the comments or send me a message on LinkedIn.


About the author

Rachid Mamai is a SFMC geek and a Digital Marketing enthusiast living in France. To get in touch with Rachid, visit his LinkedIn.

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