the personal blog of adam spriggs
Tips for the technical side of Salesforce Marketing Cloud.
Here’s an AMPscript snippet to determine if a date falls in the Daylight Savings period as defined by NIST.gov:
Output:
dateToCheck: 2023-10-25
month: 10
day: 25
dayOfWeekToFind: Wed
dayOfWeekNum: 4
prevSunday:
isDST: 1
Try it yourself with multiple dates: https://mcsnippets.herokuapp.com/s/6tGSENDB
NOTE: this doesn’t handle times, but could be expanded to do so.
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):
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:
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).
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 aListIDor theObjectIDof a Data Extension. If you’ve got an inventory ofDataExtensionSOAP 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.
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.