📊 Credit Card Segmentation & Profitability Analysis using SQL and Excel – (Internal)

🔍 Overview

This project involved analyzing Bank’s credit card portfolio (~18 million active cards) to evaluate the profitability and performance of various card variants across different customer profiles. The analysis helped in optimizing sourcing strategy by identifying underperforming segments and tailoring card offerings to high-value customer bands.

🧩 Business Objective

• Determine the profitability of credit card variants at a granular customer level.
• Identify which customer segments are associated with profitable or delinquent behavior.
• Guide targeted sourcing, risk mitigation, and portfolio optimization efforts.

🧰 Tools & Technologies

• SQL – For large-scale data extraction, joins, and aggregations from internal banking systems.
• Microsoft Excel – For customer segmentation, trend analysis, and profitability banding.
• PowerPoint – To present insights and strategy recommendations to senior stakeholders.

🧾 Dataset Details

• Source: Internal Bank credit card portfolio
• Size: ~1.8 crore (18 million) active credit cards
• Key Variables:
  • Accounts Count, UCICs Count (Customer Count), PBT per UCIC
  • Onus ENR, Offus ENR per UCIC
  • L3M (Last 3 Months) spending per customer across:
    • Credit Card
    • Debit Card
    • UPI
  • Organic vs Inorganic balances
  • DPD (Delinquency – Days Past Due)

🔎 Analysis Approach

1. Data Preparation

• Extracted 18M+ records using SQL.
• Merged datasets at the customer level (UCIC).
• Derived per-customer metrics and calculated profitability using PBT per UCIC.

2. Variant-Wise Performance Evaluation

• Measured spend behavior, balance distribution, and delinquency trends across card types.
• Flagged underperforming variants with low profitability or high risk (DPD).

3. Customer Banding and Segmentation

To go beyond just product-level trends, customer behavior was analyzed across multiple profile bands:

Banding Attribute	Description
Bureau Score (CIBIL)	Grouped customers by creditworthiness to detect risk-prone card segments.
NTB/ETB Tag	Split new vs existing customers to understand lifecycle profitability.
IRV Categories	Used internal risk-value (IRV) classification for ETB customers based on PBT bands.
Age Group	Analyzed young vs older customer trends across card types.
Profession	Segmented by salaried, self-employed, others, to see PBT and delinquency differences.
Monthly Income	Income-wise profitability mapping to align products with affordability.
Location Tiering	Compared customer profiles across metro, tier-1, tier-2, and tier-3 cities.

This helped uncover intersectional insights, such as:

• Some card variants performing well in metros but poorly in tier-2 towns.
• NTB customers with low CIBIL scores driving negative PBT on specific variants.

4. Insight Presentation

• Developed a slide deck summarizing:
  • Card-wise profitability patterns
  • Customer segment trends
  • Strategic recommendations for sourcing and risk teams

📈 Outcome & Business Impact

• ✅ Sourcing Strategy Updated
  Several card variants saw targeting adjustments based on profitability by customer segment.

• ✅ Risk Strategy Informed
  Risk teams used delinquency insights to define tighter eligibility criteria for high-risk segments.

• ✅ Portfolio Profitability Improved
  Card offerings became more aligned with customer potential, boosting the overall efficiency of the credit card portfolio.

📌 Key Learnings

• Even without ML, a structured approach to segment-wise profitability can drive high-impact business outcomes.
• Multi-band segmentation using behavioral, demographic, and credit attributes is key to understanding customer-level performance.
• Translating raw analytics into simple, visual presentations helps decision-makers act with confidence.