Category: Data Clean-Up

Keep it Clean: Tips for nonprofit mail list management

Nonprofit are moving toward the cheaper, faster email-centric communications model–but print is still king in the fundraising industry. And whereas mass email services like MailChimp and Constant Contact magically suppress duplicate emails and opt-out addresses, your organization’s back office is most likely still managing the print mail list–and most likely up to their elbows in address updates and returned mail pieces. Below…

Splitting compound name fields in Excel

The following name parsing example uses an export file from GiftWorks that shows Display Name, but the same process can be applied to any exported field with a compound name (Mrs. Jan and Mr. Joseph Rees), such as a Salutation field. Use the following process to parse the compound field and move the newly created names into appropriate fields. Be sure to include the Donor ID number in ALL donor exports.

  1. Create an Excel export of your data.
  2. Sort records so that those with no First/Last Name are grouped together.
  3. Copy the Display name column and paste a duplicate column next to it and create the header TempLast.
  4. Insert a blank column to the right of the TempLast column and create the header TempFirst. Select the data in the TempLast field and click the Data Tab of Excel.
  5. Select the Text to Column option
  6. Select Delimited and click Next
  7. In the next check box menu, select Comma
  8. Click Next and Finish
  9. Now Move the data from TempLast to the blank Last Name field, and data from TempFirst to the blank First Name fields.
  10. Repeat this process to parse the First Name field into Primary and Affiliate Name fields. To adhere to the best practices of donor record householding, you will want to import a new affiliate donor record using the Affiliate Name (which is the spouse’s first name), the Last Name and address fields.
  11. Delete blank columns and resort list as desired.

If you are planning to use this export for a mailing, conduct this parsing process in Excel prior to running the NCOA update to ensure the best possible screening results. When you import the NCOA changed file, you will update the name fields and addresses at the same time.

To correct this issue permanently, and to avoid similar difficulties related to name sorting and inaccurate householding, be sure to institute the protocol for creating new donor household records when entering a compound record .

If, after correcting the blank name field issue, you are finding your NCOA changes cannot be imported back into your database, it is likely that there is one important missing field in your export–the Address Identifier field.


Questions or Comments?

Donor record structure for an individual and spouse

Though it’s considered best practice for each individual in your file to have his or her own record with a relationship link to a spouse or children, organizations will sometimes record both a donor and a spouse on the same record. This is called a “compound” record.

In an example of a compound record, Jan Reese would be recorded as the Donor, and Joe Reese as the Spouse. All gifts, addresses, emails and notes would be associated with that one, compound record. Whereas, when using a “household” record structure, Jan Reese and Joe Reese would each have their own record, but share a Household Record through relationship links. In a householded record the gifts, addresses, emails and notes would be attributed to a specific individual, and can be reported on the individual record level, or as a combined household.

Organizations migrating out of Access or a similar, simple system that doesn’t offer the household option might opt to retain their existing compound-record structure when migrating because (a) “splitting” records requires a more complicated, multi-step import process at migration, and (b) splitting records might increase the costs of a subscription if the package pricing is tied to the number of records. However, there are significant advantages to changing the record structure from a “compound” record to the Householded structure.

1. File Screenings: NCOA and Wealth Data screenings are more effective if records have a clear First and Last Name field associated with each address. Don’t worry, Addressee and Salutation names can be managed just as simply in split records as in a single record, as can mailing options to protect against inadvertently sending multiple pieces to a single household.

2. Email: Most software requires users to define a single email to be used in mass email efforts. When using a compound record structure, an organization can send to only one email. Using a Householded structure, each household member has the option of receiving emails or opting out.

3. Gift Credit: Having a clearly defined head of household simplifies tax acknowledgement and gift receipting procedures. While each gift can be attributed to the specific individual (which simplifies tax acknowledgement and gift receipting procedures), donation reports can show the combined giving for a household.

4. Major Donor Management: Cultivation and solicitation activities can be specifically directed at the decision-maker in a household when one spouse is the primary donor, or customized for each spouse when each has different philanthropic interests or relationship with with the organization–such as a board member or volunteer.

5. Changes in Households: Maintaining individual records makes it easier to correctly capture changes in a household’s structure, such as a deceased spouse, divorce, or a child leaving the home.

6. Universality: Only a few of the more robust software products support single-record householding, but virtually all of them support an individual record structure.

Donor Perfect provides users with the optional Spouse Name fields on the primary record, so it can support a single-record household, but the software is ultimately designed for an individual record structure.

 Raisers Edge (i) requires two separate records–one as the primary constituent (called head of household) the other as the Spouse, which can be a full constituent with all fields and functionality activated, or remain a simple relationship with limited field functionality and searchability. The spouse relationship is a unique relationship in RE, and there are designated Spouse Address and Contact fields.

eTapestry, requires an individual record householding structure.

GiftWorks provides the illusion of supporting a single-record household structure, but really it doesn’t. Instead, it has a very flexible record naming process that doesn’t require a Last Name field and thus allows users to create compound display names (Jan and Joe Rees) without defining either a First or Last Name. For this software product in particular, it is very important to have a clear data entry protocol for creating new donor household records.

Salesforce very rigidly supports only individual records using household relationships.


If you elect to change householding structure when purchasing a new software product, consider doing so while in your current software. It will be easier to use processes and fields you are familiar with, it will be easier to proof changes, and the process may require significant record-by-record decision making which is better done prior to contracting new services. The main decisions to be made are which spouse will be head of household, and will the Spouse record be a full constituent by default, or remain a simple Relationship record (or Affiliate Donor).

For an overview of the file splitting process view Splitting Records: Parse Display Names in excel, and re-import complete data which steps users through an Excel process for splitting name fields.


Questions or Comments?



Stop manually entering NCOA updates

If you are finding your NCOA changes cannot be imported back into your database, it is likely that there is one important missing field in your export–the Address Identifier. This field might be an Address ID number or Address Name, depending on your software, but regardless of what it’s called, it functions as the unique identifier of a single address.

Because constituents can have multiple addresses, your software needs this unique identifier in order to know which of these address should be updated from the NCOA file. Attempting to import an NCOA file without the unique identifier will either create exceptions at import, or possibly update the wrong address on a donor’s record.

If you Include the Address ID or Name in all your mail list exports, as well as the First and Last Name fields, you will never have to manually enter updates again. If you’re still having difficulty importing updates, check for blank First and Last Name Fields in your export and import files. Both name fields should be in all exports files, even if you use the Display Name, Salutation and Addressee Names for actual mailings.

Also, most hosted software products offer an NCOA update service (usually under $200 per incident) that can save your staff time and ensure your records have the most up-to-date addresses possible. Some of these services have other useful add-ons, such as email or phone appending, Deceased Finder, and Data Health snapshots.

Donor Perfect, Data Enhancement Services

Raisers Edge, Data Health Center 

eTapestry, watch for updated features. They currently offer a Data Health Scorecard on all service packages, NCOA updates for the Essentials and Pro packages, and will soon offer Decease finder, email append and other integrated Target Analytics.

GiftWorks, CASS and NCOA updates


Comments or Questions?