Replace State abbreviation with full State name in email

[Anonymous]

Hi All,

Can you help me figure out the best way to create dynamic text for an email?  Here's what I would like the email to look like:

"Hello {FirstName}
You can be the first {anesthesiologist/urologist} in {California/New York/Texas} to have this."

Problem is, instead of the full State names we use a field with the two letter abbreviation: CA, NY, TX, etc.  Also, the entries in our custom specialty field are "Anesthesia" or "Urology" instead of "anesthesiologist" and "urologist."

I think creating additional custom fields with the spelled out State name and differently worded medical specialties would be easiest, but I'm worried I'll end up with a bunch of rarely used fields if I do this every time I run into this situation.

Snippets seem to be designed for customizing entire blocks of text, not so good at customizing a single word here and there.

Should I be looking at tokens and Velocity instead?  Something like "if lead_State = CA, return California?"  If so, does anyone have an example of such code that they could share?

Thanks

[Anonymous]

If using Salesforce in conjunction, I'd create a formula field that looks at your existing State field and returns the full value. You can use the Case operator to accomplish this.

If using Marketo standalone, I'd create one field in Marketo called "State - Full" or something and create a triggered smart campaign:

• Smartlist Trigger: When Data Value Changes, and field is the current State field, and new value is not blank.
• Flow: Create 50 "if, thens" for your states in the Change Data Value flow step. If State is "CA", changed State - Full to "California", etc.

By the way, I'm speaking about this topic at the upcoming summit so if you're attending, perhaps considering sitting in: http://summit.marketo.com/2014/speakers/charlie-liang/

[Anonymous]

Hi Julius -

If you want to go the Velocity email script token route, you can create two array variables - one containing the two-letter abbreviations and another containing the full state name.  You then grab the lead variable you want to check against the two-letter abbreviations, cycle through the two-letter array to find the matching index, then use that index to map to the full state name. 

The size of the arrays has to be equal and if an index of 1 in the first array maps to "AK", then it has to map to "Alaska" in the second array.  You also have to be careful to ensure that every possible lead value is contained in your arrays (e.g. what happens if an abbreviation for a Canadian province sneaks into your database?).

You could use the same approach to "translate" the professional titles if it's a fixed list.

Otherwise, I haven't written a Velocity script to change proper capitalizations, but that shouldn't be too hard (though this doesn't get you the overall "translation" you're looking for).

If you want actual sample scripts, let me know.  I don't have a lot of free time, but I may be game for it.  Otherwise, Charlie's suggestion should work for you - it may be the simplest route.

-patrick

[Anonymous]

Hi,

The approaches mentioned by Charlie and Patrick sound good.
One other option might be to use 'webhook' and extra fields to store 'full' values.

Here is the setup,
1> Create a php (or any web based code) page to accept say state abbrivation. It will accept it and return the full state value as JSON or XML response.
2> Define a Marketo webhook to use this page and accept JSON or XML and set the 'full' state value in the appropriate field.
3> Use this 'full' value where ever you need it.

Pros:
1> Scalable and less mechanical than choices in Marketo flow step. 
2> You can use for state, country, speciality, capitalizations and any thing else and grow it as you need. Just have to update the php lookup data. Pretty flexible and easy to scale.

Cons:
Will need to code php or some other web technology
Need access to a webserver

Hope this helps

Rajesh