Duplicate Check Filter

<< Click to Display Table of Contents >>

Navigation:  Selections > Creating A Selection >

Duplicate Check Filter

clip0260

 

Although DonorQuest is designed to prevent duplicate donor entries from happening during data entry, it is still possible there may be duplicate records in the database. This is especially true if you have imported data from another database with subtle name or address differences. A duplicate record check facility is an essential feature of fundraising software, since sending multiple copies of a mailing to the same individual wastes your money, and can lower the confidence a donor has in your organization's ability to spend money wisely.

 

The duplicate record selection provides a very flexible way of checking for duplicate donors. The check for duplicate donor records basically consists of comparing donor records by specific fields. These fields are chosen by you. You may use any combination of fields, both predefined and user-defined. In addition, you can specify the number of characters from left-to-right to consider for each field you are including in the comparison.

 

Here is an example of a typical duplicate record check based on the Last Name, Address, and City fields:

 

Creating A Selection - Duplicate Record Check

 

Notice that we are only considering the first 15 characters of the Last Name. This reduces the chance a misspelled Last Name will cause a duplicate record to be missed. Now we are a little more bold in looking at 30 characters of the Address. We can do this because DonorQuest is very good at standardizing addresses internally during the check, expanding abbreviations to full spellings for comparisons, so the addresses of duplicate records are more likely to match up. DonorQuest is even clever enough to know "1st st" and "First Street" are the same thing! We are also only looking at the first 15 characters of the City name to compensate for misspellings, as we did with the Last Name.

 

Go ahead and run this on your own database just to see what you get. You can use the View button of the Selection Results window to then see the actual suspected duplicate addresses conveniently placed right next to each other. If you just have a few you can manually mark and consolidate them as you see fit (using the Mark and Consolidate buttons on the left).

 

Back in the Selection Results window, you can use the ReSelect New button to run some variations to get a good feel for the nature of the duplicates you have. Adding fields to the comparison or increasing the number of characters per field which are compared will make it less likely that you will get false duplicates in the selection result, but you may also fail to catch some of the real duplicates. In general, the default values work quite well (which you can apply by clicking the Use Defaults button), but you are free to adjust them to best suit your donor data.

 

Just for kicks, try running the duplicate check with 15 characters for Last Name, 15 for City, and only 4 characters of the address. Any one of these considerations by themselves would give a lot of false duplicate detections -- the addresses for a lot of different donors would likely start with the same first four numbers for example. But taken together, these three considerations are unlikely to all be true for different donors. Plus, they are likely to catch pretty much every single duplicate you have, along with some false positives. If you still have very few duplicates even with these settings, it means you are in really good shape.

 

If you tend to have a lot of post office box addresses in your database, then you may wish to set the address check characters to at least 10 or more, since all the addresses starting with "POB" would always match if only the first four characters were considered for each address, and the last name happened to be blank.

 

You should also add in the first 20 characters or so of the Organization field if you have organizational givers as well as individuals.

 

You can also take an entirely different approach if you like -- checking all digits of the main telephone number, mobile number, or all characters of the primary email address for example instead of using names and address info. This is where you have to take into consideration what information you are typically able to capture for your donors. For example, if you have the main phone number for almost all of your donors, but not the email address, then using the phone number is a better way to go. Feel free to experiment here to see what works best with your data.

 

Once you get a duplicate record check selection result, you will typically do two things. First you may choose to simply view the result, ordering it by various fields such as Last Name or Address to see how well your particular mix of fields and character widths worked. If it looks like you got a pretty accurate result with mostly true duplicates, then you might print the selection using the, "Header - Donor Name And Street Address For Duplicate Record Check" report layout which is specifically designed to show duplicate records together by address. You could then review the report with other people in your organization to make a decision about clean-up, possible highlighting the names which you all agree are true duplicates. Finally, you can return to viewing the selection in DonorQuest, and physically consolidate the true duplicates using the DonorQuest Consolidation feature.

Duplicate Record Consolidation

In addition to finding and reporting suspected duplicate donor accounts, DonorQuest allows you to consolidate the histories of duplicate donors. While viewing a selection of suspected duplicate records, you can use the Mark button to mark actual duplicates, leave the blue highlight on the record you want to keep, then click the Consolidate button. Be sure and mark only the records which apply to a single duplicate donor. For example, if there are three duplicate records which all belong to donor Justin Hayward, you would mark all three, position the cursor on the one of the three that has the most correct information, then click consolidate. Note that all donations, pledges, etc. will then be copied to the one surviving donor account, and the duplicate records will be deleted. While each donor may have multiple donations, pledges, etc., each donor only has one Header record storing the main biographical information for the donor. So if you find that one of the duplicate records has a piece of biographical information you need to keep -- such as a phone number or email address, you should copy and paste it into the one record that you plan to keep before clicking the Consolidate button. This can be done by moving back and forth between duplicate accounts with the Edit form open, copying and pasting Header field data with the mouse, click the Save button to commit any changes you make. If you mistakenly consolidate to the wrong donor account, you can reverse the consolidation by clicking on Donor, Undo Consolidations To Donor.

Mass Duplicate Record Consolidation

If you have a lot of duplicates of a basic nature which you don't really need to fine tune by doing manual edits and choosing which record to keep, you can let DonorQuest make those decisions for you and consolidate all your duplicates with a few mouse clicks! This is done by highlighting the duplicate check result you wish to use on the Selection Results window, and then clicking on Selections, Mass Data Operations Via, Consolidate/Delete Duplicates. Be sure and look over the duplicate result you plan to use carefully to be sure it only has records you really want to be consolidated.

Checking For Duplicate Donations, Pledges, Contacts, Etc.

It is also possible to check for duplicate donations, pledges, contacts, etc. either across the entire database, or within donor accounts. As with the check for duplicate donors, you may choose the fields to use in the comparison between records, and how many characters of each field to use in the comparison. For example, you could check for duplicate donations using only the date and amount fields like this:

 

Creating A Selection - Duplicate Record Check - For Donations

 

To search for duplicate donations which exist within donor accounts (which can be caused by importing the same list of donations twice), include the Donor ID number in the field to consider like this:

 

Creating A Selection - Duplicate Record Check - For Donations Within Donor Accounts

 

Note that for numeric fields, you should always specify the entire width of the field in the Characters column. This is because numbers are formatted as right-justified character values before they are compared, and you always want to compare all digits of any two numeric values.

 

Also notice that we chose Donations for the Source Table when checking for duplicate donations. If we had left it set at the Header table, then only the most recent donation for each donor would have been considered.