Chapter 6 — Data quality in practice
Lesson map
Lesson 06 published. Map, example, EX026.
flowchart TD
N0["Learning outcomes"]
N1["6.1 Duplicate donors"]
N2["Lab — Duplicate donor emails before and after normal"]
N3["6.2 Results"]
N4["6.3 Fixing and preventing"]
N5["6.4 Quality habits"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5
N5 --> EX["Worked example"]
EX --> Q["Exercise EX026"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["6.1 Duplicate donors"]
N2["Lab — Duplicate donor emails before and after normal"]
N3["6.2 Results"]
N4["6.3 Fixing and preventing"]
N5["6.4 Quality habits"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX026"]
[Learning outcomes]
|
v
[6.1 Duplicate donors]
|
v
[Lab — Duplicate donor emails before and after normal]
|
v
[6.2 Results]
|
v
[6.3 Fixing and preventing]
|
v
[6.4 Quality habits]
|
v
[Worked example] --> [Exercise EX026]
Learning outcomes
You will (1) find duplicate donor records; (2) normalise simple fields; (3) set up quality habits for a small team.
6.1 Duplicate donors
The fundraising manager reports that some donors receive two copies of each newsletter and complain. Grace suspects duplicate email addresses entered in different ways. She exports synthetic donor emails and checks how many are really the same address written differently, such as with capital letters or extra spaces. She explains that duplicates waste money, annoy donors and make it look as though Northbank has more supporters than it really does. The fundraising manager agrees to export the donor email list for checking with her help.
Lab — Duplicate donor emails before and after normalising case and spaces
"""Count duplicate synthetic donor emails before and after normalising case and spaces (stdlib only)."""
import random
random.seed(2)
base = [f"donor{i}@example.org" for i in range(1500)]
emails = []
for e in base:
emails.append(e)
if random.random() < 0.08: emails.append(e.upper())
if random.random() < 0.05: emails.append(" " + e + " ")
print(f"records {len(emails)}; distinct as written {len(set(emails))}; distinct normalised {len({e.strip().lower() for e in emails})}")
print(f"duplicate records found by normalising {len(emails) - len({e.strip().lower() for e in emails})}")
Run with python3 lab_emails.py. Verified output:
records 1684; distinct as written 1684; distinct normalised 1500
duplicate records found by normalising 184
6.2 Results
The lab checks 1,684 synthetic email records. As written, all 1,684 are distinct, so a simple duplicate check finds nothing. After converting to lowercase and removing spaces at the ends, only 1,500 distinct addresses remain. Normalising reveals 184 duplicate records. Grace notes that the duplicates were invisible until the data were normalised, which is why newsletters were going out twice without anyone understanding why. Grace explains the finding to the fundraising manager with three examples of the same address written in different ways, which makes the problem easy to understand without any technical knowledge.
6.3 Fixing and preventing
The fundraising manager merges duplicate donors in the database, keeping the full gift history on one record. Grace asks the database supplier whether emails can be stored in lowercase automatically, which it can. She also asks staff to search for a donor before adding a new one. Mr. Nowak notes that fixing existing duplicates and preventing new ones are both needed, or the problem returns. After the changes, newsletter complaints about duplicate copies stop, and the fundraising manager reports a more accurate count of supporters to the trustees.
6.4 Quality habits
Grace sets up simple habits: a monthly duplicate check, a quarterly check of missing postcodes and a yearly check that household definitions are used consistently. Each check has an owner and takes less than an hour. She records results in a short log. She notes that small, regular checks suit a charity with limited staff better than large clean-up projects that compete with daily work. The log is reviewed at each monthly leads meeting, so that problems are noticed early and owners can see that their checks make a difference.
Worked example
Grace finds 184 duplicate donor emails that were invisible until 1,684 records were normalised to 1,500 addresses, merges them with gift history, adds lowercase storage and search-first entry and sets monthly checks.
Synthetic case. Emails are synthetic; outputs come from lab_emails.py.
Errors and limits
Exact-match duplicate checks miss differences in case and spacing.
Exercises
- EX026 — Distinct normalised. How many distinct addresses remain after normalising? Rubric: 3 = 1,500;; 2 = partly correct;; 1 = wrong;; 0 = none.
- EX027 — Duplicates revealed. How many duplicate records did normalising reveal? Rubric: 3 = 184;; 2 = partly correct;; 1 = wrong;; 0 = none.
- EX028 — Exact check result. What did an exact duplicate check find? Rubric: 3 = nothing; all 1,684 distinct as written;; 2 = nothing, without the reason;; 1 = vague;; 0 = no answer.
- EX029 — Supplier change. What did the supplier enable? Rubric: 3 = storing emails in lowercase automatically;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX030 — Check duration. How long should each check take? Rubric: 3 = less than an hour;; 2 = partly correct;; 1 = wrong;; 0 = none.
Continue studying — lesson 06 (chapter 06 of 12)
← Chapter 05 · Course chapter index · Chapter 07 →
Continue to the next authored lesson.