Salesforce Marketing Cloud

With the AMPscript challenge now closed – it’s time to reveal the winners and share their amazing solutions with the community. If you need a refresher on the competition, the details are here:

It has to be said that the quality of submissions from the community has been amazing! 28 people took the time to solve the challenge and most of the submissions followed the same basic (and efficient) approach to the problem. I’m really looking forward to running more of these types of activities for the community in the future!

So lets meet our code champions and their solutions!

1st Place – Shams Saif (183)

%%[for @i=1 to 1000 do SET @a=Concat(iif(Mod(@i,3)<1,'Fizz',n),iif(Mod(@i,5)<1,'Buzz',n),iif(Mod(@i,7)<1,'Boing',n),iif(Mod(@i,11)<1,'Bang',n))]%%%%=iif(@a=='',@i,@a)=%%<br>%%[next]%%

✔️Removed unnecessary spaces – saving multiple characters
✔️Minified all variables – saving multiple characters
✔️Dropped the unnecessary @i from the next function – saving 3 characters
✔️Used <1 rather than ==0 in MOD() functions – saving 4 characters
✔️Used n rather than in the iif() statement – saving 4 characters

Shams managed to find every possible string shortening trick to bring his code down to an incredible 183 characters!

“I discovered that the iif() statement accepts a random character in the false clause by chance. I was trying to find a way to get rid of the ” characters and found than any single character works! You have to put a character though, a space does not work.”

Shams Saif

Fun Fact: Only 2 people submitted the single character iif() trick. Shams Saif and Jarrett Bush, and it was discovered that you can use any single character (not just n), however this trick doesn’t work in Email Studio (only on CloudPages).

2nd Place – Sascha Huwald (187)

%%[FOR @i=1 TO 1000 DO set @s=Concat(IIF(MOD(@i,3)<1,'Fizz',''),IIF(MOD(@i,5)<1,'Buzz',''),IIF(MOD(@i,7)<1,'Boing',''),IIF(MOD(@i,11)<1,'Bang',''))]%%%%=IIF(@s=='',@i,@s)=%%<br>%%[NEXT]%%

✔️Removed unnecessary spaces – saving multiple characters
✔️Minified all variables – saving multiple characters
✔️Dropped the unnecessary @i from the next function – saving 3 characters
✔️Used <1 rather than ==0 in MOD() functions – saving 4 characters
Used n rather than in the iif() statement

With just 4 characters difference caused by the single character iif() trick, Sascha’s code is an excellent response to the FizzBuzzBoingBang challenge.

I encourage everyone to solve the problem before you start coding. A conceptual design of solving a problem can be applied to any programming language. The key is to solve the problem first.

Sascha Huwald

Sascha Huwald also identified that using a Code Resource (Text) Cloud Page eliminates the need for the 4 characters used for line break “<br>”. This fact would make the shortest possible solution to this problem only 180 characters! Nice find Sascha!

Sascha’s AMPscript code game is strong, but his SSJS code game is a force to be reckoned with! For those who didn’t know – Sascha is the Author of Email360 and the SSJS-lib; a sleek and powerful library for faster and easier development in Salesforce Marketing Cloud.

3rd Place – Josephine Danielle Biscocho (192)

%%[FOR @i=1 TO 1000 DO SET @j=concat(iif(mod(@i,3)>0,"","Fizz"),iif(mod(@i,5)>0,"","Buzz"),iif(mod(@i,7)>0,"","Boing"),iif(mod(@i,11)>0,"","Bang"))output(iif(empty(@j),@i,@j))]%%<br>%%[NEXT]%%

✔️Removed unnecessary spaces – saving multiple characters
✔️Minified all variables – saving multiple characters
✔️Dropped the unnecessary @i from the next function – saving 3 characters
✔️Used >0 rather than ==0 in MOD() functions – saving 4 characters
Used n rather than in the iif() statement
Used empty(@j) rather than @j==” in the output iif() statement – adding 3 characters
Used output() rather than iif() as an inline function – adding 2 characters

Josephine Danielle Biscocho used a slightly different approach that involved leveraging the Output() function. While this method did cost her 2 additional characters, she still had some of the cleanest code submitted by removing all unnecessary spaces and using the >0 solution in the MOD() function; a trick that only 7 people used!

Honourable Mentions

The solutions below didn’t place in the top 3 for the criteria of the competition, however their authors did bring something new and interesting to the challenge that the community will benefit from reading.
Note: I’ve formatted these solutions slightly to make them easier to read and understand their unique solution!

Rafał Wolsztyniak – BuildRowsetFromString & Replace

%%[SET @r=BuildRowsetFromString('3♫5♫7♫11♫Fizz♫Buzz♫Boing♫Bang','♫')
FOR @x=1 to 1000 DO
 SET @l=@x
 FOR @y=1 to 4 DO
  SET @l=Concat(@l,IIF(Mod(@x,Field(Row(@r,@y),1))==0,Field(Row(@r,Add(4,@y)),1),''))
 NEXT @y]%%

Rafał’s solution involved the use of a RowSet & Replace function, but don’t like the ♫ fool you – this solution is one of the most scalable options submitted.

