With Winter '25s bleak enhancements sat in our recent memory, as the pumpkin spices our lattes and spooky Salesforce Agents threaten the very fabric of our reality, it’s time to look back at some of the scary moments members of our community have had. If you’re going to jump at the sight of an Appy apparition, a scary SaaSy or a Zombie Zig the Zebra, turn away now and save yourself the dry cleaning bill.

For everyone else, steel your nerve and take a deep breath for HowToScream 2024.

I had set up the perfect SSO for Salesforce Marketing Cloud, confident that all was secured—until someone accidentally deleted the non-SSO backup user account. The next day, SSO failed, locking out everyone, and the realization hit me: there was no way back in.

We all know that SSO is great for keeping your SFMC org secure. Not managing more credentials, multiple passwords and all of the important good that it does. But, nobody dares warn you about what happens when SSO goes so very wrong. On that day you wake up and SSO is down or SSO is broken and there is no way back in to your org. You may keep a key under the flower pot for those days and have a backup user without SSO. But what happens when someone takes that key? When someone deletes the account? What do you do when you’re trapped outside the org. All of those scheduled jobs happening, even if you need them to stop. Standing helpless on the outside, wishing that SSO hadn’t failed you.

This horror face by Aman is one that sits in the back of every SFMC user, or if it didn’t before; maybe it does now.

Aman has faced more than the one horror and shares the tale of when an request for a single email resulted in 10,000 responses. Be careful with your CC’s & BCC’s on sends. You don’t want to be in the storm of multiple Super Messages, wondering why your Gmail inbox isn’t accepting emails anymore or even just your corporate mailbox filter thinking you’re getting a few suspicious messages. But, it’s not just Aman who has suffered this fate. Poor Lesley has recalled a story of a journey sharing 50,000 messages through a BCC gone wrong. If you’re personalising your subject lines, that’s a lot of ghastly messages to clean up!

A member of our community has reached out and shared some of their in platform nightmares. Chandler has shared some of those instances where the gremlins are working within us rather than just against us.

Make sure you read the map and read it twice before you follow the path. Chandler picked up a haunted map and it took him down the wrong path, with the wrong Data Extension field mapped to the SubscriberKey for the sending relationship. This SubscriberKey slip up is scary enough if you’re just worrying about duplicate rows in your billable Contacts. It’s even worse when the value in that field is in use by another Subscriber! Not just duplicate records but experimental merging of data in unintended and unexpected ways. Dr Frankenstein would be pleased if this came to life!

When you feel the need to give peace a chance and turn the triggers off, there’s often times where you need to reload and fight back against the hordes of customers who are due to pay or overdue to pay their next installment. If you forget to reactivate your triggers, who’s going to notice when things stop bouncing back? Keep an eye on those triggers everyone, you never know when you’ll need to be armed and ready to go!

An anonymous community member shared some terrifying number based horror stories that will send shivers down everyones spines. We all know one of the best ways to keep your SFMC org working smoothly is to rid it of data so old it’s got cobwebs, spiders and is probably haunted. Contact deletion is a great way to do it, but you have to make sure that you are deleting the correct old spooky data. Using the right > or < when it comes to taking the data out of your org is critical or you may wake up to the scarier nightmare than old data - No data at all. Remember, check twice and delete once.

The number based spooky tales don’t end after this though.

A bounce rate made up of ghost bounces. 600% of sends bounced. Bounced from what? Is there spectral beings in your SFMC org?

Be careful out there!

It’s not too controversial to feel that a little bit of teamwork can help overcoming these horrifying moments. People you can rely on, people who you can trust to always have your back in the face of adversity. But, then there is the beast of Internal IT. When all you need is a friend or a helping hand, sometimes all you get is a slap on the wrist and a service management runbook instead. Keeping a clients Internal IT on side is one way to make your life easier, but if you can’t do that then you may end up dealing with a Freddy Krueger style nightmare!

Sometimes your teammates just don’t think things through. They just start plugging things in and unplugging things without thinking about the onward impact. The moment of dread when you notice that someone has switched their Marketing Cloud Connector to a different Sales Cloud instance. Pulling the plug on all of your journeys, all of your data and all of your automations and wondering to yourself how you’re going to rebuild this whole thing.

Poor Duc having to face all of this!

It is far too easy to have to face the demons, ghouls and skeletons of SFMC. But, if you’d like to be around a team that is a little less like the teams community member Duc has mentioned and a little more supportive, come and join us on HowToSFMC Slack. Come and share your horror stories or ask the community to help you avoid your own entry for next years HowToScream!

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!

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 */
  OUTPUTLINE(@greeting)
  OUTPUTLINE(@emailContent)

ELSE

  /* 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 */
  OUTPUTLINE(@defaultContent)

ENDIF

]%%

</details>

<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 
    
    qd.Perform();
  
</script>

</details>

<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) {
            numCaseFields++;
            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
    Platform.Function.TreatAsContent(ampBlock);

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

</details>

<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 countdownmail.com.

Step 1. Create a dynamic timer with countdownmail.com
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")
]%%
<br>

<table width="100%" cellspacing="0" cellpadding="0">
<tr>
<td align="center">
<img src="https://pbs.twimg.com/tweet_video_thumb/C0iX_9RWQAAV7qr.jpg" width="500"/>
<h1>Countdown until the new year!</h1>
<img src="
https://i.countdownmail.com/2zigqr.gif?end_date_time=%%=v(@enddate_f)=%%"
style="display:inline-block!important;" border="0" alt="countdownmail.com"></td></tr></table>

</details>

<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>

SELECT 
    sub.SubscriberKey
 ,  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

</details>

<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
        else
            set @status = 0
        endif

        /* 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, '}')
endif
]%%
%%=v(@jsonOutput)=%%

</details>

<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 -->
<title>AddYourTitle</title>
<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="https://www.website.com/" />
<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">

</details>

<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', 
se.JobId, 
s.EmailAddress, 
se.SubscriberKey, 
se.EventDate 
from  _Sent se 
INNER JOIN ENT._Subscribers s 
ON se.SubscriberID = s.SubscriberID
INNER JOIN _Job J
ON se.JobID = J.JobID
WHERE se.EventDate >= dateadd(day, -20, getdate())
AND s.EmailAddress = 'address@email.com'

</details>

<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);  
</script>

</details>

<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;
}

</details>

<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
           }
          
           addToDEArray.push(addObject);
       }
   }
  
    try {
       HeatMap_ClicksDE.Rows.Add(addToDEArray);
       HeatMap_OpensDE.Rows.Add(addToDEArray);
   } catch (err) {
       Write(err);
   }


</script>

/*
*
* 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,
   CASE
       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,
   CASE
       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
FROM (
   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
   GROUP BY DATENAME(WEEKDAY, EventDate), DATEPART(HOUR, EventDate)
) y
LEFT JOIN (
   /* this subquery is getting the accumulative Open Count for the Weekday and Hour of Day buckets */
   SELECT Count(*) AS OpenCount,
       a.Weekday,
       a.Hour
   FROM
       (
           /* 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,
   CASE
       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,
   CASE
       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
FROM (
   SELECT Count(*) AS SendCount,
   DATENAME(WEEKDAY, EventDate) AS Weekday,
   DATEPART(HOUR, EventDate) AS Hour
   FROM _Sent
   GROUP BY DATENAME(WEEKDAY, EventDate), DATEPART(HOUR, EventDate)
) y
LEFT JOIN (
   SELECT Count(*) AS ClickCount,
       a.Weekday,
       a.Hour
   FROM
       (
           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" />
</html>
<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);

Write(christmasTree);

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;');
Write(line);
</script>
</div>
</body>

</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!