The Raiser's Edge Cleanup Series: Address Standardization

Sean Patrick here again, and welcome to our first session on cleaning your data in The Raiser's Edge! In The Raiser’s Edge Cleanup Series: Intro post, I went into my background and philosophy, such as it is, in how to approach managing your data in The Raiser's Edge.

Today, we will talk about one of the most common cleanup operations: Address Standardization. Your database may have entries for Street, St., or St; Avenue, Ave. or Ave, and many other common inconsistencies. Perhaps this is due to sloppiness in typing data from importing records from a third party or donors creating their own records online. No matter the cause, there are several simple steps to not only clean up hundreds or thousands of addresses, but also ensure that problems are stopped.

Deciding on a Standard

For the address question, I settled on the US Postal Service Standard. This has several benefits: one, obviously, the USPS handles most of our mail. But second, for reasons we'll see later, it lends itself well to the cleanup process.

Communicating the Standard

Here is where I made my first real change: I communicated this new standard to my coworkers in the easiest way possible. I decided on a one-sheet document that could be printed and kept by their machine for easy checking during data entry. I read up on the USPS standard and translated the most common address abbreviations into an easily-read format. This way, everyone inside the organization would not worsen the problem.

Fixing Existing Data - Method 1

Now with the problem solved going forward, I took to fixing our past records. All told, 80,000 addresses would need changing in my system. Fortunately, there is a very easy solution by using The Raiser's Edge Global Change process.

I started by creating a query to group together all constituents with address lines containing "Street". Then, I did the same for Avenue, Lane, Road, and so on for every other required change. Because I chose the USPS standard, which doesn't allow for any punctuation marks, I also created a query for "." and "#". If you find you have a lot of double spaces in your addresses, you can also search for " ". At this point, I had a set of queries that could be used for all future global change operations.

Now with my queries ready, I went into Global Change, where I included my Street query. I then performed a Partial Replace of Street with St on Address Lines. I would highly recommend clicking the checkbox for "Find Whole Words Only". Otherwise, an entry like Blackstreet Way would turn into BlackSt Way.

Then, all that was left to do is load the next query into the global change, modify my Partial Replace until all of my data has been cleaned. Specifically, when it comes to the double space issue, I did a Partial Replace of "__" with "_", and kept running the global change until all double spaces were eliminated. Likewise, I did a Partial Replace of "." and "#" with nothing, to eliminate punctuation.

At this point, every couple weeks or months, I could take a few minutes to re-do my global changes, fixing any bad data that found its way into the system.

If you're unsure of any of these methods, I would recommend the following online classes that I frequently teach:

Introduction to Query
Globally Add, Edit and Delete Data

Also, there is a great new curriculum called the Database Doctor Series, which are classes specifically designed to help you solve the major issues in your database.

But Global Change is just method #1 for cleaning out your data. Join me next time as we venture into Import to present an even more comprehensive way of cleaning your address information.