0

I need an automated solution for generating an account send summary. It would be ideal to send this data to a data extension. I tried to accomplish this using a tracking extract but it doesn't provide all the fields we need (EmailName, Sends, ImplicitDeliveries, UniqueOpens, and UniqueClicks). We don't want subscriber data, but rather aggregate email send data like the account send summary report provides. Currently, I'm manually running the report, editing, and adding to a data extension. Is there a way to automate the generation of this report?

0

Yes, you can store the Send SOAP object system data in a data extension using a Script Activity. Then you use it for extract or as email content. Here's an example:

<script runat="server">

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

try {

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

    // DESCRIBE - SEND
    // https://developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/send.htm
    var sendDescribe = prox.describe("Send");
    var sendCols0 = [];

    for (var i = 0; i < sendDescribe.Results[0].Properties.length; i++) {

      if (sendDescribe.Results[0].Properties[i].IsRetrievable) {
        sendCols0.push(sendDescribe.Results[0].Properties[i].Name);
      }
    }

    var sendObj = "Send";
    var sendCols = sendCols0;

    // 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() - 30);

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

    // sendFilter = {Property: "SendDate", SimpleOperator: "greaterThan", Value: filterDate};

    var sendFilter = {Property: "ID", SimpleOperator: "greaterThan", Value: 0};
    var moreRows = true;
    var reqID = null;

    while (moreRows) {

        moreRows = false;

        var sendRows = reqID == null ? prox.retrieve(sendObj, sendCols0, sendFilter) : prox.getNextBatch(sendObj, reqID);

        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 != null ) {
                      row.EmailSendDefinition_ObjectID = sendRows.Results[i].EmailSendDefinition.ObjectID;
                      row.EmailSendDefinition_CustomerKey = sendRows.Results[i].EmailSendDefinition.CustomerKey;
                    }

                    row.UpdatedDate = new Date();

                    var de = DataExtension.Init("Send"); /* external key of target data extension */
                    var rowsAddedUpdated = 0;

                    try {

                      rowsAddedUpdated = de.Rows.Add(row);

                    } catch (e) {

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

                      try {

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

                      } catch(e2) {

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

                      }
                    }

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


                } // for-loop

            } // row results


        }

    }


} catch (e) {

  if (debug) {
    Platform.Response.Write("<br><br>e: " + Stringify(e));
  }

}
</script>

More details on what's available in the schema here.

1
  • Thanks for providing this solution Adam! It's works well. – scottola Mar 21 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.