0

I need to create a report with number of sends, number of deliveries, delivery rate, unique opens, cumulative opens, open rate, unique clicks, cumulative clicks, click rate and email name, but I need to show this data for each day, starting from 01/2019 to today.

Account send summary report shows this data but in aggregate, so for example if I set a custom date range it will only show the total numbers for that period of time, not daily. So I would need to do one report for each day and that's something like 500 reports... Is there any other way? Or is there a way to automate the process?

3

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.

| improve this answer | |
  • Thanks @Adam for learning of the day :) – Naveen VM May 25 at 16:03
  • Thanks this was very helpful! I configured the DE and the script activity. Then I ran the activity and after that a used a query activity to get only the data I needed and order it by SendDate. – Paola May 26 at 12:08
1

You do it in Discovery Reports if you have access to it. Please go to Analytics Builder > Reports > Discovery Categories > Recent Email Send Summary Report.

Here you can get all email related parameters by dates.

Note: You can only able to retrieve from 0 to 180 previous days for all the reports.

enter image description here

Let me know if this helps.

| improve this answer | |
  • Thank you, but unfortunately we don't have access to Discover Reports. Is there another way? The number of emails sent per day and the email name would be enough. I'm thinking with a data view maybe? – Paola May 25 at 10:01
  • Yes you have to use data view and aggregate the count on email sends and group by date. This is also applicable for last 180 days. If you want to do more than 180 days then you would need to go to Tracking extract from the automation studio and extract one by one month in a CSV file. – Naveen VM May 25 at 11:10

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.