Insurance Claims Analytics - P&C Carrier Use Case¶

Executive Summary¶

A mid-market Property & Casualty (P&C) insurance carrier with $3 billion in gross written premium (GWP), approximately 500,000 active policies across four lines of business, and 120,000 claims per year deploys Microsoft Fabric to unify claims intake, fraud detection, loss reserving, and statutory reporting into a single lakehouse platform. The solution replaces fragmented legacy systems (AS/400 policy admin, Guidewire ClaimCenter, standalone fraud vendor, Excel-based reserving) with an end-to-end analytics architecture that delivers ML-driven severity triage at FNOL, graph-based fraud ring detection, automated loss triangle development, and NAIC-compliant statutory data feeds -- all on a single Fabric F64 capacity.

Key Outcomes: - $12M annual fraud savings from network-analysis-based fraud ring detection - 15% improvement in reserve accuracy reducing adverse development - 60% reduction in claims triage time via ML severity prediction at first notice of loss - Automated NAIC statutory reporting eliminating 200+ hours/quarter of manual preparation

Industry Context¶

P&C Insurance Data Landscape¶

Property & Casualty insurance generates massive, interconnected datasets spanning the full policy lifecycle:

Lines of Business¶

Regulatory Environment¶

P&C carriers operate under extensive state and federal regulation:

