Hi everyone,
We're looking at doing some broad clean-up of all of our bad address records - getting all the bad zip codes to be the same, with the same city and state, among other things. Anyone else done this and have any pieces of advice, technically speaking or otherwise? I'm nervous to operate on this many records in our live environment but would prefer specific fear to general!
Thanks!
Frannie
We've done a few cleanups like this in the past (and ongoing).
Some things I chose to do via SQL in bulk but those were things that I wasn't scared about making a mistake on. For example, common mistypings of Edmonton (ex. Edmotnon) I isolated and updated. Or things that we abbreviate (Street = St, Avenue = Ave, etc) I would isolate and update.
But if I couldn't build a hard and fast rule about it then it became a cleanup project for our box office staff during down times. We created several dynamic lists that would find potential issues and then the results would be analyzed by the staff and updated when appropriate. These lists are now run periodically in an attempt to keep on top of these items. These lists find things from unabbreviated words, to incorrectly abreviated words, to extranneous punctuation or spaces, and more!
My biggest advice is test, test, test!! Sometimes you think you've got a rule that will only pull bad addresses but the database surprises you with items that suddenly break your logic. :)