The Raiser's Edge Data Cleanup Series: Removing Duplicate Addresses

When I last joined you here in our series on data cleanup in The Raiser's Edge, I showed you how to clean up the nerve center of your database in your Configuration Tables. But sometimes, the cleanup isn't quite as simple as using Table Cleanup.

But first, just like last time, I want to hear from you. If there's something causing you heartache and headaches in your database, let me know in the comments, and it just might turn into a blog post or.. .series of posts, depending on severity, I guess.

We begin with a story. For reasons I could never quite determine, my last organization's addresses were split almost evenly between "Home" and "Main." There was no rhyme or reason why one would be Home and one would be Main, and sometimes constituents would have one or more of each, or both. On top of that, each address would have its own set of phone numbers and emails, some of which were duplicated in the other types, some of which were unique.

The upside of this is every time someone did a mailing, they would need to pull Home and Main, and figure out on the fly which of the two (or three, or four...) should be used. So for every single mailing, someone would spend hours hacking through the data to find a viable address. This was not good.

First, I had to develop a plan. How was I going to merge Home and Main into one specific type? How can I maintain and consolidate my phone numbers? How can I successfully mark any data I deemed obsolete?

My first step was to make sure the problem couldn't get any worse. I decided that, in spite of its dedicated service, Main had to go. So I went into Configuration->Tables->Phone Types, opened up Main, and marked it as Inactive. Inactivity in The Raiser's Edge will not change any data that currently exists on a record. What it does do, however, is ensure that Main could never again get selected for an address. The computer science term for this is Deprecation: bringing something in your system to its end-of-life.

I decided to go to my strength, and my strength is Excel. In Excel, I could use some clever formulas to pick the best address for each constituent. This meant a return to the Import File, which I chronicled in an earlier blog post. Follow the exact same instructions to push your data into Excel.

Next, I need to develop some criteria in how to pick the best addresses. First, I wanted to get rid of the deadwood. I looked for any address with empty Address Lines or a non-5 digit Zip Code and changed the Address Type to Invalid. Next, I wanted to mark any case when a constituent had the same address in both Home and Main. To do this, I created something called a Key: a way for disparate addresses to speak to one another.

Let's take a step back for a second. In your Import File, you will have a Constituent Import ID, which is a unique identifier for the constituent. You will also have the Address Lines, which will be the best way to determine if an address is unique. By combining the Constituent Import ID and the Address lines into a single field, we can easily determine if we have duplicate data.

For example: if we have John Smith (Import ID #121) with a Home Address at 10 Main St, and John Smith #121 with a Main Address at 10 Main St, the Keys we create will both be "121,10 Main St", the same exact thing. Then, by using Conditional Formatting in Excel (another highly-useful feature), you can see every single duplicate address in your entire database. Cool, right?

At this point, (making extensive use of Sort and Filter) I took every duplicated address in my database and changed its type to Duplicate Home. Finally, we were left with unique Home vs. Main addresses. For any constituent who had no Home address, but had Main, it was an easy fix to change the Address Type. For any constituent with both a unique Home and unique Main address, I changed the Main Address Type to Former Main. Then, I reimported my addresses into The Raiser's Edge.

But there was one last big decision: what to do with my new Duplicate Home and Former Main address types. Since I had no idea why Main ever existed in the first place, I had no way of knowing where this data came from. Rather than delete it altogether, I decided to send an email to every Raiser's Edge user stating that Duplicate Home and Former Main may contain some valid data, but otherwise were on the way out. From this point forward, all mailings would use the Home type first and foremost. I also stated that if anyone found valid data in Duplicate Home or Former Main that it should be immediately moved to Home or another Address Type, as every address with those types would be deleted 3 months from today.

In the end, I had cleaned out invalid addresses, consolidated my correct data under Home, eliminated Main as a selectable option, and streamlined our mailing operations immensely.

In addition to the aforementioned Import classes, much of what we discussed today involves Excel-specific functionality. The best way to learn how to use Excel features as they relate to The Raiser's Edge data is in the Preparing Data for Import with Microsoft Excel class taught in-person in many areas around the country. Check Blackbaud Training Central for upcoming classes in your area.

I hope this has been valuable information, and I'll see you all next time!