A data mapping template for data migration is a table with one row for every field in the target system, saying which source field feeds it, how the value is transformed, which code list translates it, how confident the mapping is, what evidence supports it, and who approved it and when. For an insurance agency system after an acquisition, the rows run across clients, policies, coverages, carriers, producers and activities, and the hard rows are the ones where the seller's data means something private: a status code with two meanings, a carrier code shared by two companies, a free-text field that holds a declined coverage. The template's job is to make each of those a recorded decision instead of a guess. Botminds' Agency Acquisition Data Migration builds the same map as a canvas: a field and code mapper proposes every row with its confidence and sample values checked against declarations pages, approves on its own only at or above the threshold you set, and leaves the doubtful rows for your analyst with a drafted rule.
Copy the columns below into a spreadsheet if that is where your team works. The example rows come from the solution's fictional sample acquisition, in which a buyer, Calder & Pike, maps Bramblewood Insurance Agency's 146 fields and 1,094 code values onto its own system.
The data mapping template: twelve columns for an agency system migration
| Column | What goes in it | Why it is there |
|---|---|---|
| Target field | Entity and field in the buyer's system, such as Policy · Policy status | One row per target field; nothing in the target is left unexplained |
| Target type and list | Text, date, money, person, or the code list it must come from | Tells you which rows need a code map |
| Source table and field | Where the value comes from in the seller's export, such as POLICY.STATUS | The trail back to the original data |
| Sample values | Three to five real values, including an odd one | Most mapping errors show up in the samples |
| Transformation rule | Formatting, splitting, joining or a conditional rule in plain words | A rule a second person could apply the same way |
| Code map | The code table this row uses, with its own row per value | Codes are mapped value by value, not field by field |
| Confidence | A percentage, or High / Doubtful if mapped by hand | Decides who has to look at the row |
| Evidence | Counts and documents behind the rule, such as declarations pages | Lets a reviewer check the decision without redoing it |
| Status | Mapped, doubtful, or not carried | "Not carried" is a decision, not a blank |
| Decided by and date | The person who approved the row | Accountability, and the first question an auditor asks |
| Rule version | A number that changes when the rule changes | Test loads can be compared rule version by rule version |
| Archive location | Where the source value lives if it is not carried | Retention duties outlive the mapping |
What is a data mapping template?
A data mapping template is the agreed record of how each value in the new system will be produced from the old one. It is sometimes called a source-to-target map, a field map or a data mapping document, and it does three jobs at once: it is the specification the load is built from, the checklist the test load is checked against, and the evidence that someone decided each translation on purpose.
The third job is the one a bare two-column template misses. A spreadsheet with a target column and a source column is a specification; it becomes evidence only when each row also says how sure the mapper was, what the decision rested on and who made it. After an acquisition that matters, because the person who understood the seller's data, often one of the seller's staff, may not be there when the first renewal goes wrong.
What columns should a data mapping document have?
Twelve, in three groups: where the value goes and comes from (target field, target type, source field, sample values), how it is translated (transformation rule, code map), and why anyone should believe it (confidence, evidence, status, decided by, rule version, archive location). The template above lists them with what each holds.
Two columns earn their place only on agency migrations. Sample values catch the problems that field names hide: a field called FEIN that holds nine digits without a hyphen, or an address field that holds a PO box on one record and a street on the next. Archive location answers a question that arrives months later: where is the value we decided not to carry? An agency's records of policies transacted have their own retention clock, five years after policy expiration in Florida (Fla. Stat. §626.748), and a mapping decision does not stop it. The record retention guide for acquired agencies sets out the clocks.
The template filled in for an agency system: clients, policies, servicing
Filled in, most rows are dull and a few are not. The example below shows real rows from the sample acquisition, chosen to include the kinds that go wrong.
Example rows from a filled agency mapping template
| Target field | Source field and sample | Rule | Confidence and status |
|---|---|---|---|
| Client · Client name | CUST_NAME: ACME ROOFING & SHEET METAL LLC | Copy; keep legal suffix | Confident · mapped |
| Client · Tax ID | FEIN: 482917305 | Format as 48-2917305 | Confident · mapped |
| Client · Doing business as | CUST_DBA: ACME ROOFING | Copy; never used to merge on its own | Confident · mapped |
| Policy · Writing carrier | CO_CODE: CWC01 | Via the carrier code map to the company | Confident · mapped |
| Policy · Policy type | POL_TYPE: PKG-X | Waiting on a rule | 71% · doubtful |
| Policy · Policy status | STATUS: R | Renewed when the policy number carries over, otherwise Rewritten | 61% · decided by a person |
| Policy · Annual premium | PREM_ANN: 38420.00 | Money, two decimals; tie out to the cent | Confident · mapped |
| Servicing · Account manager | CSR_INIT: HB | Initials to a person; two staff shared some initials | 68% · doubtful |
| Servicing · Coverage declined | NOTES_1: "Disc. umb w/ Gary ... Decl. due to cost" | Read declinations out of free-text notes into a log | 74% · doubtful |
| (none) | XMAS_LIST: Y | Not carried; kept in the archive | Not carried · a person's decision |
Three of those rows deserve a second look. The tax ID row is a formatting rule, but it is also the key the client matcher will use to find duplicates, so a wrong format silently splits one insured into two. The status row is a conditional rule written in words a second analyst could apply the same way. And the notes row is the template reaching beyond fields: some of the most important data in an agency system is a sentence typed by a person, and mapping it means reading it.
The template as a canvas: every target field drawn to its source
How do you map codes between two systems?
Value by value, in a code map of its own, with the number of records that use each value and the evidence for each translation. A field-level row that says "line of business → line of business" hides the real work, which is deciding what each of the seller's line codes means in the buyer's list.
Anchor what you can to identifiers outside both systems. For carriers that means the NAIC company code; for carrier families it means the NAIC group code, "a unique three to five digit number assigned by the NAIC to identify those companies that are part of a larger group of insurance companies" (NAIC glossary). A seller's carrier list often names the group where the policy was written by one company in it, and appointments, commissions and downloads all depend on the company. In the sample, one seller carrier code covered Ridgeline Indemnity on 119 declarations pages and Ridgeline Mutual on 8; the code map row split it by what each declarations page said.
Lines of business have no single external list that every agency system uses, which is why the code map needs its own evidence column. The NAIC's glossary defines a line of business only as a "classification of business written by insurers", and each agency system classifies differently. In the sample, the seller's INMRC code on 342 policies covered both contractors equipment and builders risk, so no single translation was right; the rule has to decide policy by policy, and the declarations page is where each policy's coverage is named.
A code map: one row per source value
| Source value | Policies | Target value | Confidence | Evidence |
|---|---|---|---|---|
| CPKG | 1,018 | Commercial package | Confident | Declarations pages agree |
| INMRC | 342 | Contractors equipment or builders risk | 63% | Decide policy by policy; the declarations page names the coverage |
| RDG2 | 127 | Ridgeline Indemnity (119) or Ridgeline Mutual (8) | 64% | Insurer named on each declarations page |
| R (status) | 1,286 | Renewed (1,212) or Rewritten (74) | 61%, decided | Prior-term policy number and prior carrier |
| P (status) | 58 | Quoted, not bound | 81% | Mapper's proposal; confirm against the quotes on file |
How do you handle fields with no match?
Decide each one, and record the decision. A field with no match falls into one of three cases: the target has a field the source lacks, the source has a field the target lacks, or both have the field but the values do not translate. Each needs a different answer, and none of them should be left blank.
When the target field has no source, decide whether to default it, derive it or collect it, and write which in the rule column; a defaulted renewal date or carrier is worse than an empty one, because nobody looks at it again. When the source field has no target, mark it not carried, give the reason and point to where the value is archived; in the sample, four fields were proposed as not carried and each was a person's call. When the values do not translate, the row is doubtful by definition: it goes to a person with the counts and documents, as the status, carrier and line codes did in the sample.
One class of unmatched data deserves special care: records a regulator expects the agency to be able to produce. New York, for example, requires a licensee who charges a fee for reviewing or advising on a policy to keep the signed fee memorandum for at least three years after the services are performed (N.Y. Ins. Law §2119). If the buyer's system has no field for it, the attachment still has to travel, linked to its client.
How the sample's mapping rows ended the first pass
146 fields
- Confident, 131 · 90%
- Doubtful, 11 · 7%
- Not carried, 4 · 3%
1,094 code values
- Confident, 1,062 · 97%
- Doubtful, 32 · 3%
Recording the doubtful rows for a person
A doubtful row needs four things beside the usual columns: the competing interpretations, the count of records behind each, the documents that decide between them, and a proposed rule. With those, an analyst can decide in minutes; without them, the analyst has to redo the mapper's research.
The sample's status row shows the format. The question: status "R" is on 1,286 policies; renewed or rewritten? The evidence: 1,212 policies kept their number from the prior term; 74 had a new number and a different prior carrier; the seller's previous system version had used one code for both. The proposed rule: renewed when the policy number carries over, otherwise rewritten. The options: approve the rule, map everything as renewed, map everything as rewritten, or ask the seller's account manager. The person approved the rule, the template recorded who and when, and the rule applied to every record.
What every doubtful mapping row should carry
- The question in one line
What has to be decided, in the words of the business, not the field name.
- Each interpretation with its count
How many records each reading would affect.
- The documents that decide it
Declarations pages, prior-term records, the seller's own manuals.
- A proposed rule
Written so another analyst would apply it the same way.
- Who can answer
The seller's staff member who knows the code, if the documents do not settle it.
- The decision, who and when
With a new rule version, so the next test load shows what changed.
Who approves a data mapping?
The data migration analyst approves individual rows; the integration manager accepts the map as a whole. In the sample integration, rows at or above 95% confidence need no person, rows between 90% and 94% can be approved in one batch, and everything below goes to the analyst one row at a time, with the integration manager able to accept a still-doubtful row at sign-off only with a written reason.
The thresholds are settings, and choosing them is itself an approval. Lowering the auto-approve level saves review time and moves risk into the load; raising it does the reverse. Whoever sets them should be the person who will sign the day-one load. The six-week integration plan puts the signed field map at the end of week one.
What is a source to target mapping, and where does a spreadsheet stop?
A source-to-target mapping is the same thing as a data mapping template: each target field with the source that feeds it. A spreadsheet is a fine place to start one and a poor place to finish it, for three reasons: it does not version its rules, it cannot show the records each rule affects, and it cannot tell you which test load used which version of the map.
Those gaps matter most in the last two weeks. A rule changed on a Thursday, applied to a test load on Friday and changed again on Monday leaves three versions of the truth unless something records them. The solution saves every rule change as a new rule version with who and when, which is what lets the integration manager compare test loads and see that a count moved because a rule moved.
How do you test a mapping before the load?
Run sample records through the map and compare the result with their source documents, then run a full test load and tie it out. Sample records show whether the rules do what they say; the tie-out shows whether anything was lost or doubled.
Declarations pages are the natural test source, because they are what the carrier issued. Federal flood insurance rules make the same point for transfers between insurers: the receiving insurer collects the prior insurer's declarations page and "must validate the information when there is a discrepancy between the building descriptions in the policy underwriting file and the prior insurer's declarations page" (FEMA, NFIP Flood Insurance Manual, October 2025). In the sample, the declarations reconciler compared every policy with its page and found 142 that disagreed, each difference cited to its page for a person to decide before the day-one load. Policy Servicing's data migration plan template covers the test-load cycle for carrier conversions.
One approved rule turns a dashed line solid and updates every record
Questions people ask
Is there a free data mapping template in Excel?
The twelve columns on this page are the template; paste them into a spreadsheet as headers and add one row per target field, with a separate sheet for each code map. Add a rule version column from the first day, because a spreadsheet will not track changes for you.
How do you document a data migration?
With four records: the mapping template, the code maps, the decisions on doubtful rows with who and when, and the tie-out of each test load against the export. Together they let someone who was not there reconstruct why each value in the new system is what it is.
How many fields does an agency system migration involve?
It depends on the system and the modules in use. The solution's fictional sample export had 41 tables and 146 fields that mattered on the buyer's side, plus 1,094 code values; code values, not fields, are usually where the hours go.
Should the seller's staff review the mapping?
Yes, for the doubtful rows. The seller's account managers often know what a code meant in practice, which no document records. Ask them specific questions, with the counts and examples in front of them, rather than handing them the whole map.
What happens to the mapping after go-live?
Keep it with the integration file. It explains every converted value for as long as the converted records are kept, and it is the starting point for the next acquisition, whose seller will have different codes but often the same kinds of problems.