Country and State Standardization

Anonymous
Not applicable

Country and State Standardization

A lot of our forms are free form fill outs and can cause states/countries etc. to be misspelled, abbreviated, etc.  I was wondering if someone had a list that they use to standardize these across?

16 REPLIES 16
Grégoire_Miche2
Level 10

Re: Country and State Standardization

Hi Liz,

You could use this : ISO 3166-1 - Wikipedia, the free encyclopedia

Also, this is one topic where data management smart campaigns come very handy to fix erroneous entries.

-Greg

Anonymous
Not applicable

Re: Country and State Standardization

Curious how this would help if they are entered as leads and not associated with an ISO code and if they are misspelled ex: Utd Kingdom, etc. and thanks for your feedback!

Grégoire_Miche2
Level 10

Re: Country and State Standardization

Data management campaigns will not solve your entire problem. Setting your forms with select fields instead of free entry ones will do on the long term.

But Data Management campaigns will help cleanse the data you have in the DB. You can start by replacing values you have in the db with correct ones, and continue to enhance your smart lists of bad data that will be corrected. You will not solve all of them, but you will reach quite quickly about 90% clean data.

-Greg

Nina_Valtcheva5
Level 5

Re: Country and State Standardization

Hi Liz,

Once you have a standardized list of countries & states, you can run cleanup smart campaigns to fix any leads that do not conform to the standard values.

Suggested steps:

1) Establish a standardized list of countries & states (Greg's ISO link is great)

2) Run smart list reports where Country NOT EQUALS standard values, export and map the variations to what the correct country value should be

3) Run clean up smart campaign fixing current leads who have the incorrect country values using Change Data Value flow step. It would look something like this:

pastedImage_0.png

pastedImage_1.png

4) Once you clean up the current leads in your database, you can set up a trigger campaign (just copy the batch campaign you used to fix the leads) to catch any newly created leads that come in with incorrect country & state values.

This can get tedious if you keep having to add more and more variations so the best way to ensure this is fixed going forward is to create country & state picklists for all of your forms.

Frank_Breen2
Level 10

Re: Country and State Standardization

Is there are reason you use free form fields instead of a drop down? You can set this up easily in Marketo forms.

If you use Salesforce, they have a standard country list found here: Standard Countries for Address Picklists. If you go into Setup in Salesforce, you'll also see which countries have recognised states too for added value (United States, Canada, Australia, Brazil, China, India, Ireland, Italy and Mexico). Attached is a spreadsheet (2 tabs) I created from here to do my data normalisation and my dropdown creation, this is made up from the above link and the Salesforce State and Country Picklists section (you may need Admin access to see this or I use my Salesforce Developer version [Developer Info - Salesforce Developers | API Documentation, Developer Forums & More / Signup for free - Salesforce Developers​]).

Salesforce -> Setup - > Salesforce State and Country Picklists

country.png

Bjorn_Erik_Ekst
Level 1

Re: Country and State Standardization

Thanks for the helpful Excel, @Frank_Breen2 ! 

Kristin_Johnso2
Level 2

Re: Country and State Standardization

Hey All,

I began creating the batch and trigger campaigns to clean my data, however with 200+ countries the smart campaign continues to crash my instance. I put in a support ticket and their advice was not to have as many flow steps. How have you guys gotten around this? 

Dan_Stevens_
Level 10 - Champion Alumni

Re: Country and State Standardization

We had to break this up into multiple smart campaigns: APAC, Europe, North America and Other

Anonymous
Not applicable

Re: Country and State Standardization

We did something similar by broken out by alphabetical groupings.