How to clean a lead list and reconcile every row
Learn how to clean a lead list by standardizing fields, separating invalid and duplicate rows, and checking that opt-outs survive in the export.
Free app. Runs on your Mac with the Claude or Codex plan you already have.
What it means to clean a lead list
To clean a lead list, start with the original rows and produce a usable copy with standard fields, invalid records set aside, and duplicates accounted for. Keep enough detail to trace any value back to its source and reverse a change. A clean file should answer where a record came from, why it was kept, and which facts still need attention before the record is used.
Lead list cleanup usually starts with a spreadsheet or a CSV, a file that stores table values as text separated by delimiters. Common defects include spaces around addresses, inconsistent country labels, broken columns, and repeated entries for the same contact. Contact permissions need their own rules. A row can meet the technical requirements for an import while carrying an opt-out or an unknown permission status.
What it does for the business
- Prepare a more predictable importA file with stable headers, readable values, and an explicit set of rejected rows gives the person mapping fields a concrete review task. They can address missing required values before changing the destination system.
- Reduce repeat work for repsA duplicate map shows where several source rows describe one lead. A sales manager can assign work from the cleaned records while retaining the history needed to trace conflicting fields.
- Route exceptions to someone who can fix themInvalid and unresolved rows can go back to the source owner with a specific reason. That owner can repair the export rule or confirm a value instead of reviewing the entire list.
- Protect contact preferences when records moveA documented suppression on a retained record gives campaign owners an explicit instruction when they select recipients. They can also set aside unknown permission until they have the evidence required for their intended use.
How to clean a lead list, step by step
Save the original and inspect the file
Every output row should lead back to an identifiable input row.
What to do
- Confirm the export date, source, intended use, and destination fields. If the destination is a customer relationship management system (CRM), check which fields it requires for a new record. If there are several versions, identify the current one with the owner. Keep a read-only copy so you can compare raw and cleaned values later.
- Check the encoding, delimiter, headers, and quoted fields before counting records. Parse a row containing a comma in a company name or a line break in a notes field to make sure values stay in their columns.
- Use a stable source identifier for each row. If the file lacks unique IDs, pair the file identifier with the parsed record's position. Keep that reference when you sort, filter, or move records to another export.
- Record the full input count and any selection rule you apply. Capture missing or illegible source fields and confirm what the intended use requires before calling a row invalid.
Why
Correct row counts depend on reading the file correctly. A parser that mistakes a quoted line break for a new record can create what looks like a bad lead. A retained source and an explicit scope let you diagnose errors at the right level.
Define how to standardize and validate fields
Only standardize a value when you can explain why its meaning stays the same.
What to do
- Write a data normalization map, a list of rules for putting equivalent values in a common form. Trim accidental outer whitespace and standardize domain case. Keep the original email address available to review any change to its local part, the part before the at sign.
- Map country aliases using supplied conventions. Leave ambiguous abbreviations unresolved and preserve leading zeros in text identifiers. Check sample rows with accents and punctuation so the export retains what a rep needs to read.
- Check email syntax with a documented rule and mark missing or malformed values distinctly. Treat an address that passes as structurally valid. Keep mailbox reachability and permission in their own fields or review notes.
- Record opt-outs, explicit permissions, and unknowns as separate states. If a source field is blank or ambiguous, preserve the raw value and mark the uncertainty before applying any destination mapping.
Why
Cosmetic differences can hide duplicates, while overbroad edits can create false matches. Writing the rules first gives a reviewer a way to test each transformation and makes it easier to explain which changes are safe to reuse.
Separate invalid rows and resolve exact duplicates
Keep the evidence that explains each accepted, rejected, or held row.
What to do
- Apply the documented field rules to a working copy. Put invalid records in a rejected export with their source IDs and reasons. Distinguish a missing required value from an optional value that a rep can research later.
- Group duplicate leads using an exact normalized identity under your stated rules. Review conflicting company details and shared mailboxes before collapsing a group. Leave ambiguous identity matches in review with the evidence needed to resolve them.
- Record the retained row and all collapsed source IDs in a duplicate map. Preserve conflicting facts for review and keep the strongest restriction from each group, including a relevant stop from the suppression list.
- Log changed fields with the source value, new value, and rule. Include the unresolved records in the reconciliation. If an unresolved flag can coexist with an accepted record, make that overlap explicit instead of counting the row twice.
Why
The cleaned file is only part of the result. Without rejected rows, a duplicate map, and a change log, the owner cannot tell whether a shorter list came from legitimate cleanup or from losing data. The same records let an owner correct an individual decision.
Reconcile counts and check the export
A row count is useful only when each row has a traceable destination.
What to do
- Recompute the counts from the source IDs. Check that each in-scope record appears once in the disposition accounting and that each duplicate points to a retained record or a documented review outcome.
- Trace changed fields in both directions. Start from an output value and recover its input and rule. Then start from an original value and find where it ended up, including cases that were rejected or collapsed.
- Test a group that contains an opt-out and a row whose permission is blank. Check the output values and accompanying records so a destination mapping cannot accidentally interpret a blank as consent.
- Reopen the CSV with the intended column mapping. Check quoting, preserved text IDs, and treatment of cells that a spreadsheet might interpret as formulas. Retain raw values in the source so exported representations remain auditable.
Why
Row reconciliation catches omissions that a spot check can miss. Targeted review of suppression, conflicts, and empty inputs catches defects that still allow totals to balance. Test both before a downstream system treats the output as ready.
Hand off the file and unresolved decisions
A cleaned export should say what it is ready for and what still needs review.
What to do
- Package the cleaned, rejected, and change files with the scope and rules. Give the team the source version and date and explain where to find the duplicate map. Label any review that covers only part of the input.
- Name the decision for each unresolved group. If the destination requires a missing region code, assign the correction to the source owner and keep the row out of the CRM import until the requirement is met.
- Give the person who will run the import a separate check for contact eligibility. Ask them to confirm how their destination treats stops and unknown permissions before they select recipients or initiate any campaign.
Why
The import owner needs to know which file to map and what to do with exceptions. Making that handoff explicit helps the team preserve the cleanup decisions when the records move into another tool.
Common mistakes
- Editing the only copyPreserve the original so every standardization and removal can be explained or reversed.
- Splitting a CSV by commasUse a CSV parser that understands quoted commas and line breaks.
- Removing records by similar nameRequire an exact normalized identity to collapse a row automatically.
- Calling a valid-looking address verifiedReport the syntax check separately from any evidence about whether the mailbox receives mail.
- Keeping just the least restrictive rowCarry the strongest applicable suppression across the duplicate group.
What a good result looks like
- Original rows have stable IDs and a recorded source version.
- Standardized fields have documented rules and traceable raw values.
- Every reviewed row has a reconciled disposition.
- Duplicate groups retain conflicting facts and applicable stops.
- Rejected and unresolved items have specific reasons and next checks.
And then what? Using the result
- Give the destination owner the cleaned file and have them confirm field mappings and suppression handling before import.
- Return rejected rows to the source owner with the rule each record failed.
- Assign unresolved values and identity conflicts to someone who can check the original source.
- Reuse the rules on the next comparable export and inspect exceptions before carrying over any decisions.
When to run it
- Before a CRM importCheck required fields and identity rules while corrections are still local. Bring the destination owner the row dispositions and field map.
- After combining lead sourcesReconcile repeated records and conflicting fields while preserving where each piece of information came from.
- When reps report copied or malformed rowsUse the issue as a starting point and inspect the full export for the same defect. Give the source owner a reproducible example.
Questions people ask
How do you clean a lead list?
Save the original, standardize fields under written rules, separate invalid and duplicate rows, and reconcile every source ID. Preserve suppression and document unresolved records for review.
What should you do with duplicate leads?
Collapse exact normalized identities under a documented rule and keep a map from each source row to the retained record. Preserve conflicts and stops from every member of the group.
Does a syntax check verify an email address?
A syntax check verifies the structure of an email address. Whether a mailbox accepts messages and whether you have permission to contact it require separate evidence.
What should you do when consent is missing?
Record the permission as unknown and route it for review before using the record for outreach. Keep the original consent value so the owner can trace the gap.
How do you check a cleaned CSV before importing it?
Reopen it using the intended field mapping and confirm the counts, required fields, quoting, and stop flags. Test ambiguous and rejected examples alongside straightforward accepted rows.
What belongs in a lead list change log?
Record the source row ID, changed field, original value, new value, and rule. Keep duplicate dispositions and rejected records traceable to the same source IDs.
More jobs in Leads
Clean a lead list, on your own Mac.Free app. Runs on your Mac with the Claude or Codex plan you already have. Your files stay on your computer.
Download for Mac