TriggeredSendSummary to Data Extension Row

sfmc-ssjs-triggeredSendSummary.js

<script runat="server">

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

try {

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

    // https://salesforce.stackexchange.com/questions/178299/setup-an-email-notification-to-admin-when-triggered-sends-exceed-a-level-of-500/230406#230406
    // https://developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/triggeredsendsummary.htm

    var describe = prox.describe("TriggeredSendSummary");
    var objProps0 = [];

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

      if (describe.Results[0].Properties[i].IsRetrievable) {
        objProps0.push(describe.Results[0].Properties[i].Name);

        if (debug) {
          //Write("<br>" + describe.Results[0].Properties[i].Name + "," + describe.Results[0].Properties[i].DataType + "," + describe.Results[0].Properties[i].MaxLength + ","+ describe.Results[0].Properties[i].Precision + "," + describe.Results[0].Properties[i].Scale)
          //Write("<br>" + describe.Results[0].Properties[i].Name);
        }

      }
    }

    if (debug) {
     //Write("<br><br>objProps0: " + Stringify(objProps0));
     Write("<br><br>describe.Results[0]: " + Stringify(describe.Results[0]));
    }

    var obj = "TriggeredSendSummary";
    var objProps = objProps0;

    // 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);
    // }

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

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

    while (moreRows) {

        moreRows = false;

        var objRows = reqID == null ? prox.retrieve(obj, objProps0, objFilter) : prox.getNextBatch(obj, reqID);

        if (debug) {
         //Write("<br><br>objRows: " + Stringify(objRows));
         Write("<br><br>objRows.Results.length: " + objRows.Results.length);
        }

        if (objRows != null) {

            moreRows = objRows.HasMoreRows;
            reqID = objRows.RequestID;

            if (objRows && objRows.Results) {

                var prevClient_ID = "";
                var prevTriggeredSendDefinition_ObjectID = "";
                var prevObjectID = "";
                var prevCustomerKey = "";
                var seq = 1;

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

                    var row = {};
                    row.Client_ID = objRows.Results[i].Client.ID;
                    row.TriggeredSendDefinition_ObjectID = objRows.Results[i].TriggeredSendDefinition.ObjectID;
                    row.ObjectID = objRows.Results[i].ObjectID;
                    row.CustomerKey = objRows.Results[i].CustomerKey;
                    row.RowObjectID = (objRows.Results[i].RowObjectID != null) ? objRows.Results[i].RowObjectID : "";
                    row.Client_PartnerClientKey = (objRows.Results[i].Client.PartnerClientKey != null) ? objRows.Results[i].Client.PartnerClientKey : "";
                    row.PartnerKey = (objRows.Results[i].PartnerKey != null) ? objRows.Results[i].PartnerKey : "";

                    if (
                          prevClient_ID == row.Client_ID
                          && prevTriggeredSendDefinition_ObjectID == row.TriggeredSendDefinition_ObjectID
                          && prevObjectID == row.ObjectID
                          && prevCustomerKey == row.CustomerKey
                    ) {

                      seq++;

                    } else {

                      seq = 1;
                      prevClient_ID = row.Client_ID;
                      prevTriggeredSendDefinition_ObjectID = row.TriggeredSendDefinition_ObjectID;
                      prevObjectID = row.ObjectID;
                      prevCustomerKey = row.CustomerKey;

                    }

                    row.seq = seq;

                    row.InProcess = objRows.Results[i].InProcess;
                    row.Bounces = objRows.Results[i].Bounces;
                    row.Clicks = objRows.Results[i].Clicks;
                    row.Conversions = objRows.Results[i].Conversions;
                    row.FTAFEmailsSent = objRows.Results[i].FTAFEmailsSent;
                    row.FTAFOptIns = objRows.Results[i].FTAFOptIns;
                    row.FTAFRequests = objRows.Results[i].FTAFRequests;
                    row.NotSentDueToError = objRows.Results[i].NotSentDueToError;
                    row.NotSentDueToOptOut = objRows.Results[i].NotSentDueToOptOut;
                    row.NotSentDueToUndeliverable = objRows.Results[i].NotSentDueToUndeliverable;
                    row.Opens = objRows.Results[i].Opens;
                    row.OptOuts = objRows.Results[i].OptOuts;
                    row.Queued = objRows.Results[i].Queued;
                    row.Sent = objRows.Results[i].Sent;
                    row.SurveyResponses = objRows.Results[i].SurveyResponses;
                    row.UniqueClicks = objRows.Results[i].UniqueClicks;
                    row.UniqueConversions = objRows.Results[i].UniqueConversions;
                    row.UniqueOpens = objRows.Results[i].UniqueOpens;

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

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

                    try {

                      rowsAddedUpdated = de.Rows.Add(row);

                    } catch (e) {

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

                      try {

                        var cols = [];
                        cols.push("Client_ID");
                        cols.push("TriggeredSendDefinition_ObjectID");
                        cols.push("ObjectID");
                        cols.push("CustomerKey");
                        cols.push("seq");

                        var vals = [];
                        vals.push(row.Client_ID);
                        vals.push(row.TriggeredSendDefinition_ObjectID);
                        vals.push(row.ObjectID);
                        vals.push(row.CustomerKey);
                        vals.push(row.seq);

                        rowsAddedUpdated = de.Rows.Update(row, cols, vals);

                      } catch(e2) {

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

                      }
                    }

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


                } // for-loop

            } // row results


        }

    }


} catch (e) {

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

}
</script>