~/sajjad $ cat projects/recruitment-crm
← cd ../projects
Data & reporting
Candidate database & recruitment CRM
Done · Oct 202628,686 candidates
Twenty-seven candidate spreadsheets built up over three decades of recruiting, cleaned and merged into one database of 28,686 people, with a CRM on top: type what an employer needs in plain words, get the matching candidates, and email them in one click.
The data problem
A recruiter with over thirty years in construction had his whole network in spreadsheets — twenty-seven of them, each laid out differently, the same people appearing in many of them with different spellings, phone formats, job titles and employers.
Merged into 28,686 unique candidates across 31 job categories, with every source file a person appeared in recorded on their row, so nothing about where a record came from was lost.
Cleaning rules
- Merge only what is certainly the same person. 2,991 records that might be duplicates are flagged for a human, not merged automatically — a wrong merge loses a person; a flag costs a glance.
- A placeholder email domain used throughout the files meant “no email yet”. 2,355 of them are recognised as exactly that, so the CRM never sends to an address that doesn’t exist.
- Formats fixed, roles and sectors standardised to one vocabulary, and nothing deleted except true duplicates — old records stay, because old candidates get placed too.
The CRM
- The recruiter types what an employer asked for, the way he’d say it on the phone, and gets the best and the full set of matches.
- One click emails the selection by BCC, so candidates never see each other’s addresses.
- He never sees or downloads the raw database. Only the owner account can manage logins and upload source files; new spreadsheets merge into the same table.
How the search reads plain English
- A custom parser turns “site manager Leicester” into filters. It copes with typos and plurals, several counties or regions at once, employer names, directors only, and only people with an email.
- A UK town → county → region map, so a search for one town also finds people listed under its county or region.
- Role-based access: the partner can search, email and log calls, notes, replies and placements, but can’t see source files, settings or anyone else’s data.
Agreements signed in the app
Fee agreements for employers or candidates are built in the CRM: a single fee or instalments, VAT handled, extra terms, and a live preview as it’s written. The recipient gets a link, draws or types a signature and ticks consent.
Both sides receive the signed PDF with a certificate of completion: signer IP, device, timestamp, a fingerprint of the document and the full audit trail of events. That gives a DocuSign-style record with no per-envelope fee.
Every email logged, replies captured
- An Emails tab shows every message sent and received, with its status and whether it was opened.
- A scheduled server function checks the company inbox every minute. Replies and bounces are matched to the right candidate, client, campaign or agreement through a hidden message ID, then logged on the person’s history, and the recruiter can reply in a thread inside the app.
- Built-in sending caps of 450 a day and 8 a minute keep the mailbox inside the provider’s limits. Unsubscribing marks the person do-not-contact and removes them from future searches. Data-protection risks in old candidate records were raised before anyone asked.
Tested before every delivery
Each change was run against a local copy of the database, with the server functions executed locally and the interface clicked through in an automated browser, before a single file was handed over.