Kwacha Bank | Banking Performance & Health

2. Data

2.1 Source

A MySQL relational database (kwacha_bank) created from a single Python DDL script. All data is synthetically generated, modelled on realistic Zambian banking behaviour (ZMW currency, provincial geography, local branch naming conventions, and typical retail product structures).

2.2 Schema — 17 Tables

Indexes were created on high-cardinality foreign keys and date columns (transactions.account_id, transaction_date, customer_id, channel, loans.customer_id, loan_status, loan_payments.loan_id, payment_date, fraud_alerts.customer_id, alert_datetime, salary_payments.customer_id, payment_date) to support fast aggregation.

2.3 Volume Snapshot

2.4 Data Quality

• All 10 provinces represented; Lusaka and Copperbelt dominate (≈48% of customers).

• Gender split is near-parity: 5,043 Female / 4,957 Male.

• 2026 is a partial year (through the date of extraction) and is flagged as such in all trend analysis.

• One known gap: merchants table is populated but no transaction.merchant_id values match, so merchant-category analysis returns no data (see Challenges).

3. Challenges

Several real-world data engineering challenges surfaced during the build and analysis:

3.1 Referential Integrity vs. Synthetic Generation

The schema enforces foreign keys across 17 tables (accounts → customers, account_types, branches; transactions → accounts, customers, branches; etc.). Generating data required strict insert ordering — reference tables first (branches, account_types, loan_products), then customers, employees, then accounts, then transaction and event tables. Any re-run required idempotent inserts or truncation to avoid duplicate-key errors.

3.2 Orphaned Merchant References

The transactions table has a merchant_id column, but generated values did not match the merchants table's primary keys. As a result, the Merchant Category visual returned (no data). This is a classic synthetic-data pitfall: independent generation of two tables that should be keyed together. The fix would be to sample merchant_id values from the merchants table during transaction generation.

3.3 Missing Province on Fraud Alerts

fraud_alerts stores customer_id but not province. To produce "Fraud by Province," the query had to join through customers.province — an extra hop that is fine at 53K rows but would need a materialised view at scale.

3.4 Partial Year Bias

2026 contains only part of the year. All yearly trend lines show a visible drop for 2026 that is not a real decline — it is a data cut-off artefact. Every trend visual must label 2026 as YTD.

3.5 Confirmed-Fraud Flag Interpretation

In fraud_alerts.investigation_status, statuses like Closed, False Positive, and Monitoring correctly have confirmed_fraud = 0. Only 5 statuses carry confirmed flags (Under Investigation, Account Restricted, Confirmed Fraud, Resolved — and a small edge case). Reading the confirmed-fraud rate requires filtering by status, not just summing the boolean.

3.6 Transaction Data Window

Transactions only exist for 2025 and 2026, whereas customers span 2018–2026. Any "transaction per customer" metric across the full history would be misleading — customer lifetime value analysis is therefore restricted to the two-year transactional window.

3.7 Balance vs. Available Balance Gap

The gap between current balance (ZMW 2.031 B) and available balance (ZMW 2.009 B) is ZMW 22.1 M — representing holds, pending transactions, and overdraft utilisation. This is expected but should be explained in the dashboard so it isn't mistaken for an error.

3.8 Branch Efficiency Denominator

monthly_operating_cost is a monthly figure while deposits are a point-in-time stock. The efficiency ratio (deposits ÷ monthly cost) is therefore a relative ranking metric, not a true annual cost-to-income ratio. It is useful for comparison between branches but should not be quoted as an absolute efficiency percentage.

4. Walkthrough

4.1 Database Creation

The project begins with a single Python script that connects to MySQL as root, creates the kwacha_bank database, and executes 17 CREATE TABLE IF NOT EXISTS statements plus 13 indexes. The script prints a table-by-table confirmation on success.

bash

python create_database.py

4.2 Data Generation

Synthetic data is inserted in dependency order: branches → account/loan products → customers → employees → accounts → cards → transactions → ATM/digital details → loans → payments → fraud alerts → interactions → monthly metrics → merchants → salary payments.

4.3 Analytical Layer (report.py)

A single, self-contained Python script connects to the live database and runs 32 aggregate queries, each mapped to a specific Power BI visual. Output is plain text with a clear section header per visual, so it can be read directly from cmd and copy-pasted into documentation.

bash

cd "C:\Users\Administrator\Documents\Personal Projects\mbiile\kwacha bank"
venv\scripts\activate
python report.py

4.4 Report Pages

4.5 Key Results Walkthrough

Customer base grew steadily from 960 new customers in 2018 to 1,335 in 2025 (+39%), with 2026 showing 923 YTD. Female customers (5,043) slightly outnumber male (4,957) — rare in Zambian retail banking and worth highlighting. The Mass Market segment (3,107) dominates, followed by Business (2,029).

Deposits total ZMW 2.03 B. Salary Accounts hold the largest balance (ZMW 367.5 M) despite not being the most numerous — reflecting higher average balances. Basic Savings Accounts are the most numerous (4,119) but hold ZMW 353.8 M. Account opening accelerated dramatically: 76 in 2018 → 4,399 in 2026.

Transactions in 2026 reached 605,930 (ZMW 1.67 B) vs. 394,070 (ZMW 1.12 B) in 2025 — +54% volume, +50% value. Branch is still the top channel by count (223K) and value (ZMW 588 M), but Mobile Banking is a close second (210K; ZMW 432 M). System-generated transactions carry the largest value (ZMW 1.15 B) — these are internal entries, salary credits, and automated settlements. Smartphone dominates digital device usage (125,748 transactions).

Loans total ZMW 290.9 M disbursed, ZMW 90.7 M outstanding. Mortgage (ZMW 65.3 M) and Asset Finance (ZMW 52.7 M) lead the book. Active loans (2,616) dominate, with 1,083 completed and only 76 defaulted — a healthy portfolio. Average credit score is 680.7, average default probability 6.98%. Disbursements grew from ZMW 54.1 M (2023) to ZMW 119.6 M (2025).

Fraud alerts rose from 17,113 (2025) to 26,370 (2026) — a +54% increase, mirroring transaction growth. Most alerts are Low severity (38,243), with only 12 Critical. Lusaka (12,833) and Copperbelt (8,197) account for 39% of alerts — proportional to customer concentration. Rule Engine (10,984) and Machine Learning (10,833) are the top detection methods. Critically, 14,182 alerts were False Positives and 14,191 Closed — implying a ~27% false-positive rate that represents a real operational cost.

Branch Performance: Mkushi Branch is the most efficient (686× deposits-to-monthly-cost), followed by Chingola (602×) and Kabwe Road (579×). The least efficient are Chinsali Main (72.6×), Mongu Main (93.5×), and Mansa Main (131.8×) — all high-cost, low-deposit branches that merit review. Longacres Branch holds the most deposits (ZMW 80.7 M) but is only mid-pack on efficiency due to a high operating cost (ZMW 363,515/month).

5. Key Takeaways

1. The customer base is growing but decelerating. New customer acquisition grew every year from 2018 to 2025, then moderated. 2026's 923 is a partial-year figure but still suggests acquisition is plateauing — retention and cross-sell now matter more than top-of-funnel growth.

2. Mobile banking has reached parity with branches. With 209,745 transactions vs. 223,232 for branches, mobile is on track to overtake branch as the primary channel. Digital investment is no longer optional.

3. Deposit concentration is a strength and a risk. Salary and Premier accounts hold disproportionately large balances. Losing a segment of high-balance salary customers would materially impact the deposit base.

4. The loan book is healthy but small relative to deposits. ZMW 90.7 M outstanding vs. ZMW 2.03 B in deposits implies a loan-to-deposit ratio of only ~4.5% — extremely conservative. There is significant headroom to grow lending.

5. Fraud is rising faster than the customer base. Alerts grew 54% year-on-year, outpacing both customer growth and transaction growth. A 27% false-positive rate suggests detection rules need tuning — the operational cost of investigating false alerts is real.

6. Branch efficiency varies 9.5× across the network. The gap between Mkushi (686×) and Chinsali (72.6×) is too wide to ignore. Low-performing branches should be reviewed for consolidation, resourcing, or product-mix changes.

7. Data quality issues must be fixed before scaling. The merchant-category join failure and the province gap in fraud alerts are small now but would compound as the dataset grows. Key relationship generation (transaction → merchant) must be referentially consistent.

8. Gender parity is a genuine differentiator. Female customers (5,043) outnumber male (4,957). Few banks in the region can claim this — it should be highlighted in marketing and product strategy.

   5. Key Takeaways

9. The customer base is growing but decelerating. New customer acquisition grew every year from 2018 to 2025, then moderated. 2026's 923 is a partial-year figure but still suggests acquisition is plateauing — retention and cross-sell now matter more than top-of-funnel growth.

10. Mobile banking has reached parity with branches. With 209,745 transactions vs. 223,232 for branches, mobile is on track to overtake branch as the primary channel. Digital investment is no longer optional.

11. Deposit concentration is a strength and a risk. Salary and Premier accounts hold disproportionately large balances. Losing a segment of high-balance salary customers would materially impact the deposit base.

12. The loan book is healthy but small relative to deposits. ZMW 90.7 M outstanding vs. ZMW 2.03 B in deposits implies a loan-to-deposit ratio of only ~4.5% — extremely conservative. There is significant headroom to grow lending.

13. Fraud is rising faster than the customer base. Alerts grew 54% year-on-year, outpacing both customer growth and transaction growth. A 27% false-positive rate suggests detection rules need tuning — the operational cost of investigating false alerts is real.

14. Branch efficiency varies 9.5× across the network. The gap between Mkushi (686×) and Chinsali (72.6×) is too wide to ignore. Low-performing branches should be reviewed for consolidation, resourcing, or product-mix changes.

15. Data quality issues must be fixed before scaling. The merchant-category join failure and the province gap in fraud alerts are small now but would compound as the dataset grows. Key relationship generation (transaction → merchant) must be referentially consistent.

16. Gender parity is a genuine differentiator. Female customers (5,043) outnumber male (4,957). Few banks in the region can claim this — it should be highlighted in marketing and product strategy.