As @Naveen indicated, the key field for activity data in SFMC isn't the date of the event, it's send job. If you want it by day, you'll need to extract and import tracking data using a Tracking Extract.
However, if you want quick access to aggregated activity data by send job you can retrieve the Send SOAP object data via API or via WSProxy.
WSProxy is the easiest. To get started, create a Data Extension with the following schema. Name it Send and set the Customer/External Key to Send.
|Name |Data Type |Length|Precision|Scale|Primary Key|Required|Default Value|
|-------------------------------|------------|------|---------|-----|-----------|--------|-------------|
|ID |Number | | | |1 |1 | |
|SendDate |Date | | | |0 |0 | |
|EmailName |Text |100 | | |0 |0 | |
|Subject |Text |200 | | |0 |0 | |
|Status |Text |15 | | |0 |0 | |
|Additional |Text |50 | | |0 |0 | |
|BCCEmail |EmailAddress| | | |0 |0 | |
|Client_ID |Number | | | |0 |0 | |
|Client_PartnerClientKey |Text |64 | | |0 |0 | |
|CreatedDate |Date | | | |0 |0 | |
|Duplicates |Number | | | |0 |0 |0 |
|Email_ID |Number | | | |0 |0 | |
|Email_PartnerKey |Text |64 | | |0 |0 | |
|EmailSendDefinition_CustomerKey|Text |36 | | |0 |0 | |
|EmailSendDefinition_ObjectID |Text |36 | | |0 |0 | |
|ExistingUndeliverables |Number | | | |0 |0 |0 |
|ExistingUnsubscribes |Number | | | |0 |0 |0 |
|ForwardedEmails |Number | | | |0 |0 |0 |
|FromAddress |EmailAddress| | | |0 |0 | |
|FromName |Text |130 | | |0 |0 | |
|HardBounces |Number | | | |0 |0 |0 |
|InvalidAddresses |Number | | | |0 |0 |0 |
|IsAlwaysOn |Boolean | | | |0 |0 | |
|IsMultipart |Boolean | | | |0 |0 | |
|MissingAddresses |Number | | | |0 |0 |0 |
|ModifiedDate |Date | | | |0 |0 | |
|NumberDelivered |Number | | | |0 |0 |0 |
|NumberErrored |Number | | | |0 |0 |0 |
|NumberExcluded |Number | | | |0 |0 |0 |
|NumberSent |Number | | | |0 |0 |0 |
|NumberTargeted |Number | | | |0 |0 |0 |
|OtherBounces |Number | | | |0 |0 |0 |
|PartnerKey |Text |64 | | |0 |0 | |
|PreviewURL |Text |500 | | |0 |0 | |
|SendLimit |Number | | | |0 |0 |0 |
|SendWindowClose |Date | | | |0 |0 | |
|SendWindowOpen |Date | | | |0 |0 | |
|SentDate |Date | | | |0 |0 | |
|SoftBounces |Number | | | |0 |0 |0 |
|UniqueClicks |Number | | | |0 |0 |0 |
|UniqueOpens |Number | | | |0 |0 |0 |
|Unsubscribes |Number | | | |0 |0 |0 |
|InsertDate |Date | | | |0 |0 |getDate() |
|UpdatedDate |Date | | | |0 |0 | |
Second, create a Script Activity named Send in Automation Studio with the following:
<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");
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>
Once these are both configured, simply add the Send Script Activity to an Automation and schedule it to run on the desired interval.
This will pull data for the entire send history in an account. To limit the range you can uncomment the filter section and revise it as needed. The data set is so small, I generally just retrieve everything every run.
I like to use this as a starting place for any reporting that requires aggregated activity data.