• NAIC Model Audit Rule (MAR): Requires annual audit of financial statements and internal controls over financial reporting. Adopted in all 50 states. Carriers with >$500M in premium must maintain a formal enterprise risk management (ERM) function and file an Own Risk and Solvency Assessment (ORSA). See NAIC Model Audit Rule.
• State Department of Insurance (DOI): Each state DOI requires quarterly and annual statutory financial statements filed via NAIC Financial Data Repository. Rate filings, market conduct exams, and complaint tracking are state-specific.
• Risk-Based Capital (RBC): NAIC RBC formula determines minimum capital adequacy. Data quality in loss reserves directly impacts RBC ratios -- reserve deficiencies trigger regulatory action levels.
• NAIC Statistical Agent Reporting: Carriers report premium and loss data to statistical agents (ISO, NCCI for workers' comp) for industry-wide ratemaking.
• Fair Claims Settlement Practices: Model Unfair Claims Settlement Practices Act requires timely acknowledgment (15 days), investigation, and payment of claims.

Solution Architecture¶

Data Flow Overview¶

Policy Admin (AS/400)  ──┐
Guidewire ClaimCenter  ──┤
Agent Portal           ──┤──►  Files/landing/insurance/  ──►  Bronze  ──►  Silver  ──►  Gold
SIU Referrals          ──┤                                      │           │           │
Reinsurance Treaties   ──┘                                      │           │           │
                                                                ▼           ▼           ▼
                                                          bronze_ins_*  silver_ins_*  gold_ins_*

Medallion Architecture¶

Bronze Layer: Raw Ingestion¶

Raw data is ingested as-is with _ingested_at timestamp and _source_file metadata. No transformations, no deduplication. Schema is enforced at read time to catch upstream changes early.

Silver Layer: Validated & Enriched¶

Gold Layer: Analytics & Reporting¶

Claims Intake Triage with ML Severity Prediction¶

At FNOL, the system predicts claim severity using features available at intake:

Feature Engineering: - loss_type_encoded: One-hot encoded ISO loss type - report_lag_days: Days between loss and report (longer lag correlates with higher severity) - policy_tenure_months: How long the policy has been active - prior_claims_count: Number of prior claims on the same policy - lob_risk_score: Historical severity by line of business - state_litigation_index: State-level litigation environment score - claimant_age: Age of claimant (for workers' comp and auto BI) - weather_cat_flag: Whether loss date falls in a CAT-declared event

Model: Gradient-boosted classifier (LightGBM) trained on 3 years of closed claims, predicting severity tier (minor / moderate / major / catastrophic). Retrained quarterly. AUC > 0.82 on holdout.

Triage Actions: | Predicted Severity | Action | SLA | |-------------------|--------|-----| | Minor (<\(5K) | Auto-assign to desk adjuster pool | 30-day closure target | | Moderate (\)5K-\(50K) | Assign to experienced adjuster | 60-day investigation | | Major (\)50K-\(250K) | Senior adjuster + supervisor review | 90-day management | | Catastrophic (>\)250K) | Major loss unit + reinsurance notice | Immediate escalation |

Fraud Ring Detection Using Network Analysis¶

Insurance fraud accounts for an estimated $80 billion annually across the U.S. industry (Coalition Against Insurance Fraud). Organized fraud rings -- coordinated groups of claimants, providers, attorneys, and sometimes insiders -- account for a disproportionate share of losses.

Network Construction: - Nodes: Claimants, policyholders, agents, medical providers, attorneys, repair shops, adjusters - Edges: Shared address, shared phone, same-day claims, shared provider, attorney referral patterns, policy-claim links

Detection Signals: 1. Community detection: Louvain algorithm identifies tightly connected clusters 2. Velocity anomalies: Multiple claims from the same network within a short window 3. Provider concentration: Unusually high referral rates to specific providers 4. Address overlap: Multiple claimants sharing the same address across different policies 5. Staged accident patterns: Multiple vehicles, similar damage descriptions, specific intersections

Scoring: Each claim receives a fraud_score (0-100) based on weighted network features. Claims scoring >75 are auto-referred to the Special Investigations Unit (SIU). Historical hit rate on SIU referrals improved from 22% to 58% after network analysis deployment.

Loss Ratio Analytics¶

Loss ratio (incurred losses / earned premium) is the fundamental P&C profitability metric. The gold layer computes loss ratios at multiple grains:

• By LOB: Identify unprofitable lines for re-underwriting
• By State: Geographic profitability for regulatory filings and market strategy
• By Agent: Agent-level loss ratios drive commission tiers and appointment decisions
• By Accident Quarter: Trend analysis for pricing adequacy
• Combined Ratio: Loss ratio + expense ratio for overall underwriting profitability

Reserving Accuracy (IBNR & Case Reserves)¶

Incurred But Not Reported (IBNR) reserves represent the carrier's estimate of claims that have occurred but have not yet been reported. Accurate IBNR estimation is critical for: - Financial statement accuracy (NAIC Model Audit Rule) - Risk-Based Capital adequacy - Reinsurance recoverable calculations - Pricing adequacy assessment

Loss Triangle Development: The gold layer builds standard actuarial loss development triangles:

                Development Quarter
Accident Qtr    1       2       3       4       5       ...     Ultimate
2024-Q1         $45M    $52M    $55M    $56M    $56.2M          $56.5M
2024-Q2         $48M    $54M    $57M    $58M
2024-Q3         $43M    $50M    $53M
2024-Q4         $47M    $55M
2025-Q1         $50M

Methods Supported: - Chain-ladder (weighted average development factors) - Bornhuetter-Ferguson (blend of expected and actual) - Cape Cod (weighted B-F variant)

Reserve Monitoring Dashboard: - Actual vs. expected development by accident quarter - Case reserve adequacy by adjuster (identifies over/under-reserving tendencies) - Adverse/favorable development alerts when actual exceeds expected by >10%

Subrogation Recovery Optimization¶

Subrogation -- recovering claim payments from at-fault third parties -- represents a significant revenue opportunity. The system identifies high-recovery-probability claims using:

• Liability indicators: Police reports indicating third-party fault
• Recovery history: Historical recovery rates by loss type and state
• Statute of limitations tracking: Automated alerts before recovery rights expire
• Vendor performance: Recovery rates by subrogation vendor/attorney

Target: Improve subrogation recovery rate from 35% to 50% on eligible claims, representing ~$8M additional annual recoveries.

NAIC Compliance & Statutory Reporting¶

Model Audit Rule Requirements¶

The NAIC Model Audit Rule (Model #205) requires carriers to maintain internal controls over financial reporting, including:

1. Data integrity controls: Reconciliation between policy admin, claims, and general ledger
2. Reserve adequacy: Independent actuarial opinion with data quality attestation
3. IT general controls: Change management, access controls, segregation of duties for data systems
4. Management testing: Annual testing of key controls with documented evidence

Fabric Implementation: - Delta Lake ACID transactions ensure data integrity across medallion layers - Row-level security on claims data enforces need-to-know access - Audit logging via Fabric activity logs provides change trail - Automated reconciliation notebooks compare bronze totals to source system extracts

Risk-Based Capital Data Quality¶

RBC calculations depend on accurate: - Net premium earned by line and state - Loss reserves (case + IBNR) by line and accident year - Loss adjustment expense reserves - Reinsurance recoverables

Data quality issues in any of these inputs can result in: - Understated RBC ratio triggering Company Action Level (200% threshold) - Regulatory Action Level (150%) requiring corrective plan - Authorized Control Level (100%) allowing commissioner to seize the company

The gold layer includes automated data quality checks that flag discrepancies before statutory filing.

State DOI Requirements¶

• Quarterly financial statements: Schedule P (loss development), Schedule F (reinsurance)
• Rate filings: Supporting loss data for rate change justification
• Market conduct: Claims handling timeliness metrics per Unfair Claims Settlement Practices Act
• Complaint tracking: NAIC Complaint Index data feed

Cost Model¶

Fabric F64 Capacity Allocation¶

ROI Analysis¶

Implementation Roadmap¶

Phase 1: Foundation (Weeks 1-4)¶

• Deploy Fabric F64 capacity
• Establish medallion lakehouse architecture
• Ingest policy and claims data into bronze
• Build silver validation layer

Phase 2: Analytics (Weeks 5-8)¶

• Build loss triangle gold tables
• Implement loss ratio analytics by LOB/state/agent
• Create executive claims dashboard
• Deploy severity prediction model (v1)

Phase 3: Advanced (Weeks 9-12)¶

• Fraud network graph construction
• SIU referral automation
• Subrogation scoring model
• NAIC statutory data feed automation

Phase 4: Optimization (Weeks 13-16)¶

• Model retraining pipeline
• Reserve monitoring alerts
• Agent performance scorecards
• Market conduct compliance reporting

Data Pipeline Notebooks¶

Key Metrics & KPIs¶

References¶

• NAIC Model Audit Rule (Model #205)
• NAIC Risk-Based Capital
• NAIC Own Risk and Solvency Assessment (ORSA)
• Coalition Against Insurance Fraud - By the Numbers
• NAIC Unfair Claims Settlement Practices Act (Model #900)
• ISO ClaimSearch
• Microsoft Fabric Documentation
• Delta Lake ACID Transactions

This use case is part of the Supercharge Microsoft Fabric POC -- Commercial Verticals expansion.