Not sure why this is so hard to find:
Category: Salesforce Marketing Cloud
Tips for the technical side of Salesforce Marketing Cloud.
Is it Daylight Savings?
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/KcEDbfxA
NOTE: this doesn’t handle times, but could be expanded to do so.
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
DestinationID
is either aListID
or theObjectID
of a Data Extension. If you’ve got an inventory ofDataExtension
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.