As 2021 draws to a close, it’s time to open up the door on the 2021 edition of the HowToSFMC Scriptmas tradition where the team and the community share some of their top scripts, tips, hacks and workarounds.

New for 2021 we’ll be including some specific API functionality that may otherwise go unnoticed or under utilised.

Every day in the lead up to the big day, starting on Monday 13th December up until Christmas itself, we’ll be revealing one scripted piece of goodness for you to grab and use to your heart’s content for 2022 and beyond.

On the first day of Scriptmas Lesley Higgins gave to me… An awesome utility for validating an email address within SFMC.

All you need to do is pop in your Installed Package credentials and an email address and you’ll find out if the email address has a valid Syntax, MX Record attached or if it’s going to be rejected out through ListDetective! Winner.

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

<script runat="server">
  Platform.Load("core","1.1.5");
  try {
    var email = "email@example.com"
    
    var authEndpoint = "your auth endpoint"
    var clientId = "your clientId"
    var clientSecret = "your clientSecret"
    var payload = {
      client_id: clientId,
      client_secret: clientSecret,
      grant_type: "client_credentials"
    };
    var url = authEndpoint + '/v2/token'
    var contentType = 'application/json'
    var accessTokenRequest = HTTP.Post(url, contentType, Stringify(payload));
    if (accessTokenRequest.StatusCode == 200) {
      var tokenResponse = Platform.Function.ParseJSON(accessTokenRequest.Response[0]);
      var mcAccessToken = tokenResponse.access_token
      var rest_instance_url = tokenResponse.rest_instance_url
      };
    if (mcAccessToken != null && email != null) {
      var headerNames = ["Authorization"];
      var headerValues = ["Bearer " + mcAccessToken];
      var jsonBody = {
        "email": email,
        "validators": [
          "SyntaxValidator",
          "MXValidator",
          "ListDetectiveValidator"
        ]
      }
      var requestUrl = rest_instance_url + "/address/v1/validateEmail";
      var validateEmail = HTTP.Post(requestUrl, contentType, Stringify(jsonBody), headerNames, headerValues);
      var aRes = Platform.Function.ParseJSON(validateEmail.Response.toString());
      var valid = aRes.valid;
      if (valid) {
        var status = true;
        var message = "success";
      }
      else {
        var failedValidation = aRes.failedValidation;
        var status = false;
        var message = "Please enter a valid email address";
      };
    }
    else {
      var status = false;
      var message = "server error";
    };
    var response = {
      "ok": status,
      "message": message
    }
    Write(Stringify(response));
  }
  catch (e) {
    Write("<br>" + Stringify(e))
  }
</script>

</details>

Huge thank you to Baby Shark AMPscript competition Honourable mention, Lesley Higgins for this super useful script!

On the second day of Scriptmas Greg Gifford gave to me… A robust way to find the first date of a given day of the week of next month. So, whether you have events on the first Friday of the month or if you update promotional offers on the 2nd Wednesday of the month, you can grab this snippet and adapt to your hearts content! No more manually reading calendars and working it out by hand!

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

%%[
set @dayStr = "Tue,Mon,Sun,Sat,Fri,Thu"
/* Order and name of days included will determine what day of week (DOW) you are looking for */
/* You will want to exclude the DOW you are looking for from string and then go backwards of week in listing days */

/* Used in FOR statement for setting Day Shift */
set @dayRS = BuildRowSetFromString(@dayStr,",")

/* Setting up date pieces for next month */
set @now = now()
/* set @now = "2021-03-20" */
set @nextMonth_Date = dateadd(@now, 1, "M")
set @nextMonth_Month = DatePart(@nextMonth_Date, "M")
set @nextMonth_Year = DatePart(@nextMonth_Date, "Y")

/* Setting date to first of next month and then getting the day of week (e.g. Mon, Tue, etc.) */
set @firstOfnextMonth_Date = dateparse(concat(@nextMonth_Month,"/01/",@nextMonth_Year))
set @dayOfFirst = FORMATDATE(@firstOfnextMonth_Date,"DDDD")

IF @dayOfFirst == "Thu" THEN
  SET @day_Shift = "-1"
ELSE
  /* Default day shift of 0 meaning that the first is the correct day */
  SET @day_Shift = 0

  /* FOR loop to get the day shift to find first date matching day */
  FOR @i = 1 to Rowcount(@dayRS) DO
    set @row = Row(@dayRS,@i)
    set @dayVal = Field(@row,1)
    if @dayOfFirst == @dayVal then set @day_Shift = @i ENDIF 
  NEXT @i
ENDIF

/* Calculate actual date of first occurance of selected Day of Week */
SET @FirstWedOfMonth = DATEADD(@firstOfnextMonth_Date,@day_Shift,"D")
]%%

%%=V(@FirstWedOfMonth)=%%

</details>

Huge thanks for Greg Gifford for sharing this super useful snippet of code, so many use cases for this!

Some people may tell you that the best things come in small packages, but here at H2 we know that isn’t always the case.

So without further ado - On the third day of Scriptmas, Genna Matson gave to me… A quick and reliable way to create Data Extensions for SFMC Data Views. Grab this snippet of code and never worry about having to do the same old task every time you set up a business unit ever again!

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

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

var debug = 0;
var bizUnit = 10000000; // << business unit <<


var rootFolder = 123456; // << enter categoryId for top level in BU <<

//Create new folder
var folderCustomReports = "CustomReports_" + bizUnit
var folderDataViews = "DataViews_" + bizUnit

var newFolder = {
  "Name" : folderDataViews,
  "CustomerKey" : folderDataViews,
  "Description" : "Data Views",
  "ContentType" : "dataextension",
  "IsActive" : "true",
  "IsEditable" : "true",
  "AllowChildren" : "false",
  "ParentFolderID" : rootFolder
};
var folderStatus = Folder.Add(newFolder);

var viewsFolder = Folder.Retrieve({Property:"Name",SimpleOperator:"equals",Value:folderCustomReports});
var viewsFolderID = viewsFolder[0].ID;

if (debug == 1) {
  Write('<br>folderStatus: ' + Stringify(folderStatus));
  Write('<br>viewsFolderID: ' + Stringify(viewsFolderID));
}


var prox = new Script.Util.WSProxy();

// create DataView Report DEs
var dataViewRpt1 = bizUnit + "_DV_Unsubscribe";
var dataViewRpt2 = bizUnit + "_DV_Complaint";
var dataViewRpt3 = bizUnit + "_DV_Open";
var dataViewRpt4 = bizUnit + "_DV_Bounce";
var dataViewRpt5 = bizUnit + "_DV_Sent";
var dataViewRpt6 = bizUnit + "_DV_Click";
var dataViewRpt7 = bizUnit + "_DV_Job";

// Unsub
var de1 = {
  "CustomerKey": dataViewRpt1,
  "Name": dataViewRpt1,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "OYBAccountID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "ListID",
    "FieldType": "Number"
  },
  { "Name": "BatchID",
    "FieldType": "Number"
  },
  { "Name": "SubscriberID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "SubscriberKey",
    "FieldType": "EmailAddress",
    "IsRequired": true
  },
  { "Name": "EventDate",
    "FieldType": "Date",
    "IsRequired": true
  },
  { "Name": "IsUnique",
    "FieldType": "Boolean"
  },
  { "Name": "Domain",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
    }
  ],
    "CategoryID": viewsFolderID
};

// Complaint
var de2 = {
  "CustomerKey": dataViewRpt2,
  "Name": dataViewRpt2,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "OYBAccountID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "ListID",
    "FieldType": "Number"
  },
  { "Name": "BatchID",
    "FieldType": "Number"
  },
  { "Name": "SubscriberID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "SubscriberKey",
    "FieldType": "EmailAddress",
    "IsRequired": true
  },
  { "Name": "EventDate",
    "FieldType": "Date",
    "IsRequired": true
  },
  { "Name": "IsUnique",
    "FieldType": "Boolean"
  },
  { "Name": "Domain",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
    }
  ],
    "CategoryID": viewsFolderID
};

// Open
var de3 = {
  "CustomerKey": dataViewRpt3,
  "Name": dataViewRpt3,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "OYBAccountID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "ListID",
    "FieldType": "Number"
  },
  { "Name": "BatchID",
    "FieldType": "Number"
  },
  { "Name": "SubscriberID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "SubscriberKey",
    "FieldType": "EmailAddress",
    "IsRequired": true
  },
  { "Name": "EventDate",
    "FieldType": "Date",
    "IsRequired": true
  },
  { "Name": "IsUnique",
    "FieldType": "Boolean"
  },
  { "Name": "Domain",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
    }
  ],
    "CategoryID": viewsFolderID
};  

// Bounces
var de4 = {
  "CustomerKey": dataViewRpt4,
  "Name": dataViewRpt4,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "OYBAccountID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "ListID",
    "FieldType": "Number"
  },
  { "Name": "BatchID",
    "FieldType": "Number"
  },
  { "Name": "SubscriberID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "SubscriberKey",
    "FieldType": "EmailAddress",
    "IsRequired": true
  },
  { "Name": "EventDate",
    "FieldType": "Date",
    "IsRequired": true
  },
  { "Name": "IsUnique",
    "FieldType": "Boolean"
  },
  { "Name": "Domain",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "BounceCategoryID",
    "FieldType": "Number"
  },
  { "Name": "BounceCategory",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "BounceSubcategoryID",
    "FieldType": "Number"
  },
  { "Name": "BounceSubcategory",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "BounceTypeID",
    "FieldType": "Number"
  },
  { "Name": "BounceType",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "SMTPBounceReason",
    "FieldType": "Text",
    "MaxLength": 2000
  },
  { "Name": "SMTPMessage",
    "FieldType": "Text",
    "MaxLength": 2000
  },
  { "Name": "SMTPCode",
    "FieldType": "Number"
  }
  ],
    "CategoryID": viewsFolderID
};  

// Sent
var de5 = {
  "CustomerKey": dataViewRpt5,
  "Name": dataViewRpt5,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "OYBAccountID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "ListID",
    "FieldType": "Number"
  },
  { "Name": "BatchID",
    "FieldType": "Number"
  },
  { "Name": "SubscriberID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "SubscriberKey",
    "FieldType": "EmailAddress",
    "IsRequired": true
  },
  { "Name": "EventDate",
    "FieldType": "Date",
    "Ordinal" : 2,
    "IsRequired": true
  },
  { "Name": "IsUnique",
    "FieldType": "Boolean"
  },
  { "Name": "Domain",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
    }
  ],
    "CategoryID": viewsFolderID
};  

// Click
var de6 = {
  "CustomerKey": dataViewRpt6,
  "Name": dataViewRpt6,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "OYBAccountID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "ListID",
    "FieldType": "Number"
  },
  { "Name": "BatchID",
    "FieldType": "Number"
  },
  { "Name": "SubscriberID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "SubscriberKey",
    "FieldType": "EmailAddress",
    "IsRequired": true
  },
  { "Name": "EventDate",
    "FieldType": "Date",
    "Ordinal" : 2,
    "IsRequired": true
  },
  { "Name": "IsUnique",
    "FieldType": "Boolean"
  },
  { "Name": "Domain",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "URL",
    "FieldType": "Text",
    "MaxLength": 2000
  },
  { "Name": "LinkName",
    "FieldType": "Text",
    "MaxLength": 2000
  },
  { "Name": "LinkContent",
    "FieldType": "Text",
    "MaxLength": 2000
  }
  ],
    "CategoryID": viewsFolderID
};  

// Job
var de7 = {
  "CustomerKey": dataViewRpt7,
  "Name": dataViewRpt7,
  "Fields": [{
    "Name": "AccountID",
    "FieldType": "Number"
  },
  { "Name": "AccountUserID",
    "FieldType": "Number"
  },
  { "Name": "JobID",
    "FieldType": "Number",
    "IsRequired": true
  },
  { "Name": "EmailID",
    "FieldType": "Number"
  },
  { "Name": "FromName",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "FromEmail",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "SchedTime",
    "FieldType": "Date",
    "Ordinal" : 2
  },
  { "Name": "PickupTime",
    "FieldType": "Date",
    "Ordinal" : 2
  },
  { "Name": "DeliveredTime",
    "FieldType": "Date",
    "Ordinal" : 2
  },  
  { "Name": "EventID",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "IsMultipart",
    "FieldType": "Boolean"
  },
  { "Name": "JobType",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "JobStatus",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "ModifiedBy",
    "FieldType": "Number"
  },
  { "Name": "ModifiedDate",
    "FieldType": "Date",
    "Ordinal" : 2
  }, 
  { "Name": "EmailName",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "EmailSubject",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "IsWrapped",
    "FieldType": "Boolean"
  },
  { "Name": "TestEmailAddr",
    "FieldType": "EmailAddress"
  },
  { "Name": "Category",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "BccEmail",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "OriginalSchedTime",
    "FieldType": "Date",
    "Ordinal" : 2
  },
  { "Name": "CreatedDate",
    "FieldType": "Date",
    "Ordinal" : 2
  },
  { "Name": "CharacterSet",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "IPAddress",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "SalesForceTotalSubscriberCount",
    "FieldType": "Number"
  },
  { "Name": "SalesForceErrorSubscriberCount",
    "FieldType": "Number"
  },
  { "Name": "SendType",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "DynamicEmailSubject",
    "FieldType": "Text",
    "MaxLength": 500
  },
  { "Name": "SuppressTracking",
    "FieldType": "Boolean"
  },
  { "Name": "SendClassificationType",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "SendClassification",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "ResolveLinksWithCurrentData",
    "FieldType": "Boolean"
  },
  { "Name": "EmailSendDefinition",
    "FieldType": "Text",
    "MaxLength": 50
  },
  { "Name": "DeduplicateByEmail",
    "FieldType": "Boolean"
  },
  { "Name": "TriggererSendDefinitionObjectID",
    "FieldType": "Text",
    "MaxLength": 50
  },

  { "Name": "TriggeredSendCustomerKey",
    "FieldType": "Text",
    "MaxLength": 50
  }
  ],
    "CategoryID": viewsFolderID
}; 

var res = prox.createBatch("DataExtension", [ de1, de2, de3, de4, de5, de6, de7 ]);

</script>

</details>

A massive thank you to Genna Matson for this super useful script! Grab it, use it, save yourself some time!

We may all be counting down the seconds to the big day, but sometimes you don’t need to worry about that… So - on the fourth day of Christmas, Rafal Wolsztyniak gave to me - A nifty trick to remove seconds from a timestamp!

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

Select Cast(a.dateField as DateTime2(0)) as DateTimeWithSeconds
, Concat(Year(a.dateField), '-', Right(Concat(0, Month(a.dateField)), 2), '-', Right(Concat(0, Day(a.dateField)), 2), ' ', Right(Concat(0, Datepart(hour, a.DateField)), 2), ':', Right(Concat(0, Datepart(minute, a.DateField)), 2)) as CustomDateTimeWithoutSeconds
From dataSet a

</details>

Big thanks Rafal for this useful little snippet! When you don’t need seconds, this is a great way to simplify your data!

On the Fifth day of Scriptmas Jason Cort gave to me… FIIIVE GOLD…Wait, we did that joke last time. Dangit. Well, he gave us a useful little script to see how your automations have performed over time! Just pop the names in and add in your MID and you’ll be able to see what’s happened! (Great for when you come back after Christmas and don’t want to check everything manually!)

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

<table>
  <tr>
    <th>AutomationName</th>
    <th>StartTime</th>
    <th>Status</th>
  </tr>


<script type="javascript" runat="server">
Platform.Load("Core","1.1.1");
    var mid = ""; // Put the MID in here
    var prox = new Script.Util.WSProxy(),
    objectType = "AutomationInstance",
    cols = ["Name","Status","StartTime"];
    filter = {
      Property: "Name",
      SimpleOperator: "IN",
      Value: [] }; // Fill in your list of Automations you want to see the history of here (include Quotes and commas between them!)
    moreData = true,
    reqID = null;
    prox.setClientId({"ID": mid});

while(moreData) {
    moreData = false;
    var data = reqID == null ?
           prox.retrieve(objectType, cols,filter) :
           prox.getNextBatch(objectType, reqID);

    if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
           for(var i=0; i< data.Results.length; i++) {
                var automationName = data.Results[i].Name;
                var automationStart = data.Results[i].StartTime;
                var automationStatus = data.Results[i].Status;

Write("<tr><td>" + automationName + "</td><td>" + automationStart + "</td><td>" + automationStatus + "</td></tr>")
            }
 
        }
    }
}
</script>   
</table>

</details>

Massive thank you to Jason for sharing this!

There are some things that if you don’t make SFMC write it down when it happens and sometimes the out of the box Send Log just isn’t enough! On the sixth day of Christmas, Aysha Marie Zouain gave to me… A template for a custom Send Logging solution! Just pop this in your email template and see everything logged for you to analyse at a later date!

<details>

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

%%[ InsertDE('ent.B2C_SendLog','p1_utm_term',__AdditionalEmailAttribute1,'p2_utm_source',__AdditionalEmailAttribute2,'p3_utm_campaign',__AdditionalEmailAttribute3,'p4_campaigncode',__AdditionalEmailAttribute4,'p5_brand',__AdditionalEmailAttribute5,'p6_onsite',__AdditionalEmailAttribute6,'p7_business',__AdditionalEmailAttribute7,'p8_dept',__AdditionalEmailAttribute8,'p9_',__AdditionalEmailAttribute9,'p10_',__AdditionalEmailAttribute10,'dateadded',NOW(),'JobID',JobID,'BatchID',_JobSubscriberBatchID,'ListID',ListID,'SubscriberID',SubscriberID,'SubscriberKey',_subscriberKey,'ClientID',memberid) ]%%

Big thanks to Aysha for sending this super useful little solution for custom send logging!

</details>

Building user interfaces can be tricky, particularly when the content is dynamic.

If you ever find yourself needing to output a folder structure or navigation “breadcrumbs” - then have we got the SSJS script for you!

This handy little code snippet from @Adam Spriggs (inspired by none other than Zuzanna Jarczynska) will output a traditional breadcrumbs folder structure on your cloud page; great for helping to navigate your data extension or content folders!

<details>

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

// via https://sfmarketing.cloud/2019/10/14/find-a-data-extension-and-its-folder-path-using-ssjs/
function getFolderPath(categoryID) {
   var list = "";
   var path = function(id) {

        if (id > 0) {
            var results = Folder.Retrieve({Property:"ID",SimpleOperator:"equals",Value:id});
            list = results[0].Name + " > " + list;
            return path(results[0].ParentFolder.ID);
        } else {
            return id;
        }
    };

    path(categoryID);
    return list;
}

</details>

Huge thank you to both Adam for this function and Zuzanna for the inspiration!

We all know that whilst it’s best data practice to capture a forename and a surname as two different fields, some places just don’t have that capability! But, don’t worry Jacob Edwards has your back! On the Eighth day of Scriptmas, Jacob gave to me - a sweet template for splitting names in SQL in SFMC!

<details>

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

SELECT
 LEN(FullName) as firstNameLength
,CHARINDEX(' ',REVERSE(FullName),0) as lastSpaceIndex
,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName),0) as endFnameIndex
,Substring(FullName,1,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName),0)) as FirstName
,RIGHT(FullName, (CHARINDEX(' ',REVERSE(FullName),0))) as LastName
,Substring(RIGHT(FullName, (CHARINDEX(' ',REVERSE(FullName),0))),1,2) as LastNameInitial
FROM [Names]

</details>

Thanks Jacob for this super useful little query!

We all know the limitations of building form when building a Cloud Page using the native Smart Capture capabilities. Whether you’re looking for unique data capture options or some bespoke processing, sometimes you need something more unique to your business. On the ninth day of Scriptmas, Tony Zupancic gave to me - an epic starting point to build custom forms! Just add in your fields, set the RequestParameters and point the processing URL to your new Cloud Page itself you’re good to go!

<details>

<summary>Click here to see the Day Nine script</summary>

%%[
    set @EmailAddress = RequestParameter('EmailAddress')
    set @FullName = RequestParameter('FullName')
]%%

<!DOCTYPE html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
        <title>Page Title</title>

        <!-- Bootstrap CSS -->
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
    </head>
    <body>

<!-- Check to see if EmailAddress is empty -->
%%[
    if Empty(@EmailAddress) then
]%%

<!-- If EmailAddress is empty show the form -->
<!-- Make ACTION page URL -->
<form method="POST" action="processingURL.com">
    <div class="form-group row">
        <label for="EmailAddress">Email</label>
        <div class="col-sm-10">
            <input type="email" class="form-control" name="EmailAddress" id="EmailAddress" placeholder="What's your email address?">
        </div>
    </div>
    <div class="form-group row">
        <label for="FullName">Full Name</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" name="FullName" id="FullName" placeholder="What's your name?">
        </div>
    </div>
    <div class="form-group row">
        <div class="col-sm-10">
            <button type="submit">Submit</button>
        </div>
    </div>
</form>

<!-- If EmailAddress is not empty process the form and show thank you message -->
%%[
    Else
        /*UpsertData and UpdateData*/
        UpsertData('FormDE',1,'EmailAddress',EmailAddress,'FullName',FullName)
]%%

<h1> Thank you for your submission! </h1>

%%[EndIF]%%

</html>

</details>

Huge thanks to Tony Zupancic for this super useful template!

There are times when you really need to have one-to-many relationships in your data and there are times where a one-to-many relationship may just cause you problems (especially with things like Data Filters and Journey Builder decision splits). Fortunately, there are ways and means to bring your data to single rows where you’ll be able to use different operators to get the result you need, whether it’s 1:M or 1:1 with delimited strings!

So without any further ado… On the tenth day of Scriptmas, Greg Gifford gave to me - a super helpful SQL query to convert a set of many rows into a delimited string! (Just make sure your target Data Extensions can handle all the data that could come their way!)

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

SELECT a.id,
emailStr = STUFF(
  (
    SELECT ',' + b.email
    FROM [myDE] b
    WHERE a.id = b.id
    FOR XML PATH('')
  ), 1, 1, '')
FROM [myDE]
GROUP BY ID 

</details>

Thanks Greg for this super little SQL query to help make data more accessible to more of SFMC!

One of those little pains that many SFMC users need to overcome is the fact that you can’t get details about a Job from a child business unit in the enterprise BU from the _Job Data View (even though you can get everything else!). Thankfully it’s still possible to get those details without having to switch business units thanks to the SOAP API. On the eleventh day of Scriptmas, Jason Cort gave to me - A script that pulls all of the JobIDs and EmailNames from a child BU thanks to WSProxy.

<details>

<summary>Click here to see the Day Eleven script</summary>

<script type="javascript" runat="server">
Platform.Load("Core","1.1.1");
    var targetDE = "" // Put in the name of a Data Extension configured with JobID as a PrimaryKey, EmailName and SendDate
    var mid = ""; // Put the target business unit MID in here
    var prox = new Script.Util.WSProxy(),
    objectType = "Send",
    cols = ["EmailName","ID","SendDate"]; // These are the attributes retrieved from the Send SOAP API Object - https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/send.html
    filter = {
      Property: "ID",
      SimpleOperator: "greaterThan",
      Value: "0" }; // This will get you all of the send jobs for the BU in the MID field
    moreData = true,
    reqID = null;
    prox.setClientId({"ID": mid});
while(moreData) {
    moreData = false;
    var data = reqID == null ?
           prox.retrieve(objectType, cols,filter) :
           prox.getNextBatch(objectType, reqID);
    if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
           for(var i=0; i< data.Results.length; i++) {
                var emailName = data.Results[i].EmailName;
                var emailJobID = data.Results[i].ID;
                var emailSendDate = data.Results[i].SendDate;
                var rowUpdate = Platform.Function.UpsertData(targetDE,["JobID"],[emailJobID],["EmailName","SendDate"],[emailName,emailSendDate]);
            } 
        } 
    }
} 
</script>

</details>

Thanks Jason for this helpful little script!

As we all wrap up for the final day of Scriptmas 2021, we thought that we would finish up with an epic little trick that will save time for everyone who uses Postman to interact with the SFMC APIs. Simply set up a new collection and add todays script in the collection Pre-request Script tab (and make sure the variables match your environment!) to feel the benefits for all of 2022.

On the twelfth and final day of Scriptmas 2021, Adam Spriggs gave to me - An awesome tool to make Postman pre-fetch your authentication token so you don’t have to jump between tabs when you’re building or testing some of the SFMC APIs! Huge time saving and it’s neater and easier to boot.

<details>

<summary> Click here to see the Day Twelve script</summary>

var authEndpoint = pm.environment.get("authEndpoint")
var clientId = pm.environment.get("clientId")
var clientSecret = pm.environment.get("clientSecret")

pm.sendRequest({
    "url": `${authEndpoint}v2/token`,
    "method": "POST",
    "header": {
        "Content-Type": "application/json"
    },
    "body": {
        "mode": "raw",
        "raw": JSON.stringify({
            "grant_type": "client_credentials",
            "client_id": clientId,
            "client_secret": clientSecret
        })
    }},
    function(err, response) {
        var jsonData = response.json()
        if(jsonData && jsonData.access_token) {
            pm.environment.set("accessToken", jsonData.access_token)
        }
    }
)

</details>

Massive thank you to Adam Spriggs for this time saving revelation.

Thank you everybody for your contributions, thank you to the community for an amazing 2021 in the face of such a tricky year. Thank you everyone for reading all of these scripts, tips and tricks. If you’ve read any of these and want to get involved with the H2 community, come and join us on slack!

It’s time to bring 2021 to a close as far as SFMC releases, it’s the final release of 2021! This year has had a few false starts and a couple of reruns when it comes to releases, iOS 15 Open-rate-geddon has started to take hold and there’s still bits of working out how to deal with it to come. Since the last release Salesforce announced the new mascot Brandy, which I was disappointed to find out wasn’t SF stepping in to the Cognac game. But, there’s a bunch of new things coming to you as a nice way to wrap up 2021.

The majority of this release covers Journeys and Messaging & Einstein, but we’ll get to those shortly. First things to share are what is scheduled to leave or has left SFMC since the last release. We are waving goodbye to the MSCRM Connector for Microsoft Dynamics as a result of Microsoft moving to the Unified Interface by MS Dynamics. The connector is no longer compatible with SFMC, so if you’re using MSCRM Connector, you’ll need to start using Email Studio to cover these use cases

• Adding subscribers
• Sending Emails
• Accessing tracking details

The Marketing Cloud Connector Package log has been retired, Salesforce are recommending you clear out any log files that you’ve still got floating around as part of a drive to make the platform more secure. This will impact those of you who are using an integrated Salesforce org with SFMC and find yourself troubleshooting journeys/triggered sends that rely on Salesforce CRM data. Salesforce advises moving to more secure methods of monitoring performance such as using the Initiate Log process and reviewing the activities for Triggered Sends or JBIntBulkManager.

Classic Web Tools are still on their way out, much like Classic Content this may stick around for a little while whilst everyone migrates their content to Content Builder and the new Cloud Pages experience. In August you will have lost the ability to create new piece of Classic Content Microsites and Landing Pages, early 2022 you’ll lose the ability to edit existing content and then by June 2022 it all gets unpublished, taken offline and gone from the internet. So if you’re using Classic Web Tools to host a preference centre or something business or compliance critical - get a migration project started ASAP. Last thing you want is for this to happen and have no time to act on it! Salesforce have also migrated Cloud Pages created with the classic editor to Content Builder, so do a quick audit of what you’ve got to make sure nothing has gone missing.

Non-Tenant Specific Endpoint URLs are still on the way out. Salesforce claims it is still redirecting old URLs to new TSE specific URLs, but there’s no guarantee that will continue, let alone for how long. There have been comments in the community that some users have seen pages become inaccessible overnight, so much like Classic Web Tools, don’t leave it to the last minute. Piece of advice from Salesforce is to not hardcode URLs in templates and instead to use functionality like CloudPagesURL with a Page ID reference to make sure your links are always up to date.

Web & Mobile Analytics is on its way out, if you’re using the Abandoned Cart tile in Web & Mobile Analytics, you should spend some time looking in to Behavioural Triggers instead, if you need to export the data to power Abandoned Browse, Cart or Wishlist type behaviours then take a look in to the Einstein Recommendation integration with Contact Builder. If you get this done right, it’ll populate data directly in to Data Extensions so you can grab it directly within the platform rather than a whole pile of data shepherding!

If you’re using the current Lead Capture app for Facebook/Google Leads, you’ll need to look in to updating that in November when the new version comes out. You’ll need to uninstall the current one and install the new one.

But enough about what’s being retired, sunset, end of life or whatever term you prefer to use. Let’s cover some of the fun stuff that we’re excited to play with when it lands with us.

As mentioned before, this is very much Einstein / Journeys & Messaging taking the bulk of the new features this time around. After the last release really failed to give any insight as to what Salesforce were planning to do with iOS 15 and the new “privacy” capabilities, we’re finally getting to see what’s going on. So, here’s a summary of whats going to impact your favourite Einstein featured.

Feature	What’s happening
Send Time Optimisation	Using a new Engagement Rate (appears to be a blend of Open and Click data) to determine best send time
Copy Insights	Using a new Engagement Rate to determine languages insights and make predictions
Content Insights	Click to Open rate is replaced with Click Through Rate
Messaging Insights	No real change, the model uses relative changes rather then absolute changes in performance to determine when to alert
Engagement Frequency	Using a new Engagement Rate and Unsubscribe Rate to improve accuracy over solely Open Rates
Engagement Scoring	Using a new Engagement Rate to determine personas rather than a matrix of clicks and opens

I would expect these changes to take a little bit of time to bed in and I suspect they’ll be tweaked over time as the iOS 15 change continues to roll out and impact more users. Once we know what normal open behaviour looks like again in the future, expect these things to be revised.

Copy Insights will now allow you to compare up to 10 subject lines at once to help predict performance. This should streamline your processes and make selecting the optimal subject line more straightforward. This is only available for English language subject lines (Salesforce doesn’t specify if there is any difference between British/American English but I would hope not!)

If you’re using Salesforce CDP, you’ll be able to use Einstein driven segmentation using EinsteinEmailEngagementScores and EinsteinMobileEngagementScores objects in the CDP attribute library.

When you run content tests, Einstein will now highlight predicted winners with a little badge if there are statistically significant results.

Engagement scoring now uses local or global averages to determine how your subscribers perform in the context of your org or on a global basis. If you are opted in to the global data pool, you’ll benefit from the wider range of data available to you, if you’re not then you’ll be limited to your local performance.

Each of the Einstein Models now includes a quick high-level summary of what AI data is consumed and how it is used. Adds some great clarity to the technology for those who don’t need to get into the details.

Journeys get a new history dashboard, some new filtering options and improved pagination. Which is long overdue because Journey History has been a pain to navigate for any org with a substantial amount of journeys for as long as I can remember.

Mobile App Events will be made available as entry sources to Journeys. This should allow you to manage mobile app experiences using Journey Builder as a single point of orchestration. But for now it’ll just be focused on the direct communication between your app and Journey Builder. Keep an eye out as this may not be provisioned for you as part of this release, this is going to be rolled out over the remainder of the year.

If you’re looking to do other things with Mobile Push/In-App messaging, in the interm, if you’ve upgraded to the “next-generation MobilePush SDK” you will be able to configure In-App Messaging based on specific behavioural triggers. This includes Session limiting notifications, dynamically expiring notifications on a user by user basis, delaying an in app message within Journey Builder as well as identifying exit events to cancel a message. Definitely worth taking a look into if Push or In-App Messaging are part of your channel mix!

So there’s plenty of updates to push, recommendation is that you take a look through the documentation for iOS and Android to see if there’s any opportunities that the documentation doesn’t cover that would work for you.

A key thing that has come out of the new release is the Partner Seed integrations for the AppExchange. With the imminent departure of Return Path seeding capabilities from any org that currently uses it, it’s been a bit of a wait to find out what is going to be happening.It looks like Salesforce is hedging its bets on getting third parties to do the heavy lifting, which may mean that there are some inconsistent user experience depending on who your supplier is. Just remember that there is no native seeding in Journey Builder activities yet - there’s a load of complexities as to why but fingers crossed with all of the effort in Journey Builder for the last few years it’s only a matter of time.

Interactive Emails gets a neat quality of life bonus where if there’s a rendering issue of your form element in an inbox, it’ll by default link through to the Interactive Email Cloud Page that was built for the form.

New REST API capabilities for managing File Locations, access keys, S3 buckets and much more will hit your orgs throughout November. It would have been nice to have had this update _before _the mandated end date of the default SFMC Decryption key. Migrating multiple File Transfer activities may have been a painful experience for some users. But having a programmatic way to manage keys is definitely a useful addition.

Cloud Pages added to Package Manager. Definitely useful to have, it does make me wonder if there’s some Cloud Pages APIs hidden somewhere that we could interact with… If you’ve done anything interesting with Cloud Pages via an API, let us know!

Overall there’s some really useful and promising items in this release, some of it feels a bit “Better late than never” - especially things like the Partner Seed integrations. It’s something so fundamental to a lot of businesses and it’s taken so long to get it announced post Validity pulling out of the reseller agreement with Salesforce. But - it’s definitely better to have it than not, it’s definitely a useful thing to have and something in light of iOS 15 that if you have any deliverability requirements, you should investigate a Seeding solution, especially if you’re using dedicated IP addresses.

It’s good to see so many enhancements to Einstein in response to iOS 15, it’s all a little bit ambiguous at the moment in terms of what an “Engagement Rate” looks like and how it manifests in any specific businesses. But again - better late than never. We’ve had a few months of knowing that this was coming and Salesforce kept their cards close to their chest. What remains to be seen is how effective Einstein will be in the new auto-open event world that we’ve recently entered.

I’m really looking forward to seeing all of the Mobile Push and In-App Messaging opportunities coming our way in the next few months. If you’re experienced with Mobile Push/In-App Messaging and would be interested in sharing some insights with the H2 community, get in touch and we’d be happy to support!

This is the last release of 2021, there’s been a range of awesome new capabilities and some capabilities still needing to reach maturity. But on the whole, lots of positives this past 12 months and I’m keen to see what we’ll get in 2022.

We’re up to the 4th and penultimate SFMC release of 2021. The last release before iOS15 comes in and open-rate-geddon besmirches all Email Marketing teams KPIs and results. The only release to have ever happened during an Olympic Games that has occurred on an odd numbered year. The last release prior to the major announcement coming at Marketing In Motion on August 12. Sign up here to be the first to know what’s going on. But until then… the August 2021 release. We’ve been through them, we’ve summarized them, here’s your one hit wonder of what to expect in the SFMC August 2021 release. (nb: If you’ve read the June 2021 release notes. Some of these will be familiar)

Marketing Cloud V2 connector is gone. That’s it. It’s gone. It’s been on its way out since October 2020. I really hope everyone has updated their connector by now. There’s not much more to be said. Thank you V2 Connector, you were a great starting point and sometimes a headache… But it’s long overdue for you to have been made to retire. Cloud Pages Legacy Experience is saying goodbye. In possibly the shortest retirement run for anything in SFMC to my memory, it’s also riding off into the sunset. If you’ve not been trying the “new” Cloud Pages experience, unfortunately you’re going to have to get used to it now. But, the new experience isn’t a bad thing, it has some imperfections (such as not being able to run some scripts in the preview anymore) - but it has some nice benefits, like copying pages. So, SFMC gives with one hand and takes with the other!

The announcement of Email Studio Classic Web Tools going away in June 2022, the ability to create new classic Microsites or Landing Pages is going away in this release. You’ve got until June 2022 to get them all migrated to the new Cloud Pages. Anything you have in the classic tools is going to be unpublished and inaccessible anymore. Another example where you should move to the new tools now and migrate when opportunities arise. Microsoft Dynamic on-premises integration gets turned off in 3 months. You’ve got until October 29th this year to determine how you’ll manage this situation. Salesforce have put together some comprehensive details on how to deal with it available here. Finally in the “going away” box - If you’re still using the Legacy ASPX UI for Email Studio, you’re getting upgraded to the newer UI. Enjoy your free upgrade!

More updates to Package Manager including granular permissions for the tool, which is definitely a welcome update. The addition of Shared Data Extensions and Synchronized Data Extensions is definitely useful, but there’s specific caveats around how they get deployed with regards to whether they are deployed as shared or local Data Extensions. There’s too much to specifically cover in this summary, but check out the full release note here. Package Manager is also getting a new landing page, no screenshots in the release notes but fingers crossed it’s an improvement on the list view we have today.

After the previous release allowed you to import S3 data, the new release will allow you to export data to S3. You’ll be able to use IAM Roles to authenticate when configuring your S3 bucket as an export destination. (Fingers crossed we get something for Azure sometime soon… Hey Salesforce, you announced moving SFMC to Azure 2 years ago. Any news on when you’ll get native Azure transfer capabilities?)

If you’re not using MFA yet, you’ve got 6 months before you’ll have no choice. Make sure you start getting this set up in time, rather than a last minute scramble! Check the documentation here if you’ve not provisioned this yet.

