Clean your data in Excel before sending out an email with personalization to avoid embarrassing, response-killing mistakes.
1. Decide on your personalization.
For this example, the personalized email will use two fields: <firstname>, <daysleftintrial>.</daysleftintrial></firstname>
You have <daysleftintrial> days left. Why wait? Let's upgrade you now.</daysleftintrial>
2. Create an Excel spreadsheet.
Import the data fields you are going to use for your email personalization into your Excel spreadsheet.
3. Fix capitalization.
To fix any capitalization errors in the FirstName field, use the Excel formula =PROPER(). This formula will capitalize the first letter of each word and make the remaining letters lowercase.
=PROPER('michele') = Michele
=PROPER('JESS') = Jess
=PROPER('MARy jOE') = Mary Joe
4. Use a fallback value for blank data.
Substitute "Subscriber" when the FirstName is blank so the email greeting is "Dear Subscriber".
To fix blank FirstName fields, use the =IF() formula. The =IF() formula allows you to conditionally populate a cell.
=IF(Something is True, then Do This, Else Do That).
Assume FirstName is in cell A2 of your spreadsheet. Here's how to replace a blank FirstName with "Subscriber", otherwise use the value of FirstName.
5. Clean up any negative numbers.
Since it doesn't make sense to have a negative number for DaysLeftInTrial, you can substitute the value "no more" using an =IF statement. Assume DaysLeftInTrial is in cell B2:
6. Create new columns for your cleaned data.
You can combine and nest the Excel formulas to create your final spreadsheet.
Here's a view of the formulas for one row of the sample data:
Here are the final results:
One very common oversight in email personalization is "plurals". In this example, the email is written "You have <daysleftintrial> days left". Notice that if DaysLeftInTrial = 1, the email would read "You have 1 days left." </daysleftintrial>
To fix this, you could use the FixedDaysLeftInTrial field to append either "day" or "days" depending on the value. The formula would be: