Migrating your recruitment database off Excel: clean data, shared status definitions, and a hard cutover date

I write this as chief executive officer (CEO) of Amavita Research Services, a Miami cardiovascular trial site with more than 18,000 active cardiology patients and zero findings on Food and Drug Administration (FDA) Form 483 inspections, and as CEO of bioaccess, a first-in-human contract research organization (CRO).

Why spreadsheets break as recruitment databases

A spreadsheet works when one coordinator owns a short list for one study. It breaks when the list grows to thousands of names and several people update it at once. There is no enforced deduplication, so the same person appears three times under three spellings. There is no shared vocabulary, so one coordinator marks a lead "contacted" after leaving a voicemail, while another uses the word only after a live conversation. There is no reliable permission trail, so nobody can say who agreed to be contacted again. And the file gets copied under new names, so there is no single source of truth.

A recruitment platform fixes none of that by itself. This article covers the migration itself, a separate job from choosing a clinical trial management system (CTMS). A CTMS manages study conduct after enrollment. A recruitment database manages people before that point: where they came from, whether you may contact them, and what happens next.

Step 1: Audit and clean the spreadsheet before you import anything

Importing first and cleaning later means cleaning in a system you do not yet know, with duplicates already confusing your team. Clean in the spreadsheet, and import only what deserves to move.

Deduplicate on phone and email first. Match on mobile phone number and email address, because names are unreliable: nicknames and typos multiply one person into several rows. Merge duplicates into one surviving record that preserves the earliest source, every study interest, and the full contact history. Do not delete the extra rows outright, or you lose the history that explains why this person was already called twice.

Retire dead leads with a documented rule, not a feeling. For example: a lead is retired after a defined number of attempts over a defined period with no successful contact, no response to the last outreach, and no future study the person asked to hear about. Write your thresholds down, apply them uniformly, and archive retired records outside the new system. Archiving is not destruction: you keep the record for audit purposes, and stop asking recruiters to work names that will never convert.

Fix consent and contact-permission flags before they move. For every record, the new system needs a clear answer: may we contact this person again, and on what basis? Sort records into explicit permission, permission unknown, and do not contact. Import the first group as contactable. Import the second, if at all, as not contactable until permission is established. Import the third only as a suppression record, so the person is never contacted again by accident. Never let an empty cell become a "yes." Treat the personal and health-related information in these files as protected health information (PHI): limit who handles working copies, and delete stray copies when the migration is done.

Finish with a count you can defend: rows started with, duplicates merged, leads retired, and clean records remaining. That is your baseline for every later check.

Step 2: Agree shared status definitions in writing before you map columns

Column mapping is mechanical. Meaning is not. Before anyone maps the old "Status" column, sit down with everyone who recruits and agree, in writing, what each status means and what action moves a lead forward. Define the statuses your team actually uses, in this style:

• New: Recorded, with no contact attempted yet.
• Contacted: At least one documented outreach attempt. It does not mean the person answered.
• Reached: An actual two-way conversation with the person. This is what many teams wrongly call "contacted."
• Pre-screened: A brief eligibility conversation completed using the approved questions for a specific study, with the outcome recorded. A voicemail is not a pre-screen.
• Not eligible: The pre-screen found a specific disqualifying reason, and that reason is recorded.
• Unreachable: The agreed number of attempts, across agreed channels and times of day, produced no response.
• Do not contact: The person asked not to be contacted, or permission was withdrawn. This status overrides every other workflow.

Put the definitions into a standard operating procedure (SOP) or a one-page work instruction new staff receive on day one. The argument about what "contacted" means will happen. Have it once, before the migration, instead of every week afterward in your data.

Step 3: Map fields once, test-import a small batch, and verify the counts

Build the field map once: each spreadsheet column, its field in the new system, and how each old status value translates to a new one. If an old value has no honest equivalent, decide what happens to it before import, rather than letting the import tool guess.

Then test-import a small batch, roughly one hundred records chosen to include your edge cases: merged duplicates, unknown permission, do-not-contact records, and leads in every status. Reconcile afterward: records in should equal records out, plus the duplicates and rejections you expected and can name. Spot-check records against the source, especially permissions and statuses, because a perfect count with wrong permissions is a failed import. Only when the test batch reconciles do you run the full import, reconciled against the Step 1 baseline.

Step 4: Set a hard cutover date and make the spreadsheet read-only

Choose a date, announce it weeks ahead, and state the rule plainly: from that date, the new system is the only place leads are entered or updated, and the spreadsheet is archived read-only.

Without a hard date, the predictable happens. A recruiter in a hurry updates the spreadsheet because it is familiar. Another updates the platform. Within a month you have two partial truths, double entry, and staff trusting the sheet they personally maintain. Sites tell me they are still "running both systems" a year after go-live. That is not a transition; it is the failure mode.

Finish data entry in the old sheet the day before cutover, run the final import, verify the counts, then change the file permissions. Read-only means read-only: nobody, including leadership, edits the archive. Anything missed is entered fresh in the new system.

Step 5: Train on the statuses, not just the software

Software training takes an afternoon. Status discipline takes practice. Walk the team through scenarios and ask which status applies: the lead who answered, said she was busy, and asked for a call next month; the lead whose daughter answered and said he was not interested; the lead who pre-screened for one study and wants another. Let people disagree, then settle each case against your written definitions. After cutover, audit a sample of records weekly until everyone applies the statuses consistently.

Step 6: Measure before and after, starting with leads contacted per week

Before cutover, measure at least four weeks of baseline from the spreadsheet era: leads contacted per week, time from a new lead arriving to the first contact attempt, percentage of records that are duplicates, and percentage with unknown contact permission. After cutover, measure the same numbers over the same period. Leads contacted per week is the headline measure: it shows whether recruiters spend their time working leads instead of reconciling two systems.

The point of migrating

The spreadsheet was never the enemy. Undisciplined data, private definitions, and a transition with no end date are. Clean before you import, define before you map, test before you load, and cut over on a date you enforce. Do those four things and the new system starts life as a source of truth recruiters can trust, instead of a pricier copy of the mess you already had.