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?
1 Answer
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.
answered Mar 20, 2021 at 18:29
