1

I have created a "daily sent" email that gets it's content from data pulled from the _SENT and _JOB data views. The query runs at 4:00 and with most emails finished sending by about 2:00 it ofter works with emails that have a send size under 1 million. With bigger sends, or on days when I have alot of sends, the data is not available in the data view at 4:00 but I can run the query again at 8:00 and get the data that is missing.

My query is pulling the EmailName and SubscriberKey and counting the subscriber keys for each email name to give me the sent email count for each job. Is there a way to bypass pulling all of the SubscriberKeys and simply pull the sent count total quickly after the send is complete using the EmailName?

1 Answer 1

3

Yes, you can get this data by pulling the Send SOAP API object data with a Script Activity utilizing the WSProxy library in Automation Studio.

Create your Data Extension like this. (ID is the JobID).


Name: Send
External Key: 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        |               |

Here's the code to populate the data extension:

<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 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>

Schedule this Script Activity to run on a schedule as needed. Once you have data, you can toolbar-download it, export it with an activity, or display it in an email or CloudPage.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .