Glen Mazza's Weblog

https://glenmazza.net/blog/date/20221126 Saturday November 26, 2022

Creating and Using Tracking Extracts

Up until now I've used Automation Studio's Data Extract Activity for exporting data from a data extension into a file for subsequent downloading to an FTP server. The data extensions were populated via SQL Query Activities against data views for Sends, Opens, Clicks, etc. Tracking Extracts provide another variant of Data Extracts that do not require SQL Query Activities and data extensions. With tracking extracts, you select the general data desired and Marketing Cloud will provide a CSV of it in a pre-defined format. They appear to provide a slight superset of the information available in the data views, in particular, the ability to provide User Agent information (browser, OS, device, etc.) for various email actions.

Tracking extracts must be enabled first for your account by Marketing Cloud support. If enabled, you'll see "Tracking Extract" as an option under Extract Type in the Create New Data Extract Activity window.

I've seen two main usages so far for tracking extracts. In one usage, a two-step automation of a Data Extract Activity followed by File Extract Activity in order to move tracking information to an FTP server for subsequent processing by external systems. Cameron Robert provides a simple and clear video explaining that process step-by-step. A second case is for when you wish to move this data back into a data extension for subsequent SQL Query Activity querying and joining with other data views and data extensions. This process is a little more complex, involving the data first being sent to an FTP server and then re-imported back (Import Activity) into a data extension. Genetrix Technology's Donna Redmond has provided an informative video tutorial explaining this process.

Posted by Glen Mazza in Salesforce at 07:00AM Nov 26, 2022 | Tags:  marketingcloud | Comments[0]

https://glenmazza.net/blog/date/20221120 Sunday November 20, 2022

Obtaining Marketing Cloud Sends and Opens Data (List Sends)

