Easiest Way to Normalize State & Country

[Denise_Greenb12]

Hi All - I'm not working in an instance where SFDC rejects records if State or Country isn't spelled out rather than abbreviated. Most of our leads don't come from forms (so having standard picklists in the forms won't solve this) - plus I've inherited existing nonconforming leads. Is there an easier way to normalize these fields (and keep it normalized) than creating smart campaigns that say "if state/country is x, change it to y"? At least with states there are only 50 (and a much smaller number of provinces/states in places like Canada and the UK) but the country list is way too long to handle this way.

Sanford Whiteman​ - one thing I wondered - is there a webhook built for this that already has the standard values to populate (still could be issues if the countries didn't match the SFDC picklist values, I know. At least this company is only overtly marketing to the U.S. so there isn't a big effort to include other countries - though they are there).

Denise

[SanfordWhiteman · Accepted solution]

The thing is (you probably knew I was going to start with that) there's mapping, and then there's spellcheck plus mapping.

Mapping between US state abbreviations and full names is simple enough, as you point out, to work within a smart list.

Mapping between ISO state (i.e. nation-state) 2-letter or 3-letter abbreviations and canonical state names is easy enough to package into Google Sheet and then call via a webhook.

Spellcheck, though... another matter.

[Denise_Greenb12]

Spelling issues aside, Are there any webhook providers that you know of that already have Google Sheets laid out for country and state normalization could be called by the webhook?

[SanfordWhiteman]

I put together an ISO mapping sheet for you here that you can copy then query via SheetWave. (You don't want literally the same Google Sheet to be hit by every user of a service, since that would overrun Google's API limits.)