Saturday April 29, 2023
Creating Clicks and Bounces extract files
Salesforce Marketing Cloud's Automation Studio allows for placing email interaction data into CSV files and having those files sent to an SFTP server for external access. Earlier covered were opens and sends, now I'd like to document extracting clicks and bounces into separate CSV's. As before, objects will be created using SOAP and REST calls, most easily using the Marketing Cloud Postman collection. There will still be a little bit of work needed in Automation Studio, namely plugging in the activities into the automation "shell" created below, also, configuring the scheduling and email notifications for the automation.
This is a three-step automation, with the first being SQL query activities, followed by separate steps for data extracts and file transfers. For this example, the bounces extract does more joins to get more fields than the clicks extract, but the latter can be expanded the same way by adjusting its data extension and SQL query. Note these queries reference the "common" job, subscriber, and list data extensions given in the first script of the sends and opens tutorial, so they will need to be created before the below scripts are used. Further, the SQL query activities that populate the common data extensions should run either in this automation, prior to the given first step, or another that runs before this one, so the common data extensions will be filled and up-to-date.
The first file contains a single SOAP request that creates the clicks and bounces data extensions, SQL query definitions, and the automation shell:
<?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="Automation">
<Name>Clicks and Bounces Extracts Automation</Name>
<CustomerKey>clicks_bounces_atmn_key</CustomerKey>
<Description>Automation to create extracts for bounces and clicks. Run at least 30 mins
after the common task as the latter's tables are needed in this task.
</Description>
<AutomationTasks>
<AutomationTask>
<Name>Query clicks and bounces data</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with cb_s1a_clicks_yesterday_sq</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with cb_s1b_bounces_yesterday_sq</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Create files, place in SFMC safehouse</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="DataExtractActivity">
<Name>Replace with cb_s2a_clicks_data_extract</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="DataExtractActivity">
<Name>Replace with cb_s2b_bounces_data_extract</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Move files from safehouse to SFMC SFTP server</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="FileTransferActivity">
<Name>Replace with cb_s3a_clicks_file_transfer</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="FileTransferActivity">
<Name>Replace with cb_s3b_bounces_file_transfer</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
</AutomationTasks>
<AutomationType>scheduled</AutomationType>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>cb_s1a_clicks_yesterday_de</Name>
<CustomerKey>cb_s1a_clicks_yesterday_de_key</CustomerKey>
<Fields>
<Field>
<Name>SubscriberID</Name>
<IsRequired>true</IsRequired>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>256</MaxLength>
<IsRequired>true</IsRequired>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailAddress</Name>
<FieldType>EmailAddress</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<IsRequired>true</IsRequired>
<FieldType>Date</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<IsRequired>true</IsRequired>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>LinkContent</Name>
<MaxLength>4000</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>URL</Name>
<MaxLength>900</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>LinkName</Name>
<MaxLength>1024</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>Domain</Name>
<MaxLength>128</MaxLength>
<IsRequired>true</IsRequired>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>TriggererSendDefinitionObjectID</Name>
<MaxLength>256</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<IsRequired>true</IsRequired>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<IsRequired>true</IsRequired>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>TriggeredSendCustomerKey</Name>
<MaxLength>36</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>IsUnique</Name>
<IsRequired>true</IsRequired>
<FieldType>Boolean</FieldType>
</Field>
<Field>
<Name>AccountID</Name>
<IsRequired>true</IsRequired>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>OYBAccountID</Name>
<FieldType>Number</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>cb_s1a_clicks_yesterday_sq</Name>
<CustomerKey>cb_s1a_clicks_yesterday_sq_key</CustomerKey>
<QueryText>
select c.LinkContent, c.TriggererSendDefinitionObjectID, c.SubscriberID, c.JobID,
c.URL, c.BatchID, c.LinkName, c.Domain, c.ListID, c.EventDate,
c.SubscriberKey, sub.EmailAddress, c.TriggeredSendCustomerKey, c.IsUnique,
c.AccountID, c.OYBAccountID
from _Click c
LEFT OUTER JOIN common_subscriber_table_de sub ON c.SubscriberKey = sub.SubscriberKey
WHERE c.EventDate >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND c.EventDate < DATEADD(DAY, 0, CAST(GETDATE() AS DATE))
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>cb_s1a_clicks_yesterday_de</Name>
<CustomerKey>cb_s1a_clicks_yesterday_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>cb_s1b_bounces_yesterday_de</Name>
<CustomerKey>cb_s1b_bounces_yesterday_de_key</CustomerKey>
<Fields>
<Field>
<Name>EventType</Name>
<MaxLength>10</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<MaxLength>50</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailAddress</Name>
<FieldType>EmailAddress</FieldType>
</Field>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>320</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>FromName</Name>
<MaxLength>250</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListName</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>ListType</Name>
<MaxLength>20</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>BounceType</Name>
<MaxLength>30</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>BounceCategory</Name>
<MaxLength>30</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>BounceSubcategory</Name>
<MaxLength>30</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>SMTPCode</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>Domain</Name>
<MaxLength>250</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>AccountID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>EmailID</Name>
<FieldType>Number</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>cb_s1b_bounces_yesterday_sq</Name>
<CustomerKey>cb_s1b_bounces_yesterday_sq_key</CustomerKey>
<QueryText>
SELECT FORMAT(b.EventDate, 'M/dd/yyyy hh:mm:ss tt') AS EventDate,
b.BounceType, b.BounceCategory, b.BounceSubcategory,
b.SMTPCode, b.Domain, sub.EmailAddress, b.SubscriberKey, b.ListID,
lists.ListName, 'List' as listType,
{{et_mid}} as AccountID, b.JobID, b.BatchID,
'Bounces' AS EventType, jobs.FromName, jobs.EmailID
FROM _Bounce b
LEFT OUTER JOIN common_subscriber_table_de sub ON b.SubscriberKey = sub.SubscriberKey
LEFT OUTER JOIN common_job_table_de jobs ON b.JobID = jobs.JobID
LEFT JOIN common_list_table_de lists ON b.ListID = lists.ListID
WHERE b.EventDate >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND EventDate < DATEADD(DAY, 0, CAST(GETDATE() AS DATE))
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>cb_s1b_bounces_yesterday_de</Name>
<CustomerKey>cb_s1b_bounces_yesterday_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
</Objects>
</CreateRequest>
</s:Body>
</s:Envelope>
The second file consists of four separate REST calls, to create the two data extract and two file transfer activities. For the latter, see here on setting the needed fileTransferLocationId environment variable.
POST calls to https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/dataextracts:
{
"name": "cb_s2a_clicks_data_extract",
"key": "cb_s2a_clicks_data_extract_key",
"description": "Data extract for yesterday's clicks",
"dataExtractTypeId": "bb94a04d-9632-4623-be47-daabc3f588a6",
"fileSpec": "clicks_extract_%%Year%%%%Month%%%%Day%%.csv",
"dataFields": [
{
"name": "ColumnDelimiter",
"type": "string",
"value": ","
},
{
"name": "DECustomerKey",
"type": "string",
"value": "cb_s1a_clicks_yesterday_de_key"
},
{
"name": "HasColumnHeaders",
"type": "bool",
"value": "True"
}
]
}
{
"name": "cb_s2b_bounces_data_extract",
"key": "cb_s2b_bounces_data_extract_key",
"dataExtractTypeId": "bb94a04d-9632-4623-be47-daabc3f588a6",
"description": "Data extract for yesterday's bounces",
"fileSpec": "bounces_extract_%%Year%%%%Month%%%%Day%%.csv",
"dataFields": [
{
"name": "ColumnDelimiter",
"type": "string",
"value": ","
},
{
"name": "DECustomerKey",
"type": "string",
"value": "cb_s1b_bounces_yesterday_de_key"
},
{
"name": "HasColumnHeaders",
"type": "bool",
"value": "True"
}
]
}
POST calls to https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/filetransfers:
(ensure fileTransferLocationId is defined in the SFMC Postman environment variables)
{
"name": "cb_s3a_clicks_file_transfer",
"customerKey": "cb_s3a_clicks_file_transfer_key",
"description": "File transfer for clicks data",
"fileTransferLocationId": "{{fileTransferLocationId}}",
"fileSpec": "clicks_extract_%%Year%%%%Month%%%%Day%%.csv",
"isFileSpecLocalized" : true,
"isUpload" : true
}
{
"name": "cb_s3b_bounces_file_transfer",
"customerKey": "cb_s3b_bounces_file_transfer_key",
"description": "File transfer for bounces data",
"fileTransferLocationId": "{{fileTransferLocationId}}",
"fileSpec": "bounces_extract_%%Year%%%%Month%%%%Day%%.csv",
"isFileSpecLocalized" : true,
"isUpload" : true
}
Once the objects are created, go into Automation Studio and bring up the automation "shell" and plug in the activities as given in that automation. Save and do a "run once", if all looks good, then you're ready to schedule regularly. Again, make sure though that the common data extensions referenced are also getting refreshed so the joins are accurate.
Notes:
MC's SFTP server, used in the file transfers, retains files for only a limited period of time (21 days as of this writing), so download what you will need to keep for a longer period. See the Marketing Cloud Documentation for more information.
For greater depth, Shibu Abraham offers several videos on various Automation Studio activities.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 29, 2023 | Comments[0]
Wednesday April 26, 2023
Creating an automation (shell) using Marketing Cloud API
Automations in Marketing Cloud consist of one or more activities placed in one or more steps, with all activities of one step activated simultaneously and with no proceeding to the next step until they have all completed. They also include scheduling and notification information. Activities are defined independently to be plugged into one or more automations, changing the activity's definition holds for all automations it is included in.
For activities, I've earlier provided examples of making SOAP requests to create both SQL query activities and the data extensions they fill, and REST calls to create data extract and file transfer activities. Finally, another entry shows creating an Import activity from a file on SFTP back to a data extension in SFMC. Both articles have seen been updated to include an Automation "shell" as described below.
Even without an API to create the automation holding and ordering these activities, the bulk of the work is done when the activities are created. One still has to figure out the steps and the activities that go into each one, however, requiring documentation for same. Unfortunately, when creating an automation via SOAP request, SFMC does not allow for specifying the needed activities via customer key, something known at the time of creating the automation, and something that remains the same across business units, allowing for reusable SOAP requests. (I've requested them to do so.) Instead, one must specify the (internal) object ID of the activity, which is known only after the objects are created and is different for each business unit.
However, SFMC thankfully meets us halfway. With a create automation SOAP request, one can still define the steps and types of activities needed in each step, and temporarily provide, as the activity name, instructions to the human for the actual activity to manually plug into the automation with Automation Studio. This saves manual documentation of same. The automation's name, customer key, and description can be provided and kept equivalent for all BUs the automation is deployed in.
A sample SOAP request creating an automation "shell" (from one of the two updated blog articles mentioned previously):
<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="Automation">
<Name>User Agent Info Automation</Name>
<CustomerKey>user_agent_atmn_key</CustomerKey>
<Description>Obtain user agent info for opens
</Description>
<AutomationTasks>
<AutomationTask>
<Name>Create extract for usage agent info</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="DataExtractActivity">
<Name>Replace with s1_user_agent_info_extract</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>File transfer of user agent info to SFTP server</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="FileTransferActivity">
<Name>Replace with s2_user_agent_info_transfer</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Uncompress into opens.csv on SFTP server</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="FileTransferActivity">
<Name>Replace with s3_user_agent_info_unzip</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Import user agent info back into data extension</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="ImportDefinition">
<Name>Replace with s4_user_agent_info_import</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
</AutomationTasks>
<AutomationType>scheduled</AutomationType>
</Objects>
</CreateRequest>
</s:Body>
</s:Envelope>
Once the shell and the activities have been created via API, it's a rather simple matter to hit "Replace" on each of the steps making up the automation and choose the activity reported on the step, and then test and schedule the automation as usual.
The REST endpoints for automations I believe fall into the "undocumented" (i.e., unsupported) category. In attempting to create an automation via REST, I saw the same shortcomings as this 2019 Salesforce Stack Exchange posting. However, as shown in that posting, retrieving an automation feature provides some value, showing the scheduling information as well as row counts in the data extensions that the automation's SQL activities use.
Notes
To obtain the ObjectID given an automation's customer key, one needs to request the
ProgramIDproperty, requesting the usualObjectIDdoes not work properly:<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI"> <RetrieveRequest> <ObjectType>Automation</ObjectType> <Properties>ProgramID</Properties> <Properties>Name</Properties> <Properties>Description</Properties> <Properties>CustomerKey</Properties> <Properties>IsActive</Properties> <Filter xsi:type="SimpleFilterPart"> <Property>CustomerKey</Property> <SimpleOperator>equals</SimpleOperator> <Value>user_agent_atmn_key</Value> </Filter> </RetrieveRequest> </RetrieveRequestMsg>To run, above snippet can be put within the body of the SOAP request given for the automation above.
Once you obtain the Object ID for an automation, you can further drill down to obtain information on the steps of an automation and activities with the steps, by querying the
TaskandActivityobjects respectively, with this filter:<Filter xsi:type="SimpleFilterPart"> <Property>Program.ObjectID</Property> <SimpleOperator>equals</SimpleOperator> <Value>...uuid of automation...</Value> </Filter>To find properties you can request for an object, including the
Automation,Task, andActivityobjects, make a DefinitionRequestMsg call and in the response check for properties with anisRetrievablevalue oftrue.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 26, 2023 | Comments[0]
Saturday April 22, 2023
Marketing Cloud API calls to retrieve all SQL Queries and data extensions
The following SOAP request to the Salesforce Marketing Cloud API, run within SFMC's Postman Collection, will retrieve, in a single SOAP response, all SQL Query Activities in your business unit, including the query text and the data extensions they load:
<?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>QueryDefinition</ObjectType>
<Properties>Name</Properties>
<Properties>TargetUpdateType</Properties>
<Properties>Description</Properties>
<Properties>CustomerKey</Properties>
<Properties>QueryText</Properties>
<Properties>Status</Properties>
<Properties>DataExtensionTarget.Name</Properties>
<Properties>DataExtensionTarget.CustomerKey</Properties>
<Filter xsi:type="SimpleFilterPart">
<Property>Status</Property>
<SimpleOperator>equals</SimpleOperator>
<!--SimpleOperator>notEquals</SimpleOperator-->
<Value>Active</Value>
</Filter>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>
By filtering on active/not active, you can also retrieve deleted SQL queries. SFMC apparently does a "soft delete" for at least some period of time after a SQL activity is removed, helpful if you need to retrieve something accidentally discarded.
With all the data in a single text-searchable SOAP response, it becomes much easier to efficiently match SQL queries to the data extensions they read from or write to. It can help identify activities missing descriptions or readable customer keys. Using SOAP also allows you to avoid the one-by-one inspecting in the Automation Studio UI with all its annoying confirmation and warning boxes.
The query below retrieves all data extensions in your business unit. Crossing-out those referenced by the SQL queries above can help identify data extensions perhaps no longer needed. Of course, many data extensions are read from and/or written to outside of SQL queries (such as API calls, internal SFMC processing, and SFMC add-ons), so further investigation beyond non-usage in a query activity would be needed before deleting a data extension.
<?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>DataExtension</ObjectType>
<Properties>ObjectID</Properties>
<Properties>CustomerKey</Properties>
<Properties>Description</Properties>
<Properties>Name</Properties>
<Properties>IsSendable</Properties>
<Properties>SendableSubscriberField.Name</Properties>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 22, 2023 | Comments[0]
Friday April 21, 2023
Using Content Builder templates with Marketing Cloud sender profiles
With the announced retirement of Classic Content (CC) in Marketing Cloud, in favor of Content Builder (CB), our project identified sender profiles that needed updating from CC to CB. For our triggered sends, to the subscriber we would add fromEmailName and fromEmailAddress attributes representing the email sender information. Then we would create simple and separate Classic Content templates to use these attributes:
FromEmailNameTemplate: <ctrl:field name=fromEmailName /> FromEmailAddressTemplate: <ctrl:field name=fromEmailAddress />
Then when creating a sender profile, under "Sender", "Use the specified information" section, we could activate the above templates as follows:
From Name: %%=ContentAreaByName("FromEmailNameTemplate")=%%
From Email: %%=ContentAreaByName("FromEmailAddressTemplate")=%%
With Content Builder, the template contents are the same, but the template you use is apparently important, as not all types will work. Template type Code Snippet worked for us. Further, within the sender profile definition, there are new functions to use: ContentBlockByName, ContentBlockByKey, and ContentBlockById. They are each defined in the AmpScript Guide. ContentBlockByKey, which uses the customer key of the template, seems to be the easiest way to identify the desired snippet. Note that the keys must be unique enterprise-wide, not just at the business unit level. If you use the by-name method, make sure you specify the path properly.
Sender profile configuration afterwards, using Content Builder:
From Name: %%=ContentBlockByKey("CodeSnippetFromNameKey")=%%
From Email: %%=ContentBlockByKey("CodeSnippetFromAddressKey")=%%
Reminder that SFMC advises to provide a verified fallback address in the Sender Profile in case there are problems with the address returned by the AMPscript snippets.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 21, 2023 | Comments[0]
Wednesday April 19, 2023
FuelSDK-Java fork available
Updated April 2023.
At work we use a fork of Marketing Cloud's FuelSDK, generally for keeping its libraries up-to-date as Salesforce doesn't often do so for its main branch. The fork is using the latest CXF 3.5.x branch as of this writing. Other enhancements:
JDK 11 base, above the Java 6 in the main fork.
Gradle build, with help from Francisco Mateo's cxf-codegen-gradle plugin.
The Partner API WSDL has been updated. This file is used by CXF to generate Java classes supporting the SOAP requests and responses. The main Salesforce branch is still relying on a copy of the WSDL downloaded in 2017. A comparison of the two WSDLs is showing quite a few new and modified request and response objects since then:
ClientID: CustomerID TaskResult: TblAsyncID SaveOption: TrackChanges AccountUser: IsSendable APIObject: __AdditionalEmailAttribute 1-5. Attribute: no longer inheriting APIObject SubscriberResult: ErrorCodeID TriggeredSendClassEnum TriggeredSendSubClassEnum SenderProfile: FallbackFromAddress deleted: MessagingConfiguration ChatMessagingEventType SalesforceSendActivity ImportDefinition: HasMultipleFiles ImportResultsSummary: NumberRestricted JsonWebKey DirectoryTenant AuditLogUserContext AutomationActivity: SerializedObject AttributeEntityV1 Thumbnail NameIdReference CategorynameIdReference UserBasicsEntity AssetAnyProperty Asset Category ScheduledRequest ScheduledConversation
I found necessary to make two adjustments to the WSDL copied into my fork, as detailed on the project README and commented in the WSDL. For one of them--a mismatch between what the WSDL claims the SOAP response will be for a particular call and what it actually is, causing a validation exception with CXF--I've sent a help ticket to Marketing Cloud requesting they update their WSDL. This particular issue has been around a long time, apparently, as even the 2017 WSDL needed this manual adjustment.
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 the 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.
The FuelSDK's ETClient now has a close() method to explicitly release various CXF objects prior to making a new connection. While DataDog or another memory profiler can tell you whether that is actually necessary, I've found no harm in calling it.
The fork has been running fine on production for several months now, we're using it to register and update subscribers and send emails, and it is providing us peace of mind that we're using the latest libraries while doing so.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 19, 2023 | Comments[0]
Monday April 17, 2023
Obtaining Marketing Cloud Sends and Opens Data
The steps below provide a manner of obtaining email sends and opens data from Marketing Cloud and placing this information as separate CSV files on MC's SFTP server. Almost all necessary objects (data extensions, SQL query activities, data extracts and file transfers) are created by SOAP and REST calls, see this Salesforce Developers' blog entry for using Postman to easily make them. Further, an automation "shell" listing each of the tasks needed in each step will be created, but some simple and quick manual work in Automation Studio will still be required, namely to hit "replace" on each of the (empty) task boxes and select the automation specified on the task box:
The source data for this information comes from several MC data views joined together. Good to review the columns available in each view to see what extra data beyond the sample here you'd like to have added to the output CSVs, and adjust the data extensions and SQL queries accordingly:
| Data View | Purpose |
|---|---|
| Job | Associate the EmailID (i.e., a specific email being sent) with one or more Job IDs sending it |
| Send | Associate a subscriber's SubscriberKey with the send date, the list ID, and the job and batch that sent the email. |
| Open | Same purpose as Send view, except for opens |
| List Subscriber | Provides more user-readable information, by getting the email address for the SubscriberKey, and a list name for a list ID. Separate "List" and "Subscriber" data views would be more efficient for this purpose, but Marketing Cloud doesn't offer the former and the latter is infrequently available in all business units. |
The first three steps of this automation (objects prefixed by "s1", "s2", "s3") involving loading of data extensions, the fourth a data extract to place files in the Safehouse (SFMC internal storage), and the fifth a file transfer to the SFTP server. Note the third step, just for opens, accesses the user agent info data extension created in this blog entry, so you may wish to follow that tutorial first, or can just remove the step from the automation if this data is unneeded.
Two SOAP requests are used to create the objects in the first step, one in which some "common" data extensions are created, and a second for Sends and Opens specifically. The common data extensions provide lists of subscribers, lists and email send jobs, if they are part of the first automation for the day, depending on your data freshness needs they can be used as-is for the other automations for the day. (The automation shell, which can go in any SOAP request, I've placed in the first one.)
<?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="Automation">
<Name>List Send Extracts Automation</Name>
<CustomerKey>list_stats_extracts_atmn_key</CustomerKey>
<Description>Places list sends and opens data on SFTP server.
</Description>
<AutomationTasks>
<AutomationTask>
<Name>Query base opens, sends, and lookup data for subsequent joins</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with common_list_table_sq</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with common_subscriber_table_sq</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with common_job_table_sq</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with stats_s1a_sent_yesterday_sq</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with stats_s1b_opened_yesterday_sq</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>All joins necessary for sends, opens partially done</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with stats_s2a_merged_sends_sq</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with stats_s2b_opens_subs_jobs_sq</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Join opens with user agent info</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="QueryDefinition">
<Name>Replace with stats_s3a_merged_opens_sq</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Create files, place on SFMC safehouse</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="DataExtractActivity">
<Name>Replace with stats_s4a_sends_data_extract</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="DataExtractActivity">
<Name>Replace with stats_s4b_opens_data_extract</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
<AutomationTask>
<Name>Move files from safehouse to SFMC SFTP server</Name>
<Activities>
<Activity>
<ActivityObject xsi:type="FileTransferActivity">
<Name>Replace with stats_s5a_sends_file_transfer</Name>
</ActivityObject>
</Activity>
<Activity>
<ActivityObject xsi:type="FileTransferActivity">
<Name>Replace with stats_s5b_opens_file_transfer</Name>
</ActivityObject>
</Activity>
</Activities>
</AutomationTask>
</AutomationTasks>
<AutomationType>scheduled</AutomationType>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>common_list_table_de</Name>
<CustomerKey>common_list_table_de_key</CustomerKey>
<CategoryID>{{common_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>ListID</Name>
<IsRequired>true</IsRequired>
<IsPrimaryKey>true</IsPrimaryKey>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>ListName</Name>
<MaxLength>254</MaxLength>
<IsRequired>true</IsRequired>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>NumSubscribers</Name>
<IsRequired>true</IsRequired>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>MaxCreateDate</Name>
<FieldType>Date</FieldType>
</Field>
<Field>
<Name>MaxUnsubscribeDate</Name>
<FieldType>Date</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>common_list_table_sq</Name>
<CustomerKey>common_list_table_sq_key</CustomerKey>
<QueryText>
select ListID, ListName, count(*) as NumSubscribers, max(CreatedDate) as MaxCreateDate,
max(DateUnsubscribed) as MaxUnsubscribeDate
from _ListSubscribers
where ListType = 'List'
group by ListID, ListName
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>common_list_table_de</Name>
<CustomerKey>common_list_table_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>common_subscriber_table_de</Name>
<CustomerKey>common_subscriber_table_de_key</CustomerKey>
<CategoryID>{{common_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>254</MaxLength>
<IsRequired>true</IsRequired>
<IsPrimaryKey>true</IsPrimaryKey>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailAddress</Name>
<MaxLength>254</MaxLength>
<IsRequired>true</IsRequired>
<FieldType>EmailAddress</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>common_subscriber_table_sq</Name>
<CustomerKey>common_subscriber_table_sq_key</CustomerKey>
<QueryText>
SELECT DISTINCT SubscriberKey, EmailAddress, SubscriberID
FROM _ListSubscribers
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>common_subscriber_table_de</Name>
<CustomerKey>common_subscriber_table_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>common_job_table_de</Name>
<CustomerKey>common_job_table_de_key</CustomerKey>
<CategoryID>{{common_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>EmailID</Name>
<FieldType>Number</FieldType>
</Field>>
<Field>
<Name>FromName</Name>
<MaxLength>130</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>DaysAgo</Name>
<FieldType>Number</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>common_job_table_sq</Name>
<CustomerKey>common_job_table_sq_key</CustomerKey>
<QueryText>
SELECT JobID, EmailID, FromName, DATEDIFF(day, DeliveredTime, GetDate()) as DaysAgo
FROM _Job
WHERE DeliveredTime > DATEADD(DAY, -16, CAST(GETDATE() AS DATE))
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>common_job_table_de</Name>
<CustomerKey>common_job_table_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
</Objects>
</CreateRequest>
</s:Body>
</s:Envelope>
And for the Sends and Opens:
<?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">
<!-- Define data extension before the SQL activity which will load into it -->
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>stats_s1a_sent_yesterday_de</Name>
<CustomerKey>stats_s1a_sent_yesterday_de_key</CustomerKey>
<CategoryID>{{stats_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<FieldType>Date</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>stats_s1a_sent_yesterday_sq</Name>
<CustomerKey>stats_s1a_sent_yesterday_sq_key</CustomerKey>
<QueryText>
SELECT SubscriberKey, EventDate, ListID, JobID, BatchID
FROM _Sent
WHERE EventDate >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND EventDate < DATEADD(DAY, 0, CAST(GETDATE() AS DATE))
AND TriggeredSendCustomerKey IS NULL
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>stats_s1a_sent_yesterday_de</Name>
<CustomerKey>stats_s1a_sent_yesterday_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
<CategoryID>{{stats_sql_folder_id}}</CategoryID>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>stats_s1b_opened_yesterday_de</Name>
<CustomerKey>stats_s1b_opened_yesterday_de_key</CustomerKey>
<CategoryID>{{stats_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<FieldType>Date</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>stats_s1b_opened_yesterday_sq</Name>
<CustomerKey>stats_s1b_opened_yesterday_sq_key</CustomerKey>
<QueryText>
SELECT SubscriberKey, EventDate, ListID, JobID, BatchID
FROM _Open
WHERE EventDate >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND EventDate < DATEADD(DAY, 0, CAST(GETDATE() AS DATE))
AND TriggeredSendCustomerKey IS NULL
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>stats_s1b_opened_yesterday_de</Name>
<CustomerKey>stats_s1b_opened_yesterday_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
<CategoryID>{{stats_sql_folder_id}}</CategoryID>
</Objects>
</CreateRequest>
</s:Body>
</s:Envelope>
Notes on the above:
The Opens and Sends queries have a
TriggeredSendCustomerKey IS NULLfilter that limits the stats gathering to just list sends. Change to IS NOT NULL for triggered sends only, or remove this filter to include both types of sends.Each of the objects being created specify a CategoryID indicating the folder to place the say query or data extension in. This property is optional, without it the object will be created in the root folder. SOAP calls can be made to obtain the CategoryID, or within the MC UI, sometimes hovering over the folder with the mouse will cause it to be displayed at the bottom of the screen, or viewing the HTML markup for the folder.
The filters around the EventDates for the _Sent and _Opens queries result in retrieving data from the previous calendar day, not the previous 24 hours ending at the time the query was run. So, in theory, running the query at 9am and 9pm should return the same results. Note that times in Marketing Cloud are always CST without daylight savings time.
The jobs query goes back 15 days to help match with opens that may occur several days after the sending.
The below SOAP request takes care of the data extensions and SQL query activities needed in the second and third steps. For the second step, we fully merge the sends data with jobs, subscribers, and lists, creating a data extension ready to be extracted in the fourth step. For opens, same merging done, but as mentioned we have a third step for the user agent data:
<?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="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>stats_s2a_merged_sends_de</Name>
<CustomerKey>stats_s2a_merged_sends_de_key</CustomerKey>
<CategoryID>{{stats_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<MaxLength>250</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailAddress</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EventType</Name>
<MaxLength>10</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>FromName</Name>
<MaxLength>130</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>ListName</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>AccountID</Name>
<FieldType>Number</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>stats_s2a_merged_sends_sq</Name>
<CustomerKey>stats_s2a_merged_sends_sq_key</CustomerKey>
<QueryText>
SELECT sub.EmailAddress, 'Sent' AS EventType, sent.SubscriberKey,
FORMAT(sent.EventDate, 'M/dd/yyyy hh:mm:ss tt') AS EventDate,
sent.ListID, sent.JobID, sent.BatchID, REPLACE(jobs.FromName, ',', '') AS FromName, jobs.EmailID,
{{et_mid}} as AccountID, REPLACE(lists.ListName, ',', '') AS ListName
FROM stats_s1a_sent_yesterday_de sent
LEFT JOIN common_list_table_de lists ON sent.ListID = lists.ListID
LEFT OUTER JOIN common_job_table_de jobs ON sent.JobID = jobs.JobID
LEFT OUTER JOIN common_subscriber_table_de sub ON sent.SubscriberKey = sub.SubscriberKey
WHERE sub.EmailAddress IS NOT NULL
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>stats_s2a_merged_sends_de</Name>
<CustomerKey>stats_s2a_merged_sends_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
<CategoryID>{{stats_sql_folder_id}}</CategoryID>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>stats_s2b_opens_subs_jobs_de</Name>
<CustomerKey>stats_s2b_opens_subs_jobs_de_key</CustomerKey>
<CategoryID>{{stats_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>ListName</Name>
<MaxLength>100</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<FieldType>Date</FieldType>
</Field>
<Field>
<Name>EmailAddress</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>FromName</Name>
<MaxLength>130</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailID</Name>
<FieldType>Number</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>stats_s2b_opens_subs_jobs_sq</Name>
<CustomerKey>stats_s2b_opens_subs_jobs_sq_key</CustomerKey>
<QueryText>
SELECT o.SubscriberKey, o.ListID, o.JobID, o.BatchID, lists.ListName,
o.EventDate, sub.EmailAddress, jobs.FromName, jobs.EmailID
FROM stats_s1b_opened_yesterday_de o
LEFT JOIN common_list_table_de lists ON o.ListID = lists.ListID
LEFT OUTER JOIN common_job_table_de jobs ON o.JobID = jobs.JobID
LEFT OUTER JOIN common_subscriber_table_de sub ON o.SubscriberKey = sub.SubscriberKey
WHERE sub.EmailAddress IS NOT NULL
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>stats_s2b_opens_subs_jobs_de</Name>
<CustomerKey>stats_s2b_opens_subs_jobs_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
<CategoryID>{{stats_sql_folder_id}}</CategoryID>
</Objects>
<Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
<Name>stats_s3a_merged_opens_de</Name>
<CustomerKey>stats_s3a_merged_opens_de_key</CustomerKey>
<CategoryID>{{stats_de_folder_id}}</CategoryID>
<Fields>
<Field>
<Name>SubscriberKey</Name>
<MaxLength>254</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>ListID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>JobID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>BatchID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>AccountID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>EventDate</Name>
<MaxLength>250</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailAddress</Name>
<MaxLength>100</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EventType</Name>
<MaxLength>50</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>FromName</Name>
<MaxLength>130</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailID</Name>
<FieldType>Number</FieldType>
</Field>
<Field>
<Name>ListName</Name>
<MaxLength>100</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>Browser</Name>
<MaxLength>256</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>EmailClient</Name>
<MaxLength>256</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>OperatingSystem</Name>
<MaxLength>256</MaxLength>
<FieldType>Text</FieldType>
</Field>
<Field>
<Name>Device</Name>
<MaxLength>256</MaxLength>
<FieldType>Text</FieldType>
</Field>
</Fields>
</Objects>
<Objects xsi:type="QueryDefinition">
<Name>stats_s3a_merged_opens_sq</Name>
<CustomerKey>stats_s3a_merged_opens_sq_key</CustomerKey>
<QueryText>
SELECT o.SubscriberKey, o.ListID, o.JobID, o.BatchID, {{et_mid}} as AccountID,
o.EventDate, SubscriberID, o.EmailAddress,
'Open' AS EventType, o.FromName,
o.EmailID, o.ListName,
uai.Browser, uai.EmailClient, uai.OperatingSystem, uai.Device
FROM stats_s2b_opens_subs_jobs_de o
LEFT JOIN user_agent_info_de uai ON o.SubscriberKey = uai.SubscriberKey
AND o.EventDate = uai.EventDate
AND o.ListID = uai.ListID
</QueryText>
<TargetType>DE</TargetType>
<DataExtensionTarget>
<Name>stats_s3a_merged_opens_de</Name>
<CustomerKey>stats_s3a_merged_opens_de_key</CustomerKey>
</DataExtensionTarget>
<TargetUpdateType>Overwrite</TargetUpdateType>
<CategoryID>{{stats_sql_folder_id}}</CategoryID>
</Objects>
</CreateRequest>
</s:Body>
</s:Envelope>
Now for the data extract and file transfer activities, this time as REST calls:
POST calls to https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/dataextracts:
{
"name": "stats_s4a_sends_data_extract",
"key": "stats_s4a_sends_data_extract_key",
"description": "Data extract for send data from list sends",
"dataExtractTypeId": "bb94a04d-9632-4623-be47-daabc3f588a6",
"fileSpec": "list_send_extract_sent_%%Year%%%%Month%%%%Day%%.csv",
"dataFields": [
{
"name": "ColumnDelimiter",
"type": "string",
"value": ","
},
{
"name": "DECustomerKey",
"type": "string",
"value": "stats_s2a_merged_sends_de_key"
},
{
"name": "HasColumnHeaders",
"type": "bool",
"value": "True"
}
]
}
{
"name": "stats_s4b_opens_data_extract",
"key": "stats_s4b_opens_data_extract_key",
"dataExtractTypeId": "bb94a04d-9632-4623-be47-daabc3f588a6",
"description": "Data extract for opens data from list sends",
"fileSpec": "list_send_extract_open_%%Year%%%%Month%%%%Day%%.csv",
"dataFields": [
{
"name": "ColumnDelimiter",
"type": "string",
"value": ","
},
{
"name": "DECustomerKey",
"type": "string",
"value": "stats_s3a_merged_opens_de_key"
},
{
"name": "HasColumnHeaders",
"type": "bool",
"value": "True"
}
]
}
Note the dataExtractTypeId value given in the calls above, "bb94a04d..." appears to be standard for a Data Extension Extract, what is needed here. A GET call to .../automation/v1/dataextracttypes can confirm this for your business unit.
Finally, for the file transfer activities:
{
"name": "stats_s5a_sends_file_transfer",
"customerKey": "stats_s5a_sends_file_transfer_key",
"description": "File transfer for send data from list sends",
"fileTransferLocationId": "{{fileTransferLocationId}}",
"fileSpec": "list_send_extract_sent_%%Year%%%%Month%%%%Day%%.csv",
"isFileSpecLocalized" : true,
"isUpload" : true
}
{
"name": "stats_s5b_opens_file_transfer",
"customerKey": "stats_s5b_opens_file_transfer_key",
"description": "File transfer for opens data from list sends",
"fileTransferLocationId": "{{fileTransferLocationId}}",
"fileSpec": "list_send_extract_open_%%Year%%%%Month%%%%Day%%.csv",
"isFileSpecLocalized" : true,
"isUpload" : true
}
See this blog entry for information on finding out the fileTransferLocationId for your business unit.
Notes:
I found Optimizing a SQL Query Activity in the Marketing Cloud documentation to be particularly useful in writing SQL queries.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 17, 2023 | Comments[0]
Thursday April 13, 2023
The small things need fixing too
On the surface, the Salesforce IdeaExchange sounds great: If you see a problem with certain functionality, or missing functionality, type it up, persuade others to vote for it, and three times per year (as they release that often), Salesforce will prioritize the most popular issues and fix/implement them. And the winners are indeed popular ideas that will make a lot of users happy.
Problem is, seemingly all improvements are related to the UI, as that is where the bulk of the users are, so naturally those get an overwhelming number of votes. Shortcomings in the API tend to be left out, but those are important matters to individual users using the API. I submitted a couple of ideas (here and here) based on shortcomings I found with the SOAP API. I realized they had zero chance of "winning", and of course, they didn't, but I still felt it proper for me to do my half, independent of what they would do with their half.
Then again, perhaps I did waste my time in informing Salesforce about the problems I found. Further, why bother informing them about other problems I later find, given that they won't act on it unless say 1,000 people vote for it? The problem with implying to customers that you won't fix or improve something unless thousands vote for the issue, is that people stop telling you about problems if they suspect it won't get that many votes. But you want people to be bringing you those issues. Termites, in sufficient number, can cause a giant tree to collapse.
To be sure, I did see in one or two cases Salesforce product managers closing ideas as "now fixed" even though just a few had voted for them. If I had looked further, good chance I would have found more examples. Meaning, that at least some of them are lurking for things that need patching. Good for them. But Salesforce should be more aggressive in its IdeaExchange FAQ that it wants to see the suggestions even if they don't have a realistic chance of "winning", that things that indeed need fixing will get fixed even if they come up short in the vote count. This is especially the case for companies like Salesforce that maintain both a UI and a (critical) API, because the latter is almost always going to get clobbered at the ballot box.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 13, 2023 | Comments[0]
Saturday April 08, 2023
Obtaining user agent data for email opens from Marketing Cloud
Updated April 2023.
Data Extract Activities in Salesforce Marketing Cloud are perhaps most commonly used for pulling data from data extensions (tables) into files stored in an externally-inaccessible place called the "Safehouse". From there, a File Transfer Activity can move the file to an externally-accessible SFTP server, such as the one hosted by SFMC for its customers. There is another variant of Data Extract activity, a tracking extract, in which one selects the general data desired without reference to particular data extensions and Marketing Cloud will place a CSV of it in the Safehouse. Tracking extracts provide a superset of the information available in the data views, in particular, the ability to provide User Agent information (browser, OS, device, etc.) for email opens.
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 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, perhaps for subsequent querying and joining with data views and data extensions. This process builds on the first usage by adding an file unzipping step and import activity step. Genetrix Technology's Donna Redmond has provided an informative video how to do this from Automation Studio.
The steps outlined by Ms. Redmond using SFMC's Automation Studio can also be done using the Salesforce API, using a combination of SOAP and REST calls. I've found the SFMC Postman Collection easiest for making these calls. Steps:
Create the data extension (DE) to hold the user agent data. This DE will be filled by the import activity in the last step. Important to save the Object ID of the created DE (which will be available in the SOAP response) as you will be using that value later when defining the import activity. Included in this request is the automation "shell", making it easy to plug the needed activities into the automation, as described at the end.
<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="Automation"> <Name>User Agent Info Automation</Name> <CustomerKey>user_agent_atmn_key</CustomerKey> <Description>Obtain user agent info for opens </Description> <AutomationTasks> <AutomationTask> <Name>Create extract for usage agent info</Name> <Activities> <Activity> <ActivityObject xsi:type="DataExtractActivity"> <Name>Replace with s1_user_agent_info_extract</Name> </ActivityObject> </Activity> </Activities> </AutomationTask> <AutomationTask> <Name>File transfer of user agent info to SFTP server</Name> <Activities> <Activity> <ActivityObject xsi:type="FileTransferActivity"> <Name>Replace with s2_user_agent_info_transfer</Name> </ActivityObject> </Activity> </Activities> </AutomationTask> <AutomationTask> <Name>Uncompress into opens.csv on SFTP server</Name> <Activities> <Activity> <ActivityObject xsi:type="FileTransferActivity"> <Name>Replace with s3_user_agent_info_unzip</Name> </ActivityObject> </Activity> </Activities> </AutomationTask> <AutomationTask> <Name>Import user agent info back into data extension</Name> <Activities> <Activity> <ActivityObject xsi:type="ImportDefinition"> <Name>Replace with s4_user_agent_info_import</Name> </ActivityObject> </Activity> </Activities> </AutomationTask> </AutomationTasks> <AutomationType>scheduled</AutomationType> </Objects> <Objects xsi:type="ns1:DataExtension" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI"> <Name>user_agent_info_de</Name> <CustomerKey>user_agent_info_de_key</CustomerKey> <Description>Filled by User Agent Opens tracking extract automation, stores opens browser and client info</Description> <DataRetentionPeriodLength>45</DataRetentionPeriodLength> <DataRetentionPeriod>Days</DataRetentionPeriod> <RowBasedRetention>true</RowBasedRetention> <CategoryID>{{common_de_folder_id}}</CategoryID> <Fields> <Field> <Name>ClientID</Name> <IsRequired>true</IsRequired> <FieldType>Number</FieldType> </Field> <Field> <Name>SendID</Name> <IsRequired>true</IsRequired> <FieldType>Number</FieldType> </Field> <Field> <Name>SubscriberKey</Name> <MaxLength>50</MaxLength> <IsRequired>true</IsRequired> <FieldType>Text</FieldType> </Field> <Field> <Name>EmailAddress</Name> <MaxLength>256</MaxLength> <IsRequired>true</IsRequired> <FieldType>Text</FieldType> </Field> <Field> <Name>SubscriberID</Name> <IsRequired>true</IsRequired> <FieldType>Number</FieldType> </Field> <Field> <Name>ListID</Name> <IsRequired>true</IsRequired> <FieldType>Number</FieldType> </Field> <Field> <Name>EventDate</Name> <IsRequired>true</IsRequired> <FieldType>Date</FieldType> </Field> <Field> <Name>EventType</Name> <MaxLength>50</MaxLength> <IsRequired>true</IsRequired> <FieldType>Text</FieldType> </Field> <Field> <Name>BatchID</Name> <IsRequired>true</IsRequired> <FieldType>Number</FieldType> </Field> <Field> <Name>TriggeredSendExternalKey</Name> <MaxLength>50</MaxLength> <FieldType>Text</FieldType> </Field> <Field> <Name>Browser</Name> <MaxLength>256</MaxLength> <FieldType>Text</FieldType> </Field> <Field> <Name>EmailClient</Name> <MaxLength>256</MaxLength> <FieldType>Text</FieldType> </Field> <Field> <Name>OperatingSystem</Name> <MaxLength>256</MaxLength> <FieldType>Text</FieldType> </Field> <Field> <Name>Device</Name> <MaxLength>256</MaxLength> <FieldType>Text</FieldType> </Field> </Fields> </Objects> </CreateRequest> </s:Body> </s:Envelope>The fields specified here are among those shown during the data extract activity in the next step. I defined a Postman variable for the
CategoryID, which specifies the desired data extension folder to place this DE in. This value, an integer, is usually viewable by hovering over the data extension folder in Email Studio. If not provided, the DE will be placed in the DE root folder.Creating the data extract activity: Here you'll specify the desired Business Unit MID values and the data desired. Note some data retrieval options default to true (apparently mapping to what is defaulted as checked in the UI), meaning you'll have to explicitly specify those as "false" if the data is unwanted. If what you want is the default, it does not need to be specified. The below configuration requests opens and user agent info while shutting off other items that would be provided by default.
POST https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/dataextracts: { "dataExtractDefinitionId": "6d81a94d-2f7b-4ee2-889d-ac70bce5c999", "name": "s1_user_agent_info_extract", "key": "s1_user_agent_info_extract_key", "description": "", "dataExtractTypeId": "c7219016-a7f0-4c72-8657-1ec12c28a0db", "fileSpec": "user_agent_info_extract_%%Year%%%%Month%%%%Day%%.zip", "intervalType": 2, "dataFields": [ { "name": "AccountIDs", "type": "string", "value": "...add BU MIDs here..." }, { "name": "ExtractBounces", "type": "bool", "value": "False" }, { "name": "ExtractClicks", "type": "bool", "value": "False" }, { "name": "ExtractConversions", "type": "bool", "value": "False" }, { "name": "ExtractOpens", "type": "bool", "value": "True" }, { "name": "ExtractSendJobs", "type": "bool", "value": "False" }, { "name": "ExtractSent", "type": "bool", "value": "False" }, { "name": "ExtractSurveyResponses", "type": "bool", "value": "False" }, { "name": "ExtractUnsubs", "type": "bool", "value": "False" }, { "name": "Format", "type": "dropdown", "value": "csv", "dropDownList": "csv,tab,xml" }, { "name": "IncludeTestSends", "type": "bool", "value": "False" }, { "name": "IncludeUserAgentInformation", "type": "bool", "value": "True" }, { "name": "Timezone", "type": "integer", "value": "1" } ] }I use "s1_", "s2_", etc. prefixes to indicate the step (column) in the subsequent Automation that will be using these activities, to make it easier to plug them into the automation.
Creating the first file transfer activity (move to SFTP): In this step, we move the file from the Safehouse to the SFMC SFTP server (additional destinations may be available for you.) Make sure the fileSpec specified is the same as the previous step:
POST: https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/filetransfers : { "name": "s2_user_agent_info_transfer", "customerKey": "s2_user_agent_info_transfer_key", "fileSpec": "user_agent_info_extract_%%Year%%%%Month%%%%Day%%.zip", "fileTransferLocationId": "{{fileTransferLocationId}}", "isUpload" : true, "isFileSpecLocalized": true }As shown above, I have
fileTransferLocationId, a UUID, as a Postman environment variable because it differs from business unit to business unit. To figure out this value, I searched for a UI-created file transfer activity that sends a file to the SFMC SFTP server and then copied its value for this field. (The API to obtain a list of file locations unfortunately doesn't return the SFMC SFTP one.) To get the list of file transfer activities defined in your BU:GET https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/filetransfers/From the above list, copy an ID of SFTP-destined file transfer activity and then get its details by adding that ID to the endpoint above and making another call. The details will include the
fileTransferLocationIdvalue.Creating the second file transfer activity (unzipping): The next step, a File Import activity, apparently works with unzipped files only, so in this step we unzip the file created in the previous step, while keeping it on that server. The unzipped file will be called Opens.csv (apparently hardcoded by SFMC), suggesting it can end up being overwritten if you're unzipping multiple tracking extracts.
POST: https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/filetransfers : { "name": "s3_user_agent_info_unzip", "customerKey": "s3_user_agent_info_unzip_key", "fileSpec": "user_agent_info_extract_%%Year%%%%Month%%%%Day%%.zip", "isCompressed": true, "fileTransferLocationId": "{{fileTransferLocationId}}" }Once again, make sure the fileSpec matches that of the step before it.
Create the Import Activity: Now we bring the data back into Marketing Cloud by loading a data extension with it. For the below REST query, you'll need to plug in values for the object ID of the DE created in the first step and the fileTransferLocationID determined earlier.
POST: https://{{et_subdomain}}.rest.marketingcloudapis.com/automation/v1/imports { "name": "s4_user_agent_info_import", "customerKey": "s4_user_agent_info_import_key", "dateFormatLocale": "en-US", "destinationObjectId": "{{userAgentDEObjectID}}", "encodingName": "utf-8", "fieldMappingType": "InferFromColumnHeadings", "fileNamingPattern": "Opens.csv", "fileTransferLocationId": "{{fileTransferLocationId}}", "fileType": "CSV", "allowErrors": true, "hasColumnHeader": true, "isOrderedImport": true, "isSequential": true, "sendEmailNotification": false, "standardQuotedStrings": true, "subscriberImportTypeId": 255, "updateTypeId": 4, "fileTransferLocationTypeId": 0 }Plug the activities into an automation, and schedule/run it. In the "shell" automation the SOAP request makes, just hit "replace" on each of the boxes, choosing the activity listed on the box, as as illustrated at the top of the sends & opens article. Once done, the automation can be given a test run. Check the data extension to ensure it was filled. If it wasn't, can work backwards to determine the problem, in particular by checking the files on the SFTP server, there should be an uncompressed opens.csv with data in it.
Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 08, 2023 | Comments[0]
Calendar
About Me
Blog Search
Links
Recent Posts
Recent Comments
- - Glen Mazza on Changing the row retention period for SFMC data extensions
- - venkat reddy on Changing the row retention period for SFMC data extensions
- - serxoz on Creating Chrome- and Firefox-compliant local development certificates
- - Nitin Lokhande on Deploying TightBlog on Linode
- - Anant Jaynarayana on Deploying TightBlog on Linode
About Blog
Application Server: Tomcat
Database: MySQL
Hosted on: Linode
SSL Certificate: Let's Encrypt