Summary of "Bank Loan Report | SQL And Tableau Data Analysis Portfolio Project | Project in Financial Domain"

High-level summary

This is a step-by-step portfolio project demonstrating how to build a bank-loan analytics pipeline (MySQL → SQL analysis → Tableau dashboard). The project focuses on ETL/data cleaning, KPI calculations in SQL, and building three Tableau dashboards (Summary KPIs, Overview drilldowns, Details grid) with interactive filters and navigation.

Business purpose

Provide a monitoring and reporting workspace for a bank’s consumer lending program so product, credit, operations, and leadership can monitor performance and investigate trends by time, geography, and borrower attributes. Key areas to track: volumes, funded amounts, repayments, pricing (interest), risk (DTI, grades), and good vs bad loan performance.

Key KPIs and metrics (from the dataset)

Dataset totals
- Total loan applications: 38,576
- Total funded amount (sum of loan_amount): ~435M
- Total amount received (sum of total_payment): ~473M
Month-to-date (MTD) / Previous-month-to-date (PMTD) / Month-over-month (MoM)
- Applications
  - MTD (Dec 2021): 4,314
  - PMTD (Nov 2021): 4,035
  - MoM change: +6.9%
- Funded amount
  - MTD (Dec 2021): 53,981,425 (~53.98M)
  - PMTD (Nov 2021): ~47.75M
  - MoM change: +13% (approx)
- Amount received (repayments + interest)
  - MTD (Dec 2021): ~58.07M
  - PMTD: ~50M+
  - MoM change: +15–16% (approx)
- Average interest rate (avg int_rate)
  - Overall: ~12.04%
  - MTD: ~12.36%
  - PMTD: ~11.94%
  - MoM change (relative): +3.4%
- Average debt-to-income (DTI)
  - Overall: ~13.3%
  - MTD: ~13.66%
  - PMTD: ~13.30%
  - MoM change (relative): +2.72%
Good vs Bad loan indicators (by loan_status)
- Definitions
  - Good loans: loan_status ∈ {Fully Paid, Current}
  - Bad loans: Charged Off
- Good loan %
  - By count: 86.17% (good loan count ≈ 33,243)
- Bad loan %
  - By count: ~13.82% (bad loan count ≈ 5,333)
- Good-loan totals (funded / amount received) align with most repayments coming from good loans

Note: Some numeric results were reported verbally and rounded in the video. Use the raw SQL outputs for exact values.

Frameworks, patterns and playbooks (actionable, repeatable)

Data ingestion & staging

Create schema/table in MySQL Workbench and use the Table Data Import Wizard for CSV files.
Always inspect column types after import — dates frequently import as text.

Date cleaning ETL pattern (repeatable playbook)

Add converted date columns (e.g., ALTER TABLE ADD COLUMN converted_issue_date DATE).
Use CASE + STR_TO_DATE with branches to parse multiple incoming date formats (slashes, hyphens, yyyy-mm-dd, etc.).
Drop the old text date columns and rename converted columns back to the original names.

KPI calculation pattern in SQL

Baseline totals: use simple aggregates (COUNT(id), SUM(loan_amount), SUM(total_payment), AVG(int_rate), AVG(dti)).
Time-window comparisons:
- Compute MTD and PMTD by filtering on MONTH(issue_date) and YEAR(issue_date).
- Use CTEs to compute MTD and PMTD, then calculate MoM% as (MTD - PMTD) / PMTD * 100.
Grouping and rollups: GROUP BY month(issue_date), state, term, emp_length, purpose, home_ownership.

Use of Common Table Expressions (CTEs)

Structure time comparisons and avoid repeated subqueries by using CTEs for readability and reuse.

Tableau dashboard design & interactivity playbook

Build a three-layer dashboard:
1. Summary KPIs (top row: big numbers with MTD and MoM)
2. Overview (monthly trends line, state map, term donut, employee-length bar, purpose bar, home-ownership treemap)
3. Details (tabular grid for export/operational use)
Parameter-driven visualizations:
- Create a “Select measure” string parameter and a calculated field (CASE/IF) that maps the parameter to SUM/AVG measures so one chart can display multiple measures.
Filters:
- Apply common filters (verification status, purpose, grade) across worksheets (Apply to worksheets → all using this data source).
Layout and navigation:
- Use floating containers and image backgrounds to match a visual template.
- Create navigation buttons (dashboard actions) to switch between Summary / Overview / Details.
Tableau Public workaround:
- If Tableau Public cannot connect directly or has import limits, export a full CSV from MySQL and convert to Excel if needed for import.

