Content Builder Asset Inventory in a Data Extension

Sometimes it’s handy having a list of Content Builder Assets by folder in a Data Extension, especially for migrating Package Manager.

Before you add this Script Activity in Automation Studio, add your API creds and update the Category ID for your target Data Extension. You can also update the query criteria starting on line 91, if needed.

As always, let me know if you have any feedback in the comments below (or on Github).

Storing File Import Results in a Data Extension

I don’t know about you, but I’ve found monitoring crucial File Import Activities in SFMC a challenge. I think it’s important to ensure all of the rows in the file got imported or have some recourse to identify exceptions — outside of accessing the results file that gets dropped in the SFTP Import folder or relying on the import results email.

Here’s a script that retrieves and iterates through the ImportResultsSummary SOAP object data (via WSProxy) and writes it to a Data Extension of the same name.

NOTE: The DestinationIDis either a ListID or the ObjectID of a Data Extension. If you’ve got an inventory of DataExtension SOAP object data, you can use something like this dataset to build a pretty comprehensive report on import exceptions.

I started setting proper Customer/External Keys on all Import Activities after using this data, just so ImportDefinitionCustomerKey is meaningful. One less thing to have to track down when troubleshooting.

Of course, I welcome any feedback y’all might have on this.



Storing SFMC AuditEvents in a Data Extension

Audit Events are worthwhile to explore in Salesforce Marketing Cloud (SFMC) if you’re in a vertical that includes tighter roles and permissions. You can supplement your audit scheme by retrieving these Audit Events via REST and writing them to a Data Extension (DE).

First, you’ll need to enable the feature here: Email Studio > Admin > Security Settings > Enable Audit Trail Data Collection > Yes

After you’ve collected data for a few days, create a Script Activity like this and set it to run on the desired interval in an Automation.

You’ll need to add your target Data Extension’s Category ID, MID, REST API Tenant, Client Id and Client Secret.

Recursively Republishing Triggered Send Definitions

I do love well though-out SFMC email architectures, especially ones that are modular. One of the drawbacks of using the AMPscript reference blocks for sharing content across email definitions is that, while the actual changes are done in only a few places, getting the actual send definitions updated requires an additional step — republishing. This goes for Journey emails, also.

Here’s a script that I wrote to automate pausing, publishing and starting Triggered Send Definitions (TSD) by folder. It’ll recursively traverse the folder structure if you have a bunch of nested folders. Results of the process are logged in a Data Extension. To get started, create a Data Extension with this schema:

Name: republish_trigger_log
External Key: republish_trigger_log

Here’s the script — just update line 10 with the parent folder’s Category ID.

This script starts at the CategoryID specified on line 10 and loops down through all of the child folders.

NOTE: You can find the Category ID by inspecting the URL at the bottom of your browser when you hover over the folder in SFMC.

When it encounters and “Active” Triggered Send Definition, it pauses, publishes and starts it. Results of those three actions are recorded in republish_trigger_log.

I recommend running this Script Activity in an unscheduled Automation.

Retrieving and Storing Aggregated Triggered Send Data

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

This data is helpful in that it shows aggregated send data for all Triggered Sends — traditional and Journey Builder ones.  A new row is added every time a Triggered Send Definition is published, so it’s also a historical record of publishing events — along with the performance of each “version” of the Triggered Send Definition.  The sum of each of the activity counts represents what you would see in Tracking for a particular Triggered Send Definition.

One nice benefit is that the results also contain the number of emails queued to be sent at a specific point in time.  So if you retrieve this data hourly (with some additional configuration), you could monitor the sending queue over time.

(If you’re looking for something like this for TXM Email sends, you’re stuck with the queue metrics endpoint and aggregating the activity yourself.)

To get started, create a Data Extension with this schema:

Name: TriggeredSendSummary
External Key: TriggeredSendSummary

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

This script does a Describe of a couple of SOAP objects to get the retrievable properties, sets a filter (it’s like a where-clause in SQL), iterates through the TriggeredSendSummary Object results, does a few more related retrieves for specific attribute, and finally upserts the results into the specified data extension. If you don’t want to retrieve all records every time, you can update the filter criteria on line 81 and get more specific. I’ve generally not restricted it, because it’s not that much data to retrieve.

Finally, schedule this to run on an interval appropriate for your needs.

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.