Say you need to find the lowest of three numbers in an email — numbers you’ve retrieved from a Data Extension. Here’s one way to do it in AMPscript with a set of inline-if statements:
Output
num1: 105.54
num2: 88.73
num3: 106.32
lowestNum: 88.73
Category: Salesforce Marketing Cloud
Tips for the technical side of Salesforce Marketing Cloud.
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:
Retrieving and Starting an Automation with SSJS
Server-side Script (SSJS) Activities are a hidden gem of the Salesforce Marketing Cloud. They’re useful for a wide range of applications. I ran into a scenario where I wanted to run an Automation programmatically — if it’s not already running.
It was way harder than it needed to be. Hope this saves you some frustration:
The retrieve is for getting the ObjectID and Status of the Automation, which you then use to check to see if it’s running and then invoke a Perform if it isn’t.
The frustrating part of this was that the ObjectID is not a retrievable property of the Automation Object (unlike Status). It’s only returned in the result-set of a successful Retrieve.
I’d suggest testing this script in a CloudPage until you get it working and then move it to the Script Activity.
For further reading:
Check List Membership on a Landing Page
Suppose you want to see if a Subscribers exists on a List in a Landing Page or Microsite Page in the Salesforce Marketing Cloud? I generally do that with SSJS for various reasons. It’s also possible to do it with AMPscript API functions. Bonus that it’s faster:
For more info:
Landing Pages
Microsites
AttributeValue()
RequestParameter()
Site-based AMPscript Functions
API AMPscript Functions
Using Complex Filter Parts
A case for the MicrositeURL() function
Do you know about the MicrositeURL() AMPscript function? It’s relatively new. It sure is handy if you’re building custom preference centers in Enterprise 2.0 business units.
The idea is that you can encrypt custom parameters in your email links to Microsite pages and then retrieve the unencrypted values with an AMPscript function. Once you have the business unit MID in the microsite page, you can use it to ensure your API calls are for the correct business unit.
In your email you would have this code:
In the case where the the subscriberMID is not set, default to the parent business unit’s MID, which is accessible from the memberid personalization string.
One gotcha: If you build your custom preference centers with Server-Side JavaScript, there’s no SSJS function to retrieve the additional MicrositeURL parameters — you to use AMPscript. Your code may end up looking something like this:
For further reading:
MicrositeURL()
RequestParameter()
Personalization Strings
Server-Side JavaScript