Code
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as pxAI-Powered Insurance Analytics for Assure Insurance Brokers Ltd
1 Executive Summary
This project presents a business intelligence and AI-powered analytics solution for Assure Insurance Brokers Ltd using the dataset assure_all_data_combined.csv.
The selected case study is:
1.1 Case Study
2 Business Problems Identified
2.2 2. Poor Renewal Tracking
Indicators: - RENEWAL_DATE - TO_DATE - END_DATE
2.3 3. Revenue Collection Delays
Indicators: - DATE_PAID - AMOUNT_RECEIVED - BALANCE
2.4 4. Weak Commission Monitoring
Indicators: - COMMISSION - COMMISSION_RATE_PCT
2.5 5. Customer Retention Challenges
Indicators: - CLIENT_NAME - INSURED - GROSS_PREMIUM
3 Objectives
- Analyze premium generation trends
- Identify unpaid premiums
- Measure insurer profitability
- Analyze policy renewal patterns
- Build predictive insights
- Recommend AI-driven solutions
4 Required Packages
5 Load Dataset
Code
file_path = "assure_all_data_combined.csv"
df = pd.read_csv(file_path)
print(df.head()) SOURCE_SHEET YEAR SN \
0 Sheet1_ProductionSchedule 2021 1
1 Sheet1_ProductionSchedule 2021 2
2 Sheet1_ProductionSchedule 2021 3
3 Sheet1_ProductionSchedule 2021 4
4 Sheet1_ProductionSchedule 2021 5
POLICY_NO \
0 10219044100016 & 10219044100015 (MUN437XA)
1 PRM/01/4/00079/18
2 PRM/01/4/00079/18
3 PRM/1/4/00790/19
4 CBP/1/1/00510/19
UNDERWRITING_DETAILS RISK_TYPE \
0 MOTOR FLEET COMP ON 3 SINOTRUCK TIPPERS AFN 92... NaN
1 HUNDA CRV AAA 906 AR NaN
2 KIA SPORTAGE FST 367 BQ NaN
3 MOTOR FLEET POLICY NO PRM/01/4/00790/18 NaN
4 COMBINED BUILDING POLICY NO CBP/1/1/00510/19 NaN
TYPE_OF_COVER INSURED CLIENT_NAME \
0 MOTOR COMP Crown Pacific Networks NaN
1 MOTOR COMP Sarah E. Bradford NaN
2 MOTOR T/PARTY Prof. Joseph T. Kingston NaN
3 MOTOR FLEET COMP Capt. John O. & Mrs. Carol I. Bennett NaN
4 COMB BUILDING POLICY Capt. John O. & Mrs. Carol I. Bennett NaN
NAME_OF_ASSURED ... BANK_OF_LODGEMENT DATE_OF_LODGEMENT AMOUNT_REMITTED \
0 NaN ... NaN NaN NaN
1 NaN ... NaN NaN NaN
2 NaN ... NaN NaN NaN
3 NaN ... NaN NaN NaN
4 NaN ... NaN NaN NaN
AMOUNT_UNREMITTED DATE_TO_REMIT NAME_OF_BANK CHEQUE_NO INSURER_RECEIPT_NO \
0 NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN
3 NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN
OTHER_BROKER REMARKS
0 NaN NONE
1 NaN NONE
2 NaN NONE
3 NaN NONE
4 NaN NONE
[5 rows x 50 columns]6 Dataset Overview
6.1 Dataset Shape
Code
print(df.shape)(1250, 50)6.2 Column Names
Code
print(df.columns)Index(['SOURCE_SHEET', 'YEAR', 'SN', 'POLICY_NO', 'UNDERWRITING_DETAILS',
'RISK_TYPE', 'TYPE_OF_COVER', 'INSURED', 'CLIENT_NAME',
'NAME_OF_ASSURED', 'CUSTOMER_NAME', 'INSURER', 'EFFECTIVE_DATE',
'FROM_DATE', 'TO_DATE', 'END_DATE', 'RENEWAL_DATE', 'DATE_PAID',
'DATE_PAID_PAYABLE', 'SUM_INSURED', 'SUM_ASSURED', 'GROSS_PREMIUM',
'COMMISSION', 'COMMISSION_RATE_PCT', 'BROKERAGE', 'NET_PREMIUM',
'FIVE_PCT_NET', 'BALANCE', 'OTHER_DEDUCTIONS', 'TOTAL_RECEIVABLE',
'TRANSACTION_TYPE', 'VOUCHER_NO', 'ENDORSEMENT_NO', 'LEDGER_ACCOUNT_NO',
'POLICY_TENOR', 'DEBIT_NOTE_NO', 'CREDIT_NOTE_NO', 'AMOUNT_RECEIVED',
'DATE_OF_RECEIPT', 'RECEIPT_NO', 'BANK_OF_LODGEMENT',
'DATE_OF_LODGEMENT', 'AMOUNT_REMITTED', 'AMOUNT_UNREMITTED',
'DATE_TO_REMIT', 'NAME_OF_BANK', 'CHEQUE_NO', 'INSURER_RECEIPT_NO',
'OTHER_BROKER', 'REMARKS'],
dtype='str')6.3 Data Types
Code
print(df.dtypes)SOURCE_SHEET str
YEAR int64
SN int64
POLICY_NO str
UNDERWRITING_DETAILS str
RISK_TYPE str
TYPE_OF_COVER str
INSURED str
CLIENT_NAME str
NAME_OF_ASSURED str
CUSTOMER_NAME str
INSURER str
EFFECTIVE_DATE str
FROM_DATE str
TO_DATE str
END_DATE str
RENEWAL_DATE str
DATE_PAID str
DATE_PAID_PAYABLE str
SUM_INSURED int64
SUM_ASSURED float64
GROSS_PREMIUM int64
COMMISSION str
COMMISSION_RATE_PCT str
BROKERAGE str
NET_PREMIUM str
FIVE_PCT_NET float64
BALANCE float64
OTHER_DEDUCTIONS float64
TOTAL_RECEIVABLE float64
TRANSACTION_TYPE str
VOUCHER_NO float64
ENDORSEMENT_NO str
LEDGER_ACCOUNT_NO float64
POLICY_TENOR float64
DEBIT_NOTE_NO str
CREDIT_NOTE_NO str
AMOUNT_RECEIVED float64
DATE_OF_RECEIPT str
RECEIPT_NO float64
BANK_OF_LODGEMENT str
DATE_OF_LODGEMENT str
AMOUNT_REMITTED float64
AMOUNT_UNREMITTED float64
DATE_TO_REMIT float64
NAME_OF_BANK str
CHEQUE_NO str
INSURER_RECEIPT_NO str
OTHER_BROKER str
REMARKS str
dtype: object6.4 Missing Values
Code
print(df.isnull().sum())SOURCE_SHEET 0
YEAR 0
SN 0
POLICY_NO 0
UNDERWRITING_DETAILS 805
RISK_TYPE 890
TYPE_OF_COVER 445
INSURED 446
CLIENT_NAME 891
NAME_OF_ASSURED 805
CUSTOMER_NAME 817
INSURER 0
EFFECTIVE_DATE 0
FROM_DATE 805
TO_DATE 805
END_DATE 805
RENEWAL_DATE 805
DATE_PAID 809
DATE_PAID_PAYABLE 892
SUM_INSURED 0
SUM_ASSURED 805
GROSS_PREMIUM 0
COMMISSION 0
COMMISSION_RATE_PCT 890
BROKERAGE 805
NET_PREMIUM 0
FIVE_PCT_NET 805
BALANCE 805
OTHER_DEDUCTIONS 890
TOTAL_RECEIVABLE 890
TRANSACTION_TYPE 805
VOUCHER_NO 805
ENDORSEMENT_NO 845
LEDGER_ACCOUNT_NO 805
POLICY_TENOR 805
DEBIT_NOTE_NO 860
CREDIT_NOTE_NO 842
AMOUNT_RECEIVED 805
DATE_OF_RECEIPT 805
RECEIPT_NO 913
BANK_OF_LODGEMENT 805
DATE_OF_LODGEMENT 805
AMOUNT_REMITTED 805
AMOUNT_UNREMITTED 805
DATE_TO_REMIT 1250
NAME_OF_BANK 808
CHEQUE_NO 845
INSURER_RECEIPT_NO 841
OTHER_BROKER 805
REMARKS 1
dtype: int647 Data Cleaning
7.1 Remove Duplicates
Code
df = df.drop_duplicates()7.2 Convert Date Columns
Code
date_columns = [
'EFFECTIVE_DATE',
'FROM_DATE',
'TO_DATE',
'END_DATE',
'RENEWAL_DATE',
'DATE_PAID',
'DATE_OF_RECEIPT'
]
for col in date_columns:
if col in df.columns:
df[col] = pd.to_datetime(df[col], errors='coerce')8 Exploratory Data Analysis
8.1 Premium Trend Analysis
Code
premium_by_year = df.groupby('YEAR')['GROSS_PREMIUM'].sum()
print(premium_by_year)YEAR
2021 49561912
2022 80342757
2023 222968151
2024 324160533
2025 452074371
Name: GROSS_PREMIUM, dtype: int64Code
premium_by_year.plot(kind='bar', figsize=(10,5))
plt.title('Gross Premium by Year')
plt.ylabel('Gross Premium')
plt.show()
8.2 Top Insurance Products
Code
product_premium = df.groupby('TYPE_OF_COVER')['GROSS_PREMIUM'].sum().sort_values(ascending=False).head(10)
print(product_premium)TYPE_OF_COVER
MOTOR COMP 79988331
MOTOR FLEET COMPREHENSIVE 76916285
COMMERCIAL VEHICLES COMP COVERS 69906777
COMMERCIAL MOTOR INSURANCE COVER 57496250
GLA 44730431
MOTOR TRUCKS COMPREHENSIVE 38480000
FIRE & PERILS 23780981
MOTOR FLEET COMP 22447347
COMMERCIAL VEHICLES COMP COVER 21071496
COMMERCIAL TRUCKS COMP COVERS 19646469
Name: GROSS_PREMIUM, dtype: int648.3 Outstanding Balance Analysis
Code
outstanding = df['BALANCE'].sum()
print(outstanding)319733168.09 AI Opportunities
9.1 Renewal Prediction Model
Potential features: - Premium size - Payment history - Outstanding balance - Product type
9.3 Fraud Detection
Analytics can detect: - Duplicate policies - Suspicious transactions - Abnormal commission patterns
10 Key Findings
- Motor insurance contributes significantly to premiums.
- Outstanding balances may indicate revenue leakage.
- A small number of customers likely contribute most revenue.
- Some policies may be close to expiration without active renewal monitoring.
11 Recommendations
11.1 Financial Recommendations
- Improve premium recovery systems
- Monitor unremitted balances weekly
- Automate reconciliation processes
11.2 Operational Recommendations
- Build a centralized dashboard
- Deploy AI-powered renewal alerts
- Digitize policy tracking workflows
11.3 Strategic Recommendations
- Focus on high-profit products
- Expand retention campaigns
- Introduce predictive analytics
12 Dashboard Recommendations
The company should implement dashboards showing: - Premium collections - Outstanding balances - Renewal pipeline - Commission trends - Top customers - Product performance
13 Conclusion
This project demonstrates how Assure Insurance Brokers Ltd can use analytics and AI to improve profitability, customer retention, operational efficiency, and strategic decision-making.
The company can transition from manual reporting to data-driven insurance management using predictive analytics and intelligent dashboards.