This article was originally posted to Salesforce Ben’s the Drip a while ago
Salesforce Marketing Cloud’s Content Builder offers many tools to make your newsletters really personal. It has easy-to-use dynamic content blocks to serve your audience content that’s tailored to their needs. But, what do you do when the information about your subscriber isn’t formatted the way you prefer? This is where AMPscript comes in. AMPscript is Salesforce Marketing Cloud’s own powerful scripting language and it’s not as complex as you might think – that’s what I will prove to you in this post.
Let’s go over 5 pieces of AMPscript I use regularly, that will make it easier to personalize your emails.
#1: How to Fix the Casing of your Variables
When different databases are connected, they sometimes save information in different formats. For example with all the letters upper- or lowercase like john or JOHN. This would cause names in your newsletter list to be capitalized incorrectly, for instance.
AMPscript has an easy fix for this. The Propercase() function will correctly format a name by capitalizing it properly. Propercase works by wrapping a piece of text or a variable:
Dear %%=propercase(‘ben’)=%%,
When previewing/sending your email, it should display:
“Dear Ben,”.
Instead of the static value “ben” you can use a field name from your data extension to make it dynamic:
%%=propercase(firstName)=%%
#2: How to Create a Fallback for Missing Values
But what if you can’t be certain that the first name variable in the data extension is filled? A great way to make sure that you aren’t getting blank spaces in your email is to replace the first name with a default value if the first name is empty within your data extension.
Of course, there are multiple ways of doing this; you could build custom content blocks to make sure this doesn’t happen, but creating two content blocks and a dynamic content block seems a bit excessive, if all you want to do is replace one name.
A quick and easy way to do this is with an IIF statement (and I no didn’t misspell IF!). The extra I stands for inline and it looks like this:
%%=IIF(Not empty(firstName),firstName,’Customer’)=%%.
As you can see the IIF statement consists out of three parts.
| 1 | Not empty(firstName) | The statement, has to be either true or false. In this case, we are using “not empty()” but you can also use greater than, less than or equal statements. |
| 2 | firstName | The text or variable that will be shown If the statement returns true or in our example, the firstName variable is not empty. |
| 3 | ‘Customer’ | This last part will be shown when the statement turns out to be false. In this example, we’ll use customer as our fall back but you could also use a lastname variable if you’d have it. |
If we would write out the exact function of our script it would be:
If the firstName field in the data extension is not empty then show the firstName value, if it is empty show ‘Customer’.
Obviously, you can use it for more than just filling in default values. You can use it for any dynamic content that has two possible outcomes.
#3: How to Clean up your Variables
This can happen when during an import, someone forgot to check the “Respect double quotes as text qualifier” checkbox. There are different ways of solving this; one is to re-do the import, but I use Replace(). Replace() allows you to replace one value with another, for example:
%%=Replace(firstName,'”‘,”)=%%
Let’s go through the separate parts of replace.
| 1 | firstName | It always starts with the string or variable you want to replace parts of. In this case we are using a variable called gender (note the lack of single quotes). |
| 2 | ‘ ” ‘ | This is the text Replace() will look for to replace with a different value. (I’ve added extra spaces for legibility) |
| 3 | ” | This last part will be the value that will replace the previous text. Note that there is nothing between the single quotes. This will replace the quotes with nothing, essentially deleting the quotes. |
You can use this to delete or replace any value. Other use cases involve replacing dots with commas (and vice versa), or adding underscores to values in links. Replace() is a very versatile piece of AMPscript!
#4: How to Format your Dates Properly
It might be difficult to use variable dates in your email. Dates from data extensions are formatted like this 2019-08-16 (yyyy-mm-dd) – which looks terrible. But, don’t worry, AMPscript has you covered.
With FormatDate() you can display dates in any way you like, it even has a neat translation feature. FormatDate() is a little more complicated than Propercase() but it does come with a built-in translator.
Let’s start with a complete DateFormat script:
%%=FormatDate(‘2019-10-03 09:00′,’ddddd dd MMMM yyyy’,’HH:MM’,’en-US’)=%%
| 1 | 2019-10-03 09:00 | The parameters within FormatDate start with the date you want to format, in this example I’ve used a fixed date but you can also use a variable. In that case, you don’t have to use the single quotes around it. Single quotes signify that the value is static. |
| 2 | ddddd dd MMMM yyyy | This part determines the way the date is shown, the time is determined separately. It will render as Thursday 3 October 2019. But you can format it any way you like. Here’s a great cheat sheet for all your date formats. |
| 3 | HH:MM | Time can be displayed up to the mini second, or when you don’t want to display the time it can be left empty. However you can’t skip that parameter, so if you don’t want to display the time replace the,’HH:MM’, with ,”, |
| 4 | en-US | This designates the language and culture to display the date in. For example, if we would’ve used nl-NL it would translate the day and month into Dutch, which has saved me a lot of time in the past. |
This is a great way to display an order date on a receipt or todays date by using NOW(), instead of an actual date if you want your newsletter to be more like a newspaper.
#5: What to do when Dates are in Different Timezones
This can be a hassle. Due to SFMC servers using ‘Central Standard Time’, when it saves a default time to a data extension, your dates can be several time-zones off.
Luckily there’s an easy fix for this: SystemDateToLocalDate(). This will automatically set your date to the timezone of your Marketing Cloud instance (if the initial date is in the ‘CST’ timezone that is).
The use of SystemDateToLocalDate() is fairly easy, you just have to place the date within the script like so:
%%=SystemDateToLocalDate(‘2019-10-03 09:00’)=%%
Summary
These five scripts fix most of the formatting problems my marketer colleagues and clients have, and I hope they’ll fix your formatting problems as well. If not leave a comment and I’ll try to respond as soon as possible!
