Making State/Region consistent throughout

[twj1522]

I am trying to get some email reporting based on the State.

The problem is our database is full of inconsistencies in the State/Region property. For example, some contacts are under Alabama, AL, Albama (because someone misspelled Alabama). It's like this for every state we service, North Carolina and NC, Mississippi and MS, etc.

I want to see opens and clicks for each state we service, but it's so messy because we'll have multiple sets of data for each state even though they're really the same state.

Is there a way to Merge these under one default option without having to manually change it for each contact?

[karstenkoehler · Accepted solution]

Hi @twj1522,

Out of the box, the State/Region property is a single-line text property which leads to your data inconsistencies.

Unfortunately, once it's in use (referenced in lists, reports, workflows, has values for contacts), you cannot easily change the type. You would first have to clear it before you can change its type (export values, remove all references, change type, re-import values, re-establish references).

Alternatively, create a new State property that is a dropdown (pick State from this dropdown in the screenshot).

You could then export your State/Region values, clean them up, re-import them into your new property and voilà, clean property.

Best regards!