Search Content

Scriptmas 2023

As we reach the end of 2023, we’re rapidly approaching that time of year again. It’s that time where all of the HowToSFMC Elves and helpers get together to share their Scriptmas joy with the world. For the fourth year in a row, HowToSFMC is proud to announce Scriptmas is back!

glasses photo

What is HowToSFMC

HowToSFMC is a Salesforce Marketing Cloud resource by a group of practitioners with a range of SFMC experience. The aim of the site is to take common “How do I?” questions and instead of make a single how-to document, crowd-source many options. Including from the wider community.

Armed with these choices, we’ll help to provide context to the decision making and empower you to build your ideal solution.

glasses photo

Join us on Slack

The email marketers behind HowToSFMC have set up a dedicated Slack workspace for SFMC users to connect, ask/answer questions, contribute to the ecosystem, and learn from each other. To join our community, please fill out the form and our admins will grant entry. As this is a SFMC community, please be sure to fill out the details to let us know how long you've worked with SFMC. Including a LinkedIn or StackExchange profile if available, will help our admins expedite the processes!

Hero Image

Video Playlists

Recent Articles

Published 02/15/2024
Spring '24 Release Review

After what felt like a longer than usual January, Salesforce finally shared the release notes for the Spring 2024 release. After such a long wait for new features and with the extended delay and overall lateness to the party with some stacks only having 10 days to get prepared for the changes, surely this release is setting up to be a big one!

Alas, it seems not.

In fact, much of this release seems to be about less to do with Marketing Cloud Engagement and is padded out with references to Data Cloud, Cross Cloud capabilities and repeated content - which for many customers may not be entirely useful. However, let’s get into the details of what you can expect in the Spring 2024 release.

WhatsApp as a channel in SFMC gets a little bit of a face lift and brought up to speed with some of the other channels available in the platform. New transactional messaging API capabilities allow users to send non-promotional messages from outside of Journey Builder. You’ll also start to see WhatsApp engagement reporting made available in Intelligence Reports for Engagement (The reporting that replaced Discover reports) but not in Intelligence Reports for Engagement Advanced (The additional premium Datorama SKU?). But it’s something!

More of a Google change than an SFMC change, but users who use MobilePush and leverage the Firebase FCM APIs will need to update to HTTP v1. The Firebase FCM APIs have been deprecated for almost 8 months at this point, so it’s good that Salesforce have finally started to allow users to update. Check out the Firebase documentation here to find out more.

Back in the Winter 2024 release, Salesforce moved the journey optimisation dashboard from open beta to general release and the relentless approach to making users optimise the way they work seems to be continuing. Now, SFMC will make recommendations to you if you have items such as back to back decision splits which could impact journey performance. It currently won’t prevent you from activating journeys with less than optimal performance, but I wouldn’t be surprised if that changes in future releases.

The Journey History Dashboard gets some overdue enhancements where you may have similar activities being indistinguishable in the user interface. Now activities will have Activity IDs to enable you to distinguish between them. On the subject of dashboards, from the journey dashboard you’ll be able to pause and resume multiple journeys at once from the journey dashboard rather than having to go into each individually. This is the natural progression from the bulk stop journey capability from Winter 2024. Keen to see how Salesforce tackles the configuration elements of pausing journeys as not every reason to pause is created equal!

Ever wished your emails would send faster out of Journey Builder? Approximately 5 weeks after the release lands for some accounts - Salesforce has stated a new High-Throughput Sending for Journey Builder setting will be available in the journey settings panel. No mention of cost implication, or whether this essentially applies high priority sending to each of the email activities in a journey. There’s probably not many scenarios where you would elect to not use High-Throughput Sending for Journey Builder unless there’s an additional burden on Super Message consumption? It’s a little light on the detail in the approximately 20 words in the release so keep your eyes peeled on your super message consumption!

What could be the most significant part of this release is the introduction of being able to Track any URL interaction in Engagement Split Activities in Journey Builder. Again, no details have been shared about the specific implementation of this new capability, will this be done via a list in the user interface or will users be required to paste requisite URLs into a free text field? We’ll soon find out.

Einstein Probabilistic Opens is being renamed to Einstein Metrics Guard. Nothing new to see here, just padding out the release with a rename.On the subject of padding out the release. WhatsApp is also getting referenced here. Just with a bit more detail in that you’ll get dashboards and dimensions to build your pivot tables against.

A couple of things to call out here, some documentation for what has previously been undocumented REST API endpoints. Great to see the documentation for these, many of which have been previously shared on StackExchange and through various blogs and communities. However, getting this documented means any of the solutions that have been built under the caveat of “This is undocumented and may stop working with no recourse” now have some extra support.

A neat new feature is the ability to create one use imports via the REST API. Previously you could have leveraged an existing Import Definition via the SOAP API to deliver similar type capabilities. Plenty of use cases for this kind of functionality and you can leverage S3, Azure Blob, Google Cloud Storage or any of your existing File Transfer Locations.

A couple of items here, both hygiene and optimisation related. First one is around Data Retention and Data Extensions that would be deleted via the retention configuration. These can no longer be linked to the Contact model. If you’ve got any of these and they’re used in your Journeys, make sure to check as this could end up creating some unexpected outcomes if links are getting removed.

What seems like a new enforcement of a known limit within SFMC is the cap on field length for the field used for sendable data extension data relationships. Where SubscriberKey has a cap of 254 characters, this will now be enforced within Data Extensions in the sending relationship. This shouldn’t impact any other text fields that you may use for content. Again, check the sendable fields in your Data Extensions to make sure this doesn’t cause issues in your SFMC org as this will be enforced on existing as well as new activities for email sends or Journeys.

Process Builder is being retired (and has been since 2022ish) but this time for Marketing Cloud Connect. The retirement will happen beginning after the Spring 2024 release and is expected to run until May 2024. Migrations to record-triggered flows will happen automatically when a Journey using the object is published. All journeys that depend on that same object will also be migrated.

Distributed Marketing gets a couple of enhancements.

  • A new campaign performance dashboard
  • The option to create a single use template with Phrases content blocks
  • Additional personalisation from 5 new objects including Opportunity, Account and Case