Building on my previous article on extracting email clicks data from Marketing Cloud, I'd like next show how to obtain email sends and opens data with Automation Studio, covering both together as they are quite similar. List sends are covered here. For brevity I won't detail how to create Data Extensions (DE's) nor the Data Extract and File Transfer activities as they would be the same as covered in the previous article. The primary focus instead will be the SQL Query Activities for this data.

The implementation below requires a five step automation, with the first three steps consisting of SQL Query Activities and last two being the usual Data Extract and File Transfer activities (one each for opens and for sends). This data primarily relies on the Sends and Opens data views, with some joins to ListSubscribers in order to bring in email addresses and list names.

For the first step, we have five SQL Activities to make, each of which will need to be placed in their own data extensions. I list the names of the data extension used for each query as they will be used in subsequent steps, but you can name them as you wish. The first two queries have the main sends and opens data we care about, with a DATEDIFF to get just the previous day's records, while the others serve as lookup tables. Note the Account ID refers to your business unit's MID, if you have more than one BU you may wish to filter on the ones you wish to receive data from.

Goes into a DE called SENT_YESTERDAY:

SELECT SubscriberKey, EventDate, ListID, JobID, BatchID, AccountID
FROM _Sent
WHERE DATEDIFF(day, EventDate, GetDate()) = 1

Goes into a DE called OPENED_YESTERDAY:

SELECT SubscriberKey, EventDate, ListID, JobID, BatchID, AccountID
FROM _Open
WHERE DATEDIFF(day, EventDate, GetDate()) = 1

Goes into a DE called DISTINCT_SUBSCRIBERS:

SELECT DISTINCT SubscriberKey, EmailAddress
FROM _ListSubscribers

Goes into a DE called LIST_ID_REF:

SELECT DISTINCT ListID, ListName
FROM _ListSubscribers

Goes into a DE called YESTERDAY_JOBS:

SELECT JobID, EmailID, AccountID, FromName
FROM _Job
WHERE DATEDIFF(day, DeliveredTime, GetDate()) <= 15

For the Jobs view above we go back 15 days in case it takes two weeks for someone to open an email.

For the 2nd Step in the Automation we join the SENT_YESTERDAY and DISTINCT_SUBSCRIBERS DE's, and the SENT_YESTERDAY and YESTERDAY_JOBS DE's, in order to bring in the sender and sendee's email addresses. The same thing is done in this step with the OPENS_YESTERDAY DE, omitting below as it is otherwise identical with the Sends join:

Goes into a DE called SENT_WITH_SUBSCRIBER_EMAIL:

SELECT sub.EmailAddress 
  , 'Sent' AS EventType
  , sent.SubscriberKey
  , sent.EventDate
  , sent.ListID
  , sent.JobID
  , sent.BatchID
  , jobs.FromName
  , jobs.EmailID
  , sent.AccountID
FROM SENT_YESTERDAY sent WITH (NOLOCK)
LEFT OUTER JOIN DISTINCT_SUBSCRIBERS sub ON sent.SubscriberKey = sub.SubscriberKey
LEFT OUTER JOIN YESTERDAY_JOBS jobs ON sent.JobID = jobs.JobID

For the 3rd Step, we query the List Name lookup table we created in the first step to bring in the list names for each ID. Again just providing the Sends here, as the Opens one is analogous.

SELECT sent.AccountID 
  , sent.JobID 
  , sent.BatchID
  , sent.SubscriberKey
  , sent.EmailAddress 
  , sent.ListID
  , REPLACE(lists.ListName, ',', '') AS ListName
  , FORMAT(sent.EventDate, 'M/dd/yyyy hh:mm:ss tt') AS EventDate
  , 'Sent' AS EventType
  , REPLACE(sent.FromName, ',', '') AS FromName
  , sent.EmailID
FROM SENT_WITH_SUBSCRIBER_EMAIL sent WITH (NOLOCK)
LEFT JOIN LIST_ID_REF lists ON sent.ListID = lists.ListID

As these files are going into a CSV, the query above strips away commas that might be present in the input to simplify processing downstream. (That said, MC's data extract activity probably can properly escape commas anyway.) This automation can be finished by adding the fourth and fifth steps for the Data Extract and File Transfer activities.

Posted by Glen Mazza in Salesforce at 03:08PM Nov 20, 2022 | Tags:  marketingcloud | Comments[0]

https://glenmazza.net/blog/date/20221118 Friday November 18, 2022

Extracting email interaction data from Marketing Cloud

Salesforce Marketing Cloud's Automation Studio is available for placing email interaction data into CSV files and having those files sent to an external location. Typical interaction data include sends, opens, within-email clicks, and bounces (full list) which are queryable via Data Views provided by MC. Once sent to the FTP server, the files can be downloaded and subsequently fed into other systems for further analysis. In this article, I'll be showing how to export clicks data. (For greater depth, Shibu Abraham offers several videos on various Automation Studio activities.)

Sending any data to the SFTP server usually involves a three-step automation:

To export Clicks data:

  1. An FTP location will need to be set up to receive the extracted files. There are multiple FTP server options supported by MC, but the Enhanced FTP option, using MC's own SFTP server, seems the most common and easiest to set up. Note though it will retain files for only a limited period of time (21 days as of this writing). See the Marketing Cloud Documentation for more information.

  2. Create a data extension (DE) via either Email Studio or Contact Studio with names equal to the columns desired to retrieve from the Clicks view. It is this DE that will be holding the results of the SQL query activity. Unneeded view fields can just be omitted in the data extension definition. For any data extension field that is not guaranteed to have a value per the view definition, be sure to mark as "Nullable".

  3. In Automation Studio, the three activities can be defined first and then attached to a new Automation, or the Automation created first and within the automation the activities created. (Be careful that activities can be shared between automations, so changing the activity definition within one automation will affect it for all automations.) For the SQL Query activity, one SQL query to run for clicks could be: select * from _Click WHERE DATEDIFF(day, EventDate, GetDate()) = 1 to obtain the prior day's clicks. When creating this activity be sure to specify the DE from the previous step to hold the query results. The SQL Query activity allows for specifying how to populate the DE, appending or overwriting, the latter is probably best in this case, so at each daily run there will be an extract of just the prior day's clicks.

  4. Next, in the second step in the automation, create a Data Extract Activity, which generates a file from the prior populated DE and stores it in an MC internal area they call the "Safehouse". Substitution strings can be used to include the run date in the file name, e.g. prior_day_clicks_%%Year%%%%Month%%%%Day%%.csv. Ensure the filename chosen will generate unique filenames for each running of the automation, else the files will be overwritten (e.g., an automation running hourly but just having the day in the name will repeatedly overwrite the file.)

  5. Create a File Transfer Activity in the third step, specifying the Safehouse file name chosen in the previous step and the FTP server to send it to.

  6. Perhaps best to run the SQL query activity first manually to confirm the DE is being populated as desired (its contents can be viewed and exported in Email Studio.) Then, in Automation Studio, do a "run once" of all three steps and view the file on the SFTP server to confirm everything working as expected. Finally, the automation can then be configured to run daily or as otherwise desired, making a new file available on the FTP server each time.

Posted by Glen Mazza in Salesforce at 07:00AM Nov 18, 2022 | Tags:  marketingcloud | Comments[0]

https://glenmazza.net/blog/date/20221114 Monday November 14, 2022

Inserting multiple Salesforce CRM records with a single API call

Salesforce CRM's REST API allows for inserting up to 200 records into Salesforce using just a single API call, a considerable time- and cost-savings over inserting records one-by-one. I've updated my Java Salesforce client to be able to make such calls. Some notes about this process:

  • This multiple insertion process is distinct from Salesforce's Bulk API, an asynchronous method that relies on CSV files that is suggested for data sets of over 2000 records where immediate responses are not necessary.

  • This process is just for record insertions. For other CRUD actions, Salesforce's Composite API can be used, albeit with lower limits (25 requests per query).

Salesforce provides a multi-insert example showing the request and successful (201 Created) response JSON, but is missing the format of error messages specific to these types of calls. I'm providing below the format of the error responses determined while updating the Java client. Given a snippet of the SF example's request body:

{
"records" :[{
    "attributes" : {"type" : "Account", "referenceId" : "ref1"},
    "name" : "SampleAccount1",
    "phone" : "1111111111",
    "website" : "www.salesforce.com",
    "numberOfEmployees" : "100",
    "industry" : "Banking"   
    },{
    "attributes" : {"type" : "Account", "referenceId" : "ref2"},
    "name" : "SampleAccount2",
    "phone" : "2222222222",
    "website" : "www.salesforce2.com",
    "numberOfEmployees" : "250",
    "industry" : "Banking"
    },...
]
}

Each item to be inserted needs an attributes metadata property, specifying the type and a referenceId, the latter of which can be anything but must be unique for each item in the insertion. The reference IDs are used in the response for either providing the Salesforce IDs for successful insertions, or in referring to any errors with that particular record. The attribute type field seems redundant, as the API call made already specifies the type being inserted, but it is nonetheless required for these types of calls.

Generic error response (403) if any reference IDs are missing:

  [
      {
          "message": "Include a reference ID for each record in the request.",
          "errorCode": "INVALID_INPUT"
      }
  ]

Above message will be uncommon so long as the request has referenceIds for every record provided. The more common 400 Bad Request response can occur due to missing attribute types, duplicate reference IDs, as well as validation failures, missing required fields, etc. For 400s, the response body will list the problem records by the referenceId provided in the request, example:

  {
      "hasErrors": true,
      "results": [
          {
              "referenceId": "ref3",
              "errors": [
                  {
                      "statusCode": "INVALID_INPUT",
                      "message": "Duplicate ReferenceId provided in the request.",
                      "fields": []
                  }
              ]
          },
          {
              "referenceId": "ref8",
              "errors": [
                  {
                      "statusCode": "INVALID_INPUT",
                      "message": "Include an entity type for each record in the request.",
                      "fields": []
                  }
              ]
          }
      ]
  }

What is important to note with multiple record insertion is that, if there are any reported problems with any of the items being inserted, none of the records in the request will be inserted (all-or-nothing). One way to handle failures is to make a second request of the same records minus those reported as failures in the prior response (matching on referenceId), to at least get those records inserted. The failed records can instead be logged and analyzed to see what to do with them.

As for making these calls using the Salesforce Client, an included integration test shows the process for inserting multiple rows with one call, and also how to trap and read any 400 exceptions that may occur. The code is fairly the same regardless of which objects are being inserted, however each type of object being inserted will need a MultipleEntityRecord subclass (similar to here for the integration test). The MultipleEntityRecord base class stores the required type and referenceId attributes, while the subclass is to store the fields specific to the object being inserted.

Posted by Glen Mazza in Salesforce at 07:00AM Nov 14, 2022 | Tags:  salesforce  salesforce-crm | Comments[0]

https://glenmazza.net/blog/date/20221111 Friday November 11, 2022

New ETTokenRequestException for FuelSDK fork

I've found in rare instances that access token requests to Marketing Cloud fail with a MC-side 500 system error with little extra detail provided. In such cases the ETClient in the FuelSDK would throw a generic EtSdkException for the client to handle. Our logging has shown that a second access token request immediately thereafter would usually be successful.

To make it easier for FuelSDK clients to trap and recover from this specific error, in my FuelSDK fork I created a new ETTokenRequestException, subclassed from the current ETSdkException, that the ETClient now instead throws. Subclassing from the current exception maintains backwards compatibility for current clients of the library while allowing them to update as they wish.

Posted by Glen Mazza in Salesforce at 06:00AM Nov 11, 2022 | Tags:  fuelsdk  marketingcloud | Comments[0]

https://glenmazza.net/blog/date/20221110 Thursday November 10, 2022

Activating List Sends with Marketing Cloud

This post provides SOAP and Java examples of activating list sends in Marketing Cloud (see my earlier post for the alternative of triggered sends). In this situation, we provide the email contents along with the MC-stored subscriber list(s) to send the email to.

For SOAP, one creates a Send object which wraps (among other values) an Email, EmailSendDefinition and one more more subscriber List objects. Some of the more important values stored at each level:

ObjectInformation to provide within object
SendWrapper for below three objects, also stores the email from-address and from-name.
EmailHTML and text versions of the email, subject line, and character set. See some examples.
ListMC List IDs to send the email to. List IDs are available from MC Email Studio, menu item Subscribers | Lists, selecting the list and viewing its Properties tab.
EmailSendDefinitionWhether or not to use multipart emails, to send de-duplicate (not to send multiple copies to the same email address if the address is on multiple lists that the email is being sent to). The default values, need to provide them, and whether MC actually does anything with certain properties aren't always clear, you will probably need to experiment a bit.

Here's a SOAP example using the MC Postman workspace:

<?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>
        <a:Action s:mustUnderstand="1">Create</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</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">
            <Objects xsi:type="Send">
                <Client>
                    <ID>{{et_mid}}</ID>
                </Client>
                <Email>
                    <Name>Sample Email Send</Name>
                    <IsHTMLPaste>true</IsHTMLPaste>
                    <Subject>Sample Test Message</Subject>
                    <CharacterSet>UTF-8</CharacterSet>
                    <HTMLBody>Testing message: %%[ if listid != 1234567 then ]%% Welcome reader!  %%[else]%% Greetings reader! %%[endif]%%</HTMLBody>
                    <TextBody>Welcome Reader! (text only)</TextBody>
                </Email>
                <List>
                    <ID>1234567</ID>
                </List>
                <List>
                    <ID>2345678</ID>
                </List>
                <EmailSendDefinition>
                </EmailSendDefinition>
                <FromAddress>bobsemail@yopmail.com</FromAddress>
                <FromName>Bob Sender</FromName>
            </Objects>
        </CreateRequest>
    </s:Body>
</s:Envelope>

The "Name" field with value "Sample Email Send" does not appear in the email but is used to help identify a specific email send. It is what is displayed in the Sends section of the Email Studio home page and the also the Tracking section of the list details. It does not have to be unique (sends are identified by a unique Job ID) but making it so helps make sends easier to tell apart from each other.

Sometimes you may wish to adjust the email a bit depending on the list being sent to. In the HTMLBody element of the above example, I've added AMPScript tags showing how this can be done.

For Java, an example using the Fuel SDK is below.

public void sendEmail() {
    CreateRequest createRequest = new CreateRequest();

    CreateOptions createOptions = new CreateOptions();
    createOptions.setRequestType(RequestType.SYNCHRONOUS);
    createOptions.setQueuePriority(Priority.HIGH);
    createRequest.setOptions(createOptions);

    Send send = new Send();

    com.exacttarget.fuelsdk.internal.Email email = new com.exacttarget.fuelsdk.internal.Email();
    email.setName("Sample Email Send via Java");
    email.setEmailType(EmailType.HTML.value());
    email.setIsActive(Boolean.TRUE);
    email.setIsApproved(Boolean.TRUE);
    email.setIsHTMLPaste(Boolean.TRUE);
    email.setSubject("Sample test message subject");
    email.setCharacterSet("UTF-8");
    email.setHtmlBody("<p>Email Body</p>");
    email.setTextBody("Text version of email body");
    send.setEmail(email);

    // add as many lists as needed
    List listToSendTo = new List();
    listToSendTo.setId(1234567);
    send.getList().add(listToSendTo);

    // More on EmailSendDefinition:
    // https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/creating_an_email_send_definition_using_the_web_service_api.html
    EmailSendDefinition emailSendDefinition = new EmailSendDefinition();
    emailSendDefinition.setIsMultipart(isMultipart);
    emailSendDefinition.setDeduplicateByEmail(true);
    send.setEmailSendDefinition(emailSendDefinition);

    send.setFromAddress("bobsemail@yopmail.com");
    send.setFromName("Bob Sender");

    createRequest.getObjects().add(send);

    // configure ETClient similar to here: https://salesforce.stackexchange.com/a/312178
    // ETClient etClient = ....
    CreateResponse response = etClient.getSoapConnection().getSoap().create(createRequest);
    if (response != null && "OK".equalsIgnoreCase(response.getOverallStatus())) {
        // success! Check email inbox...
        LOGGER.info("Success sending email w/Request ID {}", response.getRequestID());
    } else {
        Optional.ofNullable(response)
                .ifPresent(cr -> Optional.ofNullable(cr.getResults())
                        .filter(errorList -> !errorList.isEmpty())
                        .map(errorList -> errorList.get(0))
                        .ifPresent(createResult -> {
                                    LOGGER.error("{}: {}", createResult.getErrorCode(), createResult.getStatusMessage());
                                }
                        )
                );
    }
}

Further Reading

Posted by Glen Mazza in Salesforce at 07:00AM Nov 10, 2022 | Tags:  salesforce  marketingcloud | Comments[0]

https://glenmazza.net/blog/date/20220906 Tuesday September 06, 2022

Activating Triggered Sends with Marketing Cloud

Marketing Cloud has two primary ways of sending an email. One method involves specifying the email content and a Marketing Cloud list. For these list sends, everyone marked as subscribed on the list will receive the email. A second method, demonstrated here, involves Triggered Sends, in which a triggered email interaction ("TEI") defined in Email Studio is specified along with subscriber(s) to receive an email. While the TEI defines the Content Builder template that will be used in sending the email, attributes defined for each subscriber with the triggered send request can be used by the template to personalize emails. Indeed, if the template itself is largely blank, with the contents defined in those attributes, then each email sent can differ radically for each subscriber.

For both SOAP calls and using the FuelSDK wrapper, the Triggered Send wraps a TriggeredSendDefinition primarily used to identify the TEI that will be sending the emails, along with the Subscribers to be receiving an email. TEIs are created and viewable from Email Studio, menu item Interactions -> Triggered Emails. Links at the bottom provide information on creating TEIs as well as the Content Builder templates that they use.

Briefly, for the below SOAP and Java samples, in Content Builder I created first a template and then a template-based email, with the latter importing the former. For the template, this is what I used:

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"><title></title></head>
<body>
  <div style="font-size:0; line-height:0;"><custom name="opencounter" type="tracking"><custom name="usermatch" type="tracking" /></div>
  <div data-type="slot" data-key="t81q1twaz2p" data-label="Drop blocks or content here"></div>
  <custom type="footer" />
</body>
</html>
Screenshot showing an email template being edited.

The data-key above (apparently) refers to a specific "enter HTML" widget visible when editing the Template-based email, as shown in the screenshot above. When editing the latter, click on that widget to be able to enter the email-specific content that you see on the left side of the screenshot:

<ctrl:field name="contentHtml"/>
<hr>
This email was sent to %%emailaddr%% by:<br>
%%Member_Busname%%<br>
%%Member_Addr%%<br>
%%Member_City%%, %%Member_State%% %%Member_PostalCode%%<br>
%%Member_Country%%

The plain text version (for subscribers specifying a preference for it) can be entered by clicking on the "Plain Text" area in the screenshot above. Here is what I used:

<ctrl:field name=contentText />

This email was sent to %%emailaddr%% by:
%%Member_Busname%%
%%Member_Addr%%
%%Member_City%%, %%Member_State%%, %%Member_PostalCode%%
%%Member_Country%%

Using the Marketing Cloud Postman workspace, a Sample SOAP request to send an email to two subscribers using a specified TEI would be as follows:

<?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>
        <a:Action s:mustUnderstand="1">Create</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</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">
            <Objects xsi:type="TriggeredSend">
                <Client>
                    <ID>{{et_mid}}</ID>
                </Client>
                <TriggeredSendDefinition>
                    <CustomerKey>external-id-of-TEI</CustomerKey>
                </TriggeredSendDefinition>
                <Subscribers>
                    <SubscriberKey>...one subscriber key...</SubscriberKey>
                    <Attributes>
                        <Name>subject</Name>
                        <Value>Greetings 4!</Value>
                    </Attributes>
                    <Attributes>
                        <Name>contentHtml</Name>
                        <Value>This is &lt;strong>HTML&lt;strong>!</Value>
                    </Attributes>
                    <Attributes>
                        <Name>contentText</Name>
                        <Value>This is plaintext.</Value>
                    </Attributes>
                </Subscribers>
                <Subscribers>
                    <SubscriberKey>...another subscriber key...</SubscriberKey>
                    <Attributes>
                        <Name>subject</Name>
                        <Value>Another email 4</Value>
                    </Attributes>
                    <Attributes>
                        <Name>contentHtml</Name>
                        <Value>This is &lt;em>HTML&lt;em>!</Value>
                    </Attributes>
                    <Attributes>
                        <Name>contentText</Name>
                        <Value>This is in plaintext.</Value>
                    </Attributes>
                </Subscribers>
            </Objects>
        </CreateRequest>
    </s:Body>
</s:Envelope>

Note the attributes attached to the subscriber ("contentHtml", "contentText", etc.) will need to match the attributes used by the TEI's configured email template. For an email to be sent, all attributes referenced by the template must be present for each subscriber in the triggered send.

If there are obvious errors with the SOAP request (for example, not providing any subscribers), the SOAP response coming back normally will identify the problem for you. But if the request appears solid, you'll get the following response that the triggered send was enqueued:

<soap:Body>
    <CreateResponse xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <Results xsi:type="TriggeredSendCreateResult">
            <StatusCode>OK</StatusCode>
            <StatusMessage>Created TriggeredSend</StatusMessage>
            <OrdinalID>0</OrdinalID>
            <NewID>0</NewID>
        </Results>
        <RequestID>...some UUID...</RequestID>
        <OverallStatus>OK</OverallStatus>
    </CreateResponse>
</soap:Body>

This does not guarantee a successful sending of an email, however. From Email Studio, menu item Interactions | Triggered Emails shows completed, queued, and errored counts for each triggered email interaction. If the intended recipient is not getting an email while the error counts for the triggered email interaction keep increasing with each attempt, opening a support ticket with Marketing Cloud, providing them the RequestID above for one of the responses will allow them to search their logs to see what the error is and report back to you.

In Java using the FuelSDK: Java programming involves putting TriggeredSend object(s) into a CreateRequest. The triggered send objects are populated in a similar manner as direct SOAP requests. Sample snippet:

TriggeredSend triggeredSend = new TriggeredSend();
TriggeredSendDefinition tsDef = new TriggeredSendDefinition();
tsDef.setCustomerKey("...external ID of TEI...");
triggeredSend.setTriggeredSendDefinition(tsDef);

Subscriber subscriber = new Subscriber();
subscriber.setSubscriberKey("...subscriber key...");
addAttribute(subscriber, "subject", "email subject line");
addAttribute(subscriber, "contentHtml", "<b>html</b> here");
addAttribute(subscriber, "contentText", "plain text here");
triggeredSend.getSubscribers().add(subscriber);

// Add more subscribers to triggeredSend as desired

CreateOptions options = new CreateOptions();
options.setRequestType(asyncSendsEnabled ? RequestType.ASYNCHRONOUS : RequestType.SYNCHRONOUS);
options.setQueuePriority(Priority.MEDIUM);

CreateRequest request = new CreateRequest();
request.setOptions(options);
request.getObjects().add(triggeredSend);

// configure ETClient similar to here: https://salesforce.stackexchange.com/a/312178
// ETClient etClient = ....
CreateResponse response = etClient.getSoapConnection().getSoap().create(request);
if (!response.getOverallStatus().equalsIgnoreCase("OK")) {
    // success
}

The addAttribute(...) above is just a convenience method:

private void addAttribute(Subscriber s, String name, String value) {
    Attribute a = new Attribute();
    a.setName(name);
    a.setValue(value);
    s.getAttributes().add(a);
}

Further reading:

  1. Official Marketing Cloud documentation on the non-API portions of this process: Create a Triggered Email Message Interaction and Create a Content Builder Email.

  2. Introduction to Triggered Sends - Nice overview of entire process by Zuzanna Jarczynska, which also includes another option of sending a Triggered Email, here using AmpScript on a Cloud Page.

  3. Out of scope for this tutorial, but see here for adding a custom tag to your email template to enable tracking (How many subscribers opened the email, etc.)

Posted by Glen Mazza in Salesforce at 07:00AM Sep 06, 2022 | Tags:  emails  marketingcloud | Comments[0]

https://glenmazza.net/blog/date/20220828 Sunday August 28, 2022

Using Marketing Cloud's SOAP endpoint to manage subscriptions

Within Marketing Cloud's Automation Studio, SQL Query activities on the ListSubscribers view can be run to determine subscription information for users and lists. For SOAP calls, and programming languages using them, we work with ListSubscriber and SubscriberList types defined in the MC SOAP WSDL for querying and updating, respectively. This entry provides some examples of querying and updating, both via direct SOAP calls using MC's Postman workspace and programmatically with the FuelSDK-Java (see here for initialization code not covered in the code snippets below.)

How to query a user's subscriptions: The below SOAP request provides options on querying user subscription information by subscriberKey and optionally on subscription status (Active or Unsubscribed). The filters equals and IN are shown but there are several others, see the WSDL above (search on SimpleOperator) to see the full list.

<?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>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>ListSubscriber</ObjectType>
                <Properties>SubscriberKey</Properties>
                <Properties>ListID</Properties>
                <Properties>Status</Properties>
                <Filter xsi:type="SimpleFilterPart">
                    <Property>SubscriberKey</Property>
                    <SimpleOperator>IN</SimpleOperator>
                    <Value>...subscriberKey1...</Value>
                    <Value>...subscriberKey2...</Value>
                    <Value>...subscriberKey3...</Value>
                </Filter>
                <!--Filter xsi:type="ComplexFilterPart">
                    <LeftOperand xsi:type="SimpleFilterPart">
                        <Property>SubscriberKey</Property>
                        <SimpleOperator>equals</SimpleOperator>
                        <Value>...subscriberKey1...</Value>
                    </LeftOperand>
                    <LogicalOperator>AND</LogicalOperator>
                    <RightOperand xsi:type="SimpleFilterPart">
                        <Property>Status</Property>
                        <SimpleOperator>equals</SimpleOperator>
                        <Value>Active</Value>
                    </RightOperand>
                </Filter-->
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

Sample output:

<Results xsi:type="ListSubscriber">
    <PartnerKey xsi:nil="true" />
    <ObjectID xsi:nil="true" />
    <Status>Unsubscribed</Status>
    <ListID>2552723</ListID>
    <SubscriberKey>...subscriberKey1...</SubscriberKey>
</Results>
<Results xsi:type="ListSubscriber">
    <PartnerKey xsi:nil="true" />
    <ObjectID xsi:nil="true" />
    <Status>Active</Status>
    <ListID>2591367</ListID>
    <SubscriberKey>...subscriberKey2...</SubscriberKey>
</Results>

If querying by subscriber it is much faster to query many at once using the IN clause as shown above instead of making separate API calls for each subscriber. (I've found 50 at a time works well, I did not test larger amounts.) Most of the overhead involves making a request and receiving the response, with each extra user queried adding only a minor amount to the total response time.

A similar query, this time using Java and the Fuel SDK:

public RetrieveRequestMsg buildActiveListRequestMsg(String subscriberKey) {
    RetrieveRequest retrieveRequest = new RetrieveRequest();

    retrieveRequest.setObjectType("ListSubscriber");
    retrieveRequest.getProperties().add("SubscriberKey");
    retrieveRequest.getProperties().add("ListID");
    retrieveRequest.getProperties().add("Status");

    retrieveRequest.setFilter(ETUtils.composeFilter(
            ETUtils.composeFilter("SubscriberKey", SimpleOperators.EQUALS, subscriberKey),
            LogicalOperators.AND,
            ETUtils.composeFilter("Status", SimpleOperators.EQUALS, "Active")
    ));

    RetrieveRequestMsg retrieveRequestMsg = new RetrieveRequestMsg();
    retrieveRequestMsg.setRetrieveRequest(retrieveRequest);

    return retrieveRequestMsg;
}

private static FilterPart composeFilter(FilterPart leftPredicate, LogicalOperators operator, FilterPart rightPredicate) {
    ComplexFilterPart filter = new ComplexFilterPart();
    filter.setLeftOperand(leftPredicate);
    filter.setLogicalOperator(operator);
    filter.setRightOperand(rightPredicate);
    return filter;
}

Subsequent processing to get the subscriber's active subscriptions:

RetrieveResponseMsg response = etClient.getSoapConnection().getSoap().retrieve(buildActiveListRequestMsg("subscriberKey1"));
if ("OK".equalsIgnoreCase(retrieveResponseMsg.getOverallStatus())) {
    List results = retrieveResponseMsg.getResults();
    List listIDs = results.stream()
            .map(apiObject -> ((ListSubscriber) apiObject).getListID())
            .collect(Collectors.toList());
}

How to update a user's subscriptions: The MC documentation provides several examples of working with the SubscriberList object for subscribing and unsubscribing users. Note there are two ways to unsubscribe a user from a list: marking the user Unsubscribed, which keeps an entry for the user on the subscription list with an unsubscribed date, and just deleting the user from the list. Marketing Cloud generally recommends the former approach. For list maintenance, we specify a user by subscriber key, and a series of lists. For each list, the List ID, an action (create, update, upsert, delete) and sometimes a status (Active or Unsubscribed) is provided. The delete action does not take a status, and if status is otherwise omitted, it is treated as Active if the user is not already on the list or keeps the status as-is if the user is. Sample Postman query:

<?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>
        <a:Action s:mustUnderstand="1">Create</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <UpdateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <Objects xsi:type="Subscriber">
                <SubscriberKey>...subscriberkey1...</SubscriberKey>
                <Lists>
                    <ID>2716501</ID>
                    <Action>upsert</Action>
                </Lists>
            </Objects>
            <Objects xsi:type="Subscriber">
                <SubscriberKey>...subscriberkey1...</SubscriberKey>
                <Lists>
                    <ID>2712867</ID>
                    <Action>delete</Action>
                </Lists>
            </Objects>
            <Objects xsi:type="Subscriber">
                <SubscriberKey>...subscriberkey2...</SubscriberKey>
                <Lists>
                    <ID>2712867</ID>
                    <Status>Active</Status>
                    <Action>upsert</Action>
                </Lists>
                <Lists>
                    <ID>2716962</ID>
                    <Status>Active</Status>
                    <Action>upsert</Action>
                </Lists>
            </Objects>
        </UpdateRequest>
    </s:Body>
</s:Envelope>

Some notes for the above request:

  • Multiple lists can be updated for a single user by either creating one Objects block per list, as with the SubscriberKey1 user, or by having multiple Lists under a single Objects block as done with the SubscriberKey2 user. For the latter case, if an error would occur with any single list, testing is showing that none of the updates will occur under that Objects block, and furthermore the error response will specify the problem but not which of the subscription changes is causing it.
  • Multiple users cannot be specified under a single Objects block, each user needs its own Objects block(s).
  • Using an action of upsert for subscribes (status = Active) and unsubscribes (status = Unsubscribed) are very unlikely to throw exceptions, providing the list ID and the user's subscriber key exists. This makes upserts a better option for many-lists-in-one-Objects-block changes. (The upsert creates a record if it does not exist, but updates it otherwise.)
  • If you'd like exceptions if you're adding someone to a list who is already there, or making someone unsubscribed who is not subscribed presently, instead of upsert use create or update.
  • Common error codes: SubscriberNotFound (12001), ListNotFound (13000), OnListAlready (13006) (for a create of a user of whatever status who is already on the list), NotOnList (13007) (for an update of a user not on the list).
  • If for an upsert you don't provide a status for the user, if the user is not on the list he will be added with status of Active. Otherwise, no change for the user (will remain Active or Unsubscribed as before.) Providing you don't delete users from lists but just mark them as Unsubscribed, this will allow you to add users to a list while respecting past unsubscribes.

In Java using the FuelSDK, subscription changes are handled via an UpdateRequest. Here, we take Subscriber object(s) in which we identify each user by his SubscriberKey, and then populate each Subscriber object's lists attribute with SubscriberList objects, with each object containing a List ID, action (create, update, delete, upsert, with the default apparently being upsert) and optionally status (Active, Unsubscribed), subject to the same rules given above for the direct SOAP calls. Sample adding and unsubscribing lists from a given Subscriber:

import com.exacttarget.fuelsdk.internal.Subscriber;
import com.exacttarget.fuelsdk.internal.SubscriberList;
import com.exacttarget.fuelsdk.internal.SubscriberStatus;
import com.exacttarget.fuelsdk.internal.UpdateOptions;
import com.exacttarget.fuelsdk.internal.UpdateRequest;
import com.exacttarget.fuelsdk.internal.UpdateResponse;
import com.exacttarget.fuelsdk.ETSdkException;

class BulkSubscriptionUpdate {
    private Subscriber subscriber;
    private final Set subscribes;
    private final Set unsubscribes;
    
    ....
}

public UpdateResponse updateSubscriptions(BulkSubscriptionUpdate bulkSubscriptionUpdate) throws ETSubscriberServiceException {

    UpdateRequest updateRequest = new UpdateRequest();
    UpdateOptions options = new UpdateOptions();
    options.setQueuePriority(Priority.HIGH);
    options.setRequestType(RequestType.SYNCHRONOUS);
    updateRequest.setOptions(options);

    Subscriber subscriber = new Subscriber();
    subscriber.setSubscriberKey(generateSubscriberKey(user.getId()));

    List subscriptions = new ArrayList<>();
    if (!CollectionUtils.isEmpty(bulkSubscriptionUpdate.getSubscribes())) {
        for (Integer etid : bulkSubscriptionUpdate.getSubscribes()) {
            subscriptions.add(buildSubscriptionUpdate(etid, SubscriberStatus.ACTIVE));
        }
    }
    if (!CollectionUtils.isEmpty(bulkSubscriptionUpdate.getUnsubscribes())) {
        for (Integer etid : bulkSubscriptionUpdate.getUnsubscribes()) {
            subscriptions.add(buildSubscriptionUpdate(etid, SubscriberStatus.UNSUBSCRIBED));
        }
    }

    if (!CollectionUtils.isEmpty(subscriptions)) {
        subscriber.getLists().addAll(subscriptions);
        updateRequest.getObjects().add(subscriber);

        try {
            return update(updateRequest);
        } catch (ETSdkException e) {
            LOGGER.error("Failed to update subscriptions for subscriber {}", bulkSubscriptionUpdate.getSubscriber(), e);
            throw e;
        }
    }
    
    // nothing to update
    return null;
}

private static SubscriberList buildSubscriptionUpdate(Integer etid, SubscriberStatus status) {
    SubscriberList subscription = new SubscriberList();
    subscription.setId(etid);
    subscription.setStatus(status);
    subscription.setAction("upsert");
    return subscription;
}

Posted by Glen Mazza in Salesforce at 07:00AM Aug 28, 2022 | Tags:  marketingcloud  salesforce | Comments[0]


Calendar
« April 2023
Sun Mon Tue Wed Thu Fri Sat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Today
About Me
Java Software Engineer
TightBlog project maintainer
Arlington, Virginia USA
glen.mazza at pm dot me
GitHub profile for Glen Mazza at Stack Overflow, Q&A for professional and enthusiast programmers
Blog Search


Blog article index
Navigation
About Blog
Blog software: TightBlog 3.7.2
Application Server: Tomcat
Database: MySQL
Hosted on: Linode
SSL Certificate: Let's Encrypt
Installation Instructions