CRM deduplication
Brightwater Events (fictional)
Bring web sign-ups and event badge scans together, respect deletion requests, and build one golden record per person.
- Level
- advanced
- Time
- ~65 min
- Deliverables
- 2
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the marketing team one clean contact per person, with where they came from, so the next mailing reaches each person once and never reaches anyone who asked to be forgotten.
Brightwater runs trade events. People sign up on the website, and at the door their badge is scanned by an app that exports JSON. The same person often appears several times across both systems, typed differently each time. Some people have asked for their data to be deleted; their addresses are on a suppression list that must always win.
Graded deliverables
1. Contact Records
50 pointsEvery usable record from both systems in one shape, normalised and with deletion requests removed.
- Columns: Source, SourceID, Name, Email, Phone, SeenOn
- Email lower case and trimmed; Phone digits only or null
- No rows without a usable email; no suppressed address
Expected output schema for Contact Records Column Type Source text SourceID text Name text Email text Phone text or null SeenOn date - Grain:
- One row per web sign-up or badge scan that has a usable, non-suppressed email.
- Order:
- Not graded.
- Nulls:
- Phone is null when the record has no digits; nothing else is null.
2. Golden Contacts
50 pointsOne row per email address, built from that person's records.
Builds on: Contact Records
- Columns: Email, Name, Phone, FirstSeen, LastSeen, Sources, Records
- Exactly one row per email
- Name from the latest record; Phone from the latest record that has a phone
- Sorted by Email
Expected output schema for Golden Contacts Column Type Email text Name text Phone text or null FirstSeen date LastSeen date Sources text Records whole number - Grain:
- One row per normalised email address.
- Order:
- Graded: Email ascending.
- Nulls:
- Phone is null only when none of the person's records has a phone.