Having been lagging behind other elements of SFMC, CloudPages will move from collections to Folders and get a new recycle bin similar to Content Builder. This should make things easier and remove the need for hacky workarounds to move Cloud Pages between folders. You’ll also be able to nest folders which will allow a more efficient storage taxonomy for your Cloud Pages.

The Marketing Cloud Engagement App is being retired. If you have it installed already you can continue to use it until retirement on May 5th 2024. Recommendation from Salesforce is to use your mobile browser instead. If you’re an active SFMC user on the go, it would be good to hear some community comments of your experience of interacting with SFMC on your mobile device.

IP addresses for Event Notification System are changing. If you have an IP Allowlist within your business to listen to events, you’ll need to get these updated to match your stack.

Automation Studio gets some general enhancements. Salesforce is claiming that scheduled automations are now 62% more on time. Salesforce have also previously claimed that Importing Data from one Data Extensions to another would be up to 10x faster, so it would be interesting to see if there is any evidence to back up this claim.The Data Extension Storage report available in the Setup tab is getting the customer key added to it, this should help you find any of those Data Extensions that don’t appear in the user interface but still have storage usage attached to them!If you’ve ever had a situation where an automation has been paused and you’re not sure why or who did it. Salesforce has decided that you should know who most recently paused the journey so you can make sure the right person is aware that they’ve not reactivated a journey that has been paused!

This is the shortest set of release notes and the review is the shortest written, even when compared to the 5 release cycle that was replaced a few years ago. Progress in the platform is slow, there is padding in the documentation and repetition between releases. The level of detail in the release notes is getting less and less. With the release notes published as late as they are to the release going live this is becoming unacceptable. Salesforce Marketing Cloud Engagement is an enterprise platform and the quality of the information provided in these notes is a fraction of what is published for other platforms in the Salesforce ecosystem.

That’s not to say that there aren’t good things in this release, there are. Most of them are quality of life releases rather than game changing capabilities. Cloud Pages getting folders is great, the new REST API documentation is also welcomed (albeit not new capability just not admission of the capability) and the single use imports without needing an Import Definition through the REST API is definitely welcomed.

Fingers crossed we get a bit more in the next release!

Read more
Published 12/14/2023
12 Days of Scriptmas - 2023

As we reach the end of 2023, we’re rapidly approaching that time of year again. It’s that time where all of the HowToSFMC Elves and helpers get together to share their Scriptmas joy with the world. For the fourth year in a row, HowToSFMC is proud to announce Scriptmas is back!

Whether you’ve got a script you’d like to submit for the HowToSFMC Helpers to celebrate Scriptmas cheer in 2023 or you’re looking to see what the community has got to offer, that may be a little gift to yourself. Make sure to check back in the run up to the big day.

If you’re not familiar with Scriptmas, head up to the search bar and check out the submissions and contributions we’ve had over the last few years. From December 13th up to Christmas Eve itself we’ll be revealing one piece of Scriptmas magic for the world to see.

Check out the scripts revealed so far below:

On the first day of Scriptmas, <ins>Ruchika</ins> gave to us - a complete example to robustly personalise email content to subscribers, including graceful fallback content! Take a look and see how you can use this in your day to day.

<details> <summary>Click here to see the Day One Script</summary>

/* Define variables */
SET @recipientName = "Friend"
SET @magicalReunionEvent = "Christmas Gathering"

