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.

(BTW, check out what’s available — it’s everything in the Recent Email Sending Summary Report, plus some more details.)

Simply create a Script Activity in Automation Studio like this. Before the first run, update the TargetDECustomerKey and TargetDECategoryID values for your preferred Data Extension and folder. I generally use Send for the Data Extension name and customer key. As noted in the script, you can comment out Data Extension creation part after the first run.

This script sets a filter (it’s like a where-clause in SQL), iterates through the Send Object results, and upserts them into the specified Data Eextension. 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, query it, export it with an Extract Activity, or lookup and display it in an email or CloudPage with AMPscript.

Automation Status Across Business Units

If you need to get a status snapshot of Automations across business units, here’s one way to go about it.

Add this code to a CloudPage in the parent business unit, update the mids array to include the desired values and publish.

You could also set something like this up in a Script Activity and write the results to a Data Extension for use in another process.

NOTE: As leto96 mentioned in the comments, CloudPages are public and should not be considered secure. Anyone with the URL can access it. Initial SSJS development is best done in a CloudPage, where you have a way to debug with output. Just be careful to unpublish or secure any utility/reporting type pages after you’re done.

Updating Salesforce Objects with Server-Side JavaScript

If the custom preference center in your integrated Marketing Cloud account needs to update Salesforce object attributes, you’re probably acquainted with the UpdateSingleSalesforceObject, RetrieveSalesforceObjects and CreateSalesforceObject AMPscript functions.

Well, if you’re like me and you’d rather build your custom preference center with Server-Side JavaScript (SSJS), then you’ll need a way to use these AMPscript-only functions. Here’s my general approach for wrapping the AMPscript function in SSJS:

One-Click Unsubscribe with AMPscript

Need to custom one-click unsubscribe landing page in AMPscript? Here’s how I generally code it:

This leverages the AMPscript API functions and mirrors the process you’d take if you were doing this with the Marketing Cloud SOAP API.


20190320 Update
If you’re having trouble finding the Publication ListIDs in your account, you can drop this bit of Server-Side JavaScript in your page. It’ll output a list of them.

Prune rows in a Data Extension with SSJS

Say you want to delete a million rows from a Data Extension in Salesforce Marketing Cloud (SFMC). Doing that with the SFMC Web Services can be a pain. One alternative is to utilize Server-Side JavaScript (SSJS) and a Landing Page.

I built a landing page that posts to itself and runs this pruneRows() function. I configured a Data Extension that contains the keys to delete. The script retrieves all of the rows with that key, deletes them and updates the DE of keys with the results.

You may have to experiment with the number of Row Keys to retrieve at a time. If the number is too high, the landing page will time-out. In my scenario, I found that processing 50 at a time worked well (line #8).

For further reading: