Simulate 1,000,000 realistic insurance policy records and corresponding claims data to analyze:
- Premium revenue patterns across policy tenures
- Claims behaviour and cost distribution
- Profitability indicators using Loss Ratio analysis
- Risk concentration across purchase days and months
- Future liability estimation for active policies
insurance-risk-analytics/
│
├── data/
│ ├── policy_sales_data.csv ← 1M simulated policy records
│ └── claims_data.csv ← 2025 & 2026 claims records
│
├── notebooks/
│ └── insurance_data_simulation.ipynb ← Data generation notebook
│
├── powerbi/
│ └── insurance_dashboard.pbix ← Interactive Power BI dashboard
│
├── report/
│ └── Insurance_BI_Assignment_Report.pdf
│
├── queries/
│ └── analysis_queries.sql ← 10 analytical SQL queries
│
├── presentation/
│ └── Insurance_BI_Presentation.pptx ← Professional presentation
│
└── README.md
| Column | Description |
|---|---|
| Customer_ID | Unique customer identifier (1 to 1,000,000) |
| Vehicle_ID | Unique vehicle identifier (1,000,001 onwards) |
| Policy_Purchase_Date | Evenly distributed across 2024-01-01 to 2024-12-31 |
| Policy_Tenure | 1/2/3/4 years — distribution: 20%, 30%, 40%, 10% |
| Vehicle_Value | Fixed at ₹1,00,000 |
| Premium | Policy_Tenure × ₹100 |
| Policy_Start_Date | Policy_Purchase_Date + 365 days |
| Policy_End_Date | Policy_Start_Date + (Tenure × 365) days |
| Column | Description |
|---|---|
| Claim_ID | Unique claim identifier |
| Vehicle_ID | Foreign key → policy_sales_data |
| Customer_ID | Customer reference |
| Policy_Tenure | Tenure of the claimant's policy |
| Claim_Date | Date claim was filed |
| Claim_Amount | Fixed at ₹10,000 per claim |
| Claim_Year | 2025 or 2026 |
- 1,000,000 policies sold evenly throughout calendar year 2024
- Tenure distribution: 1yr (20%) | 2yr (30%) | 3yr (40%) | 4yr (10%)
- Vehicle value: ₹1,00,000 (fixed)
- Premium calculation: ₹100 × Policy Tenure
- 2025 Claims: 30% of vehicles purchased on 7th, 14th, 21st, 28th file a claim on their Policy Start Date
- 2026 Claims: 10% of 4-year tenure vehicles file a claim between Jan 1 – Feb 28, 2026
- Claim amount: ₹10,000 per claim (10% of vehicle value)
The interactive dashboard contains 3 pages:
- KPI Cards: Total Premium, Total Claims, Loss Ratio, Total Policies
- Bar Chart: Claims by Policy Tenure
- Line Chart: Monthly Claim Trend
- Pie Chart: Policy Tenure Distribution
- Claims by Purchase Month
- Claim Frequency by Tenure
- Claim Cost vs Premium
- Claims Heatmap
- Claim Ratio by Tenure
- Premium Earned vs Remaining
- Future Liability Estimate
Total Premium = SUM(Policy_Sales_Data[Premium])
Total Claims = SUM(Claims_Data[Claim_Amount])
Loss Ratio = DIVIDE([Total Claims], [Total Premium])
Claims 2025 = CALCULATE([Total Claims], YEAR(Claims_Data[Claim_Date]) = 2025)
Claims 2026 = CALCULATE([Total Claims], YEAR(Claims_Data[Claim_Date]) = 2026)
Data Model: policy_sales_data ↔ claims_data joined on Vehicle_ID
| Tool | Purpose |
|---|---|
| Python 3.9 | Dataset simulation |
| Pandas / NumPy | Data manipulation |
| Jupyter Notebook | Data engineering workflow |
| SQL | Analytical queries |
| Power BI Desktop | Interactive dashboard |
All charts are generated from the actual simulated data. Open the GIFs in any browser or GitHub README to see the animations.
| Metric | Value |
|---|---|
| Total Policies | 1,000,000 |
| Total Premium (₹) | 24,01,10,800 |
| Total Claims (₹) | 49,45,80,000 |
| Overall Loss Ratio | 205.98% |
| 2025 Claims | 39,456 claims (₹394.6M) |
| 2026 Claims | 10,002 claims (₹100.0M) |
Note on Loss Ratio: The high loss ratio (>100%) is intentional — it reflects the simulation design where 30% of vehicles on anomaly days file claims worth ₹10,000 against a premium of ₹100–₹400. This is the risk-insight the analysis is meant to expose.
- Finding: Days 7, 14, 21, 28 each generate ~10,100–10,330 claims vs an average of ~1,594 claims on normal days
- Multiplier: 6.3× to 6.5× higher claim frequency on anomaly days
- Business Action: Flag these purchase dates for additional underwriting scrutiny and adjust pricing upward for risk concentration
- 1-year tenure: Loss Ratio = 10,000% (₹100 premium vs potential ₹10,000 claim)
- 4-year tenure: Loss Ratio = 2,500% (₹400 premium vs potential ₹10,000 claim)
- Finding: Longer tenure buffers the loss ratio — a 4-year policy is 4× less exposed than a 1-year policy per-rupee of premium collected
- Business Action: Aggressively promote multi-year tenure products; consider premium recalibration toward ₹2,000–₹5,000 per year
- Day 28 generates 10,330 claims — 6.5× the average of ~1,594
- Days 7, 14, 21 follow closely: 6.3–6.4× above average
- Combined, the four anomaly days account for ~82% of all 2025 claims despite representing only ~13% of purchase days
- 10,002 claims already filed in Jan–Feb 2026 from 4-year policies (₹100.0M exposure)
- Active 2-year (purchased 2024) and 3-year policies will continue generating claims into 2026–2027
- Estimated remaining liability: ₹100M–₹200M based on claim rate trajectory
- Business Action: Increase IBNR reserves; flag 4-year portfolio for enhanced monitoring
| Month | Claims Cost |
|---|---|
| March 2025 | ₹33.58M |
| August 2025 | ₹33.39M |
| May 2025 | ₹33.01M |
Distribution is relatively uniform because the anomaly day pattern repeats every month — reinforcing that the issue is structural (which days people buy), not seasonal.
# 1. Install dependencies
pip install pandas numpy
# 2. Run the notebook to generate CSVs
cd notebooks/
jupyter notebook insurance_data_simulation.ipynb
# 3. Load CSVs into Power BI Desktop
# Home → Get Data → Text/CSV → Select policy_sales_data.csv and claims_data.csv
# Create relationship on Vehicle_ID
# 4. Run SQL queries against your preferred database engineSathvik
Data Analytics Intern project – Zopper
Tools: Python | SQL | Power BI
Date: March 2026