Skip to content

Instantly share code, notes, and snippets.

@wvpv

wvpv/sfmc-triggeredsendsummary-soap-object-retrieve-to-de.js

Created December 28, 2021 15:46
Show Gist options
  • Save wvpv/e3f36971b186ba5a6d3038a6e806351b to your computer and use it in GitHub Desktop.
Save wvpv/e3f36971b186ba5a6d3038a6e806351b to your computer and use it in GitHub Desktop.
Download ZIP
Retrieve TriggeredSendSummary SOAP object data and write to a Data Extension
Raw
sfmc-triggeredsendsummary-soap-object-retrieve-to-de.js
<script runat="server">
Platform.Load("core","1");
var debug = false;
try {
var prox = new Script.Util.WSProxy();
// credit Jason Hanshaw: https://salesforce.stackexchange.com/questions/178299/setup-an-email-notification-to-admin-when-triggered-sends-exceed-a-level-of-500/230406#230406
// SOAP Object: https://developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/triggeredsendsummary.htm
// GET RETRIEVABLE ATTRIBUTES FOR OBJECTS
var tsdDescribe = prox.describe("TriggeredSendDefinition");
var tsdObjProps = [];
for (var i = 0; i < tsdDescribe.Results[0].Properties.length; i++) {
if (tsdDescribe.Results[0].Properties[i].IsRetrievable) {
tsdObjProps.push(tsdDescribe.Results[0].Properties[i].Name);
}
}
if (debug) {
Write("<br><br>tsdObjProps: " + Stringify(tsdObjProps));
//Write("<br><br>tsdDescribe.Results[0]: " + Stringify(tsdDescribe.Results[0]));
}
var spDescribe = prox.describe("SenderProfile");
var spObjProps = [];
for (var i = 0; i < spDescribe.Results[0].Properties.length; i++) {
if (spDescribe.Results[0].Properties[i].IsRetrievable) {
spObjProps.push(spDescribe.Results[0].Properties[i].Name);
}
}
if (debug) {
Write("<br><br>spObjProps: " + Stringify(spObjProps));
//Write("<br><br>spDescribe.Results[0]: " + Stringify(spDescribe.Results[0]));
}
var scDescribe = prox.describe("SendClassification");
var scObjProps = [];
for (var i = 0; i < scDescribe.Results[0].Properties.length; i++) {
if (scDescribe.Results[0].Properties[i].IsRetrievable) {
scObjProps.push(scDescribe.Results[0].Properties[i].Name);
}
}
if (debug) {
Write("<br><br>scObjProps: " + Stringify(scObjProps));
//Write("<br><br>scDescribe.Results[0]: " + Stringify(scDescribe.Results[0]));
}
var describe = prox.describe("TriggeredSendSummary");
var tssObjProps = [];
for (var i = 0; i < describe.Results[0].Properties.length; i++) {
if (describe.Results[0].Properties[i].IsRetrievable) {
tssObjProps.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>tssObjProps: " + Stringify(tssObjProps));
//Write("<br><br>describe.Results[0]: " + Stringify(describe.Results[0]));
}
// RETRIEVE TRIGGEREDSENDSUMMARY OBJECTS
var obj = "TriggeredSendSummary";
var objProps = tssObjProps;
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, objProps, 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;
// ITERATE THROUGH RESULTS
for (var i=0; i < objRows.Results.length; i++) {
// POPULATE DATA EXTENSION ROW COLUMN VALUES
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;
// RETRIEVE TSD NAME, SENDER PROFILE NAME and SENDCLASSIFICATION NAME USING TSS ATTRIBUTE VALUES
try {
var tsdObjectID = objRows.Results[i].TriggeredSendDefinition.ObjectID;
if (debug) {
Write("<br><br>tsdObjectID: " + tsdObjectID);
}
if (tsdObjectID) {
var tsdObj = prox.retrieve("TriggeredSendDefinition", tsdObjProps, {Property: "ObjectID", SimpleOperator: "equals", Value: tsdObjectID});
if (tsdObj && tsdObj.Results.length > 0) {
row.Name = tsdObj.Results[0].Name;
var spObjectID = tsdObj.Results[0].SenderProfile.ObjectID;
if (debug) {
Write("<br><br>spObjectID: " + spObjectID);
}
if (spObjectID) {
var spObj = prox.retrieve("SenderProfile", spObjProps, {Property: "ObjectID", SimpleOperator: "equals", Value: spObjectID});
if (spObj && spObj.Results.length > 0) {
row.SenderProfile_Name = spObj.Results[0].Name;
}
}
var scObjectID = tsdObj.Results[0].SendClassification.ObjectID;
if (debug) {
Write("<br><br>scObjectID: " + scObjectID);
}
if (scObjectID) {
var scObj = prox.retrieve("SendClassification", scObjProps, {Property: "ObjectID", SimpleOperator: "equals", Value: scObjectID});
if (scObj && scObj.Results.length > 0) {
row.SendClassification_Name = scObj.Results[0].Name;
}
}
}
}
} catch (e2) {
if (debug) {
Platform.Response.Write("<br><br>e2: " + Stringify(e2));
}
}
if (debug) {
Write("<br><br>row: " + Stringify(row));
}
row.UpdatedDate = new Date();
var de = DataExtension.Init(obj);
var rowsAddedUpdated = 0;
// ADD/UPDATE DATA EXTENSION ROW
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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment