Glen Mazza's Weblog 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] 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


SELECT DISTINCT SubscriberKey, EmailAddress
FROM _ListSubscribers

Goes into a DE called LIST_ID_REF:

FROM _ListSubscribers

Goes into a DE called YESTERDAY_JOBS:

SELECT JobID, EmailID, AccountID, FromName
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:


SELECT sub.EmailAddress 
  , 'Sent' AS EventType
  , sent.SubscriberKey
  , sent.EventDate
  , sent.ListID
  , sent.JobID
  , sent.BatchID
  , jobs.FromName
  , jobs.EmailID
  , sent.AccountID
LEFT OUTER JOIN DISTINCT_SUBSCRIBERS sub ON sent.SubscriberKey = sub.SubscriberKey

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
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] 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] 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" : "",
    "numberOfEmployees" : "100",
    "industry" : "Banking"   
    "attributes" : {"type" : "Account", "referenceId" : "ref2"},
    "name" : "SampleAccount2",
    "phone" : "2222222222",
    "website" : "",
    "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] 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] 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="" xmlns:a="" xmlns:u="">
        <a:Action s:mustUnderstand="1">Create</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}</a:To>
        <fueloauth xmlns="">{{dne_etAccessToken}}</fueloauth>
    <s:Body xmlns:xsi="" xmlns:xsd="">
        <CreateRequest xmlns="">
            <Objects xsi:type="Send">
                    <Name>Sample Email Send</Name>
                    <Subject>Sample Test Message</Subject>
                    <HTMLBody>Testing message: %%[ if listid != 1234567 then ]%% Welcome reader!  %%[else]%% Greetings reader! %%[endif]%%</HTMLBody>
                    <TextBody>Welcome Reader! (text only)</TextBody>
                <FromName>Bob Sender</FromName>

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();

    Send send = new Send();

    com.exacttarget.fuelsdk.internal.Email email = new com.exacttarget.fuelsdk.internal.Email();
    email.setName("Sample Email Send via Java");
    email.setSubject("Sample test message subject");
    email.setHtmlBody("<p>Email Body</p>");
    email.setTextBody("Text version of email body");

    // add as many lists as needed
    List listToSendTo = new List();

    // More on EmailSendDefinition:
    EmailSendDefinition emailSendDefinition = new EmailSendDefinition();

    send.setFromName("Bob Sender");


    // configure ETClient similar to here:
    // ETClient etClient = ....
    CreateResponse response = etClient.getSoapConnection().getSoap().create(createRequest);
    if (response != null && "OK".equalsIgnoreCase(response.getOverallStatus())) {
        // success! Check email inbox..."Success sending email w/Request ID {}", response.getRequestID());
    } else {
                .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]

« November 2022
Sun Mon Tue Wed Thu Fri Sat
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
About Blog
Blog software: TightBlog 3.7.2
Application Server: Tomcat
Database: MySQL
Hosted on: Linode
SSL Certificate: Let's Encrypt
Installation Instructions