The short answer
Migration failures are silent by default. Structural problems — a ragged row, a malformed email — get caught. Value-level problems do not: a dropdown value with no matching option in the target lands empty, with no error. On one field that was 579 of 3,432 records, 17%, and the import reported success throughout.
Two rules do most of the work. Inventory the complete export, never a sample — a 71-row sample showed 59 empty columns where the full file had 26, and a field that read 0% populated was actually 100%. And reconcile every dropdown's value set against the target's options before importing, not after.
01The business problem
An organisation is moving its contact database out of one system and into the CRM. On the face of it this is a mapping exercise: line up the columns, run the import, done by lunchtime.
What it actually has to achieve:
- Completeness — every column that carries real data ends up somewhere, or is dropped by an explicit decision rather than by accident;
- Attribution — each record lands with the right owner, because ownership drives visibility and reporting;
- Consent integrity — marketing opt-in and GDPR flags arrive intact, since getting these wrong has legal consequences rather than merely annoying ones;
- Idempotency — the import can be run twice without producing two of everything, because it will be run twice;
- Provenance — you can still tell, afterwards, where each record came from and when it was originally created.
The export in this build was 91 columns wide and 3,432 records deep. It was structurally clean — no ragged rows, no malformed or blank email addresses, no duplicate source identifiers. Every problem worth writing about was hiding inside otherwise valid data.
02Why the obvious approach fails
Designing the target schema from a sample export
The sample lies, and it lies in both directions. A 71-row sample from the source system showed 59 columns as completely empty. The full 3,432-row export showed only 26 genuinely empty.
One column read 0% populated in the sample and 100% populated — all 3,432 records — in the full file. Build from the sample and that column has no destination at all.
The reason is mundane and generalises: a sample is usually a recent slice, and recent records exercise different fields from historical ones. Address blocks, partner-programme fields, campaign attribution and referral data were all plausibly populated on older records and simply absent from a four-week window.
The same trap has a sharper edge. In the sample, every email address was unique — which suggested email as a natural deduplication key. In the full export there were duplicate email addresses across 10 records. A dedupe strategy chosen from the sample would have silently merged distinct people.
Treating the export as one homogeneous list
It rarely is. This export contained several distinct cohorts of contact — registrations from a content property, product trial signups, marketing prospects, and a handful of one-off enquiries — and the fill pattern followed the cohort, not the contact. Each cohort populated a different subset of columns, and each had its own owner.
The consequence is a design consequence, not a data one: do not build one flat form. Eighteen new fields spread across three cohorts means a single combined form reads roughly 70% empty on every record, whichever cohort it belongs to.
Mapping the columns and pressing import
This is where the silent loss happens, and it deserves its own section.
03The silent failure — dropdown values
Dropdown fields import by option identifier, not by label. A source value with no matching option in the target does not error, does not warn, and does not halt the import. The field lands empty.
Measured on this export, before any correction:
| Field | Would blank | Cause |
|---|---|---|
| Contact status | 579 of 3,432 · 17% | Five values present in the source had no option created at all — including a status carried by 335 records and another by 128. |
| Product tier | 117 of 418 · 28% | Mostly casing variants of options that did exist, plus two genuinely junk numeric values. |
| Team size band | 39 | One missing option, plus spreadsheet date corruption. |
| Product version | 1 | A single casing variant. |
Three distinct causes, three different fixes
- Options that were never created. The obvious case, and the easiest to fix once you have counted the distinct values in the source rather than assuming the set from documentation.
-
Casing variants. The same value arriving as both
Unlimitedandunlimited. These are not new options — creating them would fragment your reporting. Normalise them at import time instead. -
Spreadsheet corruption. Range values such as
2-4and5-10had been silently converted to dates by spreadsheet software somewhere upstream, arriving as4-Feband10-May. This is not the source system's fault and not the CRM's; it is what happens when a CSV passes through a spreadsheet. Detect it by counting distinct values and reading the list with your own eyes.
All three are invisible after the fact. A blank field looks exactly like a field that was legitimately empty in the source.
04Field-level configuration
Of 91 source columns, the outcome was 8 mapped to existing standard fields, 18 new custom fields, and the remainder either genuinely empty or explicitly dropped.
What to drop deliberately
Some populated columns are worthless, and identifying them is part of the job:
- Constants. A column holding the same value on every row is usually the source system's tenant identifier or a type discriminator implied by your mapping. It carries no per-record information.
- Export artefacts. One column was byte-identical to the record identifier wherever it was populated, and empty elsewhere — a duplicate produced by the export, not a field anyone maintained.
- Codes with no lookup table. Numeric codes are meaningless without the legend, and the legend is often not exportable. Better to drop than to import numbers nobody can interpret.
Normalisation the import has to do
| Symptom in the export | Fix before import |
|---|---|
Booleans exported as 1.00 / 0.00 | Convert to true/false |
Integer identifiers exported as 104857.00 | Strip the decimal suffix, or they import as text with a spurious tail |
| Country as lowercase ISO-2 codes | Expand to the names the CRM expects |
| Phone numbers with inconsistent spacing and bracketing | Normalise |
| Dropdown casing variants | Fold to the canonical option — do not create a second option |
Tags
Tags are a multi-value field, so a contact keeps all of its source tags — but the tag vocabulary must exist in the space before the contacts are imported. Three details from this export worth checking in yours: verify the delimiter is genuinely safe (here, no value contained a comma that was not followed by a space); watch for typographic apostrophes, which make two visually identical tags distinct; and expect junk — one meaningless tag appeared on 182 records.
Form membership
Every new field must be placed on the form, not merely created. A field that exists in the schema but is absent from the form is inert in Coevera — see Blueprint 002, where the same constraint silently disables AI and calculated fields.
Because the export is cohort-shaped, section the form by cohort rather than
listing eighteen fields in one block. Note that Coevera form columns must use one
of five valid four-unit splits — [4], [2,2],
[1,1,2], [2,1,1], [1,1,1,1].
05The import sequence
Order matters, because several steps are prerequisites for the next.
- Inventory the full exportEvery column: fill count, distinct count, and the actual distinct values for anything that will become a dropdown. Not the sample — the whole file.
- Partition into cohortsGroup rows by fill pattern. This drives the form design and often reveals that one owner maps to one cohort.
- Decide each column's fate explicitlyStandard field, new custom field, or dropped with a stated reason. A column with no decision is a column that will be lost.
- Reconcile dropdown optionsFor every dropdown, diff the source's distinct values against the target's options. Create what is genuinely missing; normalise casing variants; fix corruption at source.
- Create the tag vocabularyBefore any contact is imported, or tags silently fail to attach.
- Build fields and place them on the formBoth, in the same change.
- Write one real record end to endAn actual row from the export, not synthetic data. Read it back and compare every field. Then delete it.
- Import, then verify by fill rateCompare the post-import fill rate of every field against the source's fill count. A mismatch is the only signal you get.
Step 7 is the one people skip and the one that catches most problems. Synthetic test data is clean by construction; a real row carries the casing variants, the decimal suffixes and the typographic apostrophes.
06Limits & trade-offs
System-managed fields cannot be imported
The record creation timestamp is set by the platform. You cannot import the source system's created date into it, so preserving provenance needs a separate custom date field — decide this up front, because it is not recoverable later without a re-import.
The last-contacted date is likewise CRM-managed and not importable. In this export that column was populated on all 3,432 records, and without a fifteenth custom field it had nowhere to go at all.
URL fields rewrite one domain on write
Values written into a url-typed field have the string
pipelinersales.com replaced with coevera.com at storage
time. Verified in a live space on 2026-09-03 with a paired control: the same
value written simultaneously to a url field and a text field in one
request came back rewritten in the first and verbatim in the second.
It is a substring replacement applied anywhere in the value, including inside
query strings — so a tracking or redirect parameter referencing the old domain
is silently repointed. Scheme, subdomain and path are preserved, and unrelated
domains are untouched. In the original migration this affected 28 of 29 URL
values. If your source data contains such URLs and you need them verbatim, use a
plain text field rather than a url field.
Owner is mandatory, and names are not keys
Every record requires a valid owner, so user accounts must exist in the target before the import runs. Map on the source user's email address, not their display name — in this export one rep's name mapped to two different email addresses, 2,432 records against one and 15 against the other. Mapping on name would have collapsed a real distinction.
Choose the deduplication key deliberately
Import the source system's own record identifier into a dedicated custom field. It is unique in the source, stable across re-exports, and makes a repeat import idempotent. Email is the intuitive choice and is unsafe — this database contained genuine duplicate addresses that a sample did not reveal.
What the export will not tell you
- Absence of a value is not evidence of absence in the source system. Every opt-in status in this export read Subscribed — which almost certainly means the export was filtered to subscribers, not that nobody had unsubscribed. Importing it as though it were the full picture would have silently discarded the unsubscribe list, which is the one mistake in this whole article with legal consequences.
- Genuine data quality problems travel with the data. This export carried records with no first name, no last name, or neither. The migration is not the moment to fix them, but it is the moment to count them.
07Verification
The failure mode is silence, so verification cannot be "did the import report success?" — it did.
- Field count before and after. The Contact entity went from 83 fields to 98. A simple count confirms every intended field was actually created, and catches the one that silently was not.
- Every dropdown option confirmed present, with correct names and sort order — before importing, not after.
- One real record written end to end, using an actual row from the export, read back through both the REST and admin APIs, and compared field by field. This is what surfaced the URL rewrite: every other value persisted exactly, and one did not. The test record was deleted afterwards.
- Post-import fill rate per field, compared against the source fill count. This is the check that catches silent dropdown blanking at scale — a field that should be 3,432 populated and reads 2,853 has lost 579 records, and nothing else will tell you.
- Deduplication key uniqueness re-verified in the target, not assumed from the source.
- Tag attachment spot-checked on contacts that should carry several, since tags fail quietly if the vocabulary was incomplete.
What would signal a regression: a dropdown field whose populated count drops after a re-import; duplicate records appearing on a second run, which means the dedupe key is not being matched; URLs in the target that differ from the source; or a consent flag with a different distribution from the export.