How to clean donor data and build a reliable nonprofit report
Clean donor data by separating person, household, organization, gift, pledge, payment, campaign, and restriction records; resolving identity candidates with reviewed evidence; and preserving anonymous or confidential preferences. Reconcile gift and cash views separately before reporting donor count, retention, campaign, or restricted funds.
The decisions that matter
- Keep donor, household, organization, pledge, gift, payment, campaign, fund, and restriction relationships.
- Use reviewed contact and household evidence while respecting anonymity and communication preference.
- Distinguish pledged, committed, received, refunded, matched, restricted, and unrestricted amounts.
- Match finance totals and publish only the donor detail each audience needs.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Name dedupe | Exact formatting cleanup | Households and organizations |
| Gift spreadsheet | Small cash-only programs | Pledges, matches, and restrictions |
| Linked fundraising model | Reliable donor and board reporting | Governance and privacy |
Where the difficulty begins
A household may donate under two names, a pledge can create several payments, and one matching gift can arrive from an employer. Deduplicating names or adding pledge and payment amounts together inflates donors and funds.
Build the result step by step
- Model fundraising entities: Keep donor, household, organization, pledge, gift, payment, campaign, fund, and restriction relationships.
- Resolve identities: Use reviewed contact and household evidence while respecting anonymity and communication preference.
- Separate reporting views: Distinguish pledged, committed, received, refunded, matched, restricted, and unrestricted amounts.
- Reconcile and protect: Match finance totals and publish only the donor detail each audience needs.
From the field · Nonprofit fundraising analyst
Combine campaign exports before a nonprofit board report
Online, event, and major-gift exports overlap. One couple donates separately and jointly, an employer provides matches, several gifts are restricted, and two donors requested anonymity.
Columns used in the example
donor_id · household_id · gift_id · pledge_id · payment_id · campaign · fund · restriction · amount · anonymous_flag
Follow it with your own file
- Map source records to donor, household, gift, pledge, and payment grains.
- Review identity and household candidates without overwriting preferences.
- Calculate received cash, pledges, matches, refunds, and restrictions separately.
- Reconcile finance and fundraising totals and create privacy-appropriate board summaries.
Check: Payments must reconcile to finance, pledge balances to pledge history, restricted totals to fund records, and anonymous identities must never appear in published output.
Fundraising relationships are not duplicate rows
People, households, employers, pledges, gifts, and payments form a relationship model. Preserving it allows the nonprofit to recognize donors appropriately and report cash and commitments without double counting.
Where this usually goes wrong
Donor data is sensitive and may carry legal, ethical, tax, and safety implications. Honor anonymity, consent, restriction, retention, and access policies throughout the workflow.
What may be distorting the answer
- Pledges and payments are summed as if both were cash.
- Household members are merged without preserving individual preferences.
- Restricted and unrestricted gifts share one total.
Check the source before you publish
- Entity grains separated
- Identity matches reviewed
- Pledge and cash views separate
- Restrictions reconciled
- Privacy preferences enforced
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Clean nonprofit donor records and build a reconciled fundraising report. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Text analysis · Charts · Reports · Sensitive Column protection
A donor and fundraising report whose people, gifts, payments, campaigns, restrictions, and privacy choices all reconcile and remain traceable.
Questions readers usually ask next
Are a pledge and a donation payment the same thing?
No. A pledge is a commitment; payments fulfill it over time. Report committed and received amounts separately.
Should household donors be merged?
Link household relationships when supported, but preserve individual identities, communication preferences, crediting rules, and anonymity.