Payment Intelligence System

Python Analysis • Google Colab

Exploratory Data Analysis

Python (Pandas + Matplotlib/Seaborn) was used on the full 3.5M row dataset to identify failure patterns across payment methods, time windows, transaction amounts, and error codes. The daily trend chart confirms 6 months of continuous data from Jul 2025 to Jan 2026.

01_failure_by_method.py — UPI leads at ~10% failure rate

02_failure_by_hour.py — Peak spikes at 12–1 PM & 8–9 PM

03_amount_distribution.py — Failures skew toward low-value txns

04_gateway_performance.py — Uniform ~7% across all gateways

05_error_codes.py — 60K+ INSUFFICIENT_FUNDS failures

06_daily_trend.py — Stable ~7% baseline across 6 months

~14%

Failure rate spikes at hour 12 and hour 20 — exactly 2× the 6% off-peak baseline. Annotated directly in the chart.

60K+

INSUFFICIENT_FUNDS failures across 3.5M rows — the single largest error code, followed by 50K BANK_TIMEOUT events.

6 mo

Jul 2025 – Jan 2026 daily trend confirms consistent ~7% baseline with no seasonal degradation — a systemic issue, not a spike.

SQL Analysis • PostgreSQL

Query-Driven Pattern Discovery

Six structured SQL queries drill from overall baseline down to error-level root cause. Click any query to expand.

1

Overall Transaction Health

▼ expand

SELECT 
    COUNT(*) AS Total_Transactions,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS Failed_Transactions,
    ROUND(
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS Failure_Rate_Percentage
FROM payment_data;

9.98% baseline failure rate across 3.5M transactions — consistent enough to rule out random noise

2

Revenue Impact Quantification

▼ expand

SELECT 
    SUM(amount) AS Total_Revenue,
    SUM(CASE WHEN status = 'failed' THEN amount ELSE 0 END) AS Failed_Revenue,
    ROUND(
        SUM(CASE WHEN status = 'failed' THEN amount ELSE 0 END) * 100.0 / SUM(amount), 2
    ) AS Revenue_Loss_Percentage
FROM payment_data;

₹4.84M revenue locked in failed transactions — slightly above the 9.98% rate, meaning failed txns skew higher-value

3

Payment Method Failure Comparison

▼ expand

SELECT 
    payment_method,
    COUNT(*) AS Total_Transactions,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS Failed_Transactions,
    ROUND(
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS Failure_Rate_Percentage
FROM payment_data
GROUP BY payment_method
ORDER BY Failure_Rate_Percentage DESC;

UPI: ~10% failure vs Card: ~3.7% — UPI dominates volume, making this gap the single highest-impact fix

4

Peak vs Off-Peak Load Analysis

▼ expand

SELECT 
    is_peak_hour,
    COUNT(*) AS Total_Transactions,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS Failed_Transactions,
    ROUND(
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS Failure_Rate_Percentage
FROM payment_data
GROUP BY is_peak_hour;

15.59% peak vs ~6% off-peak — 2.5× variance confirms infrastructure capacity constraints, not gateway issues

5

Gateway Performance Benchmark

▼ expand

SELECT 
    gateway,
    COUNT(*) AS Total_Transactions,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS Failed_Transactions,
    ROUND(
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS Failure_Rate_Percentage,
    AVG(response_time_ms) AS Avg_Response_Time
FROM payment_data
GROUP BY gateway
ORDER BY Failure_Rate_Percentage;

All 3 Razorpay gateways show ~7% failure and ~300ms response — rules out the "bad gateway" hypothesis entirely

6

Error Code Root Cause Breakdown

▼ expand

SELECT 
    error_code,
    COUNT(*) AS Failed_Transactions,
    ROUND(
        COUNT(*) * 100.0 / (SELECT COUNT(*) FROM payment_data WHERE status = 'failed'), 2
    ) AS Percentage_Of_Failures
FROM payment_data
WHERE status = 'failed'
GROUP BY error_code
ORDER BY Failed_Transactions DESC
LIMIT 6;

BANK_TIMEOUT (50K) + NETWORK_ERROR + GATEWAY_TIMEOUT = ~100K technically recoverable failures — prime targets for retry logic

Methodology note: SQL analysis run on full 3.5M row dataset. Power BI dashboard uses a 117K representative sample for visualization performance. All Python EDA charts reflect the complete dataset.

Power BI Dashboard • 3 Pages

Monitoring Dashboard

Multi-page Power BI report covering KPIs, deep failure analysis with day × hour hotspot matrix, and a structured action plan. Scroll to explore all three pages — click to expand.

Page 1 — Home: KPIs, failure trend by day, method comparison, gateway comparison

Page 2 — Deep Analysis: Day × Hour hotspot matrix, top error codes, peak vs off-peak, revenue by size

Page 3 — Recommendations: 5-point action plan with recovery impact estimates

Download Dashboard PDF

Risk Assessment

Key Business Risks Identified

⚠️

UPI Concentration Risk

UPI is the highest-volume method yet carries ~10% failure rate. Any UPI degradation creates outsized revenue impact with no fallback routing in place.

⚠️

Infrastructure Capacity

15.59% failure at peak vs 6% off-peak is a 2.5× spike. System cannot sustain lunch and evening traffic without load balancing or auto-scaling.

⚠️

No Retry Mechanism

BANK_TIMEOUT and NETWORK_ERROR account for ~100K failures across the dataset. These are technically recoverable but permanently lost without automated retry.

Action Plan

Data-Driven Recommendations

1

Smart Retry for Timeout Failures

Auto-retry BANK_TIMEOUT and GATEWAY_TIMEOUT transactions within 30 seconds. These ~100K errors across the dataset are technically recoverable with no user intervention needed.

→ Estimated recovery: 20–30% of timeout-related failures

2

Infrastructure Scaling at Peak Windows

Auto-scale server capacity 30 minutes before 12 PM and 8 PM. The Python hourly chart shows failure spikes are sharp and predictable — making preemptive scaling highly effective.

→ Estimated impact: Reduce peak failure from 15.59% toward baseline 6%

3

High-Value Transaction Routing

Route transactions above ₹5,000 to card-based methods (lower failure) instead of UPI. Amount distribution analysis shows failures are disproportionately concentrated in low-value transactions, but high-value failures drive most revenue loss.

→ Reduce failure concentration in highest-revenue transactions

4

Gateway Failover System

Automatically switch to a backup Razorpay gateway when primary response time exceeds threshold. All three gateways show ~300ms avg response — failover can be implemented without performance penalty.

→ Minimize downtime-related failures during partial outages

5

User Retry Notifications

Instant SMS/notification on failure with a one-click retry link. Reduces permanent abandonment from users who don't manually retry after a failed transaction.

→ Recover a portion of the 74.45% off-peak abandoned failures

Reflection

Key Takeaways

01 / SEGMENTATION

Drill down before concluding

A flat 9.98% failure rate told us nothing. Slicing by time, method, and error code revealed three separate fixable problems.

02 / ROOT CAUSE

Same metric, different cause

All gateways showed identical failure rates, ruling out the "bad gateway" hypothesis. The real culprit was infrastructure load and UPI bank connectivity.

03 / TOOLING

SQL + Python + BI is the full stack

SQL for querying patterns, Python for visual EDA on the full dataset, Power BI for stakeholder-facing dashboards. Each tool has its role.

04 / IMPACT

Translate technical to business

"50K bank timeouts" becomes "recoverable revenue locked in retryable failures" — the framing is what drives action in a real org.

Payment IntelligenceSystem

Need Data Analysis for Your Platform?

Payment Intelligence
System