/* Check if the recipient's name is available */
IF NOT EMPTY(@recipientName) THEN

  /* Personalized greeting for the recipient */
  SET @greeting = CONCAT("Dear ", @recipientName, ",")

  /* Content for the email */
  SET @emailContent = CONCAT(
    "<h2 style='color: 
    "<p>As we prepare for our ", @magicalReunionEvent, ", we can't help but feel the warmth of togetherness in the air.</p>",
    "<p>We hope this Christmas event will bring back fond memories and create new magical moments!</p>",
    "<p>Looking forward to seeing you at the gathering!</p>"

  /* Output the personalized greeting and content */


  /* Default content if recipient's name is unavailable */
  SET @defaultContent = "<p>Dear friend, join us at our Magical Reunion for a heartwarming Christmas event!</p>"

  /* Output default content */




<br /> Thank you again Ruchika! Make sure to check back tomorrow for our second piece of Scriptmas joy!

On the second day of Scriptmas, <ins>Nicolò</ins> gave to us… A script to update and run a single script activity in one fell swoop. No more writing, saving and manually running once. Perfect for those times where your query needs to change automatically! Customise this and stick it in an automation and you’ll be laughing your way to the automation bank!

<details> <summary>Click here to see the Day Two Script</summary>

<script runat="server">
    Platform.Load("core", "1.1");

    // setting the required parameters

    var deName = "DeName";                  // Name of the DE target of the query
    var deCustomerKey = "deCustomerKey";    // Customer Key of the DE target of the query
    var queryParameter = "queryParameter";  // Parameter to be changed in the query
    var queryCustomerKey = "queryKey";      // Customer Key of the query
    var queryObjectId = "queryObjectId";    // Object Id of the query

    // running function to edit the query

    var qd = QueryDefinition.Init(qkey);

    var sql = "SELECT\r\n      sub.Subscriberkey\r\n    , sub.EmailAddress\r\nFROM _Subscribers AS sub\r\nINNER JOIN _Sent AS sent\r\n    ON sub.SubscriberKey = sent.SubscriberKey\r\nINNER JOIN _Job AS job\r\n    ON sent.JobId = job.JobId\r\nWHERE job.EmailName = \"" + queryParameter + "\"";
    var prox = new Script.Util.WSProxy();
    var options = {
      SaveOptions: [
          "PropertyName": "*",
          SaveAction: "UpdateAdd"

    var data = {
      CustomerKey: queryCustomerKey,
      ObjectID: queryObjectId,
      QueryText: sql,
      TargetType: "DE",
      DataExtensionTarget: {
        Name: dename,
        CustomerKey : deCustomerKey

    var desc = prox.updateItem("QueryDefinition", data, options);

    // run the query 


<br />

Thank you again Nicolò! I wonder what Scriptmas will bring us tomorrow? You’ll have to come back and see!

On the third day of Scriptmas, <ins>Ralph</ins> gave to us… A neat little trick to leverage AMPscript within an SSJS Activity to create or update a case in Salesforce CRM.

<details> <summary>Click here to see the Day Three Script</summary>

var userInput = Platform.Function.ParseJSON(Platform.Request.GetPostData());
if (userInput.casedata) {
var caseId = createCaseInSalesforce(userInput);
response.caseId = caseId; 

 * @function createCaseInSalesforce
 * @description Creates a case in Salesforce by executing an AMPscript block.
 * @param {Object} userInput - The user input containing case data.
  * @returns {String} The ID of the created case.
 * @throws Will throw an error if case creation fails.
function createCaseInSalesforce(userInput) {
    try {
    var caseVariables = userInput.casedata.split(',');
    var caseFieldValues = [];

    // Loop through the dynamic AMPscript variables and populate the caseFieldValues array
    for (var i = 0; i < caseVariables.length; i++) {
        var key = caseVariables[i].replace(/^\s+|\s+$/g, ''); // Trim whitespace
        var value = userInput[key]; // Get the value from the userInput object

        // Only count fields if a value exists
        if (value != undefined) {
            caseFieldValues.push("'" + key + "', '" + value + "'"); // Push the field name and value directly
        // Build the AMPscript string to create the Case object in Salesforce
        var createCaseSalesforceObjectAmpscript = 'SET @Case = CreateSalesforceObject("Case", ' + caseFieldValues.length + ', ' + caseFieldValues.join(', ') + ')';
        // Execute the AMPscript block and retrieve the result
        var caseId = caseAmpScript(createCaseSalesforceObjectAmpscript);
        return caseId;
    } catch (error) {
        throw { message: "Error creating case in Salesforce: " + error.message };

/*------------ EXECUTE CASE AMPSCRIPT FUNCTION ------------*/
 * @function caseAmpScript
 * @description Executes a block of AMPscript code within SSJS. This function
 *              takes a string of AMPscript code, wraps it in delimiters to
 *              form a valid AMPscript block, then uses the TreatAsContent 
 *              function to evaluate the block. After execution, the function 
 *              retrieves the value of a predetermined variable set within the
 *              AMPscript code.
 * @param {String} code - The AMPscript code to execute. This should be a 
 *                        string containing valid AMPscript syntax.
 * @returns {String} The value of the '@Case' variable as set by the executed 
 *                   AMPscript. It's assumed that the AMPscript code sets a 
 *                   variable named '@Case'. If '@Case' is not set, the 
 *                   function returns undefined.
 * @example
 * // Example of using the ampScript function:
 * var caseIdAmpScript = "SET @Case = CreateSalesforceObject('Case', 3, 'Subject', 'Inquiry', 'Description', 'Details')";
 * var caseId = ampScript(caseIdAmpScript);
 * // caseId now contains the Salesforce ID of the created case object.
function caseAmpScript(code) {
    // Wrap the provided code in an AMPscript block
    var ampBlock = '%%[' + code + ']%%';

    // Treat the AMPscript block as content and execute it

    // Retrieve the value of the '@Case' variable set by the AMPscript
    return Variable.GetValue('@Case');


<br />

Thank you for your Scriptmas treat, Ralph! Check back tomorrow to see what’s behind Script Door Number Four…

On the fourth day of Scriptmas, Salesforce Marketing Champion <ins>Pato</ins> gave to us… An AMPscript driven method to retrieve a dynamically populated countdown timer for use in email or Cloud Pages!

<details> <summary>Click here to see the Day Four Script</summary>


This script lets you display a countdown timer in your emails using

Step 1. Create a dynamic timer with
Step 2. Get the id of your timer from the embed code window
Step 3. Set the enddate variable with the end date using ISO format
Step 4. Display the timer!


set @enddate = "2024-01-01T00:00:00.0000000-00:00"
set @enddate_f = formatdate(@enddate,"YYYY-MM-DDThh:mm:sszzz")

<table width="100%" cellspacing="0" cellpadding="0">
<td align="center">
<img src="" width="500"/>
<h1>Countdown until the new year!</h1>
<img src=""
style="display:inline-block!important;" border="0" alt=""></td></tr></table>


<br />

Thank you for your submission, Pato! If you’re looking to see this Scriptmas treat in a bit more context, head over to <ins>MCSnippets</ins> and take a look.

On the fifth day of Scriptmas, <ins>Erlend</ins> gave to us… An all in one Data View query to summarise each subscribers email engagement behaviour for the last 60 days! A great way to see across 5 golden data views!

<details> <summary>Click here to see the Day Five Script</summary>

 ,  COUNT(DISTINCT o.JobID) AS TotalOpens
 ,  COUNT(DISTINCT c.JobID) AS TotalClicks
 ,  COUNT(DISTINCT s.JobId) AS TotalSent
 ,  COUNT(DISTINCT b.JobId) AS TotalBounces
 ,  COUNT(DISTINCT CASE WHEN c.IsUnique = 'true' THEN o.JobID END) AS TotalUniqueOpens
 ,  COUNT(DISTINCT CASE WHEN c.IsUnique = 'true' THEN c.JobID END) AS TotalUniqueClicks
 ,  COUNT(DISTINCT CASE WHEN c.IsUnique = 'true' THEN b.JobId END) AS TotalUniqueBounces
 ,  COUNT(DISTINCT CASE WHEN o.EventDate > DateAdd(Day, -7, GetDate()) THEN o.JobID END) AS OpensLast7Days
 ,  COUNT(DISTINCT CASE WHEN c.EventDate > DateAdd(Day, -7, GetDate()) THEN c.JobID END) AS ClicksLast7Days
 ,  COUNT(DISTINCT CASE WHEN s.EventDate > DateAdd(Day, -7, GetDate()) THEN s.JobID END) AS SentLast7Days
 ,  COUNT(DISTINCT CASE WHEN b.EventDate > DateAdd(Day, -7, GetDate()) THEN b.JobID END) AS BouncesLast7Days
 ,  COUNT(DISTINCT CASE WHEN o.EventDate > DateAdd(Day, -30, GetDate()) THEN o.JobID END) AS OpensLast30Days
 ,  COUNT(DISTINCT CASE WHEN c.EventDate > DateAdd(Day, -30, GetDate()) THEN c.JobID END) AS ClicksLast30Days
 ,  COUNT(DISTINCT CASE WHEN s.EventDate > DateAdd(Day, -30, GetDate()) THEN s.JobID END) AS SentLast30Days
 ,  COUNT(DISTINCT CASE WHEN b.EventDate > DateAdd(Day, -30, GetDate()) THEN b.JobID END) AS BouncesLast30Days
 ,  COUNT(DISTINCT CASE WHEN o.EventDate > DateAdd(Day, -60, GetDate()) THEN o.JobID END) AS OpensLast60Days
 ,  COUNT(DISTINCT CASE WHEN c.EventDate > DateAdd(Day, -60, GetDate()) THEN c.JobID END) AS ClicksLast60Days
 ,  COUNT(DISTINCT CASE WHEN s.EventDate > DateAdd(Day, -60, GetDate()) THEN s.JobID END) AS SentLast60Days
 ,  COUNT(DISTINCT CASE WHEN b.EventDate > DateAdd(Day, -60, GetDate()) THEN b.JobID END) AS BouncesLast60Days
FROM _Subscribers AS sub
/*Join with all Opens a subscriber have done*/
/*NOTE: Opens may not be correct as some email provider like Apple is auto opening emails. Making this statistick flaed. Salesforce recomend to always use engagement data, like clicks instead.*/
    LEFT JOIN _Open AS o
    ON o.SubscriberKey = sub.SubscriberKey
/*Join with all Clicks a subscriber have done*/
    LEFT JOIN _Click AS c
    ON c.SubscriberKey = sub.SubscriberKey
/*Join with all Sent emails a subscriber recieved*/
    LEFT JOIN _Sent AS s
    ON s.SubscriberKey = sub.SubscriberKey
/*Join with all Bounc event a subscriber is linked to*/
    LEFT JOIN _Bounce AS b
    ON b.SubscriberKey = sub.SubscriberKey
GROUP BY sub.SubscriberKey


<br />

Thank you again Erlend for your Scriptmas treat! This is something many SFMC users will add to their toolbox I’m sure. Check back tomorrow to see what’s next this Scriptmas!

On the sixth day of Scriptmas, Salesforce Marketing Champion <ins>Lesley</ins> gave to us… The back end code to create a SFMC hosted and built rendition of hit internet game, Wordle - Using AMPscript! All you need to do is add your own front end & a Data Extension with your Wordle Words in to start your own version, with your own words.

<details> <summary>Click here to see the Day Six Script</summary>

var @rows, @row, @today, @word, @attempt, @j, @letterFromWord, @letterFromAttempt, @letterStatus, @status
var @jsonOutput, @letter1, @letter2, @letter3, @letter4, @letter5

set @today = Format(Now(), "MMMM dd, yyyy")
set @rows = LookupRows("Wordle Words","Date", @today)

/* Retrieve the 'attempt' form field value */
set @attempt = RequestParameter("attempt")

if rowcount(@rows) == 1 then
    set @row = row(@rows, 1) /* Get the first (and only) row */
    set @word = field(@row,"Word") /* Get word from row */

    /* Compare the word and the attempt */
    for @j = 1 to length(@word) do
        set @letterFromWord = substring(@word,@j,1)
        set @letterFromAttempt = substring(@attempt,@j,1)

        if @letterFromWord == @letterFromAttempt then
            set @status = 2
        elseif IndexOf(@word, @letterFromAttempt) > 0 then
            set @status = 1
            set @status = 0

        /* Concatenate the status to the @letterStatus string */
        set @letterStatus = Concat(@letterStatus, @status)
    next @j

    /* Split the @letterStatus string into individual values */
    set @letter1 = substring(@letterStatus, 1, 1)
    set @letter2 = substring(@letterStatus, 2, 1)
    set @letter3 = substring(@letterStatus, 3, 1)
    set @letter4 = substring(@letterStatus, 4, 1)
    set @letter5 = substring(@letterStatus, 5, 1)

    /* Construct the JSON-like output */
    set @jsonOutput = Concat('{"letter1": ', @letter1, ', "letter2": ', @letter2, ', "letter3": ', @letter3, ', "letter4": ', @letter4, ', "letter5": ', @letter5, '}')


<br />

Thank you Lesley for this fun little script! Now to start thinking of as many 5 character Scriptmas themed words we can all think of… Check back tomorrow for more Scriptmas joy!

On the seventh day of Scriptmas, Marketing Champion <ins>Rodrigo</ins> gave to us… A neat little bit of HTML/CSS to encourage your Cloud Pages to get found by web crawlers and for better experiences when sharing the content on social media!

<details> <summary>Click here to see the Day Seven Script</summary>

<!-- SERP metadata -->
<meta name="description" content="AddYourDescription">

<!-- Facebook metadata -->
<link rel="image_src" href="YourImageLink.png" /> <!-- always use an absolute link -->
<meta property="og:type" content="website" />
<meta property="og:title" content="AddYourTitle" />
<meta property="og:description" content="AddYourDescription" />
<meta property="og:image" content="YourImageLink.png" /> <!-- always use an absolute link -->
<meta property="og:image:width" content="1200" /> <!-- By using this image size, you define both facebook and twitter large image card types -->
<meta property="og:image:height" content="630" />

<!-- Twitter Cards -->
<meta property="twitter:creator" content="@YourName" /> <!-- If you don't have a Twitter/X fanpage, you can delete this line of code -->
<meta property="twitter:url" content="" />
<meta property="twitter:card" content="summary_large_image" /> <!-- This is the large card version for Twitter Cards -->
<meta property="twitter:site" content="@YourCompanyName" /> <!-- If you don't have a Twitter/X fanpage, you can delete this line of code -->

<!-- Add alt txt for accesibility -->
<meta property="og:image:alt" content="Add your alternative text here" />
<meta name="twitter:image:alt" content="Add your alternative text here">


<br />

Thanks again Rodrigo for this helpful boilerplate to help manage user experience when interacting with your Cloud Pages! Make sure you check back tomorrow to see what our next Scriptmas helper has in store…

On the eighth day of Scriptmas, Salesforce Marketing Champion <ins>Corrina</ins> gave to us… A SQL query to help you find what emails a subscriber was really sent (if for some reason they don’t believe it). Subscribers like to get themselves on the Scriptmas naughty list!

<details> <summary>Click here to see the Day Eight Script</summary>

SELECT J.EmailName as 'EmailName', 
from  _Sent se 
INNER JOIN ENT._Subscribers s 
ON se.SubscriberID = s.SubscriberID
ON se.JobID = J.JobID
WHERE se.EventDate >= dateadd(day, -20, getdate())
AND s.EmailAddress = ''


<br />

Thanks again for your Scriptmas treat, Corrina! Check back tomorrow to see what our Scriptmas friends have in store for us…

On the ninth day of Scriptmas, Erick gave to us… A joyful WSProxy Script to trigger an email send on a CloudPage using some nifty query string parameters. Just update the ExternalKey variable, pass an Email Address and SubscriberKey in query parameters – and you’re off to the races!

Want to make it a bit more magical? Update the script to grab a dynamic Triggered Send External Key so this can be used for multiple triggered sends.

<details> <summary>Click here to see the Day Nine Script</summary>

<script runat="server">
var prox = new Script.Util.WSProxy();

//Set TriggeredSend External Key
var tsExKey = 'ExternalKey'; 
//pass Email Address as query string parameter
var EmailAddress = Platform.Request.GetQueryStringParameter('email');
//pass Subscriber Key as query string parameter
var SubscriberKey = Platform.Request.GetQueryStringParameter('subscriberkey');

var tsDef = {
    TriggeredSendDefinition: {
        CustomerKey:  tsExKey
    Subscribers: [{
        EmailAddress: EmailAddress,
        SubscriberKey: SubscriberKey

var res = prox.createItem('TriggeredSend', tsDef);  


<br />

Thank you again Erick for sharing this nifty little trick! We’re almost in the double digits of Scriptmas, make sure you check back tomorrow what’s in store for Day 10 of Scriptmas 2024.

On the tenth day of Scriptmas, Marketing Champion <ins>Rafal</ins> gave to us… A Server Side Javascript Function to calculate the time between two dates and easy to read for all of our Scriptmas helpers!

<details> <summary>Click here to see the Day Ten Script</summary>

function timeDifferenceForHumans(minuend, subtrahend) {
    // Make sure the minuend and subtrahend are numeric timestamps that can be parts of subtraction
    try {
        if (typeof minuend !== "number") {
            minuend = new Date(minuend).getTime();
        if (typeof subtrahend !== "number") {
            subtrahend = new Date(subtrahend).getTime();
    } catch (e) {
        return "timeDifference function: Unable to parse the provided dates";

    // Calculate the time difference in milliseconds
    var timeDifference = Math.abs(minuend - subtrahend);

    // Calculate days, hours, minutes, and seconds
    var oneDay = 24 * 60 * 60 * 1000;
    var oneHour = 60 * 60 * 1000;
    var oneMinute = 60 * 1000;

    var days = Math.floor(timeDifference / oneDay);
    var hours = Math.floor((timeDifference % oneDay) / oneHour);
    var minutes = Math.floor((timeDifference % oneHour) / oneMinute);
    var seconds = Math.floor((timeDifference % oneMinute) / 1000);

    // Build strings in a format readable for humans
    var shortString = days + "d " + timePadding(hours) + "h " + timePadding(minutes) + "min " + timePadding(seconds) + "s";
    var variableString = '';

    // Days
    if (days == 1) {
        variableString += "1 day ";
    } else if (days > 1) {
        variableString += days + " days ";

    // Hours
    if (hours == 1) {
        variableString += "1 hour ";
    } else if (hours > 1) {
        variableString += hours + " hours ";

    // Minutes
    if (minutes == 1) {
        variableString += "1 minute ";
    } else if (minutes > 1) {
        variableString += minutes + " minutes ";

    // Seconds
    if (seconds == 1) {
        variableString += "1 second";
    } else if (seconds > 1) {
        variableString += seconds + " seconds";

    var object = {
        "days": days,
        "hours": hours,
        "minutes": minutes,
        "seconds": seconds,
        "variableString": variableString,
        "shortString": shortString

    // Returned object
    return object;

// Helper function to pad single-digit numbers with a leading zero
function timePadding(num) {
    return (num < 10 ? "0" : "") + num;


<br />

Who’s going to be the first to use the function to count down to Christmas? Check back tomorrow for our Day 11 Scriptmas treat!

On the eleventh day of Scriptmas, <ins>Jake</ins> gave to us… A SQL & SSJS based solution to get everything you’d need to create an Open & Click Heatmap. This is a bit of a big one, so make sure you check out the full details from Jake below!

<details> <summary>Click here to see the Day Eleven Script</summary>

* Fill with Weekday and Hours DE SSJS Script 
<script runat="server" language="JavaScript">
  Platform.Load("core", "1");
   var addToDEArray = [];

   var HeatMap_ClicksDE = DataExtension.Init('HeatMap_Clicks');
   var HeatMap_OpensDE = DataExtension.Init('HeatMap_Opens');
   for (var i = 0; i < 24; i++) {
       for (var x = 0; x < 7; x++) {
           if (x == 0) {
               var weekday = "Monday"
           } else if (x == 1) {
               var weekday = "Tuesday"
           } else if (x == 2) {
               var weekday = "Wednesday"
           } else if (x == 3) {
               var weekday = "Thursday"
           } else if (x == 4) {
               var weekday = "Friday"
           } else if (x == 5) {
               var weekday = "Saturday"
           } else if (x == 6) {
               var weekday = "Sunday"
           var addObject = {
               Weekday: weekday,
               Hour: i
    try {
   } catch (err) {


* Open Rate SQL

/* This query will provide you with the data necessary to make an Open/Click heat map for day of the week and time of day based on send time.   */

SELECT y.SendCount, y.Weekday, y.Hour,
       WHEN x.OpenCount IS NOT NULL
           /* Get the open count based on 'total opens in a Weekday & Hour of Day bucket' divided by
              'total sends in a Weekday & Hour of Day bucket' */
           THEN (CONVERT(DECIMAL(9,4),x.OpenCount) / CONVERT(DECIMAL(9,4),y.SendCount))
       WHEN y.SendCount IS NOT NULL AND x.OpenCount IS NULL
           /* instead of returning null when the resulting OpenCount is 0, we want to set the rate to 0 */
           THEN 0.0000
       ELSE null
   END AS OpenRateByTime,
       WHEN y.SendCount IS NOT NULL AND x.OpenCount IS NULL
           /* instead of returning null when the resulting OpenCount is 0, we want to set the open count to 0 */
           THEN 0
       WHEN y.SendCount IS NOT NULL AND x.OpenCount IS NOT NULL
           THEN x.OpenCount
       ELSE null
   END AS OpenCount
   SELECT Count(*) AS SendCount,
   /* the DATENAME function allows us to group by things like 'Monday' and '4' aka 4am */
   DATENAME(WEEKDAY, EventDate) AS Weekday,
   DATEPART(HOUR, EventDate) AS Hour
   FROM _Sent
) y
   /* this subquery is getting the accumulative Open Count for the Weekday and Hour of Day buckets */
   SELECT Count(*) AS OpenCount,
           /* this subquery gets all unique open data. you can always add a WHERE clause to limit
              the results to a shorter time period than the full past 6 months of data that Data Views retain */
           SELECT s.JobID, s.BatchID, o.IsUnique, s.EventDate,
           DATENAME(WEEKDAY, s.EventDate) AS Weekday,
           DATEPART(HOUR, s.EventDate) AS Hour
           FROM _Sent s
           INNER JOIN _Open o
               ON s.JobID = o.JobID AND s.BatchID = o.BatchID AND s.SubscriberKey = o.SubscriberKey
           WHERE o.IsUnique = 1
       ) a
   /* groups by weekday and hour so we can now grab the open counts just like the send counts before */
   GROUP BY a.Weekday, a.Hour
) x
ON x.Weekday = y.Weekday AND x.Hour = y.Hour

* Click Rate SQL

SELECT y.SendCount, y.Weekday, y.Hour,
       WHEN x.ClickCount IS NOT NULL
           THEN (CONVERT(DECIMAL(9,4),x.ClickCount) / CONVERT(DECIMAL(9,4),y.SendCount))
       WHEN y.SendCount IS NOT NULL AND x.ClickCount IS NULL
           THEN 0.0000
       ELSE null
   END AS ClickRateByTime,
       WHEN y.SendCount IS NOT NULL AND x.ClickCount IS NULL
           THEN 0
       WHEN y.SendCount IS NOT NULL AND x.ClickCount IS NOT NULL
           THEN x.ClickCount
       ELSE null
   END AS ClickCount
   SELECT Count(*) AS SendCount,
   DATENAME(WEEKDAY, EventDate) AS Weekday,
   DATEPART(HOUR, EventDate) AS Hour
   FROM _Sent
) y
   SELECT Count(*) AS ClickCount,
           SELECT s.JobID, s.BatchID, c.IsUnique, s.EventDate,
           DATENAME(WEEKDAY, s.EventDate) AS Weekday,
           DATEPART(HOUR, s.EventDate) AS Hour
           FROM _Sent s
           INNER JOIN _Click c
               ON s.JobID = c.JobID AND s.BatchID = c.BatchID AND s.SubscriberKey = c.SubscriberKey
           WHERE c.IsUnique = 1
       ) a
   GROUP BY a.Weekday, a.Hour
) x
ON x.Weekday = y.Weekday AND x.Hour = y.Hour

</details> <details> <summary>Full details here</summary> This query will provide you with the data necessary to make an Open or Click heat map by day of the week and time of day based on send time.

The open and click rates are calculated by grouping all individual unique open and click events by the email job that those open and clicks are attributed to. So, the data does not necessarily represent when users are opening or clicking, but rather the amount of users that are opening or clicking from emails sent in a given hour and time of the week.

There’s a good chunk of subqueries going on, so I’ll break down what’s going on at a high level and leave some comments in the query for more context.

In the FROM clause we reference the “_Sent” Data View and group by Weekday and Hour of the day. This breaks up our sends into buckets of Time of Day and Day of Week. We have to do this mainly because we have to get the total send counts for each of those buckets.

We reference “_Sent” Data View again in our LEFT JOIN subquery’s FROM clause. This is so we can get the total number of opens/clicks that are attributed to a send time hour & weekday bucket.

Now that we have our Send totals and Open/Click totals attributed to hour and weekday buckets, we can do some math in our SELECT statement to get our “OpenRateByTime” metric.

[PREREQUISITE]: you will want to fill your Data Extension with values for Weekday and Hour of Day. Here are the Data Extension asset requirements you’ll need as well as an SSJS script you can run to fill this Data Extension will all 24 Hours and all 7 Days. The script assumes you made 2 Data Extensions with the external keys of “HeatMap_Opens” and “HeatMap_Clicks”.

Field Name - Field Type - PK/Nullable

Weekday - Text - Primary Key Hour - Text - Primary Key SendCount - Number - Nullable OpenCount - Number - Nullable OpenRateByTime - Date - Nullable

Run the SSJS script and then run your SQL query and you will have heat map data in your Data Extension!

You can then create a UI of some sort that can display the results of this query or even export the results in your Data Extension and create a Pivot Table in Excel or Google Sheets.

Disclaimer: I have only run this on relatively small SFMC accounts. If scaling is an issue, note that you may have to break some of the subqueries up into separate staging queries/Data Extensions.

Merry Scriptmas! Reach out on LinkedIn for any questions and I’m happy to help!

</details> <br />

Thanks Jake for this Scriptmas treat! Check back tomorrow to see what our final Scriptmas gift may be…

On the twelfth day of Scriptmas, Marketing Champion <ins>Duc</ins> gave to us… A perfect ASCII art Christmas Tree, built using SSJS and HTML.

<details> <summary>Check back to see the Day Twelve Script</summary>

<html lang="en">
   <meta charset="utf-8" />
<body style="font-family: monospace">
<script runat="server">
Platform.Load("Core", "1.1.1");
var debugging = false;
var heightOfTree = 20; // Change the height here
var leaf = '*'; // Change the leaf symbol here
var christmasTree = createChristmasTree(heightOfTree, leaf, debugging);


function createChristmasTree(height, leaf, debugging) {
  /* USAGES 
    para 1: height is the height of tree
    para 2: leaf symbol
    para 3: debugging
  var tree = '';
  for (var i= 0; i < height; i++) {
      var stars = '';
      var spaces = '';

      var quantityOfStars = i*2 + 1;

      if(debugging){Write(quantityOfStars + '<br>');} // DEBUGGING
      for (var j=0; j < quantityOfStars; j++){
        stars += leaf;

      var quantityOfSpaces = height -i -1;

      if(debugging){Write(quantityOfSpaces + '<br><br>');} // DEBUGGING
      for (var e=0; e < quantityOfSpaces; e++){
        spaces += '&nbsp;';
      tree += spaces + stars +"<br>"

  // Add the tree trunk
  var maxElement = height*2 + 1;
  var trunkSpaces = '';
  for (i = 0; i < (maxElement-3)/2; i++) {
      trunkSpaces += '&nbsp;';
  trunkSpaces += '| |<br>';
  for (i = 0; i < height / 4; i++) { // Adjust trunk height based on the tree height
      tree += trunkSpaces;

    return tree;

// Add Merry Xmas line
var line;
var line1 = " __  __                       __  __                    _ ";
var line2 = "|  \\/  | ___ _ __ _ __ _   _  \\ \\/ /_ __ ___   __ _ ___| |";
var line3 = "| |\\/| |/ _ \\ '__| '__| | | |  \\  /| '_ ` _ \\ / _` / __| |";
var line4 = "| |  | |  __/ |  | |  | |_| |  /  \\| | | | | | (_| \\__ \\_|";
var line5 = "|_|  |_|\\___|_|  |_|   \\__, | /_/\\_\\_| |_| |_|\\__,_|___(_)";
var line6 = "                       |___/                              ";
  line = "<br>" + line1 +"<br>" + line2 +"<br>" + line3 +"<br>" + line4 +"<br>" + line5 +"<br>" + line6 +"<br>";
  line = line.replace(/ /g, '&nbsp;');

</details> <br />

Thank you Duc for the Scriptmas tree! Everyone, make sure to send us screenshots of your very own custom Scriptmas tree, courtesy of Duc!

Thank you once again for all of our Scriptmas contributors for Scriptmas 2023!

Read more
Published 10/30/2023
HowToScream - Community SFMC Horror Stories

As the Winter ‘24 release finishes casting its influence on the world of Salesforce Marketing Cloud, the witching hour is rapidly moving towards us. Now, the only shadows that decorate our Marketing Automation platform are those cast from the jack-o-lanterns carved into the shapes of everyone’s favourite mascots. Whether you’ve got a Wicked Astro, an Evil Einstein or Creepy Codey in your pumpkin this year, we’ve got tales from our community to share. Brace yourself, get your comforting Trailblazer hoodie at the ready and scroll through the cursed texts of their deepest, darkest and most terrifying SFMC stories.

There’s little that births fear in the heart of an SFMC user than the thought of a quick fix. A simple concoction of actions to remedy the ills of an org. Whether it’s a data extension that needs some new fields or a journey that needs a new version… Here are some examples where that simple, quick fix led to a little more than a ghostly pause. It led to despair and dismay for users and customers alike.

Sometimes when you hear a knocking in your org, it’s better for you to sit tight and wait for it to just go away on its own. When you go looking for ghosts, sometimes ghosts find you as Lesley found out…

It was a dark and stormy afternoon when I paused a crucial automation for a “quick fix” before darting off to my attic to see what was making a suspicious noise. It was not until a week later when asked “why are these essential notification not going out?” that I realized I must retreat to that attic for the rest of my life to live in the shadows in shame.

But, just because you didn’t go looking for ghosts, it doesn’t mean the ghosts aren’t looking for you. The ghost may have been in the platform all along.

I’ll always remember the moment early in our SFMC usage when I learned that if a Triggered Send hit any error, it just stopped - for EVERYONE, not just the problem record - without any sort of notification of failure. I can still taste the bile that rose as I realized that critical emails had been paused for weeks.

Even if the ghosts don’t find you, time ticks for us all and whether we like it or not, time will always get us. Sometimes, times and date maths are the scariest parts of all.

I created coupon codes with 23:59 UTC-8 expiration dates; the SFMC import converted the code expirations to UTC-6. The email’s AMPscript had FormatDate(@dateString,“M”) with no conversion back to UTC-8, which meant the email showed the expiration as the next day.

The biggest challenge with ghosts is they can be incredibly tricky to see, you may think you’ve done the right thing but they can still appear later on or even straight away and leave you none the wiser. Duc has expressed their fears of not always knowing whether the job that was done was the only job that was done or whether the ghosts in the platform were having a little fun at their expense.

Refresh triggered sends (pause, publish, start). Did it include all the inactive ones…

As with everything you can’t see, you can never know what is happening in the background. Is there a queue of 1 thing, 20 things or 100 things in front of your request. Is the platform just cackling away in the background? Is that one of the things causing you a delay?

“Run Once" to perform Automation activities with clients in the test session. The automation was put in queue…

When traversing the hallowed fields of Marketing Cloud, one must always watch their step. One wrong move can turn a normal day into a weeks, months or even years long nightmare. Remember folks, the spirits behind Salesforce Marketing Cloud are old, ECMAScript 3 old in fact, and these spirits can be vindictive. They want nothing more than to see you slip.

Like a responsible developer, Aman maintains documentation of the history of their SFMC org, but, some words should be shared only through ephemeral means or locked away securely and away from prying eyes.

I just found our client ID, and Secret is available openly on Google. Good, they don’t have our login credentials

This anonymous community member summoned upon themselves the woe of misplaced contacts, sending them through the rabbit hole, never to be found again.

A user loaded a big chunk of our opted-in Contacts dataset into a new Data Extension, with smartly designed segmentation and personalisation flags to run a fancy new journey. Then they selected the wrong Subscriber Key for the Contact relationship, and hit Send…

Never trust phantom data. Especially when it comes to sending test emails as Stephan found. Nobody knows what lurks in the shadows and unless you’ve been and explored, there may be ghouls and zombies waiting for you.

We sent a test email to random generated supposedly non-existing e-mail addresses. We had clicks and opens!

Sometimes you don’t have to make a misstep to be caught out by the shifting tides within SFMC, as the tool moves, evolves and creates new capabilities it can be all too easy to be caught out by some form of new hex.

The moment you realised missing an enhancement to lookup lead records in CRM before creating new ones and your SFMC landing pages pumping duplicates into salesforce CRM over an year!

The shifting tides aren’t always on the surface, the undercurrent can look very different from the surface. What was a good idea in years past may no longer be that same good idea. What worked before may not work now. Exercise caution, especially when others have elected to freeze and stay still.

I accidentally changed the wrong FTP user password in the enterprise business unit and it wouldn’t let me change it back because it didn’t meet the latest password security baseline baked into SFMC. It was the week between Christmas and New Year’s Eve during a tech freeze and no one was around to help me update passwords in the systems dropping off data so most of file drops needed to power customer journeys failed for 8-10 days

Be aware of mimics. What may look harmless and safe to play with, doesn’t necessarily mean it is

Delete an original data extension instead of a test DE in an accident…

You never know what’s lurking around the corner…

We all need to know the best tools for the job we need to do and we all need to use them wisely. Whether you’re looking to hunt vampires or crush bugs, getting the right tool can be the difference between success and failure. But, sometimes we have to make do with the only tools we have.

Billy found this out first hand when trying to overcome the creatures of SSJS in SFMC.

Not feeling as comfortable with SSJS, so I build out the original solution in a cloudpage just so I can get a feeling of any errors as opposed to putting it all in the SSJS and then running it and hoping that it won’t error. There as to be a better way!

Sometimes the right tool for the job doesn’t exist in our own tool kits. We should look at all of the tools available to us and perhaps the right tool is in the software next door. But if you use the tool badly, the issue may persist or even get worse as Cam alludes to…

Once upon a time there was a business who self implemented Marketing Cloud and Marketing Cloud Connect. Their Salesforce Org was full of duplicate records due to bad merge rules, and rather than cleaning up the source data, they built a SQL solution in Marketing Cloud to dedupe records and assign them a new subscriber key - which was a random number generated in a SQL query

Whilst some tools may not do the job and some tools may be supported a little further away from home than we’d typically be used to, sometimes the right tool is in front of our eyes within SFMC. These zombie links could have remained alive rather than undead in Content Builder.

We used external social media icon URLs for every MC email template. The URLs are broken after 3 years…

Even if we’re using the right tool, things can still go wrong in the shadows. Things can still not go according to plan as highlighted in this warning for everyone who activates journeys. Not all that appears fine is always fine.

Journey ran perfectly when I checked on the UI. Recipients didn’t receive the EDM…

The wisened veteran Pato was seemingly using the right tool at one point, but in an effort to enhance things may have instead opened a crypt of configuration requiring 120 re-integrations!

One hundred and twenty BUs, I decided to turn on multi-org. One by one all users had to be re-integrated again.

At times in our lives in SFMC, we need to communicate with the other side, whether that is customers, clients or contacts. These communications can sometimes be fraught with danger, with or without a ouija board it’s easy for requests or requirements to go astray.

Unexpected voices bring about a fear and sense of Halloween dread unlike any other. Seeing an unfamiliar name, sensing an unfamiliar presence can bring about a swift change of priorities, especially when it’s throwing the bile of a bad customer experience into the world as Pep once found out.

Yes, it’s me… WHAT???.. call center says customers asking us to stop sending duplicate emails?.. MORE THAN 200 TO EACH CUSTOMER???

Lesley has seen a fair share of horror stories, but nothing could have prepared for the sheer pain of logging in to find someone else lurking in the shadows and releasing wickedness into the world!

It was a seemingly normal day when my client allowed another contractor into their org. They created an all subscriber data extension and made it testable.

Sometimes the communications are all hidden away where mortal eyes cannot see, the beasts of the back end can play havoc with your finest technology work and just rear their ugly heads without warning as Duc found out.

Script activity, SQL query activity worked perfectly with test records. Runtime error occurred when running with real data volume…

But the worst can sometimes be what is not said. Rafał shared this tale of something going wrong, something being acknowledged as wrong and everyone being left in the darkness knowing not what happened, nor what to do. The anticipation of finding out whether something was getting better, getting fixed or even getting worse playing on the mind is a fear that will keep even the most stalwart of us all on edge.

In the shadowed realm of the server stack, a ghastly silence stretched for 12 hours, ensnaring a team in a maddening abyss of worklessness and desolation. Like characters in a cursed tale, they languished, their souls tormented by the absence of updates on the incident report and halted communication

At times communication can lead to a sacrifice that you may not be ready to make. It may not be avoidable and it

I cc’d my boss on the SFMC email blast today. However, for some reason, I can’t log in now.

After seeing all of these horror stories, we should remember that sometimes the biggest reason to scream in fear is a little closer to home. It’s not the ghouls, ghosts or gremlins that live in the SFMC platform that can strike fear into our hearts. Sometimes it’s in the friends we meet and the people we collaborate with where the fear can come. We know everyone involved means the best, but sometimes it cannot be helped that these scary challenges will come across us. Here are a few horror stories that have led to some of our community having to wrap up extra safe in their Salesforce swag.

The client asked to update an EDM. Not aware where it is but only the EDM screenshot is given…

The client asked for keeping their contact counts not overage, but… They ingest and consume new data every day

The client was not happy with the cost. Now they are starting to compare other competitors with SFMC.

What are your scariest SFMC tales? What has spooked you out or made you scared to turn out the lights for bed? Come and join us on Slack and share some of the things that taught you HowToScream.

Read more
HowToSFMC Community
All Rights Reserved
Made with by your fellow SFMC users.