Motor Insurance Portfolio

Visual Analytics for Data Discovery

Wu Jiayan

2026-05-10

Contents

1. Dataset Overview — Portfolio scope, structure and key KPIs
2. Univariate: Premium & Claims — Distribution shape and skewness
3. Univariate: Categorical Variables — Coverage, bonus score and geography
4. Temporal Trends — Year-on-year premium, claims and loss ratio evolution
5. Bivariate: Driver Age vs Risk — Frequency-severity trade-off
6. Bivariate: Bonus Score vs Loss Ratio — Claims history as a risk discriminator
7. Bivariate: Coverage Type vs Profitability — Product line analysis
8. Bivariate: Vehicle Value — U-shaped loss ratio and rising frequency
9. Bivariate: Urban vs Rural — Geographic risk divergence
10. Correlation Landscape — Premium structure and predictive power
11. Key Insights & Recommendations

This presentation applies visual analytics to a real-world Spanish motor insurance portfolio dataset from Mendeley Data, covering 354,140 policy-year observations across 2022–2024. The investigation follows a structured pipeline: univariate exploration of distributions and compositions, followed by bivariate analysis testing factors influencing profitability (loss ratio, profit per policy) and volatility (claim frequency, claim severity). All visuals are produced with ggplot2 and its extensions — ggridges, ggcorrplot, patchwork — within the tidyverse ecosystem. The contents page outlines the eleven analytical slides that follow.

Dataset Overview

The dataset contains 354,140 policy-year records representing 189,576 unique policyholders. Total written premium is €105.1M with €73.9M incurred, yielding an overall loss ratio of 70.3%. The claim frequency of 14.3% means roughly 1 in 7 policies generates at least one claim per year. The portfolio grew substantially each year at a CAGR of ~65%, driven by new business and renewals. Comprehensive with excess (COMP_E) dominates the product mix at 58% of policies. The four variable groups in the dataset are: Policy and Insured Characteristics, Driver and Vehicle Characteristics, Premiums (eight components), and Exposure/Claims/Incurred outcomes.

Univariate: Premium & Claims Distributions

Total annual premiums are strongly right-skewed with a median of €258, indicating most policies are modestly priced while a long upper tail exists from high-value comprehensive covers. Incurred losses among claimants show even more extreme right skewness — a hallmark of insurance loss distributions consistent with log-normal or Pareto models. Both distributions are truncated at the 99.5th percentile for visual clarity. The right skew in both variables has important actuarial implications: mean-based pricing overestimates the typical policy cost, while reserve calculations must account for the heavy upper tail of large claims.

Univariate: Categorical Variables

Comprehensive with excess (COMP_E) is the most common product at 57.8%, followed by Third Party+Multi-Cover (CC) at 57.8% and Third Party+Glass (TPG) at 10.3%. The bonus score distribution is heavily skewed toward “Good (Favourable History)” at 96.5% — a positive portfolio quality signal, though it limits the variable’s discriminatory power for the majority. Only 3.5% hold Neutral or Bad scores. The geographic split shows urban policies dominate across all municipality types. Inland and coastal distinctions matter for territorial pricing; island policies warrant separate analysis given their distinct risk environment compared to mainland Spain.

Temporal Trends

Premium written grew at a CAGR of approximately 65% over 2022–2024, reflecting aggressive portfolio expansion. The loss ratio was stable at approximately 66% in both 2022 and 2023, then spiked sharply to 74.7% in 2024 — a 9 percentage-point single-year deterioration. This sudden spike, rather than a gradual drift, is more consistent with a large claims event, reserve strengthening, or a structural shift in the book than with general claims inflation. The pattern strongly contrasts with a typical inflation-driven gradual deterioration and warrants urgent root cause investigation before any pricing action is taken in 2025.

Bivariate: Driver Age vs Risk

Driver age reveals a critical frequency-severity trade-off. Claim frequency declines monotonically from 20.5% for the youngest cohort (18–25) to 12.1% for the oldest (66+) — a 70% reduction. However, severity moves in the opposite direction: the 66+ cohort generates the highest average incurred cost per claim at €1,982, versus the lowest at €1,658 for the 26–35 group. This means older drivers file claims less often but generate more expensive claims when they do. The ridge plot (in the technical report) confirms younger drivers (18–25) are charged substantially higher median premiums (~€474) versus ~€252–€262 for other age groups. A two-part GLM (frequency × severity) is strongly recommended to correctly price both dimensions.

Bivariate: Bonus Score vs Loss Ratio

The bonus score is a meaningful risk discriminator but reveals a surprising finding: the “Neutral” category (81.4% loss ratio) is actually worse than “Bad (Poor History)” (79.0%), both far exceeding “Good” (69.7%). This suggests Neutral policyholders may represent a deteriorating segment in transition toward Bad rather than a genuinely moderate-risk group. The claim frequency gap is substantial — Bad at 21.9% versus Good at 14.2%. The ridge plot confirms Bad/Neutral policyholders pay higher premiums on average (mean €589 and €416 versus €290 for Good), but the persistent loss ratio gap indicates current loadings are still insufficient. A 12–15% additional premium loading would be required to bring non-Good scores to portfolio-average profitability.

Bivariate: Coverage Type vs Profitability

Coverage type is a key driver of portfolio profitability. Third Party achieves the best loss ratio at 54.3% but represents only 1.6% of policies. The standout concern is Comprehensive (No Excess) — COMP_N — at 93.9%, far above the portfolio average of 70.3%. This is actuarially expected: without any excess, policyholders bear no cost-sharing incentive, driving small claim frequency up. Third Party+Multi-Cover (CC) performs better than expected at 63.4%, likely due to selective underwriting. Comprehensive with excess (COMP_E) at 73.8% is close to the portfolio average, confirming the excess partially compensates for broader coverage. The violin plots show COMP_N and COMP_E carry the widest downside profit distributions — the greatest per-policy volatility sources in the book.

Bivariate: Vehicle Value

Vehicle value reveals a nuanced pattern that contradicts simple assumptions. The loss ratio is U-shaped across quintiles — Q1: 72.3%, Q2: 69.3%, Q3: 69.0%, Q4: 67.2%, Q5: 73.3% — meaning mid-value vehicles (Q3–Q4) are actually the most profitable, not the cheapest ones. This is likely because low-value vehicles (Q1) have high claim frequency relative to their low premium base. Claim frequency, however, does rise monotonically from 10.9% (Q1) to 17.9% (Q5), confirming higher-value vehicles are claimed more often. The vehicle value loading in theft and property damage premiums (correlation r ≈ 0.33–0.41) only partially compensates for this elevated frequency at the top end.

Bivariate: Urban vs Rural

Urban policyholders have a higher overall loss ratio (71.4%) versus rural (68.9%) — a gap of 2.5 percentage points. Crucially, the year-by-year breakdown reveals this gap only emerged clearly in 2024: in 2022, urban (61.8%) was actually more profitable than rural (70.5%), with positions normalising in 2023 before diverging sharply in 2024. The age-stratified analysis confirms the urban disadvantage persists across all age groups, with young urban drivers (18–25) showing the highest claim frequency at 22.2% versus 18.9% for young rural drivers. This urban-age interaction supports a two-dimensional rating factor structure rather than treating geography and age as independent additive factors.

Correlation Landscape

The correlation matrix reveals the internal structure of the premium model. Premium components correlate moderately to strongly with total premium: property damage r = 0.75, liability r = 0.68, glass r = 0.52, theft r = 0.49. Most importantly, total incurred shows a very weak correlation with premium (r ≈ 0.07) — far weaker than commonly assumed — confirming that individual policy outcomes are highly stochastic and largely unpredictable. Vehicle value correlates positively with premium (r ≈ 0.32) and theft/property premiums (r ≈ 0.33–0.41), validating the rating model. Driver age has a very weak negative correlation with claim counts (r ≈ −0.03), indicating age alone explains minimal variance — multivariate modelling is essential.

Key Insights & Recommendations

Critical Findings

🔴 2024 loss ratio spike — 66% → 75% in one year; root cause investigation needed before pricing action

🔴 COMP_N at 93.9% LR — no excess = no cost-sharing; most urgent pricing problem

🟠 Neutral bonus score (81.4%) worse than Bad (79.0%) — counter-intuitive; currently under-priced

🟠 Severity rises with age — 66+ most expensive at €1,982/claim despite lowest frequency

🟡 Vehicle value is U-shaped — mid-value most profitable; frequency rises monotonically Q1→Q5

Recommended Actions

Priority	Action
🔴 High	Investigate 2024 LR spike — root cause first
🔴 High	Introduce mandatory minimum excess for COMP_N
🔴 High	Increase bonus-malus loading for Neutral & Bad
🟠 Medium	Age-specific severity loading for 66+ drivers
🟠 Medium	Strengthen urban territorial rating factors
🟡 Low	Monitor vehicle value frequency trend Q1→Q5

Premium–incurred r ≈ 0.07: individual outcomes are largely unpredictable; portfolio diversification is essential.

This slide synthesises the nine key findings into a prioritised action plan. Three high-priority actions stand out: the 2024 loss ratio spike (9 pp in one year) needs root cause analysis before any rate changes, COMP_N at 93.9% requires structural reform via a mandatory excess, and the counter-intuitive Neutral-worse-than-Bad bonus score finding suggests the current segmentation needs redesigning. Medium-priority actions address the frequency-severity age trade-off and worsening urban-rural divergence. The very low premium-incurred correlation (r ≈ 0.07) is a sobering reminder that visual analytics identifies patterns at the portfolio level — individual policy prediction requires multivariate GLM or machine learning approaches beyond the scope of this investigation.