Retrieve Send SOAP Object data and write to a Data Extension

sfmc-send-soap-object-retrieve-to-de.js

<script runat="server">

Platform.Load("core","1");
var debug = false;

// Mirrors Send SOAP Object data into a data extension
// - creates its own data extension
// - without any date filters includes all sends from account inception

try {

    var prox = new Script.Util.WSProxy();

    var TargetDECustomerKey = 'Send';
    var TargetDECategoryID = // CATEGORY ID OF TARGET DE GOES HERE;

        // CREATE SEND DE
    try {

      var result=prox.createItem("DataExtension",{"CustomerKey":TargetDECustomerKey,"Name":TargetDECustomerKey,"Description":"Send SOAP Object","CategoryID":TargetDECategoryID,"Fields":[
        {"CustomerKey":"ID","Name":"ID","FieldType":"Number","IsPrimaryKey":true,"IsRequired":true},
        {"CustomerKey":"SendDate","Name":"SendDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"EmailName","Name":"EmailName","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Subject","Name":"Subject","FieldType":"Text","MaxLength":"200","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Status","Name":"Status","FieldType":"Text","MaxLength":"15","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Additional","Name":"Additional","FieldType":"Text","MaxLength":"50","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"BCCEmail","Name":"BCCEmail","FieldType":"EmailAddress","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Client_ID","Name":"Client_ID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Client_PartnerClientKey","Name":"Client_PartnerClientKey","FieldType":"Text","MaxLength":"64","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CreatedDate","Name":"CreatedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Duplicates","Name":"Duplicates","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"Email_ID","Name":"Email_ID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"Email_PartnerKey","Name":"Email_PartnerKey","FieldType":"Text","MaxLength":"64","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"EmailSendDefinition_CustomerKey","Name":"EmailSendDefinition_CustomerKey","FieldType":"Text","MaxLength":"36","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"EmailSendDefinition_ObjectID","Name":"EmailSendDefinition_ObjectID","FieldType":"Text","MaxLength":"36","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SendClassification_CustomerKey","Name":"SendClassification_CustomerKey","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SendClassification_Name","Name":"SendClassification_Name","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SendClassification_Type","Name":"SendClassification_Type","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"ExistingUndeliverables","Name":"ExistingUndeliverables","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"ExistingUnsubscribes","Name":"ExistingUnsubscribes","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"ForwardedEmails","Name":"ForwardedEmails","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"FromAddress","Name":"FromAddress","FieldType":"EmailAddress","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"FromName","Name":"FromName","FieldType":"Text","MaxLength":"130","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"HardBounces","Name":"HardBounces","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"InvalidAddresses","Name":"InvalidAddresses","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"IsAlwaysOn","Name":"IsAlwaysOn","FieldType":"Boolean","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"IsMultipart","Name":"IsMultipart","FieldType":"Boolean","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"MissingAddresses","Name":"MissingAddresses","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"ModifiedDate","Name":"ModifiedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"NumberDelivered","Name":"NumberDelivered","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"NumberErrored","Name":"NumberErrored","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"NumberExcluded","Name":"NumberExcluded","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"NumberSent","Name":"NumberSent","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"NumberTargeted","Name":"NumberTargeted","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"OtherBounces","Name":"OtherBounces","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"PartnerKey","Name":"PartnerKey","FieldType":"Text","MaxLength":"64","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"PreviewURL","Name":"PreviewURL","FieldType":"Text","MaxLength":"500","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SendLimit","Name":"SendLimit","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"SendWindowClose","Name":"SendWindowClose","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SendWindowOpen","Name":"SendWindowOpen","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SentDate","Name":"SentDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"SoftBounces","Name":"SoftBounces","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"UniqueClicks","Name":"UniqueClicks","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"UniqueOpens","Name":"UniqueOpens","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"Unsubscribes","Name":"Unsubscribes","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"CampaignID","Name":"CampaignID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
        {"CustomerKey":"CampaignCreatedDate","Name":"CampaignCreatedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CampaignModifiedDate","Name":"CampaignModifiedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CampaignName","Name":"CampaignName","FieldType":"Text","MaxLength":"50","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CampaignDescription","Name":"CampaignDescription","FieldType":"Text","MaxLength":"500","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CampaignCode","Name":"CampaignCode","FieldType":"Text","MaxLength":"36","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CampaignColor","Name":"CampaignColor","FieldType":"Text","MaxLength":"6","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"CampaignFavorite","Name":"CampaignFavorite","FieldType":"Boolean","IsPrimaryKey":false,"IsRequired":false},
        {"CustomerKey":"InsertDate","Name":"InsertDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"getdate()"},
        {"CustomerKey":"UpdatedDate","Name":"UpdatedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false}
      ]});

    } catch (e) {

    }

    var sendCols0 = ["Additional","BccEmail","Client.ID","Client.PartnerClientKey","CreatedDate","Duplicates","Email.ID","Email.PartnerKey","EmailName","EmailSendDefinition.CustomerKey","EmailSendDefinition.ObjectID","ExistingUndeliverables","ExistingUnsubscribes","ForwardedEmails","FromAddress","FromName","HardBounces","ID","InvalidAddresses","IsAlwaysOn","IsMultipart","MissingAddresses","ModifiedDate","NumberDelivered","NumberErrored","NumberExcluded","NumberSent","NumberTargeted","OtherBounces","PartnerKey","PreviewURL","SendDate","SendLimit","SendWindowClose","SendWindowOpen","SentDate","SoftBounces","Status","Subject","UniqueClicks","UniqueOpens","Unsubscribes"]
    var sendObj = "Send";
    var sendCols = sendCols0;

    // SEND FILTER - IF NEEDED
    var filterDate0 = new Date();
    filterDate0 = filterDate0.setMinutes(0);
    filterDate0 = filterDate0.setSeconds(0);
    filterDate0 = filterDate0.setMilliseconds(0);
    filterDate0 = filterDate0.setHours(0);

    var filterDate = DateTime.SystemDateToLocalDate(filterDate0);
    filterDate.setDate(filterDate.getDate() - 999);

    if (debug) {
     Write("<br><br>filterDate: " + filterDate);
    }

    var sendFilter = {Property: "SendDate", SimpleOperator: "greaterThan", Value: filterDate};
    //var sendFilter = {Property: "ID", SimpleOperator: "equals", Value: 1048264};
    //var sendFilter = {Property: "ID", SimpleOperator: "greaterThan", Value: 0};
    var moreRows = true;
    var reqID = null;
    var opts = { BatchSize: 500 };
    var props = {};

    while (moreRows) {

        moreRows = false;

        if (reqID) {
          props.ContinueRequest = reqID;
        }

        var sendRows = prox.retrieve(sendObj, sendCols0, sendFilter, opts, props);

        if (sendRows != null) {

            moreRows = sendRows.HasMoreRows;
            reqID = sendRows.RequestID;

            if (sendRows && sendRows.Results) {

                for (var i = 0; i < sendRows.Results.length; i++) {

                    var row = {};
                    row.ID = sendRows.Results[i].ID;
                    row.PartnerKey = sendRows.Results[i].PartnerKey;
                    row.CreatedDate = sendRows.Results[i].CreatedDate;
                    row.ModifiedDate = sendRows.Results[i].ModifiedDate;
                    row.Client_ID = sendRows.Results[i].Client.ID;
                    row.Client_PartnerClientKey = sendRows.Results[i].Client.PartnerClientKey;
                    row.Email_ID = sendRows.Results[i].Email.ID;
                    row.Email_PartnerKey = sendRows.Results[i].Email.PartnerKey;
                    row.SendDate = sendRows.Results[i].SendDate;
                    row.FromAddress = sendRows.Results[i].FromAddress;
                    row.FromName = sendRows.Results[i].FromName;
                    row.Duplicates = sendRows.Results[i].Duplicates;
                    row.InvalidAddresses = sendRows.Results[i].InvalidAddresses;
                    row.ExistingUndeliverables = sendRows.Results[i].ExistingUndeliverables;
                    row.ExistingUnsubscribes = sendRows.Results[i].ExistingUnsubscribes;
                    row.HardBounces = sendRows.Results[i].HardBounces;
                    row.SoftBounces = sendRows.Results[i].SoftBounces;
                    row.OtherBounces = sendRows.Results[i].OtherBounces;
                    row.ForwardedEmails = sendRows.Results[i].ForwardedEmails;
                    row.UniqueClicks = sendRows.Results[i].UniqueClicks;
                    row.UniqueOpens = sendRows.Results[i].UniqueOpens;
                    row.NumberSent = sendRows.Results[i].NumberSent;
                    row.NumberDelivered = sendRows.Results[i].NumberDelivered;
                    row.NumberTargeted = sendRows.Results[i].NumberTargeted;
                    row.NumberErrored = sendRows.Results[i].NumberErrored;
                    row.NumberExcluded = sendRows.Results[i].NumberExcluded;
                    row.Unsubscribes = sendRows.Results[i].Unsubscribes;
                    row.MissingAddresses = sendRows.Results[i].MissingAddresses;
                    row.Subject = sendRows.Results[i].Subject;
                    row.PreviewURL = sendRows.Results[i].PreviewURL;
                    row.SentDate = sendRows.Results[i].SentDate;
                    row.EmailName = sendRows.Results[i].EmailName;
                    row.Status = sendRows.Results[i].Status;
                    row.IsMultipart = sendRows.Results[i].IsMultipart;
                    row.SendLimit = sendRows.Results[i].SendLimit;
                    row.SendWindowOpen = new Date(sendRows.Results[i].SendWindowOpen).getFullYear() > 2000 ? sendRows.Results[i].SendWindowOpen : null ;
                    row.SendWindowClose = new Date(sendRows.Results[i].SendWindowClose).getFullYear() > 2000 ? sendRows.Results[i].SendWindowClose : null ;
                    row.IsAlwaysOn = sendRows.Results[i].IsAlwaysOn;
                    row.Additional = sendRows.Results[i].Additional;
                    row.BCCEmail = sendRows.Results[i].BCCEmail;

                    if (sendRows.Results[i].EmailSendDefinition && sendRows.Results[i].EmailSendDefinition.ObjectID && sendRows.Results[i].EmailSendDefinition.CustomerKey) {

                        EmailSendDefinition_ObjectID = sendRows.Results[i].EmailSendDefinition.ObjectID;
                        EmailSendDefinition_CustomerKey = sendRows.Results[i].EmailSendDefinition.CustomerKey;
                        row.EmailSendDefinition_ObjectID = EmailSendDefinition_ObjectID;
                        row.EmailSendDefinition_CustomerKey = EmailSendDefinition_CustomerKey;

                    }

                    row.UpdatedDate = new Date();

                    var de = DataExtension.Init(TargetDECustomerKey);
                    var rowsAddedUpdated = 0;

                    try {

                      rowsAddedUpdated = de.Rows.Add(row);

                    } catch (e) {

                      try {

                        rowsAddedUpdated = de.Rows.Update(row, ['ID'], [sendRows.Results[i].ID]);

                      } catch(e2) {

                      }
                    }

                } // for-loop

            } // row results


        }

    }

} catch (e) {


}
</script>