Annuity Scoring ETL

🔙

Automating Insurance Product Analysis & Scoring Engine at 10Life

Company: 10Life Ltd.

Role: Data Analyst

Live Work Demonstration:

10Life Deferred Annuity Comparison Page

10Life Immediate Annuity Comparison Page

10Life Scoring Methodology

Project Overview & The Challenge

10Life is dedicated to revolutionizing the insurance industry by eliminating unfair practices and empowering consumers with transparency. A core challenge in the insurance market is the inherent complexity and opacity of products, making it incredibly difficult for individuals to make informed, apple-to-apple comparisons.

To address this, 10Life's actuarial team developed a unique, fair, and sophisticated scoring methodology (based on a 10-point scale) across a wide range of insurance products, including Annuities, Life, Savings, Medical, and Critical Illness. This allows the public to finally compare complex insurance products with clarity.

My project focused on transforming the intricate, Excel-based actuarial model for annuity products (specifically QDAP - Qualifying Deferred Annuity Plans) into a fully automated, Python-driven data pipeline. The goal was to scale the analysis, ensure accuracy, and efficiently deliver these crucial insights to consumers via the 10Life website and to business clients through the "10Life Sense" digital insight solution.

My Role & Key Contributions: Building the Data Engine

As a Data Analyst, I spearheaded the development of an end-to-end ETL (Extract, Transform, Load) program that operationalized 10Life's proprietary annuity scoring methodology.

1. System Design & Data Modeling:

Pythonization of Actuarial Model: Transitioned the complex annuity scoring logic from manual Excel spreadsheets into a robust and maintainable Python application.

Standardized Input Framework: Designed and implemented a structured input template to organize and manage relational data tables extracted from diverse insurance product proposals, ensuring data consistency for the Python models.

2. ETL Pipeline Development (Python):

Extraction Phase:

Developed Python automation scripts to systematically download financial product proposals (PDFs) from various insurance company portals.
Engineered a Python script leveraging PDF parsing techniques to accurately extract critical financial details, terms, and conditions from these unstructured proposal documents.

Transformation Phase: This was a multi-step Python program designed to:

Calculate Financial Metrics: Implement algorithms to compute essential financial indicators such as the Internal Rate of Return (IRR) based on the extracted policy details.
Apply Proprietary Scoring Logic: Integrate 10Life's detailed scoring model (as outlined in the QDAP Savings Scoring Methodology) to calculate comprehensive product scores. This involved multifaceted calculations considering:

Guaranteed Returns (60% weight)
Projected Returns (15% weight)
Early Surrender Coverage (20% weight)
Insurer Fulfilment (5% weight)

FIVE Annuity scoring models: Savings, Stable Income, Growth Income, Longevity, Immediate Annuity
Compute Analytical Details: Calculate and append other vital data points such as breakeven years, various return ratios, and guaranteed/projected income figures.
Award 5-Star Ratings: Implement the logic to identify and flag top-tier products, awarding the "10Life 5-Star Annuity" designation to plans scoring 8.95 (rounded to 9) or higher out of 10.
All transformed data and calculated metrics were exported to CSV files for review and as inputs for the loading phase.

Loading Phase:

Authored a Python script to efficiently write the full, enriched financial details, scores, and all calculated analytical data into 10Life's central PostgreSQL database.

3. Key Achievements & Impact:

The data pipeline I developed directly populates the PostgreSQL database, which serves as the single source of truth for:

10Life Public Website: Dynamically feeding data to user-facing comparison tools, such as the Annuity Product Comparison Page, allowing users to filter and compare products based on objective scores and detailed financial attributes.
10Life Sense: Powering a B2B digital insight solution that provides subscribed users (e.g., insurance companies) with comprehensive product data and market intelligence.