Projects

Project 2
Insurance Analytics

Excel Dashboard

Goal

I developed this Excel dashboard to monitor the operational health of an insurance portfolio, focusing on policy lifecycle management and claim approval efficiency. The objective was to provide underwriters and regional managers with a high-level view of policy transitions (Active vs. Lapsed) and demographic distributions to optimize premium growth strategies.

Process

Data Cleaning: Used Power Query to handle massive datasets, standardizing "Marital Status" and "Gender" fields while cleaning null values in policy end dates
Data Modeling: Integrated separate datasets for Claims and Policies into a cohesive data model using Pivot Tables and dynamic named ranges
KPI Engineering:
- Premium Growth Rate %: Developed a year-over-year calculation to track the 9.32% growth trend
- Age Bucket Segmentation: Created custom grouping logic to categorize policyholders by age (18–84) for targeted risk analysis

Key Insights

Policy Instability: There is a concerning volume of Terminated policies (20.89M) compared to Active ones (17.74M), suggesting a high churn rate that outpaces new acquisitions.
Claim Bottlenecks: While 405 claims are approved, a combined 688 claims are either denied or pending, indicating a potential friction point in the documentation or verification process.
Demographic Balance: Policy distribution by gender is remarkably even (approx. 412 per group), suggesting the current product offerings have broad market appeal across genders.

Recommendations

Retention Campaign: Investigate the "Lapsed" and "Terminated" segments. Implementing a 30-day "Save" desk for policies nearing termination could stabilize the portfolio.
Streamline Approvals: The high number of denied/pending claims suggests a need for clearer customer guidelines during the filing stage to reduce administrative rework.

Tableau Dashboard

Goal

I designed this Tableau dashboard to visualize long-term claim trends and customer loyalty patterns. By examining a $251.38M claim history, the objective was to pinpoint when claims spike during the year and how gender demographics influence repeat-customer behavior.

Process

Calculated Fields: Used LOD Expressions to calculate "Repeat Customer" status, identifying the percentage of policyholders who have filed more than one claim
Trend Forecasting: Implemented a Linear Regression Trend Line over the "Claim Amount Trend Over Time" to project future liability growth through 2024
Interactive Slicers: Developed cross-sheet filters for "Policy Type" and "Claim Status" to allow for instant segmentation of the $50,276 average claim value

Key Insights

The 2020 Spike: Claim amounts saw a massive surge in 2020 ($52.25M), likely due to external market factors, before stabilizing into a steady upward trend line.
Loyalty Distribution: Repeat customers are distributed nearly equally among "Other," "Male," and "Female" groups (all ~33%), indicating that claim frequency is not tied to gender.
Approval Parity: The Claim Status distribution is almost perfectly split three ways (Approved, Denied, Pending at ~33% each), confirming a consistent—if perhaps overly rigid—claims processing pipeline.

Recommendations

Address the Trend Line: The regression line indicates a steady increase in claim values. Underwriters should adjust premiums for the 2025 cycle to account for this rising trajectory.
Targeted Loyalty Perks: Since repeat customers are evenly split by gender, a gender-neutral "Loyalty Discount" for claim-free years could help shift the "Pending/Denied" segments into a more profitable, long-term Active status.

Power BI Dashboard

Goal

I built this Power BI dashboard to provide a deep dive into financial risk and claim distribution. By analyzing $251M in total claims, the goal was to identify which states and policy types (Life, Health, Auto) carry the highest financial exposure and how payment methods correlate with claim status.

Process

Data Modeling: Established a Star Schema to connect Claims, Geography and Policyholders, enabling granular drill-downs by State and Year
DAX Measures:
- Total Risk Score: Authored a DAX measure to aggregate risk factors across the portfolio, totaling 252K
- Waterfall Analysis: Created a variance measure to track claim amount increases and decreases across policy types
Advanced Visuals: Integrated a Gauge chart for real-time tracking of Total Claim Amount against the $503M historical ceiling

Key Insights

High-Exposure Segments: Life Insurance represents the largest financial liability at $57M, closely followed by Auto and Property.
Geographic Hotspots: Louisiana and Hawaii lead in Active Policies, while Texas and Washington are receiving the highest Policy Discounts, potentially impacting long-term profitability in those regions.
Payment Neutrality: Claim amounts are distributed almost equally across Cash, Bank Transfer and Credit Card (approx. 33% each), showing no significant bias in how customers pay vs. how they claim.

Recommendations

Risk Mitigation in Life/Auto: Given the $120M+ combined exposure in Life and Auto, the company should tighten underwriting requirements for high-risk profiles in these categories.
Discount Audit: Texas and Washington lead in discounts; a review is needed to ensure these discounts are driving volume without eroding the loss ratio.

SQL Queries

Goal

I engineered this SQL script to act as the primary data warehouse engine for the Insurance Analytics project. The goal was to consolidate five distinct data sources, covering customer demographics, policy lifecycles and claim histories, to provide a granular view of financial exposure. By structured querying, I aimed to identify high-risk policy types and evaluate the efficiency of the claim adjudication process.

Process

Database Schema Construction: Initialized the insurance environment and performed data audits using SELECT * across the policy_details, claims and payment_history tables to ensure relational integrity
Relational Joins & Data Cleaning:
- Executed JOIN operations between claims and policy_details to link financial liabilities to specific insurance products
- Applied the TRIM() function to policy_id fields to handle whitespace inconsistencies and ensure accurate record matching during aggregation
Calculated Metrics & Formatting:
- Used ROUND and CONCAT functions to transform raw financial figures into readable business units (Millions/Thousands)
- Developed aggregation logic using GROUP BY to segment data by gender, payment frequency and claim status
Operational Filtering: Implemented WHERE clauses to isolate "Pending" claims and identify specific financial bottlenecks within the fulfillment pipeline

Key Insights

Claim Status Breakdown: Identified the ratio of Approved vs. Rejected claims, providing a clear metric for the underwriting department's risk appetite.
Product Exposure: Discovered that certain policy types carry significantly higher "Pending" claim amounts, pointing to specific areas where the legal or medical review process is slowing down.
Premium Behavior: Analyzed how payment frequency (e.g., Monthly vs. Annual) impacts the average_premium amount, revealing customer preferences and cash flow stability.
High-Value Liabilities: Identified the "Top 5" individual policies by claim volume, allowing for targeted auditing of high-risk accounts.

Recommendations

Automate Low-Risk Claims: Based on the claim_status distribution, implement an automated approval path for low-value, standard-policy claims to reduce the "Pending" backlog.
Refine Premium Pricing: Use the Average Premium by Payment Frequency data to offer incentives (discounts) for annual payments, which improves the company's immediate liquidity.
Gender-Targeted Marketing: Leveraging the Gender wise policy Distribution insight, the marketing team should develop specialized coverage options for under-represented demographics to expand the customer base.
Policy Health Monitoring: Utilize the Policy Status Distribution query to trigger automated "Renewal Alerts" for policyholders in the "Lapsed" or "About to Expire" categories to improve retention rates.

Projects

Project 2 Insurance Analytics

Excel Dashboard

Project 2
Insurance Analytics