Concrete SQL / sample patterns

Count total applications

SELECT COUNT(id) AS total_loan_applications
FROM bank_loan;

MTD and PMTD counts and MoM (CTE pattern)

WITH mtd AS (
  SELECT COUNT(id) AS mtd_count
  FROM bank_loan
  WHERE MONTH(issue_date) = 12 AND YEAR(issue_date) = 2021
),
pmtd AS (
  SELECT COUNT(id) AS pmtd_count
  FROM bank_loan
  WHERE MONTH(issue_date) = 11 AND YEAR(issue_date) = 2021
)
SELECT (mtd.mtd_count - pmtd.pmtd_count) / pmtd.pmtd_count * 100 AS mom_pct
FROM mtd, pmtd;

Date conversion pattern

UPDATE bank_loan
SET con_issue_date = CASE
  WHEN issue_date LIKE '%/%/%' THEN STR_TO_DATE(issue_date, '%d/%m/%Y')
  WHEN issue_date LIKE '%-%-%' THEN STR_TO_DATE(issue_date, '%d-%m-%Y')
  WHEN issue_date LIKE '____-__-__' THEN STR_TO_DATE(issue_date, '%Y-%m-%d')
  ELSE NULL
END;

Good/bad loan percentage

SELECT 100.0 * SUM(CASE WHEN loan_status IN ('Fully Paid','Current') THEN 1 ELSE 0 END) / COUNT(*) AS good_loan_pct
FROM bank_loan;

Aggregation by month

SELECT MONTH(issue_date) AS month_num,
       MONTHNAME(issue_date) AS month_name,
       COUNT(id) AS applications,
       SUM(loan_amount) AS total_funded,
       SUM(total_payment) AS total_received
FROM bank_loan
GROUP BY month_num, month_name
ORDER BY month_num;

Actionable recommendations & checklist for practitioners

Data ingestion & validation
- Inspect raw imports for date formats and inconsistent separators.
- Add conversion columns, parse with STR_TO_DATE, validate min/max dates, then drop raw columns.
- Document column definitions (create a KPI/term doc).
SQL/analytics playbook
- Implement CTEs for clean time-window comparisons (MTD, PMTD, YoY, MoM).
- Keep raw/base queries separate from presentation queries. Use views to simplify and reuse dashboard queries.
Dashboard design
- Top-row KPIs: show current total, MTD, and MoM% for each core KPI (applications, funded, received, avg interest, avg DTI).
- Provide consistent interactivity: filters (verification, grade, purpose), state drilldowns, parameterized charts.
- Include a details/exportable sheet for operational follow-up.
Reproducibility & portfolio readiness
- Save and publish SQL scripts and the Tableau workbook. Put code and README on GitHub and record a short demo video.
- Note Tableau Public limitations: export CSV from DB if necessary, or use Tableau Desktop.
Business-use tips
- Use good-vs-bad segmentation early for cohort analysis to prioritize collections and credit policy reviews.
- Track MoM changes in funded amount and repayments to detect channel shifts quickly.
- Combine map (state) and term/purpose breakdowns to identify product-market fit and areas of elevated risk.

Concrete examples / case study elements shown

End-to-end worked example using a ~38.5k-row consumer-loan dataset:
- MySQL import and schema creation → multiple date conversions via CASE + STR_TO_DATE → computed CTEs for MTD/PMTD → KPI aggregates in SQL.
- Tableau: 3 dashboards (Summary / Overview / Details); parameter-driven line chart (MTD/PMTD/MoM); choropleth map by state; term donut; employee-length bar; purpose and home-ownership treemap; details table for exports.
Implementation hints:
- Increase result limit in Tableau Public; export from MySQL as CSV, convert to Excel, then import into Tableau Public when direct connections are limited.
- Use floating containers for precise layout and image background templates to match branding; add navigation actions to switch dashboards.

Limitations & notes

Several numeric outputs in the video were described verbally and rounded. Use the raw SQL outputs for precise values when implementing.
Dashboard design choices (colors, fonts, background images) are aesthetic and should be adapted to company/brand guidelines.
The project is a re-creation of a mentor’s project (presenter credits a mentor from “Data Tutorials”); the presenter adapted it for Tableau Public and walked through code and dashboard design.

If you replicate this pipeline, rely on exact SQL outputs for reporting and treat the video’s verbal numbers as approximate guides.

Presenters / sources

Video presenter: unnamed YouTube channel author (demonstrates MySQL + Tableau Public bank loan report recreation).
Original mentor / source referenced: Data Tutorials (credited by the presenter).