Glen Mazza's Weblog

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:

1. 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.

2. 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.

3. 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 fileTransferLocationId value.

4. 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.

5. 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
   }
   

6. 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.