Data Cleaning for Insurance Agency Books of Business, Policy Management Systems, Agency Management Systems, Carrier Portals and Producer Databases
On this page
Why Insurance Agency Data Gets Messy
Insurance agencies have a data problem that is structural, not accidental. The nature of insurance distribution creates multiple data silos because every party in the insurance transaction maintains their own record of the policyholder:
| System | Who maintains it | What it stores | Data quality issues |
|---|---|---|---|
| Agency management system (AMS) | The agency | Master client record: name, address, phone, email, date of birth, driver's licence, policy details, claims history, certificates of insurance, documents, activity notes, producer assignment | The AMS is supposed to be the agency's "single source of truth" but is only as good as the data entered; common issues: duplicate client records (same person entered twice with slight name variations), incomplete contact information (email missing on 30-50% of records), outdated addresses (clients move; policies renew without address updates), inconsistent name formatting ("Bob Smith" vs "Robert Smith" vs "R. Smith") |
| Carrier portals (each carrier has its own) | The carrier (insurer) | Policy records, billing, claims, underwriting data, loss runs, commission statements | An agency with 15 carrier appointments has 15 separate portals, each with its own record of the policyholder; data entered in the carrier portal may differ from the AMS (the carrier may have the insured's name as "Smith, Robert J." while the AMS has "Bob Smith"); policy data in the carrier portal may be more current than the AMS (the carrier processed an endorsement that the agency did not download) |
| Rating / quoting platforms | The agency (via the rater) | Quote data: prospect name, address, vehicle/property information, coverage selections, pricing; quotes may or may not convert to policies | Rater data is often disconnected from the AMS; a prospect who was quoted but did not bind may exist in the rater but not the AMS; a prospect who was quoted and bound may exist in both, as separate records |
| Lead management / CRM | The agency | Prospect records: name, email, phone, lead source, follow-up history, quote status | Some agencies use a separate CRM alongside their AMS; prospect data in the CRM may not flow to the AMS when the prospect becomes a client; the same person may exist as a prospect in the CRM and a client in the AMS |
| Accounting / commission system | The agency | Client billing records, premium receivables, commission data | Commission data is tied to policies, which are tied to clients; discrepancies between the accounting system and AMS can result in misattributed commissions or untracked revenue |
| Downloaded carrier data (IVANS, downloads) | Automated (via data exchange between carrier and AMS) | Policy downloads: new business, renewals, endorsements, cancellations, claims | Download data sometimes creates new client records instead of matching to existing ones (if the name or policy number does not match exactly); this is one of the largest sources of duplicate records in agency management systems |
Common Data Issues by Agency Type
| Agency type | Specific data issues | Impact |
|---|---|---|
| Independent P&C agency (personal lines: auto, home, umbrella) | Highest volume of client records (personal lines agencies may have 3,000-15,000+ clients); highest duplicate rate (same household members entered as separate clients; married couples with two auto policies and a home policy may have 2-4 separate client records); highest rate of missing email addresses (many personal lines clients are older and may not have provided email at policy inception years ago) | Duplicate records make it impossible to see the complete household picture (all policies, total premium, cross-sell opportunities); missing emails prevent digital communication and force expensive paper mail |
| Independent P&C agency (commercial lines: business insurance, workers' comp, commercial auto) | Complex client structures: a business may have multiple policies (general liability, commercial property, commercial auto, workers' comp, umbrella, professional liability) across multiple carriers, with multiple named insureds, additional insureds, and certificate holders; the business owner's personal lines may also be in the agency | Incomplete commercial client records create E&O (errors and omissions) exposure: if the agency does not have a complete picture of the client's coverage, they may miss a gap or fail to recommend necessary coverage; this is a professional liability risk |
| Benefits broker / employee benefits consultant | Client data includes the employer (the client) AND their employees (plan participants); employee census data is sensitive (date of birth, SSN, salary, dependents, medical plan elections); data changes frequently (employees join and leave; open enrollment updates; life events) | Census data quality directly affects quoting accuracy (incorrect census data = incorrect premium quotes); HIPAA considerations for health plan data; outdated census data means former employees receiving benefits communications |
| Managing general agent (MGA) / wholesale broker | MGAs receive submissions from retail agents, not directly from insureds; data includes the retail agent's information AND the insured's information; submissions come from hundreds of retail agents with inconsistent data formatting | Duplicate insured records created by different retail agents submitting the same account; agent data management (tracking which retail agent controls which account) |
Cleaning Workflow
| Step | Action | Details |
|---|---|---|
| 1. Export from all systems | AMS client export (CSV): client ID, name, address, phone, email, date of birth, policy numbers, lines of business, effective dates, premiums, producer, status (active/inactive); carrier portal exports (CSV/XLSX): policy records with insured name, address, policy number, status; CRM/lead system export (CSV): prospect records with name, email, phone, status | Export from every system that holds client or prospect data; the goal is to identify all records across all systems |
| 2. Extract and deduplicate emails | Upload all exported files to Email Extractor to extract and deduplicate email addresses across all systems | The deduplicated email list reveals the true unique contact count; source-tagged results show which system has each client's email and which are missing it; clients with emails in the carrier portal but not the AMS are identified |
| 3. Identify duplicate client records in AMS | Match on: email address (most reliable); last name + address; last name + date of birth; phone number; policy number cross-reference | AMS platforms (Applied Epic, Vertafore AMS360, HawkSoft, EZLynx, QQ Catalyst) have built-in duplicate detection tools; use them first; manual review is required for fuzzy matches (name variations, old vs. new addresses) |
| 4. Merge duplicate records | Merge duplicates preserving: all policies from both records; all activity notes and documents; the most current contact information; all certificates of insurance issued; producer assignment (use the primary producer) | Merging in the AMS ensures the client record shows the complete relationship: all policies, total premium, complete history; this is essential for coverage reviews and cross-sell identification |
| 5. Fill missing email addresses | For clients without email: check carrier portal data (the carrier may have the client's email from their online account or e-billing enrollment); check the agency's own email/inbox for correspondence with the client; request email at the next service interaction (endorsement request, certificate request, renewal review) | Prioritise email collection for: high-premium clients (commercial accounts), clients approaching renewal (email is needed for renewal documentation delivery), clients with expiring certificates (certificate holders need to be notified) |
| 6. Verify email addresses | Run the consolidated email list through a verification service (ZeroBounce, NeverBounce, Clearout) | Results: valid, invalid, catch-all, disposable; update AMS with verification status; do not email invalid addresses (bounces damage the agency's sender reputation and the agency's domain reputation, which may affect transactional email delivery for policy documents and certificates) |
| 7. Standardise data formatting | Standardise: name formatting (First Last, capitalised); address formatting (USPS standard); phone formatting (consistent format); policy number formatting (match carrier's official format) | Standardisation enables accurate duplicate detection in the future; consistent formatting improves search and reporting in the AMS |
AMS-Specific Workflows
| AMS | Duplicate detection | Merge capability | Email tracking | Bulk data management |
|---|---|---|---|---|
| Applied Epic | Built-in duplicate detection on client creation; "Possible Duplicate" flag when new client matches existing on name + address or SSN/FEIN | Merge tool: select primary record; merge secondary into primary; preserves all policies, activities and documents from both records | Email field on client record; activity tracking for email correspondence; integration with email marketing (via Applied Marketing Automation or third-party tools) | Data management tools for bulk updates; import/export via CSV; API for programmatic access |
| Vertafore AMS360 | Duplicate detection during data entry; duplicate search by name, address, phone, SSN/FEIN | Merge functionality; preserves policy and activity history | Email field on customer record; email logging from Outlook integration | Bulk data tools; CSV import/export; Integration Manager for data exchange |
| HawkSoft | Duplicate checking on client creation | Client merge tool | Email field; email marketing integration | CSV export; limited bulk update capabilities compared to larger AMS platforms |
| EZLynx | Built-in duplicate detection; matches on name, address, phone | Merge tool for duplicate resolution | Email field; email marketing tools built in (EZLynx Communication Center) | CSV import/export; bulk data tools |
Metrics
| Metric | Before cleaning | After cleaning | Business impact |
|---|---|---|---|
| Duplicate client rate | 15-25% (average independent agency) | Under 5% | Accurate client count; complete household/business view; reliable revenue per client metrics; accurate book-of-business valuation |
| Email capture rate (% of clients with a valid email on file) | 40-60% (many agencies; particularly personal lines agencies with older client bases) | 65-80% (after systematic email collection and carrier portal data integration) | Higher email capture enables: digital document delivery (cost savings on paper and postage); renewal marketing campaigns; cross-sell campaigns; satisfaction surveys; review requests |
| Cross-sell identification (% of clients with coverage gaps identified) | 10-20% (when client records are duplicated or incomplete, the agency cannot see the full coverage picture) | 40-60% (complete client records reveal cross-sell opportunities: homeowners without umbrella, commercial clients without cyber liability, personal lines clients without life insurance) | Cross-selling to existing clients is the most profitable growth strategy: no acquisition cost; the client relationship already exists; cross-sold clients have higher retention (clients with 3+ policies retain at 95%+ vs. 85% for single-policy clients) |
| Policy retention rate | 85-88% (industry average for P&C agencies) | 90-93% (proactive renewal communication to verified email addresses; complete client records enable personalised renewal reviews) | Each 1% improvement in retention on a $5M premium book preserves $50,000 in annual commission revenue; over 5 years, that compounds significantly |
| Book-of-business valuation impact | Buyers discount the purchase price when data quality is poor (duplicate records inflate the apparent client count; missing emails reduce the value of the client relationships; incomplete records increase perceived E&O risk) | Clean data increases book valuation: accurate client count, complete policy records, verified contact information, documented cross-sell potential | Independent agency books of business typically sell for 1.5-3x annual commission revenue; data quality can affect the multiple by 0.1-0.3x; on a $1M commission book, that is $100,000-$300,000 in purchase price difference |
