Retrieving and Storing Aggregated Send Data

One of the great things about the WSProxy library for Server-Side JavaScript in Salesforce Marketing Cloud (SFMC) is that it has reinvigorated the use of SOAP API objects in the platform. SOAP objects are still foundational to understanding the SFMC data model.

Here’s one of my favorite applications of WSProxy — retrieving and storing aggregated Send data in a Data Extension using a Script Activity.

First, create a Data Extension with this schema:

(BTW, check out what’s available — it’s everything in the Recent Email Sending Summary Report, plus some more details. This data in a Data Extension for use in other places — mmmmm, so good.)

Next, create a Script Activity in Automation Studio like this.

This script does a Describe of the SOAP object to get the retrievable properties, sets a filter (it’s like a where-clause in SQL), iterates through the Send Object results, and upserts them into the specified data extension. If you don’t want to retrieve all sends every time, you can uncomment the filter criteria and get more specific. I’ve generally not restricted it by date, because it’s not that much data to retrieve.

Finally, schedule this to run on an interval appropriate for your needs. At least daily is good.

Once you have data, you can toolbar-download it as needed, export it with an Extract Activity, or lookup and display it in an email or CloudPage with AMPscript.