Cleaning donor and member data: entity resolution on a shoestring

Here is a donor record pattern most charities will recognise. Someone gives monthly through the website under her full name. She buys gala tickets as “M Nguyen” through the event platform. In the finance system she sits on a joint record with her husband, because a cheque came in from both of them years ago. Each record is correct on its own. Put together, they mean extra receipts at the end of June, thank-you letters that miss half her giving, and a major donor report that leaves her out.

The textbook name for fixing this is entity resolution, which also goes by record linkage or deduplication. It means finding records that refer to the same person, either across systems or inside one, and then linking or merging them (Spot Intelligence). This post is part of our Practical AI for Not-for-Profits series. It describes how we approach the problem when the budget is small and the “data team” is one person who also has other work. Plain rules handle the easy matches. An LLM sorts the leftovers into suggestions, and a person approves or rejects each one.

Where the duplicates come from

Yeshaya’s nonprofit data cleanup guide puts most of the mess down to systems rather than people. When donation platforms, CRMs, event tools and accounting software don’t exchange data cleanly, staff re-key it and the inconsistencies multiply. Without entry standards, one donor ends up as “John Smith” in one place and “Smith, John” in another. The guide’s checklist covers membership, event and financial data as well as the donor list, and the same applies here. Members and event attendees duplicate just as easily.

Australian data has its own variants. Unit addresses get written as “3/12 Smith St” or “Unit 3, 12 Smith Street”. Mobiles get stored with spaces, without the leading zero, or with +61. And many older records are households, with a couple sharing a single line.

Normalise before you match

Matching on raw fields fails on trivial differences. Before comparing anything, build a normalised copy of each record and leave the source untouched. In practice that means:

• splitting “Smith, John” into given and family names and dropping titles
• converting mobiles to one format (E.164, so +61412345678)
• expanding street abbreviations and standardising state codes
• lower-casing and trimming emails

The People Data Labs entity resolution guide gives standardisation and parsing a stage of their own, before any matching logic runs. We store the normalised fields in a staging table along with the source system and the original record ID. Every later step reads from that table, so you can always trace a suggestion back to the records it came from.

Rules do most of the work

Deterministic matching is cheap and easy to explain, so it goes first. An exact match on normalised email or mobile, or on family name plus postcode plus first initial, picks up the obvious duplicates without any model involved.

-- Candidate pairs sharing a normalised email or mobile
select a.id as left_id, b.id as right_id, 'email' as rule
from staged_contacts a
join staged_contacts b
  on a.email_norm = b.email_norm and a.id < b.id
where a.email_norm is not null
union all
select a.id, b.id, 'mobile'
from staged_contacts a
join staged_contacts b
  on a.mobile_e164 = b.mobile_e164 and a.id < b.id
where a.mobile_e164 is not null;

Comparing every record against every other record gets slow quickly. Blocking fixes this by grouping records on a criterion such as postcode and comparing only within each group (Spot Intelligence). Within a block, phonetic encodings like Soundex or Metaphone catch surname spelling variants.

Watch out for transitive closure. If A matches B and B matches C, a naive process will conclude that A matches C (Spot Intelligence). A family sharing one email address can collapse three people into one record that way, which is why rule-based matches also go to review. They just go in larger batches.

The fuzzy tail is where the LLM helps

Once the rules have run, you’re left with pairs that share a block and a surname but differ elsewhere. “Bob Jones, 14 Ocean Pde” and “Robert Jones, PO Box 22” in the same postcode could be one person or a father and son. Nickname tables help with the names. The address question needs judgement, and a language model is cheap enough to apply that judgement to thousands of pairs.

We send the model the two normalised records and ask for a structured verdict. We never ask it to perform the merge.

{
  "verdict": "likely_same",
  "confidence": "medium",
  "reason": "Bob is a common short form of Robert; same postcode; PO Box and street address both plausible for one person",
  "conflicting_fields": ["address_line_1"]
}

Send only the fields the comparison needs. Donor names and addresses are personal information under the Privacy Act, so use a provider with an Australian hosting region and terms that exclude your data from training. Leave donation amounts and free-text notes out. Notes fields often contain sensitive details nobody intended to share with a third party.

The model doesn’t need to be large. Each pair is a few hundred tokens, so a few thousand pairs through a small model usually cost a few dollars per run. Most of the spending goes into building the pipeline and staff time on review.

A person confirms every merge

Merging is hard to undo in most CRMs, and a wrong merge sends one donor’s history and contact preferences to someone else. So the model only suggests. A staff member sees the two records side by side with the rule or model reason and chooses merge, keep separate or skip.

People Data Labs recommends logging match decisions and keeping manual review paths for low-confidence matches, even at scale (People Data Labs). We log everything, rejections included. A rejected pair is stored so it doesn’t come back next week, and the log shows which rules or prompts produce bad suggestions.

The surviving record lives in the CRM. Yeshaya recommends treating one system, usually the CRM, as the canonical record that the others flow into (Yeshaya). Run the merge through the CRM’s own merge function so gifts and activity history move with the record. One rule applies to every merge: the most restrictive contact preference wins. If either record says “do not mail”, the merged record says it too.

Centazio does the plumbing

Centazio is PicNet’s open-source data integration platform. In this design it moves the data. It reads contacts from the CRM, the website and the finance system into staging, then writes approved merges and cross-reference IDs back to each system. The matching code runs alongside it and stays small enough for one person to understand.

This matters because a one-off cleanup decays. Yeshaya warns that cleaning up without governance just recreates the same mess within 12 months. We’ve done CRM integration work for Guide Dogs NSW/ACT, and integrations like that one connect systems so staff stop re-keying data. The downside of any automatic sync is that it copies a duplicate created in one system to the others. Matching belongs inside the integration, so new records get checked as they arrive and don’t pile up for an annual cleanup.

Costs and limits

The model is the cheapest part. Most of the effort goes into normalisation rules for your particular data and a review screen that staff will use. Plan for a few rounds of rule tuning, because every organisation’s imports go wrong in their own way.

Some cases stay hard. Households, deceased donors, and people who share a name and suburb with a relative will always produce “unsure” verdicts. Those stay in the queue for someone who knows the supporters. A missed duplicate costs you an extra receipt, while a false merge can send a bequest acknowledgement to the wrong person. Set your thresholds with that difference in mind.

APP 10 asks organisations to take reasonable steps to keep personal information accurate, up to date and complete. A repeatable, logged matching process with human sign-off is a defensible way to meet it.

PicNet builds production AI systems for Australian organisations. If your donor data needs this kind of work, talk to us about what a first project could look like.