Glen Mazza's Weblog

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:

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 &lt; 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 &lt; 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 NULL filter 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.