Thursday, April 14, 2011

9 Guaranteed Ways to Improve the Use of Your Database

Each of the following processes will improve the use of your database - really! Some are simpler than others, some are free and some definitely require investment, some require technical skills and some just need a new business approach. But they are guaranteed to bring you benefits if you don't already follow such practises or you haven't implemented such functionality. And you can quote me on that!

I have also tried to indicate for each one how easy it is, how long it would take and how much it would cost. Please bear in mind these will of course vary depending on your specific requirements, your database, your in-house skills and so on. So don't quote me on those figures quite so much...

I have split the blog into 2 posts, so ideas 6-9 will follow in a separate posting.

1. Clean your data (and do a data audit)

It doesn’t matter if you have the best database software in the world – if your data is rubbish then you won’t be able to use the database efficiently. End of story. Because if you have records without key data, incomplete addresses, blank fields where they shouldn’t be blank, duplicate records, inconsistent data in the same field, different use of the same field, if you can’t store specific data items which your users need, unreliable data and so on and so on, then how can your users trust the database, how can they produce reports or do queries or segment the system properly or base decisions on correct data, plan their strategies and do their every day work.

So clean your data. Ideally, do a data audit: create a report which shows the different areas of your database, what fields you have, the type of data, record counts, counts on data items in key fields, data integrity issues and so on. Then you can plan your data cleaning exercise properly and in a structured way.

Ease: Not too difficult Time: Audit: 1-2 weeks. Data Clean: several weeks upwardsCost: Free if you can do it internally (Otherwise you'll need to pay someone)

2. Add data integrity rules

Following on from (1), we need to keep our data clean and one of the best ways to do that is at the point of data entry, and one of the best ways to help enforce good practise for data entry is to implement data integrity rules into the database. So, for example:
  • Ensure that a drop-down table (a.k.a. look-up or reference table) is used on fields which have a set or finite number of options within their data set. E.g. Counties, ethnicity, appeal code, type of interaction. (And don’t let end-users add their own codes to such tables!)
  • Make fields “Required” where they must have data recorded in them (e.g. an individual’s last name, a specific code). But bear in mind that if you do that, then there must be an option for all possibilities. E.g. if you make Post Code required then what does a user add if they really don’t know a contact’s post code?
  • Use defaults. For fields where there can be a default, add one. Users can over-write if appropriate.
  • Manage rules on date fields; e.g. don’t let dates get added which are after today’s date where this shouldn’t happen; e.g. date of birth.
  • Use calculated fields. e.g. Automatically calculate age based on date of birth, as opposed to asking a user to key that data.
  • Automatically populate fields based on other fields: e.g. gender based on title (where possible), cost centre based on fund designation.
  • Properise fields where possible and appropriate; i.e. with an initial capital letter. e.g. First Name.
And run “data integrity checks” against your database at regular periods. So where it isn’t possible to create a simple rule on a specific field, create a query/report whereby you can at least produce lists of any data anomalies. For example, appeal codes which shouldn’t be on specific record types, individuals marked as deceased who are still giving donations, UK counties on a record with a foreign country.

Ease: Often needs technical skills Time:  From a few days to on-going Cost: Free if you can do it internally (Otherwise you'll need to pay someone)

3. Don't allow people to use any spreadsheets instead of your database

If you have a database which your staff should be using then don’t allow them to use spreadsheets instead. As soon as the first, external spreadsheet is created, then the data integrity between the two will be lost. Data updates won’t be done, your other users won’t know about specific information now stored on that spreadsheet, you can’t keep tabs of communications with the people on the spreadsheet, there is no security on the spreadsheets etc.

Keep all data which should be on your database on your database. You may need to get a central message from senior management to help you enforce this but it will all be worthwhile.

Ease: Easy to start! (Can be harder to enforce...) Time:  Immediate... but then on-goin Cost: Free

4. Automate what isn't automated

This will always help your database, whether it is for the database team or the end-users. If you can automate something then it will be quicker, more efficient, more accurate, more timely and really optimise what a database is for.

For example: Gift aid claims, reports, mailings, acknowledgement letters, exports to fulfillment houses, importing data from fulfillment houses, extracting data for your finance system.

Ease: Need technical skills Time: Depends on task but each will probably take timeCost: Free if you can do it internally (Otherwise you'll need to pay someone)

5. Integrate it with your web site

If you take online donations, accept online event registrations, let people sign-for newsletters or if you would like to let your supporters update their own address and contact details, opt-in/out to specific communications etc, then you need to update your fundraising/CRM database in one way or another with such data. If you do it manually at the moment then the time and effort is likely to be pretty high.

Integrating your database with your web site so that this can be automated in one way or another will bring great benefits. But it isn’t the simplest thing to do from a technical point of view, nor the cheapest. And you’ll need input and co-operation between different teams so it may not be the easiest in that way either and may take some time.

But if you can automate such integration then you will save so much time and effort internally and add some great data capture, so that the benefits you can get from doing so may well outweigh the cost.

Ease: Often difficult Time: Allow several months from start to finishCost: Potentially high

In the next post, I will consider training, reporting and the benefits you can get from a business analyst.

No comments: