Why this example exists
Anonymous stories can make a workflow sound proven without giving a reader evidence to inspect. This page takes a different approach. It uses a fictional staffing team to demonstrate how a mixed CRM export could be assessed, cleaned, reviewed, and returned. The scenario does not describe a real customer, database, delivery schedule, or business outcome.
The useful part is the sequence. A staffing organization may combine candidate, hiring-manager, client, and prospect records from several systems. Each source can use different title conventions, company names, field definitions, and ownership rules. The cleanup has to preserve those differences long enough to resolve them deliberately.
Scenario assumptions
Assume a staffing team has combined exports after an acquisition. The files contain contacts with overlapping identities, previous employers, several phone types, mixed consent or contact-status fields, and inconsistent account names. Some records are current, some are historical, and some belong in a suppression or archive workflow rather than an outreach audience.
Before processing begins, the team defines the system of record, the fields allowed to change, the fields protected from overwrite, and the reviewers who can approve ambiguous merges. Those decisions belong in a written rule sheet. A tool configuration without that sheet is not a repeatable process.
Intake and preservation
The source exports stay unchanged. Each receives a dated identifier, a row identifier, and a source-system field before any normalization occurs. A working copy carries the transformations while the untouched input remains available for reconciliation. This makes rollback possible and gives reviewers a way to trace each returned value.
The intake review records encoding, delimiters, object type, needed identifiers, null conventions, and obvious field drift. It also checks whether a column changed meaning between systems. A field called status, for example, might describe a candidate in one export and an account in another. Combining those values without a mapping would create a clean-looking but incorrect field.
Normalization before matching
Normalization creates comparison fields without destroying the originals. Names can be trimmed and case-folded, phones can be parsed into country code and national number, domains can be separated from email addresses, and company suffixes can be stored apart from the core name. The displayed values remain available for the final record.
Job-title mapping needs a documented target taxonomy. A creative title can map to a function and seniority while the raw title stays intact. Unknown and hybrid titles should remain reviewable rather than being forced into the nearest category. This protects downstream segmentation from confident but untraceable guesses.
Candidate generation and duplicate review
Exact identifiers generate the safest candidate pairs, but they are not enough. People change employers and email addresses, phone numbers are reformatted, and names have common variants. Candidate generation can therefore use several fields, while the final merge decision applies stricter evidence and conflict rules.
Each proposed pair should carry the fields that caused the match, the conflicting fields, the source dates, and a confidence band. High-confidence pairs can follow an approved automated rule. Ambiguous pairs go to a review queue. Rejected pairs should be retained as negative examples so the same false match is not proposed again during the project.
CRM-native behavior also matters. Review the current Salesforce duplicate-management documentation or HubSpot merge documentation for the destination system before designing survivorship rules. The platform may retain activity history, associations, or property values differently from a flat-file merge.
Field survivorship
A merge rule answers which value survives when records disagree. Recency can be useful for a current employer or title, but it is not a universal rule. A verified business email may outrank a newer unverified address. A user-maintained account owner may outrank an imported blank. A direct suppression or suppression reason should not be erased by a later marketing import.
Write the rule for each material field and include a fallback for ties. Preserve the losing values in an audit artifact when they may matter later. Explain the final golden record field by field and include the supporting evidence beyond the similarity score.
Validation and status handling
Email and phone checks should return statuses, timestamps, and method details rather than a single valid-or-invalid label. Domains with domain-wide acceptance, unknown responses, disconnected numbers, shared lines, and role inboxes require different operational treatment. The project team decides which statuses are eligible for outreach, review, suppression, or retention.
Validation is time-bound. Record when a check ran and avoid presenting the status as permanent. The returned file should separate the observed result from the business rule applied to it, so a future reviewer can change the rule without rerunning the underlying check immediately.
Quality review
Quality assurance samples both changed and unchanged records. Changed rows test whether transformations are correct. Unchanged rows test whether the rules missed eligible records. Reviewers should also inspect boundary cases: common names, shared company domains, records with recent job changes, international phone formats, and accounts with similar registered names.
The team logs disputes by reason, updates a rule only when the correction generalizes, and reruns affected rows. A one-off manual fix can conceal a systematic problem. A documented rule change makes the correction testable and repeatable across the working file.
Delivery artifacts
A useful delivery contains more than an import file. It includes the untouched input reference, the cleaned file, a proposed-merge file, a rejected-or-flagged file, the field map, the survivorship rules, status definitions, and a reconciliation summary. No record should disappear silently.
The destination import is tested on a small reversible batch. Reviewers confirm associations, ownership, activity history, needed fields, and automation behavior before the broader load. After import, counts are reconciled against the approved files and exceptions are recorded.
How to adapt the workflow
Start with a representative sample that includes difficult records as well as clean rows. Agree on acceptance criteria before a vendor or internal team runs the full file. The sample should reveal whether matching, taxonomy, validation, and delivery formats fit the actual CRM and operating rules.
Use the scenario as a checklist while developing a forecast from the submitted records. File condition, object relationships, review capacity, destination behavior, and approval requirements determine the work. A scoped assessment should produce its own measured baseline and plan rather than borrowing a result from another organization.
Questions to resolve before work starts
What is the authoritative source for each field?
Name the system, field, and date rule that wins when sources conflict. If no source is authoritative, define a review rule and preserve the competing values.
Which proposed merges require human approval?
Set confidence bands using the evidence available in your data, then require review for conflicts that could join different people, companies, or activity histories.
How will removed or excluded records be handled?
Return them in a separate file with a reason code and source reference. The owner of the system decides whether to archive, suppress, correct, or retain each category.
What proves that the delivery reconciles?
Use stable row identifiers and counts for inputs, merges, flags, exclusions, and final outputs. Review a sample of each category and retain the dated rules used for the run.
How should the team test the import?
Load a small approved batch into a sandbox or other reversible test environment. Check field mapping, automations, ownership, related records, and rollback steps before approving the production import.