How do I set up a loss ratio dashboard for monitoring pet insurance retention and claims trends in near real time?

Here’s a practical way to set up a near-real-time loss ratio dashboard for pet insurance retention and claims trends.

1) Define the metrics you want to monitor

For pet insurance, the core dashboard usually needs:

Loss ratio

• Incurred loss ratio = incurred claims / earned premium
• Often also show:
  • Paid loss ratio = paid claims / earned premium
  • Reported loss ratio if you track case reserves and RBNS

Retention

• Policy retention rate = active policies at end of period / active policies at start of period
• Better: track by cohort:
  • Month of policy inception
  • Renewal cohort
  • Channel / product / state / breed / age band

Claims trend metrics

• Claim count
• Claim frequency = claims / exposure
• Average severity = incurred claims / claim count
• Average premium
• Average time to report
• Average time to pay
• Open claim inventory
• New claims, closed claims, paid claims, reserve changes

Supporting operational indicators

• Policy count in force
• New business
• Lapse/cancel rate
• Renewal rate
• Premium written vs earned
• Claim approval/denial rate
• Breakdown by product, geography, breed, age, channel, vet network, etc.

---

2) Set up your data sources

To make it near real time, you’ll typically need these systems feeding the dashboard:

Core systems

• Policy admin system
  • policy issuance, renewals, cancellations, status changes, premiums
• Claims system
  • claim submissions, payments, reserves, closures, diagnoses
• Billing/payment system
  • collection status, failed payments, refunds
• Customer/CRM system
  • retention and churn reasons, contact history
• External reference data
  • policy month, calendar, breed, region, sales channel, veterinary category

Important design point

Use a single policy ID and claim ID strategy across systems so you can join data reliably.

---

3) Build a clean data model

A simple model works well:

Fact tables

• fact_policy_daily or fact_policy_event
  • one row per policy per day or per policy event
• fact_claim_event
  • one row per claim event: submitted, adjusted, approved, paid, closed
• fact_premium_daily
  • earned/written premium by date
• fact_reserve_daily
  • outstanding reserves by claim and date

Dimension tables

• policyholder
• pet
• product
• geography
• breed
• age band
• channel
• calendar

Why this matters

Near-real-time dashboards often fail because they try to calculate everything directly from raw transactional tables. A clean star schema makes calculations faster and more trustworthy.

---

4) Decide the refresh strategy

For “near real time,” you usually don’t need second-by-second updates. A good target is:

• Every 15 minutes for claims and policy events
• Hourly if systems are slower
• Daily for financial close quality metrics like earned premium and ultimate loss ratio

Best practice

Use a hybrid refresh approach:

• Operational metrics refresh intraday
• Financial reporting metrics refresh daily
• Clearly label the freshness timestamp on the dashboard

---

5) Calculate the metrics correctly

This part is critical.

Loss ratio calculation

Use:

• Earned premium, not written premium, for the denominator
• Incurred claims = paid claims + case reserves - salvage/subrogation recoveries, if applicable

Formula:

• Loss Ratio = Incurred Claims / Earned Premium

Retention calculation

If you want a simple retention view:

• Retention = retained policies / policies up for renewal

For a more meaningful view:

• cohort-based retention by policy issue month
• renewal retention by renewal month

Claims trend calculations

• Claim frequency: claim count / exposure
• Severity: incurred claims / claim count
• Paid-to-incurred ratio: paid claims / incurred claims
• Reserve development: compare current reserves to prior periods

---

6) Create dashboard views that answer business questions

A good dashboard should have 3 layers:

A. Executive summary page

Show:

• current loss ratio
• month-to-date vs last month vs same month last year
• retention rate
• claim frequency
• average severity
• active policies
• open claims
• trend lines with alerts

B. Trend analysis page

Show time series for:

• loss ratio
• retention
• claim count
• paid claims
• incurred claims
• premium
• reserve changes

Add filters for:

• product
• region
• breed
• age band
• channel
• policy tenure
• claim type

C. Drill-down / root cause page

Show breakdowns like:

• loss ratio by breed
• retention by acquisition channel
• claim frequency by age band
• severity by illness vs accident
• new business vs renewal policies
• canceled policies by reason

---

7) Add alerting and anomaly detection

Near real time is most useful when it flags unusual movement.

Set alerts for:

• loss ratio above threshold
• retention drop beyond expected range
• claim count spikes
• severity spikes
• payment backlog increase
• reserve drift
• failed payment increase

Example alert rules

• Loss ratio > 75% for 3 consecutive refreshes
• Retention drops > 5 points week over week
• Claim frequency increases > 15% vs 4-week average

You can start with simple threshold alerts, then add statistical anomaly detection later.

---

8) Choose the right tooling

Typical stack options:

Data ingestion

• Fivetran, Airbyte, Informatica, dbt, custom APIs, Kafka
• CDC tools like Debezium if you need change-data capture

Storage / warehouse

• Snowflake
• BigQuery
• Redshift
• Databricks SQL
• Postgres for smaller setups

Transformation

• dbt
• SQL ELT pipelines
• Spark if volumes are large

BI/dashboard

• Tableau
• Power BI
• Looker
• Mode
• Superset

Orchestration

• Airflow
• Dagster
• Prefect

---

9) Handle data quality and latency explicitly

In insurance dashboards, the biggest issues are stale or incomplete data.

Add checks for:

• duplicate policies/claims
• missing claim dates
• negative premiums or paid amounts
• status mismatches
• delayed posting of claims/payments
• open claims without reserves
• policies counted as active after cancellation

Also display:

• last refresh time
• source system lag
• % of expected records received

---

10) Recommended dashboard layout

A clean layout:

Top row KPI cards

• Loss ratio
• Retention rate
• Claim frequency
• Severity
• Active policies
• Open claims

Middle row

• 12-month trend line for loss ratio
• retention cohort chart
• claims volume trend

Bottom row

• breakdown by product
• breakdown by breed/age/region
• top movers and alerts

---

11) Example metric definitions to standardize

To avoid confusion, publish a metric dictionary:

• Active policy: policy in force at snapshot time, excluding canceled/lapsed policies
• Earned premium: premium recognized over coverage period
• Incurred claims: paid + outstanding case reserves net of recoveries
• Retention: percentage of policies renewed/kept active from a starting cohort
• Claim frequency: claims per 100 policies or per exposure unit

This is especially important if finance, claims, and product teams all use the dashboard.

---

12) A good implementation pattern

A practical architecture:

1. Pull policy and claims events from source systems every 15 minutes
2. Land raw data in a staging layer
3. Transform into conformed tables with dbt/SQL
4. Update daily aggregates and rolling intraday snapshots
5. Feed BI dashboard from curated tables
6. Run data quality checks and alert on anomalies

---

13) What “near real time” should mean in practice

For pet insurance, near real time usually means:

• claims submitted today appear within minutes to an hour
• policy cancellations/renewals update quickly
• financial metrics may lag slightly due to premium recognition logic
• the dashboard shows freshness clearly

If your users expect instant final numbers, set expectations early: operationally near real time, financially controlled and reconciled daily.

---

If you want, I can also give you:

1. a sample dashboard wireframe,
2. a SQL/dbt metric model, or
3. a Power BI / Tableau implementation plan.