Many businesses with connected Sales Cloud and Marketing Cloud instances will be using an attribute on the source object as a record collection filter. If that attribute changes on the Sales Cloud side, you’ll now find that your synchronisation gets paused rather than completely breaking. Which is nice.

This is pretty self explanatory, if you’re using Distributed Marketing Bulk Sending, you can add some custom personalization tabs to add a little complexity and nuance to it. But Salesforce recommends no more than 10 of these to keep it performant.

You may recognize this one from the last release, looks like it got postponed. But up until this gets released you’re required to load content via a CSV or through the user interface. The new Einstein Content Selection API will allow you to introduce content via an API from your digital asset management solution or CMS to ensure Einstein knows as much as is relevant to provide the best customer experience. You can also use the API to update Subscribers as and when an attribute value changes rather than relying on other scheduled processes.

Cast your eyes back to the June 2021 release summary… ! !

So if you’re using Einstein Engagement Scoring in any great amount, you’ll be able to configure your specific thresholds for different engagement scoring definitions. Really looking forward to seeing this one land when the release goes live, especially with the iOS15 open rate-geddon due to land between this and the final release of 2021.

Someone has taken a Conversion Rate Optimisation course at Salesforce based on the summary of this. If you’re not sure what Einstein can do for you, the App Facts will help you to compare what they should be able to do in a side by side fashion. For anyone who is unsure about what they can expect Einstein to help them with in their workflows, this should be a good one stop shop for you.

Another one that’s getting a second bite of the release notes review pie having been featured last time around. Doesn’t look like there’s anything different, so I’ll quote the previous summary.

Einstein Content Asset testing is being added to Einstein Content Selection, so if you’re using Content Builder drag and drop blocks, you’ll be able to drag an Einstein Content Testing block into your email and see the results within Einstein Content Selection. You’ll also be able to view performance analytics at a tag level, so where Einstein is tagging your content for you you can drill into the Performance Analytics tab to see how different tags are performing.

Insights will now check the language of your subject line to identify if there is bias around age, gender, orientation etc… If Einstein does detect bias, it will provide an alert and encourage users to consider their content choices to prevent bias being introduced. Details for this are available here and will help users to identify attributes that may introduce biases.

We’ve had the Wait Until API Event option available to us for the better part of the last 4 months and this looks to be much of the same, only focused on Push notification engagement. If an individual doesn’t act within your specified window, they’ll go down an alternative path. It’s great to see more and more Push type activities added to Journey Builder to bring a more unified way of working regardless of your customers preferred channels.

If you use Sitecore, you can use the Sitecore Connect activity to send contact statuses through to Sitecore as the contact moves through the journey. Does what it says on the tin really.

These are now live and whilst currently there is a redirect in place for those who have older CloudPages without Tenant Specific Endpoints, it would be wise to look at starting to migrate those to the Tenant Specific Endpoints. There’s no indication of how long the redirect will be in place for and if this is how you collect or manage customer subscription statuses this would be a bad reason to find out you’re affected! This does only affect people without a private domain, if you have a private domain you’re all good.

This looks to be another little hangover from the June (and April) 2021 release notes where this was announced, but seemingly not actioned. The default timing is being cut from 14 days to 2 days. Makes it quicker for contacts to be removed for you to bring them back in at a later stage. If you’re not sure what this means for you, take a look at the June 2021 and the April 2021 release notes and do a ctrl-f for Contact and you’ll find some more info about it there.

Currently if you get a large queue of people sitting in a Journey Builder email send activity as a result of an unsubscribe or any other reason, you would need to raise a support case to clear that queue. In the new release, you’ll be able to jump into the journey version, click on View Queue and you’ll have the option to delete the queue.

If you’re in an org with SFMC with the Pro edition, Datorama Reports for Marketing Cloud is now an option for you. Reach out to your account exec to find out if it’s included in your current contract or if it’s an additional line item. If you’re already using Discover reporting I would suspect it’s included but definitely worth a check! It’s a great tool to get under your belt (and it’ll only get better as they bring all the attributes into it)

If you’re subscribed to Datorama Reports Advanced, you’ll find a few new things made available to you including:

• Using Query Builder to get more granular subscriber level insights
• A single place to manage your calculated fields and custom KPIs
• Management of App Credentials for all platforms available within Datorama in the Cross Channel tab

This is a slightly peculiar set of features for this release. Besides the fact that there seems to be a number of items included in previous release notes coming back through again. Some with very little change in the release note itself (Looking at you Contact Suppression).

It doesn’t feel like there’s anything earth shattering in this release. It feels like a range of items that Salesforce has been warning are going away are actually due to be closed down. After the last set of releases were very much about “here’s some cool new stuff you get!” with Journey Builder and Einstein, there’s only a few things here that stand out for me. What I’m surprised and slightly disappointed about is there has been no announcement in these release notes about how Einstein STO and the email engagement models are going to respond to the iOS15 release. Salesforce has been seriously encouraging businesses to introduce these machine learning algorithms to their workflows and their campaign management for over a year now. But, with one fell swoop a huge chunk of many businesses’ customer base is about to have that data validity massively undermined and Salesforce (at least in a public setting) seems to be pretty quiet about it.

This is the last set of release notes before iOS15 lands and there is no indication of how Salesforce recommends you deal with this change to the data feeding their product. If you’ve put a load of your eggs into the Einstein basket as a result of the investment in the tools over the last year, reach out to your account exec and ask how they are managing this change. With just one release left to go in 2021, what’s on your wishlist? We’ve got 10 weeks until that release gets announced so not long before it’ll be 2022!