Glen Mazza's Weblog

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.