Data Clean-Up

Jennifer Rudder

This topic has been partially addressed on this message board before but I am working on getting more clarification. I inherited a database with over 73,000 constituents, and it seems that almost everyone who working on this database before me had their own way of entering information. I am trying to do a long-term database clean up. So far, I've built and used queries for things such as Zip Code = Blank, or Title = Blank. Does anyone else have any suggestions on how I can further clean up the data and make entries more unfied?


Thanks!

shani traum

Let's see...some of our standard "clean-up queries"...


Marital status = widowed and combined name/salutation not blank (to catch those cases where we forgot to delete the combined info when one spouse died)


Preferred Address type = former and No Valid Address = No (to catch when the preferred address was marked to former but invalid address was not checked)


Preferred Address Type = former and Send mail to this address it marked yes


Preferred Address Type = Home, Business, etc (not former) and send mail = no (sometimes happens in error when copying preferred to alternate before adding new address)


Preferred Address line 1 = blank and No Valid Address = no


Spouse Name not blank and Combined Name/Salutation = blank


Spouse deceased and marital status = married


Spouse deceased and combined name/salutation not blank


Individual is our default constit code.  If they later have a stronger connection that should be replaced with a more descriptive code (Volunteer Leader, Family Member, etc).  So I have a query asking if Constit Code = Individual and Number of Codes is greater than 1. That way, I can delete the code of "Individual".


Gender = Male and Title = Ms. or Mrs. .... and the alternate: Gender = Female and Title = Mr.


Those are the biggies...


Good Luck!


Shani

Jen Claudy

I have been in my current position 4.5 years and still haven't made significant progress towards scrubbing the database.  Just not enough time in the day, at least not without putting in 65-80 hour weeks.


I recommend de-duping constituents, checking for deceased constituents, screening your addresses (and formatting them per USPS standards, unless there is significant objection from your team), and then as you start writing reports, pulling lists, and cleaning up other things, you'll find more and more cleanup projects to add to your list.  At least, that's what happens for me.  I go in to clean up one thing and find several other pieces I'd like to scrub.  Kind of like "two steps forward and one step back" but feels more like "one step forward and three steps back"...


I also suggest that you meet with anyone at your org who has a vested interest in the database, whether they will need to pull data out or whether they'll be putting data in.  Knowing what they have experienced, how they feel about using RE, and what they want to plan on in the upcoming months (ask for a wish list) can be helpful in a couple of ways.  It will give you ideas on what to tackle next (in preparation for a project someone wants to do), it will help your fundraisers feel like you're working to help them be more efficient (that you care about their opinions, even if you can't accomplish everything on the wish list or on the ideal timeline), and it will get your data entry staff feeling like part of the team (which means they'll start fixing things as they go and making suggestions as they find things that could use a scrubbing, because they are part of the plan/team).


Start documenting everything and reference the documentation every single time you perform that task.  This way, if you modify the process, you can immediately update the documentation, meaning later you don't have to find time to go back and update no-longer-current documentation.

 

Rachel Rosen

I agree with many of the things listed below. Having a plan is paramount so you can estimate the size and timeline of a project of this nature. Getting input from your colleagues who use the database will no doubt peak their interest and help you keep things clean. Once you have scoped out how intense this cleanup might be, I would recommend a few next steps.


Hiring a consultant (or skilled intern who's been trained) can help knock out some of the time-intense but mind-numbing clean up you just don't have time for. 


Taking the "Keeping Your Data Clean" class can also give you a sense of where to start and what to look out for. 


The last resort might be to very well have Blackbaud come in and scrub your data. It can be expensive, depending on the size of the project, but there is something to be said for just having it all taken care of and being able to take advantage of clean data. You'll have cleaner mailings, accurate reports, and be able to interact in a more accurate way with your donors, instead of constantly excusing it ("sorry, we're working through some data cleanup, these reports might be off..." and so on). 


Just my two cents, having lived through a similar size data scrub myself. 

Regena Weddle

We are fairly new to Raiser's Edge, so I am still doing a lot of data clean up.  Each time I do a project (like clean up additional addressees or look for periods after middle initials, etc.) I write a query and an export and add it to a Queue.  I run a series of queues over the weekend and come in Monday morning and check the reports to see if anything needs to be fixed.  Right now there are 18 reports in the queue and it only takes a few minutes to fix the issues - most reports are blank because no errors occurred.  I suppose you could do the same thing even if you did not have queue - just do a series of queries and run them one after another.  I put a number in front of the name of my special queries (all my clean up queries start with '98' so that way they sort together and you could just click on one after the other to run them.  I make them really simple so they just take a few seconds to run and each one looks for one thing - like "Missing CFA Salutation" or "Donor with Non Donor constituent code".  This way, once I get something cleaned up, it stays that way.

Gina Gerhard

There are many other forum posts on this topic as well - most are titled 'auditing' .....

Faith Murray

I am also in the process of a much-needed database cleanup. One area I would look at is the proper use of Attributes/Constituent Codes/Solicit Codes. At our organization, Constituent Codes and Attributes were used interchangeably, with a Constituent code for marking donors with outstanding pledges, more Constituent Codes to mark Do Not Solicit and Invalid Address people, an Attribute for marking Alumni, and no Solicit Codes in use at all until 2 years ago. As a result, we were unable to use many of our canned reports which are designed to utilize Constituent Codes as a financial segmenting tool in Annual Reporting, and we also had a gazillion Attributes for everything.


My re-working has been to reduce the number of Attributes to a minimum, and converting all Attribute fields into Tables instead of Text fields, for reduced entry error. Instead of having 10 Attributes indicating a donor's origin, I have created one "Donor Source" attribute with a table of ten possible selections. Instead of five Attributes indicating a connection with each of our Events, I have made one Attribute with a table selection for Event 1 Volunteer, Event 2 Volunteer, Event 1 Committee Member, Event 2 Committee Member, and so forth.


Constituent Codes will be reduced to the 5 segments most useful for financial reporting, including the usual "Alumnus", "Foundation", "Friend", and so on. Solicit codes will be used to filter all special mailing list parameters, such as Do Not Solicit, or No Alumni Mailings.


One word of note is, if you're planning to move to RE NXT, don't bother with the solicit codes. I have read on other forums that NXT doesn't have an optimal Solicit Code capability. In that case, you would be better off leaving those as Attributes.


I have also been cleaning out Queries left over from the Stone Age (we have had RE since 1999, and none have ever been deleted), and am contemplating deleting the ancient gift batches from the year 2000 as well. My "clean-up" Queries have their own special folder called "Imports and System Maintenance", so I can keep them separate. All my other Queries, Exports, and Batches are likewise sorted by topic into folders.


Clean-up has also included reviewing User's Security privileges and reducing privileges to the minimum needed to perform the job without inconvenience. This way, only I and one other user can delete constituents and add to Attribute tables. I learned this to be necessary the hard way, after one User began randomly adding duplicate Solicit Codes and Attributes while editing donor records. Performing clean-up is pointless if it then becomes undone by other Users who don't really need those rights to do their jobs.