I am taking over a RE database that has been maintained for many years by someone that didn't understand all the interworking of the tool. Now I am not claiming to know everything about RE but I do have an Oracle DB background so I understand the whole relational database concept.

One of my first tasks is to clean up the way records relate to each other. The Head of Household processing has never been correcly implemented and we have married constituents with seperate individual records as well as linked business records.

There are no best practices - established procedures so I have A LOT of freedom to do what I want and get everything going correctly. The goal is that I can clean it up - configure appropriate reports and data entry guidelines and then we can get a data entry person to do batch gift entry which will lead to appropriate letter mail runs. That is the goal but let me tell you we are a VERY long way from there currently. (The previous RE person did all gift entry and letter from individual constituent records.) Don't get me started on the Funds and Appeals - arg!

I need to put this on project status so when our new fiscial year begins in October all my changes and corrections are in place. I have told the powers that be the spring/summer is my time to figure out what is wrong and then create procedures to fix it. There are approximatly 12,000 records in the database - a few are quite old with no recent giving history

Does anyone have any suggestions/documentation on the best course of action to attack something like this???? to their credit Support has been helpful and the User Guides have proven invaluable - but if someone else has other suggestions please feel free to comment.

Thanks in advance,

PS: in my spare time I am redesigning and launching the web presence - guess who is going to have a busy summer - no tanning for me :-)
Scott A. Crow
VP Information Services
Girl Scouts of Glowing Embers Council
[Email Removed]

I had a similar problem when I took over our RE database. I had to do a complete audit of the database and found a hugh number of problems; not only with the gift and address entry but with all aspects of the data entering.

I found the best way to tackle the problem was to do a lot of quering to find the problem records, and changing them globally where possible. If global change wasn't possible I exported the data (with relevant Import ID's) into excel or Access, made the changes then imported the data back into the database.

We now have a very clean database of around 50,000 records.

Hope that helps! Karen Sheppard [Email Removed]]
Database Officer
Alumni & Development
University of Bath

I also inherited a database in disarray - in fact we were maintaining 3 databases when I started, only one of which was Raisers' Edge, with much overlapping data between them and no single database the authoritative source for correct information. Raisers' Edge frequently had multiple records for each constituent - one or more from each of the other two databases - and they didn't always show the same addresses, relationships or gift history. I have a lot more wrinkles and gray hair now than I had when I started 4 years ago! Anyway, to build on my own experience and what Karen had to say: I'd try to establish an ideal database design and then a priority list for modifying the database to meet the design before doing anything else, then work to that priority list. The process will be almost like designing a new database from scratch. Do a needs assessment to determine how the database is to be used, by whom, what reports and exports need to be derived from it, etc. Determine which modifications are the most important to achieve the design, then come up with a clean-up plan that gets you there as fast as possible. Remember, there are some things about any database that, while annoying, aren't worth your time to fix, especially if no one else cares or if there is a fairly simple work-around. It is really easy to get bogged down trying to be perfect, when pretty good would have been good enough from the organizational perspective. Also remember that the needs assessment can take a long time and might not appear to others within the organization to be all that important or worthwhile, but once you have it you have something to measure against while without it you are basically flying blind. You are going to need to sell the plan to your management before you get started or they may not allow you to complete it. That's my two cents' worth anyway. David Rosenbloom Membership Database Manager [Email Removed] Minnesota International Center - A World Affairs Council Dedicated to inspiring our community to understand global issues and cultures in an ever-changing world

David, thank you for your wonderful, thoughtful reply. It's always nice to see someone step back from the day-to-day humdrum data entry and really put RE into perspective. As for me, my dilemma was the merging of two databases with many, many duplicates (Joe Chitwood, Joie Chitwood, Jo Chitwood Jr. and Joie Chitood Sr.), so that is where I started. I found the Duplicate Record report invaluable. Good luck! Rebecca Schafer Big Brothers Big Sisters Indianapolis, IN [Email Removed]

Many thanks for everyone's great replies. I really appreciate it! Scott A. Crow VP Information Services Girl Scouts of Glowing Embers Council [Email Removed]

I would highly recommend picking the brains of support as much as possible to find answers and to figure out how the tools work, etc.

My first recommendation for you is to set up a test environment. This way you can do whatever you want to test data and see what happens. If it works, apply it to the production system. And if you're not already on the latest version, I would upgrade and then start doing clean-up. Pick the biggest problems to deal with first. Some of the items are difficult to actually do within Raiser's Edge, but if you export and clean in Access or even a Oracle system since that's what you're familiar with it will be much quicker. You just need the final file in CSV format before you export back into the system. Also look at the Import Guide, if you have the fields named correctly they will automatically map when you import them back into RE7. The biggest thing you probably want to look into getting correct if they aren't, is comstituent codes and the from/to dates on them and setting the hierarchy of codes in the table under configuration. I've found the best way to do this is to use an alpha-numeric short code then name it whatever you want. It will make it very apparent what the proper order is for the records. It also makes custom reporting outside the database easy to do. You can search for a range between "01*" and "10*", try doing that with a purely alpha short code.

ex.
01-TRC Trustee, Chair
02-TR Trustee
03-FS Faculty/Staff
05-CP Current Parent
etc...

I would highly recommend just exporting information from each section of the database, then rollup the data by field to look for patterns or anomalies. Incorrect data will just jump out at you. Search for fields missing data that should be populated. Addresses without from/to dates will also cause you problems. Make sure you enforce required fields, but know that they are affected by a lack of data(ie blank fields). Looks for items in attributes that don't belong there such as appeal and event information. Try to keep attributes limited to a specific use for each one, not a generic catch all. Audit your security settings and make sure items like Credit Card Numbers and Social Security numbers are hidden for everyone except the very few that truly need to see them. Check gift records to make sure the fund/campaign on them belong together and that there is actually a campaign and a fund on every gift record.

I too inherited a huge mess of data from predecessors who did not understand the software nor databases. I'm still cleaning up data and trying to put things into the correct places 2 years later. I literally had to start from scratch developing reports and trying to make sense out of the data and how the system was designed to store data. I try to keep my data granular enough to analyze but also allow it to be group together at the same time. You could just tag everyone as a blob of donors and it's great for totals but impossible to analyze by group. So you'll want to sit down with your team and find out what they want to do with the data and how they are trying to use it. Then give them some of what they want and then what you need to make it possible to give them what they need a lil bit more.

Good luck and keep in mind, your biggest assets will be a test system, analyzing your data, and thinking through how you will utilize the data and the impact before making changes to pieces of data. Jeff Schroeder
Database Manager
Francis Parker School
San Diego, CA
[Email Removed]

"Work Smarter, Not Harder!"