This is the fourth installment in the series, and this is one that can apply to everyone. It is possible to correct some data issues with AMPScript. You just have to leverage all the power of AMPScript and think outside the box.
At a previous employer, we had "special characters" in our subscriber keys. While normally you can function day to day with them, and slowly get them cleaned up, there are other times where you have to bite the bullet to fix them.
Our issue was that we wanted to publish a unique landing page for each subscriber when they purchased a ticket. This page could be printed and the bar-code (produced by ampscript) could be scanned at the turnstile for boarding. It would allow people who didn't have our app to actually bypass the kiosks or guest services teams in the stations to get a printed ticket. They could actually even scan that bar-code from their phone by zooming in on the landing page too. I was able to get the the landing page and the whole process worked out in about two days using the same data extension we used for ticket confirmation emails. Then the random complaints from the station teams started. At about two weeks in, one of our more tech-savvy teammates finally got me the information I needed to fix the problem.
The problem was if the subscriber key had a "+" in it, the web browsers all read that as a space. Here's what I mean, if the url contained "?c=123+456" the web would replace that as "?c=123 456". I had AMPScript on the page to match the subscriber to the c parameter, and if you're not familiar with that command here's what the AMPScript that is bringing in the space looks like:
%%[var @subscriberkey
set @subscriberkey = RequestParameter("c")
]%%
How to Correct Your Bad Data
While this looks a bit cumbersome, once you logic it out, you'll kick yourself that you didn't think of this. First let's look at the new AMPScript:
%%[var @skey, @subscriberkey
set @skey = RequestParameter("c")
IF indexof(@skey, " ") > 0 then
set @subscriberkey = replace(@skey, " ", "+")
ELSE
et @subscriberkey = @skey
ENDIF
]%%
As you can see the AMPScript is a bit more robust. But it's not that bad. The @skey is basically a "staging variable", it's still being populated by the url parameter of "c". Since it is a "staging variable" you'll validate the information that was used to populate it. The next line introduces an If then statement and a function that you don't see to often, indexof.
Indexof tells SFMC to search the information within that variable (or content) for a single criterion or a string, and return a count of the number of instances. Since my case was to replace the "captured space" , I'm only looking for the space. If there is a space in @skey (or rather a count of a space more than zero) then we'll need to set the @subscriberkey variable with the data but with the space replaced with the '+'. But if there is no space in the @skey, then just set @subscriberkey = @skey.
Here's what the variables look like for "?c=123+456":
@skey = 123 456
indexof @skey and space would be "1"
@subscriberkey would now equal "123456"
Now that subscriberkey is properly captured, all of the rest of the AMPScript functions on the landing page, and in the case of this landing page, there were hundreds of them, will function properly based on the matching variable of @subscriberkey (with no space).
You may be asking yourself so great, you fixed your problem, but it looks pretty specific to your problem, how else can I use this? Well, one that comes to mind is all of the subscribers of the world who key in gmial, or .comm or .con or yaho.com or other common typos that you may see in your database? You could potentially gain subscribers that you've been excluding. You can build on the script idea from my earlier posting, "Solving Common SFMC Problems with Ampscript (Using Automation Studio) - Part 3" and build an snippet that will look at the email address and look for those common mistakes.