Journey Email and Job Queries

Journey Emails

This comes up a lot — trouble joining the _Journey and _JourneyActivity data views. The issue is that the VersionID is a UniqueIdentifier data type behind the scenes. The workaround is to cast/convert it to a varchar(36):

Journey Jobs

I like to maintain a list of Journey _Job records beyond the 6 month lookback threshold for the data views. You can apply the same VersionID join trick on this one also:

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.

Schema

Code

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.