Marouenne Belhaj – 2x BuildRowsetFromString

%%[SET @m=BuildRowsetFromString("3*5*7*11","*")
SET @p=BuildRowsetFromString("Fizz*Buzz*Boing*Bang","*")
FOR @i=1 to 1000 DO
 SET @c=""
 FOR @j=1 TO 4 DO
  SET @c=Concat(@c,IIF(MOD(@i,FormatNumber(Field(Row(@m,@j),1),"N"))==0,Field(Row(@p,@j),1),""))
 SET @c=IIF(@c=="",@i,@c)

Similarly to Rafał’s solution, Marouenne has developed an extremely scalable solution that can be very easily adapted to add new FizzBuzz conditions. An excellent example of code that is built to be easy to read and altered in the future!

Jarrett Bush – 183 character MOD() comparison trick

%%[FOR @x=2 TO 1001 DO
SET @y=Concat(IIF(Mod(@x,3),"Fizz",?),IIF(Mod(@x,5),"Buzz",?),IIF(Mod(@x,7),"Boing",?),IIF(Mod(@x,11),"Bang",?))]%%%%=IIF(@y=="",Add(@x,-1),@y)=%%<br>%%[NEXT]%%

Jarrett found this little hack while playing with the MOD function. The MOD function give a remainder as the result, so if the remainder is “1”, that also works as a TRUE value for a comparison. Therefore you can drop the comparison statement and force a reminder of 1 by shifting the counter up by 1 (from 2 to 1001). Amazing find Jarrett!

Read more

I first learnt about the FizzBuzz code challenge in one of Tom Scott’s YouTube videos and it’s stuck with me ever since. The problem is so simple to explain, but can be achieved so many different ways and the way you choose to approach it can say a lot about your code development style and skill level.

Here is how the FizzBuzz Challenge works

Develop some code that outputs each number from 1 to 100 (on a new line for each record).
For multiples of 3, replace the number with “Fizz”.
For multiples of 5, replace the number with “Buzz”.
And for numbers which are multiples of both 3 and 5, output “FizzBuzz” instead of the number.

For example, the first 20 outputs would look like this:

  1. 1
  2. 2
  3. Fizz
  4. 4
  5. Buzz
  6. Fizz
  7. 7
  8. 8
  9. Fizz
  10. Buzz
  11. 11
  12. Fizz
  13. 13
  14. 14
  15. FizzBuzz
  16. 16
  17. 17
  18. Fizz
  19. 19
  20. Buzz

The FizzBuzz Challenge in AMPscript

The internet is littered with solutions to the original FizzBuzz code challenge, and while there is a lot to learn from reading how other people have solved this problem, the most value will be gained by attempting it yourself! So here is my alteration on the FizzBuzz Challenge – called “FizzBuzzBoingBang” – for you to try using AMPscript in Salesforce Marketing Cloud:

Develop some code that outputs each number from 1 to 1000 (on a new line for each record).
For multiples of 3, replace the number with “Fizz”.
For multiples of 5, replace the number with “Buzz”.
For multiples of 7, replace the number with “Boing”.
For multiples of 11, replace the number with “Bang”.
When a number meets multiple conditions, the output should be in the above display order without spaces.
For example: 21 would be “FizzBoing”, 55 would be “BuzzBang”, and 1155 would be the first instance of “FizzBuzzBoingBang”.

If you’re looking for some tips on how to approach this challenge, take a look at the AMPscript Mod() function. I explain how this and other math function works in my AMPscript Math Functions video!

Competition: Smallest Solution to FizzBuzz in AMPscript

Now for some fun – lets see how small you can make your AMPscript code to solve the “FizzBuzzBoingBang” challenge above. Submit your code entry using the Competition Forms Link below (closed). The winning solutions will be announced publicly on social networks at the end of the competition.

The purpose of this competition is to have fun in AMPscript and to identify, celebrate and learn from AMPscript experts – for the benefit of the community. There are no “prizes” to be won, other than the social gratification gained from authoring a winning solution to this challenge.

Competition Entry Conditions

  1. Submissions will be collected via Google Forms. Only 1 submission per person.
  2. Competition opens on September 13, and closes on September 30. Or put more simply, if the Google Forms link doesn’t work, the competition is closed.
  3. Submissions that do not comply with the Competition Rules (Below) will be excluded.
  4. The information you submit in the form (solution code, name and social links) will be curated and shared publicly for other trailblazers to view and learn from.
  5. Winning and noteworthy submissions will be publicised on social networks including but not limited to LinkedIn, Twitter, YouTube, etc. to give credit/kudos/praise to the code authors.

Competition Rules

  • The winner will be the code author who submits the shortest AMPscript code snippet that achieves the required output (see below). Code submissions that fail to produce the required output will be disqualified.
  • Your code must be fully self-contained AMPscript, able to copied and run on a CloudPage. Your code can not leverage Server-Side JavaScript or external/reference resources (such as DEs or GET/POST requests).
  • The “shortest code” will be determined by character count based on, using the “Characters (including spaces)” value.
  • Code best practices are not assessed, the aim is to write the shortest functional code.
  • In the event of 2 authors submitting solutions with equal (winning) code length, the first/earliest entry will be the winner.
  • Attempts to subvert the rules or deviate from the purpose/spirit of this competition will result in disqualification.

The required output can be found here:

The output must be visually identical with a new line for each record as shown below. The method chosen for line breaks is up to you.

Character Count validation of the FizzBuzzBoingBang output.
1000 words in 4402 characters (without spaces)

Go questions about the challenge or the competition? Post your comments here:

Read more

Process Loops, are, awesome.
However I often see loops treated as a developer-level function and they are avoided by marketing teams – which is a shame. So to help make loops more approachable to beginners, I’ve made an introductory video and developed a simple learning challenge that will help users to understand the power of loops!

To complete this challenge you will need to use multiple Loops and comparison statements to produce a desired output. Follow the task list below to complete this challenge so that the final output is as follows:

Challenge Tasks:

The 6 tasks below are progressive code challenges that will build up to the final result (shown above).
Read each task description (left) and try to replicate the results shown (right).

I recommend attempting this code challenge in a Cloud Page and checking your result in the Preview screen; it’s going to be the fastest way to develop and test your code during these tasks!

SPOILER ALERT! I’ve provided a solution to each task at the bottom of this page – so don’t scroll down too far!

1: Using a FOR LOOP, create a comma separated string of characters from 1 to 10.

1, 2, 3, 4, 5, 6, 7, 8, 9, 10

2: Using a table with a 1px border, change your code so that the 10 numbers are placed into 10 cells on 1 row of a table.

3: Create a 2nd FOR LOOP to produce a 10×10 grid, counting up to 100.

Tip: You’ll need to use a 2nd FOR LOOP to achieve this!

4: Style the grid so that every 2nd Row has the Background Colour “#DCDCDC” (Light Grey) as shown.

5: In addition to the previous style, make every 2nd Column have the “#DCDCDC” Background Colour.
However, when both Row & Column are “#DCDCDC”, make the cell background “#C3C3C3” (Dark Grey).

6: Style the grid so that every multiple of 3 is “#FFFF00″(Yellow), and if the cell is already “#C3C3C3” (DarkGrey), then make it “#FFA500” (Orange); as shown.


Try to complete the challenge above before reviewing the answers. Note that is more than 1 way to solve each of these tasks, and the code below is just one of the possible solutions.

Task 1:
FOR @i = 1 TO 10 DO
Output(Concat(@i,IIF(@i==10,"",", ")))
NEXT @i]%%
Task 2:
<table border="1"><tr>
%%[FOR @i = 1 TO 10 DO]%%
%%[NEXT @i]%%
Task 3:
<table border="1">
%%[FOR @x = 0 TO 9 DO]%%
%%[FOR @i = 1 TO 10 DO]%%
%%[NEXT @i]%%
%%[NEXT @x]%%
Task 4:
<table border="1">
%%[FOR @x = 0 TO 9 DO]%%
%%[FOR @i = 1 TO 10 DO
SET @color =
<td style="background-color:%%=v(@color)=%%;">%%=v(Add(Multiply(@x,10),@i))=%%</td>
%%[NEXT @i]%%
%%[NEXT @x]%%
Task 5:
<table border="1">
%%[FOR @x = 0 TO 9 DO]%%
%%[FOR @i = 1 TO 10 DO
SET @color = IIF(MOD(@i,2)==0,
<td style="background-color:%%=v(@color)=%%;">%%=v(Add(Multiply(@x,10),@i))=%%</td>
%%[NEXT @i]%%
%%[NEXT @x]%%
Task 6:
<table border="1">
%%[FOR @x = 0 TO 9 DO]%%
%%[FOR @i = 1 TO 10 DO
IF MOD(Add(Multiply(@x,10),@i),3)==0 THEN
IF MOD(@i,2)==0 AND MOD(@x,2)==1 THEN
SET @color = "#FFA500"
SET @color = "#FFFF00"
IF MOD(@i,2)==0 THEN
IF MOD(@x,2)==0 THEN
SET @color = "#DCDCDC"
SET @color = "#C3C3C3"
IF MOD(@x,2)==0 THEN
SET @color = "#FFFFFF"
SET @color = "#DCDCDC"
<td style="background-color:%%=v(@color)=%%;">
%%[NEXT @i]%%
%%[NEXT @x]%%


Loops are a great way to introduce programmatic content into your communications, and I hope this AMPscript Code Challenge has helped you to understand how to use Loops. I don’t expect you’ll have much use for a number grid in your emails, however I’m sure the output you’ve managed to create has inspired you to think about AMPscript code differently!

Read more

Date formats and time zone can be a tricky thing to manage in Marketing Cloud due to how each studio handles and displays date time information; I have spent more than my fair share of time testing formats and time zones to ensure the correct date is captured. So here are my tips and technical documentation of how to navigate this part of the platform.

What time zone is Marketing Cloud in?

The Salesforce Marketing Cloud servers operate in Central Standard Time (CST), also known as GMT-6. This means it’s 6 hours behind GMT+0, UTC and “Zulu” time. If it’s midday in Greenwich (UK), then it’s only 6am in CST; 6 hours behind. All data and programming languages will operate with CST as the server time, so you have to account for this in your code!

What time zone should I store Marketing Cloud data in?

Not really a clean cut answer here – store you customer/business data as you need to use it. However I recommend storing data in the server time (CST, GMT-6) so that it’s accurate at the data level. If you know the underlaying data is correct, then you can choose how to display it with some simple formatting tricks. Attempting to store date data in your local time zone may look good on the surface, but you’re going to run into issues later when you try and use SSJS or SQL to compare dates.

What date formats does Marketing Cloud accept?

Pretty much all of them… Marketing Cloud is built to handle all the standard localisations of date formats. The date format setting on file imports gives you some more direct control over this. However if you are looking for the “Best” formats to use when importing data into Marketing Cloud – here is my recommendations:

  • YYYY-MM-DD hh:mm:ssZ (eg: 2021-07-14 12:05:13Z, where Z indicates GMT+0, aka Zulu time)
  • YYYY-MM-DDThh:mm:ssZ (eg: 2021-07-14T12:05:13Z, where Z indicates GMT+0, aka Zulu time)
  • YYYY-MM-DD hh:mm:ss+##:00 (eg: 2021-07-14 12:05:13+10:00, where +10 indicates GMT+10)
  • YYYY-MM-DDThh:mm:ss+##:00 (eg: 2021-07-14T12:05:13+10:00, where +10 indicates GMT+10)
  • YYYY-MM-DD hh:mm:ss.SSSZ (eg: 2021-07-14 12:05:13.000Z, where Z indicates GMT+0, aka Zulu time)
  • YYYY-MM-DDThh:mm:ss.SSSZ (eg: 2021-07-14T12:05:13.000Z, where Z indicates GMT+0, aka Zulu time)
  • YYYY-MM-DD hh:mm:ss.SSS+##:00 (eg: 2021-07-14 12:05:13.000+10:00, where +10 indicates GMT+10)
  • YYYY-MM-DDThh:mm:ss.SSS+#:00 (eg: 2021-07-14T12:05:13.000+10:00, where +10 indicates GMT+10)

The above formats are all subsets of the ISO 8601 international date format, and are the most command (and least ambiguous) ways to represent datetimes. Try to use one of these formats when bringing data into Marketing Cloud to ensure it’s being handled correctly on import.

How can I convert DateTime data in Marketing Cloud?

There are a few ways you can display datetime values in Marketing Cloud.
If you are looking for a way to print your datetime data on a CloudPage or Email, then you can use AMPscript functions like Format() and FormatDate(), or SSJS Functions like Format() or Get Date Parts. You can also use string alteration functions to manually recreate any date format you need.

For SQL date in Marketing Cloud you can use functions like Convert(), Cast() and DatePart(). You can also assemble your own custom date format using Concatenation and string alteration functions if needed (although not recommended).

How can I convert time zones in Marketing Cloud?

Offt, this one is not as easy.
The only time zone converter you have out of the box is the “Server to Local Time Zone” functions in AMPscript, SSJS and SQL. This can be really useful for taking your data stored in Server Time GMT-6 and parsing it into Local Time, however there are a few known quirks with these functions. One such quirk is that it will convert the actual date time (the date & hours) however it wont update the GMT to the correct value, which can cause downstream issues for anyone receiving your data.

You can play around with the FormatDate() (and similar) functions to produce a usable time zone converted date, however you’re likely going to need some string alteration to clean it up a bit. My preferred way is to manually add/remove hours from the date object. Given the server is in CST (GMT-6), you can easily add 6 hours to that time (AMPscript, SSJS & SQL) to enforce Zulu time. From here you can safely export the “YYYY-MM-DD hh:mm:ssZ” format or use a data formatter to convert the Zulu timestamp to a localised one.

Read more

Email marketing lives by a number of key metrics – open/click/conversion/unsubscribe rates chief amongst them – and most EDM teams will focus on the day-to-day campaign send stats, since that’s all they are being asked to report on. So what if I told you there is a bunch of stats that very few people think to look at, and you can use them to impress your boss in only a few mouse clicks!

Einstein has been around for a few years now, however I’m constantly being surprised by how few customers are actually using Einstein in any way! After you unwrap all the buzz words and machine learning hype, Einstein is quite simply the easiest way to impress your boss – let me explain.
Einstein in Marketing Cloud has 3 features that you should activate immediately:

Engagement Scoring

Einstein Engagement Scoring gives you a database health check – grading your subscriber’s open, click & unsubscribe actions to produce a series of insights.

Email Engagement Prediction:
Use this matrix to review the open and click engagement of your subscribers to qualify types of opportunities in your business. Marketing Cloud categorises your subscribers into 4 key groups. Here is a quick rundown of how to treat large populations in each category:

  • Win-back/Dormant:
    A large population here could be due to your content no longer grabbing the attention of your subscribers. It can also be caused by a slowing send schedule, something many companies have been doing during Covid.
    You should look at running a Reengagement campaign or an Update your Preferences journey to keep subscribers engaged.
  • Window Shoppers:
    These subscribers are opening a lot more than they are clicking. The most common causes here are misleading subject lines, poor email content, and bad email content design.
    In any case – this is an email content problem – so getting your product and creative teams to take a closer look at your emails may be worth your time!
  • Selective Subscribers:
    I rarely see large populations here, as this indicates subscribers who are clicking more than they are opening; however this doesn’t mean its a bad thing! This can mean that your content is worth revisiting after the initial click, or there are multiple items worth exploring in each email.
    This kind of engagement can be really positive, however it might be worth doing some click analysis to make sure your subscribers are clicking the links you want them to. Emails should have only 1-2 strong CTAs – if you find yourself with more, try splitting them into multiple emails to ensure each send has a focus.
  • Loyalists:
    If you have a high population here, then you’ve won email. Take a screenshot and ask your boss for a raise!

Engagement Frequency

Einstein Engagement Frequency works in a similar way to the Engagement Scoring, however it focuses on the number of sends rather than the quality. The charts shown in this feature will help you to identify the optimum number of email sends during a 28 day period. Your database is split into Undersaturated, On Target, and (Over) Saturated, and as the names imply, they reflect many many emails Einstein thinks each subscribers should be receiving to optimise their open/click engagement.

This function is very useful for negotiating with management to create additional sends to your subscribers – or cancelling surplus sends (we’ve all had that 1 email we wish could just go away).
You can also select the “Einstein_MC_Email_Frequency_Oversaturation” and “Einstein_MC_Email_Frequency_Undersaturation” Data Extensions to target or suppress sends.

Send Time Optimization

Einstein Send Time Optimization (STO) is one of the most well known Einstein features due to it’s Journey Builder applications – however users rarely look into the data that is available in the STO user interface.

Interrogating the charts in Einstein Send Time Optimization can help you to replan your email send schedule as it will indicate what day and time of day your subscribers are engaging with your emails. The insights gathered from this page could even help reshape your send schedule!

However be warned, this feature can suffer from becoming a self fulfilling prophesy. Sending more emails on your best email open day is likely to make they day look even better. If you opt in to the global STO feature then the model will anonymously consider open/click stats gathered from other Marketing Cloud instances, which can help prevent the self fulfilling prophesy from occurring.


The insights available in these 3 dashboard is more than enough for most business channel reports, and can empower a CRM or Digital Marketing Manager with flashy charts to showcase in their next Marketing Management meeting.

The availability of these Einstein tools will depend on your Marketing Cloud edition; so take a look in your instance and see if you’ve got some valuable data to impress your boss with!

Read more

Once upon a time there was a neat trick that SFMC Developers could use to quickly write and run code in Marketing Cloud. You could create a Cloud Page Landing Page, write your code, and press Preview – this would quickly run the code without publishing the Cloud Page or consume any Super message credits.

However a recent update to how Cloud Pages handles programmatic code (specifically SSJS) has made this rapid development method inoperable. Marketing Cloud also has a 5 minute publishing window for changes made to Cloud Pages, so simply updating your code and republishing it can be a very time consuming activity.

Luckily, there is still a workaround that you can use to quickly build and test your programmatic code!

Use Cases

Working with large blocks of code or 3rd party APIs can sometimes take some trial and error to get right, so unit testing with text outputs can really speed up development. I’ve also had tasks that you just need to process once – such as separating email addresses from their email domain – and having a quick workbench to write the necessary script and run it once can save you a lot of hassle.

How it works

In Content Builder, create a HTML block and paste the programmatic code you want to test. Save the Content Block and copy the Content Block ID from Content Builder.
Next, create a new Cloud Page, and insert the ContentBlockbyId() AMPscript function, pasting the Content Block ID from your saved HTML Block.
Save and Publish the Cloud Page.

Content Block containing some SSJS code.
Cloud Page using ContentBlockbyId() to reference the SSJS Content Block

Now you can access the Cloud Page via it’s published URL. Each time you access the Cloud Page, it will make a dynamic lookup to the HTML Content Block, meaning you can update the content block and refresh the Cloud Page to see changes immediately!

The drawback to this method is that it does consume a Super message credit each time the Cloud Page is reloaded. Depending on the code you are testing and the messages/details you want to print on the page, you can use one of the Code Resource page types to get around the super message cost, however be carful of which code page type you choose as they each contain different headers which could affect your testing.


There are loads of other ways that members of the SFMC community have found – including using Github repositories – to speed up development in Marketing Cloud. See what works for your development style, but always check if your solution introduces any security or utilization concerns!

Read more

One of my most common use cases for SSJS is making API calls in Automation Studio using the Script Activity. Building an automation that can retrieve or refresh data from an external source can open up so many amazing communication opportunities.

You could get the local weather data feed and use it to advertise weather appropriate products or destinations – I actually did a video on this here:
You could get up to date price data on hotels or flights to identified cheap travel opportunities; or even query the daily movement of bitcoin for your subscribers!

However one thing that is common with APIs is the need for a “date” field to specify a range of time to retrieve data for – and while AMPscript gives us some very intuitive date functions to work with – SSJS can be a little trickier to use. So lets go through a few basic principals that can help you to master the datetime data type in Javascript!

Step 1: Getting a Date value

There are many ways you can get and set a date value in Javascript, including setting the value manually, retrieving it from a Data Extension, or even rendering it in real time. Here are some examples:

var static_date = "January 1, 2021";

var de_date = Platform.Function.Lookup('CustomerData','DateofBirth',['FirstName','LastName'],['Angela','Ruiz'];

var Current_date = new Date();

Step 2: Convert String to a Date

Now we need to convert the datetime value into the Javascript “date” datatype. By converting your string or number value into the date datatype, Javascript will be able to use some of the specific date functions. You can read more about JS date functions here:

Lets use the date values we captured above. We can use the following JS codes to convert these dates into the correct JS date objects:

var static_date = new Date("January 1, 2021");

var de_date = Platform.Function.Lookup('CustomerData','DateofBirth',['FirstName','LastName'],['Angela','Ruiz'];
var new_de_date = new Date(de_date);

var Current_date = new Date();

Step 3: Altering a Date

Now that we have date objects, we can make adjustments to the date values easily using the JavaScript Date Functions. This could be needed for triggering a reminder communication a few hours before an appointment, or for setting a voucher expiry 30 days into the future. To achieve this, we can use the setHours() function to add or subtract hours from the date, or we could use the setDate() function to add or subtract days from the date. Here are some example of these functions in use:

static_date.setDate(static_date.getHours()-6); //subtracts 6 hours from the "static_date" value

new_de_date.setDate(new_de_date.getDate()+30); //adds 30 days to the "new_de_date" value

Current_date.setMonth(13); //adds 1 year to the "Current_date" value

Note that at the time of writing, Salesforce Marketing Cloud has a problem with the “setFullYear()” function, however the “setMonth(13)” workaround shown above is a suitable way to add years to your dates.

Step 4: Output your Date value

Now it’s time to output the date value into your code. I’ve seen a number of different date format requirements in APIs, so lets step through a little trick I use to make date formats as easy as possible.

Lets assume we need to produce the following date structure for our API call: 2021-05-14 00:00:00
The current date that we have in our date value is: Fri, 14 May 2021 20:58:13 GMT-06:00
So we can manually output the date parts in the correct format by using the following codes:

var NowDate = new Date("Fri, 14 May 2021 20:58:13 GMT-06:00");
var output= NowDate.getFullYear()+'-'+("0"+(NowDate.getMonth()+1)).slice(-2)+'-'+NowDate.getDate()+" 00:00:00";
//Output: 2021-05-14 00:00:00

What the code is doing:
Firstly we are setting the “NowDate” value using new Date();
Next we are building the “output” value by concatenating each of the date parts as we need them:
getFullYear() will return the YYYY date part.
getMonth() will return the month number from 0 to 11, so we need to +1. Some APIs enforce a 2-digit month value, so we can add a “0” to the front and use the slice() function to return only the last 2 characters in the string.
The getDate() function will give us the date from 1 to 31.
And lastly the ” 00:00:00″ is added to be compliant with the example date time format, assuming we wish to get data starting from midnight. The getHours(), getMinutes(), and getSeconds() functions could have been used to specify these dynamically if required.


I’ve seen some creative examples of people using AMPscript to set and alter the date values before using them in SSJS – however working with Dates in SSJS doesn’t need to be a burden. Below is a copy of my code snippet that I use for quickly building dates for API calls:

//Format: YYYY-MM-DD 00:00:00
var Today = new Date(); 
var Yesterday = new Date();

var Today_string = Today.getFullYear()+'-'+("0"+(Today.getMonth()+1)).slice(-2)+'-'+Today.getDate()+" 00:00:00";
var Yesterday_string = Yesterday.getFullYear()+'-'+("0"+(Yesterday.getMonth()+1)).slice(-2)+'-'+Yesterday.getDate()+" 00:00:00";
Read more

A little while ago I encountered an interesting issue; a prospect was unable to complete a form on a cloud page, and after some isolation testing I found that their email address was the problem – they we’re using a “Role-Based” email address which apparently Salesforce Marketing Cloud didn’t like.

Salesforce Support was able to confirm that “News@<domain>” is in fact a “Role-Based” email that is blocked in Marketing Cloud, however they weren’t willing to provide a list of other email usernames that are blocked by the “List Detective” function. This was a problem for me as I wanted to ensure that other known/safe email usernames could also be pre-emptively added to the List Detective exemptions.

After some quick research into Role Based emails, I came across this repository of email usernames, which was far more extensive than the Pardot Role-Based and Reserved Email Addresses.

Working with Abikarsa Kristanto from Datarati, we we’re able to append a safe domain to all ~950 usernames and load them into the All Subscribers list in Salesforce Marketing Cloud. Since the List Detective triggers during subscriber imports, the emails that failed to import would be blocked, giving us a list of all (at the time & from this list) email usernames that are in List Detective.

From ~950 emails, only 28 were blocked – as below:

  • abuse
  • admin
  • email
  • feedback
  • hostmaster
  • info
  • junk
  • mail
  • mailerdaemon
  • marketing
  • news
  • newsletter
  • nobody
  • none
  • noreply
  • nospam
  • null
  • ops
  • orders
  • postmaster
  • press
  • remove
  • root
  • security
  • spam
  • subscribe
  • unsubscribe
  • www

I hope this list saves you time and helps any fellow trailblazers who find themselves in a similar situation. Again a huge thanks to Abikarsa Kristanto for his efforts on this discovery!

Read more

From my very first days in Salesforce Marketing Cloud learning how to create personalised content, I was always told that AMPscript is far easier to learn and much raster to run. While most typical digital marketers will agree with the first point, I’ve always held a shred of scepticism for the latter; is SSJS really slower than AMPscript?

I’ve been spending more time using SSJS recently, so I thought it was high time to qualify this long standing bias.


To make these tests as fair as possible, I’ll be running each language in a cloud page with timestamp markers at the top and bottom of the code block being tested. The AMPscript and SSJS test functions will be as close as possible to one another, and I will run each code multiple times to achieve an average.
The following code was added to the top & bottom of each test to track the run times:

<script runat="server">
var startDate = new Date();

//Code being tested

<script runat="server">
var endDate   = new Date();
var seconds = (endDate.getTime() - startDate.getTime()) / 1000;
Write("<br><br>Call took "+seconds+" seconds.");

Test 1 – A simple For Loop with no outputs

My first test was a simple FOR LOOP, testing how quickly the AMPscript and SSJS can execute a cruel 1 million loops.
The codes used for each language were as follows:

FOR @i=1 To 1000000 DO
<script runat="server">
for (i = 0; i < 1000000; i++) {

AMPScript was able to complete this task in an average 7.5 seconds, while SSJS ran in an average 2.5 seconds!

I upped the ante to a near sisyphean task of 5 million loops (sorry Salesforce) to create more difference between the times.
AMPscript again came in 2nd place with an average 33 seconds, and SSJS completed in a respectable 12 seconds.

This was not what common knowledge was predicting would happen, so I altered the test conditions to see if I could find a weak point.

Test 2 – A Simple For Loop with some personalised text outputs

Similar to the first test, however this time there would be outputs. The “name” variable would be set on every loop to simulate personalisation. I also lowered the loop count to 10,000 to make sure the page load time could handle all the data being transferred.
The codes I tested were as follows:

FOR @i=1 To 10000 DO
SET @name = "Test"
output(concat("Hi there ",@name, " ", @i, "<br>"))
<script runat="server">
for (i = 0; i < 10000; i++) {
var name = "Test"
  Write("Hi there " + name + " " + i + "<br>");

The AMPscript code ran in under 0.01 seconds every time, while the SSJS code averaged 4.5 seconds! To confirm the findings, I whispered a small prayer and ran it a few times at 100,000 loops.

AMPscript had processed all 100k rows in 0.75 seconds, while SSJS took on average 37 seconds to complete it’s 2.2 MB payload.

This was more inline with my expectations; however given how well SSJS performed in the non-output tests, LOOPS weren’t the problem, and there was clearly something more worth testing about how each language handled outputting data.

Test 3 – DE Lookups

A more practical and real world example is looking up and presenting personalised data from data extensions during an email send or cloud page load. To simulate this test I downloaded a list of over 900 Lord of the Rings characters (link) and loaded it into a DE. The code below looks up that data and then produces a table with all of the character info, which was looped over 10 times – producing over 9000 rows – to help emphasise a winner.

<table border="1">
FOR @x=1 TO 10 DO
SET @lotr = LookupRows('LOTR Characters','Show',1)
FOR @i=1 TO RowCount(@lotr) DO]%%
<table border="1">
<script runat="server">
  for (x = 0; x < 10; x++) {
var lotr = DataExtension.Init("battleoftwolanguages");
var data = lotr.Rows.Lookup(["Show"], [1]);
  for (i = 0; i < data.length; i++) {

AMPscript consistently ran 10 loops in under 0.5 second; while SSJS took 9 seconds to complete 10 loops. Not forgetting how well SSJS ran in the first test, I removed the text outputs (leaving only the lookup functions), and increased the loops to 100. This would remove the now known SSJS Achilles heal of text outputs and just test the lookup function.

After rerunning each language a few times, AMPscript was the clear winner at around 0.25 second for 100 loops, while SSJS really lagged behind on 25 seconds.

This confirmed that raw looping functions seemed to be SSJS’s strength, while it really struggled with any data or text handling. However there was still 1 practical use case left to test.

Test 4 – API GET Request

I decided to host a small .txt file on my web-server and use the AMPscript and SSJS HTTPGET requests to see how well each of them handles getting data from an external source. The text file only contained the word “empty” to keep data transfer size low, and I added the LOOP count to the call to ensure each request was unique (to bypass any caching).

I placed the call in a FOR LOOP for 50 cycles, as below:

FOR @x=1 TO 50 DO
set @HTMLContent = HTTPGet(concat(".../text.txt?i=",@i),false,0,@CallStatus)
<script runat="server">
for (x = 0; x < 50; x++) {
var response = HTTP.Get(".../text.txt?x="+x);
  Write(response.Content + '<br />');

AMPscript continued it’s winning streak with a consistent run time of under 1 second, while SSJS took an average 20 seconds to complete all 50 calls.

I removed the output/write lines to see if that was affecting the times (like it did in previous tests), however after a few runs the execution times remained largely the same.


I think most SFMC Trailblazers would agree this was an unsurprising result, however it also wasn’t as bad as I thought it was going to be. Given most of these tests were run using FOR LOOPS that far exceed any practical use case, the run time difference for small batch processes would be practically negligible. Enterprise customers sending emails to millions of customers would experience a send time difference using SSJS for personalisation – however that kind of scale attracts other operational limitations/bottlenecks anyway.

For me the key learning has been that AMPscript is a faster language for Marketing Cloud to process, so use it over SSJS where possible; however don’t avoid SSJS for fears of degraded performance.
SSJS has some amazing benefits and strengths over AMPScript – such as JSON handling, WSProxy and GET Requests with headers – and it’s worth your time to understand the benefits and applications of each language.

Read more

The following article assumes you have read/completed the following:

How the GA360 Connector works

Put simply, the GA360 Connector creates a link between your Google Analytics 360 and Salesforce Marketing Cloud accounts that enables Google to send non-personally identifiable information to SFMC for the purpose of re-marketing.

This is achieved with the following user/data flow:

  1. An email is sent from SFMC to a Subscriber with personalized links/tracking. The Subscriber clicks on the email and is sent through to the company website with tracking parameters; including “sfmc_id=”.
  2. Google Analytics detects these parameters and stores the “sfmc_id” value against it’s own Google ID.
  3. As users interact with your website, Google Analytics checks the Audience Definitions in your account to see if they qualify with any of the definitions you’ve built.
  4. When a web user has qualified for an audience that has been connected with Salesforce Marketing Cloud, Google Analytics checks to see if they have a “sfmc_id” value stored. If the user has a sfmc_id, then Google Analytics sends the value through to Salesforce Marketing Cloud as a Synchronized Data Source.

How tracking changes everything

Not so long ago, the only data most email marketers had access to was Open/Click data from their email platform, and any uploaded 1st party data such as customer information or purchases. Now with the GA360 connector, email marketers can access website traffic generated by their subscribers, closing the loop and uncovering the interactions that take place in-between the email engagement and purchase.

Best of all, the website tracking data isn’t limited to the session that started with an email click! Google Analytics stores the sfmc_id value against their own customer identifier; so your subscribers are tracked even if they return to the website days later and from different channels!

The advanced conditions & sequences in Google’s Audience Builder allows marketers to create an incredible range of re-targeting opportunities. Google has created a sample list of activities you can create using Audience Builder, however the sky is the limit!

Activating your audiences in Salesforce Marketing Cloud

After you’ve created your Google Audiences and selected Salesforce Marketing Cloud as the Audience Destination, it will take up to 24 hours for them to appear in SFMC. For Australian users, Google Analytics publishes audience data at 4am EST each day.

The standard way to activate this data is via a GA360 Entry Source in Journey Builder. This will be sufficient for most use cases, however it can become impractical when you have multiple audiences/activities that a subscriber could qualify for at the same time.
There is a way to access GA360 Audience data in a Data Extension:

  1. Create a new Journey using the “Google Analytics 360” entry source.
  2. Add some flow controls to the journey – such as a Random Split or Wait Activity – and press save.
  3. Close and reopen the Journey to refresh the interface.

If you look at the details in the Entry Event tile you will see the “Data Extension Name” has a funny value starting with “EA_”. This is the name of the synchronized Data Extension used by Google Analytics. Since this is a Data Extension, it can be accessed just like every other DE in SFMC.
The following SQL can be used to access a GA360 Audience Data Extension:

FROM ent.[EA_xxxxxxx_X_xxxxxx]

More ways to activate web traffic

Accessing the data directly allows marketers to use GA Audience data to create opportunities outside of the standard Journey Builder activation. Rather than thinking of GA Audiences as a tool to create re-targeting segments to activate on directly – we can expand the use cases to include data sets based on customer behaviors that may not be individually actionable, but can instead support other activities.

For a B2C Retail/eCommerce example, we could make an audience that captures web users who have visited over 10 generic pages, but have not made it further down the conversion funnel into the shopping cart. We could use this audience data to infer that the customer is just browsing and not in a purchase state of mind, and then suppress them from more sales-heavy sends.

Alternatively, we could make an audience for customers that have visited the “shipping prices” page in the same session as an “Add to Cart” event, but has not clicked purchase. Normally this would trigger an “Abandoned Cart” activity, however we could infer that this customer is conscious of the shipping cost, and perhaps we could add them to a DE of Subscribers in SFMC that will have a “Free Shipping” promotion banner added in the body of their next EDM.

Closing thoughts

Listening to activities after the initial outbound click of an email gives marketers access to each customers full digital story, and these insights can help to personalized targeted messages and make you customers feel like you truly understand their experience with your brand.

Unassuming actions such as a visit to the Contact Us or Shipping Information page are not reason enough to trigger an activity or communication, but are a great modifiers to existing activities like Card Abandonment, Welcome Emails or general Sales and Newsletters sends.

Read more