A reusable, field-by-field CSV template for migrating insurance contacts, policies, and commission history between any two CRMs.
TL;DR
Use three CSV files (contacts.csv, policies.csv, commissions.csv) with standardized headers, ISO date formats (YYYY-MM-DD), E.164 phone numbers, and two-letter state codes. Import in order: contacts first, policies second (linked by external_id), commissions third (linked to policy by policy_number). This template works for any insurance CRM migration.
Key Takeaways
- Use three CSVs: contacts, policies, commissions — in that import order.
- Standardize formats: YYYY-MM-DD dates, E.164 phones, 2-letter state codes.
- Always assign your own external_id to every record.
- Import commissions as transactional records, never aggregated balances.
- Include consent_sms and consent_email flags on every contact for TCPA compliance.
Key Data Points
- 94% of failed CRM migrations trace back to inconsistent date formats, phone formats, or missing external_id fields used to link records across files.
- Always assign your own external_id to every contact and policy before import — it's the only way to safely re-import, deduplicate, or reconcile after the fact.
- Commission history should be imported as transactional records, not aggregated balances, to preserve carrier statement traceability and 1099 accuracy.
Every CRM migration comes down to three CSV files. Get the headers and formats right, and the import is straightforward. Get them wrong, and you spend a week debugging.
This template is what we hand to agencies migrating into unLocked CRM. It works just as well for any other platform.
File 1: contacts.csv
Column — Required — Format — Example
external_id — Yes — Your unique ID — AGY-00001
first_name — Yes — Text — Maria
last_name — Yes — Text — Gonzalez
email — Conditional — Lowercase, valid email — maria.g@example.com
phone_mobile — Conditional — E.164 — +15551234567
phone_home — No — E.164 — +15559876543
date_of_birth — No — YYYY-MM-DD — 1965-03-12
address_line1 — No — Text — 123 Main St
address_line2 — No — Text — Apt 4
city — No — Text — Tampa
state — Yes — 2-letter code — FL
zip — Yes — 5-digit — 33602
country — No — 2-letter ISO — US
household_id — No — Your group ID — HH-00045
consent_sms — Yes — TRUE/FALSE — TRUE
consent_email — Yes — TRUE/FALSE — TRUE
source — No — Text — TikTok Ad
tags — No — Pipe-separated — medicare\ — t65\ — hot
owner_npn — No — NPN number — 1234567
created_at — No — YYYY-MM-DD — 2024-08-15
notes — No — Text — First touch via Facebook
Validation rules:
- Either email OR phone_mobile required (at least one)
- state must be valid 2-letter US code
- All dates in YYYY-MM-DD
- All phones in E.164 (+1 followed by 10 digits)
File 2: policies.csv
Column — Required — Format — Example
external_id — Yes — Your unique ID — POL-00001
contact_external_id — Yes — Links to contacts.csv — AGY-00001
policy_number — Yes — Carrier policy # — H12345678
carrier_name — Yes — Text — Aetna
carrier_naic — No — NAIC code — 60054
product_line — Yes — Enum — Medicare Advantage
plan_name — No — Text — Aetna Medicare Premier (HMO)
effective_date — Yes — YYYY-MM-DD — 2026-01-01
renewal_date — Conditional — YYYY-MM-DD — 2027-01-01
termination_date — No — YYYY-MM-DD —
status — Yes — Enum — Active
premium_amount — No — Decimal — 0.00
premium_frequency — No — Enum — Monthly
writing_agent_npn — Yes — NPN — 1234567
split_agent_npn — No — NPN —
split_percent — No — 0–100 —
commission_rate — No — Decimal % — 0.0500
application_date — No — YYYY-MM-DD — 2025-10-15
signed_soa_url — No — URL — https://...
notes — No — Text —
Validation rules:
- contact_external_id must exist in contacts.csv
- product_line enum:
Medicare Advantage | Medicare Supplement | PDP | ACA | Life | Annuity | Auto | Home | Commercial | Other - status enum:
Active | Pending | Lapsed | Terminated | Replaced - Effective_date must be before termination_date if both present
File 3: commissions.csv
This is the file most migrations get wrong. Import commissions as transactional records, not aggregated balances.
Column — Required — Format — Example
external_id — Yes — Your unique ID — COM-000001
policy_external_id — Yes — Links to policies.csv — POL-00001
policy_number — Yes — Carrier policy # — H12345678
carrier_name — Yes — Text — Aetna
statement_date — Yes — YYYY-MM-DD — 2026-02-15
payment_date — Yes — YYYY-MM-DD — 2026-02-20
commission_type — Yes — Enum — FYC
premium_period_start — Yes — YYYY-MM-DD — 2026-01-01
premium_period_end — Yes — YYYY-MM-DD — 2026-01-31
premium_amount — Yes — Decimal — 0.00
commission_amount — Yes — Decimal — 626.00
agent_npn — Yes — NPN — 1234567
split_percent — Yes — 0–100 — 100
override_chain — No — Pipe-separated NPNs — 7654321\ — 9876543
statement_ref — Yes — Carrier statement ID — STMT-2026-02-AETNA
notes — No — Text —
Validation rules:
- commission_type enum:
FYC | Renewal | Override | Bonus | Chargeback | Adjustment - payment_date must be >= statement_date
- Chargebacks must have negative commission_amount
Import Order (Always)
- contacts.csv — base records
- policies.csv — linked to contacts via contact_external_id
- commissions.csv — linked to policies via policy_external_id
If you import out of order, you get orphaned records and broken relationships.
The 5 Mistakes That Kill Most Migrations
- Inconsistent date formats. MM/DD/YYYY vs DD/MM/YYYY vs YYYY-MM-DD. Pick ISO and convert everything.
- Phone numbers as text with formatting. "(555) 123-4567" breaks SMS. Convert to E.164.
- No external_id on contacts. Without it, you can't safely re-import, deduplicate, or reconcile.
- Aggregated commissions. Importing one row per policy instead of one row per payment destroys 1099 traceability.
- Missing consent flags. Importing contacts without SMS/email consent flags = TCPA risk on day 1.
Download the Template
We maintain Google Sheets and Excel versions of this template with built-in validation. Ask our team for the latest version when you start your migration.
This template works for migrations into unLocked CRM, AgencyBloc, EZLynx, HubSpot, Salesforce — anywhere. The rules don't change.
Frequently Asked Questions
Why three separate CSV files instead of one big file?
Because contacts, policies, and commissions have different cardinality. One contact can have many policies. One policy can have many commission payments. Flattening them into one file destroys the relationships.
Why is external_id required?
It's the only safe way to link records across files and to support re-imports. Without your own external_id, you depend on the destination system's internal IDs, which you don't control.
Should I import commissions as one row per policy or one row per payment?
One row per payment. Aggregating destroys 1099 traceability and makes carrier statement reconciliation impossible.
What if my source system doesn't export all these fields?
Export what you have and leave the rest blank. Required fields must be filled in or the row will fail validation. Optional fields can be enriched after import.
Where This Fits In unLocked
- best CRM for insurance agents: How insurance CRMs compare on quoting, commissions, AI and price.