Spring Cleaning: Preparing the Database
Updated: Apr 8
To begin any job, you need the right work environment. For this project, cleansing the address information in the database was a focal point that would set the stage for all future work.
The Task: There are two levels of cleansing related to donor address records. First, using the @EASE format we removed unwanted punctuation, ensured address data was housed in the proper fields, and corrected typing errors. This put the database in a form where it could be sent to an address cleansing business for a postal code and change of address check-up. These corrections were returned and imported along with a code for each record describing the results.
Correct address information is essential if a charity wishes to contact its donors. @EASE uses a unique method to handle address data separating fields for all major address components. The program assembles these components to create a mailing address compliant with Canada Post and US Post whenever an export is requested. This strategy makes addressing easy to manage and it’s quick to find problem areas that need correction.
It’s not commonly known that address features are determined by each municipality. For example, adding the ordinal suffix ‘th’ for 4 Street in Calgary is incorrect while adding ‘th’ to 11th Ave W in Vancouver is correct. Keeping addresses in good form takes time and effort; a job no computer can do as well as the person in charge of data entry.
With address cleansing completed, the next step was to look for duplication. Duplicates are master records of organizations and individuals which have been entered more than once. Naming strategies often contribute to this problem. In some cases, duplicates were the result of an improper or non-existent lookup prior to entering a new donor record.
Six main issues arose when identifying duplicate records:
1. Establishing a procedure to ensure data entry staff performed an adequate lookup to see if a donor already existed, prior to adding a new record. Using a wild card search, particularly for company names, service clubs, or church groups. Checking by postal code for names like Smith, Brown can help identify duplicate entries.
2. Ensure complete names are sourced when a donation is received or a contact is entered. Understanding that a few extra seconds at the point of entry makes a big difference is important to impress upon staff. Knowing your donors and contacts helps to ensure better quality data entry.
3. Defined naming standards for groups like businesses, banks, service clubs, churches, & unions.
4. Record websites in the Communications tab for donors and funders. Add resources to look up complete names for groups like service clubs, and banks in the KMS.
5. Employing methods on all input forms whether printed or electronic to get better information including whether a donor has given before or is new, helps with address changes.
6. Accurate keying and a quick check before moving on will improve data quality.
The task of clean up was extensive. Many years of staff turnover created duplicate donors who were in fact the same organization and entered more than once. Duplicate organizations were provided with one standardized naming convention.
Examples like Telco companies with multiple offices were brought down to one organization by using the Other Address option. Service clubs like Rotary were updated to use the naming standard shown on their website.
This work continued for several weeks and involved staff and volunteers to clean up as many duplicates as possible. Was the task complete? The answer to that was no, but we were in a good position to identify and resolve any areas missed.
Creating standards helps prevent or at least circumvent the problem of entering duplicate donor master records. A master record identifies a unique donor and is linked to their gifts. When more than one master record exists finding information like total donations for a given donor, becomes impossible.
Developing a standard for entering organization names helps to address what type of information is required. A donation allocated to a group simply called Knights of Columbus begs us to ask … which K of C group is being credited?
Rules of entry become rules of “what to ask” or “when to source more complete information” whenever an organization interacts with a charity and where an accurate identity is inconclusive.
Service clubs, churches and church groups come immediately to mind when you think of naming convention issues. Less obvious are the names of businesses where the addition of the word “The” to the name of a business or not, can create duplicate entries.
Suggestions that we offered used the web as a resource.
Here are a few examples.
Most service clubs have a branch number associated with their name. In most cases, service clubs have standardized their naming strategy.
Here's an example of how Rotary International identified correct names for groups in Edmonton Alberta Canada. The club name showed as Rotary Club of Edmonton followed by the group’s name, not the name of the group followed by Rotary Club.
Royal Canadian Legions have a unique number for each group; the Ladies Auxiliary shares the same number. When receiving a donation from either group, the number should be included as part of the name to prevent confusion or the inability to credit the donation correctly.
Churches can be difficult when dealing with multiple communities as church names could be identical. The use of Parish is another area to be explored. Is the parish the same as the Church? Decisions need to be made and documented.
We have recommended the use of the EaseKMS to record decisions that affect data integrity like naming strategies. Whatever the decision is, it needs to be part of the basic training for existing and new staff members.
The use of a Resource folder that contains listings of group websites is beneficial when more information is required.
Addressing all service clubs and church groups can be a further area of discussion. How often is the address that of the current president? We recommend that all service clubs have a Board Change quality so that new information can be updated on an annual basis. Where possible, the group's PO Box or main church address should be used rather than personal information.
Phone and email become valuable when contacting individuals and a conscious effort to collect this information needs to be included as a policy and procedure.
@EASE provides the use of Other Addresses for all corporate master records. An 'Other Address' could be a branch office or simply a different building housing staff in the same city.
The ability to group a company and its participation with a charity around one master record rather than multiple records enables staff to keep information current, up-to-date, and accurate.
There are some instances, where grouping a series of branches under one corporate master record would not suffice. In the case of bank branches, we recommend that each branch be handled as a unique corporate record, linked by an @EASE membership to show the bank's full participation.
Managing data of any kind is not easy. When dealing with people and companies, the information requires thought and standards to ensure the charity has the best data possible.
Periodic monitoring is advantageous to ensure standards are being adhered to.