Investigate the threshold
The $3.08B status-based amount exceeds the illustrative $3.00B level.
Use connected status, state and purpose views to explain the signal before proposing a response.
Data analytics case study · Portfolio risk
I used BigQuery and Looker Enterprise to validate 270,299 loan records, build reusable analytical tables and explore how status, geography, purpose and origination year shape a portfolio risk review.
A training case simulating a Treasury analytics assignment. My work covered source validation, SQL modelling, a Looker dashboard and a documented review framework.
The calculated amount exceeds the illustrative $3.00B threshold. Concentration and status measures provide the next level of investigation.
Above the illustrative $3.00B internal monitoring level.
Share of the not-Fully-Paid amount held by the five largest states.
Original loan amount in debt consolidation, excluding Fully Paid loans.
Charged Off or Default amount ÷ total amount issued to small-business loans.
Looker Enterprise
The Looker dashboard connects headline measures to states, origination cohorts, purposes and synthetic borrower records. These are screenshots of the completed report; the risk explorer below uses its validated aggregate outputs.
The image will appear when the corresponding site asset is available.
Risk lenses
The status view uses original loan amounts rather than remaining balances. Charged Off and Default are included in the denominator.
87.89% of the not-Fully-Paid amount is classified Current. Adverse statuses remain visible separately; their amounts do not measure net financial loss.
Select a row to explore its meaning. Bar lengths compare amounts or shares within the selected view.
Source: the downloadable analytical CSV exports and original Looker status view. Amounts use the dollar denomination in the training outputs. Status shares are rounded. Review priority combines exposure and loss-rate quartiles; it is a descriptive framework.
| Measure | Value | Definition |
|---|---|---|
| Loan records | 270,299 | Distinct loan IDs in the validated training snapshot |
| Total amount issued | $4,166,072,400 | Original loan_amount across all statuses |
| Not-Fully-Paid amount | $3,080,553,000 | Original loan_amount excluding Fully Paid, including Charged Off and Default |
| Loss-classified amount | $279,669,425 | Original loan_amount for Charged Off or Default |
| Portfolio loss-classified rate | 6.71% | Loss-classified amount ÷ total amount issued |
| Top-five state share | 41.81% | Five largest state amounts ÷ all not-Fully-Paid amounts |
| Jurisdictions represented | 51 | Distinct mapped jurisdictions in loan records; mapping table contains 52 entries |
| Loan status | Share |
|---|---|
| Current | 87.89% |
| Charged Off | 9.07% |
| Late (31–120 days) | 1.72% |
| In Grace Period | 0.90% |
| Late (16–30 days) | 0.42% |
| Default | 0.01% |
| State | Not-Fully-Paid amount | Share | Loss-classified rate | Priority |
|---|---|---|---|---|
| California | $419,531,275 | 13.62% | 6.93% | Priority 2 |
| Texas | $268,687,125 | 8.72% | 6.63% | Monitor |
| New York | $247,618,650 | 8.04% | 7.44% | Priority 1 |
| Florida | $223,337,275 | 7.25% | 7.06% | Priority 2 |
| Illinois | $128,796,600 | 4.18% | 6.14% | Monitor |
| New Jersey | $119,291,450 | 3.87% | 7.63% | Priority 1 |
| Ohio | $102,053,125 | 3.31% | 7.09% | Priority 2 |
| Georgia | $101,972,625 | 3.31% | 5.24% | Monitor |
| Purpose | Not-Fully-Paid amount | Loss-classified rate |
|---|---|---|
| Debt consolidation | $1,832,403,625 | 7.26% |
| Credit card | $743,918,225 | 5.42% |
| Home improvement | $194,797,125 | 6.32% |
| Other | $134,245,825 | 6.49% |
| Major purchase | $55,560,975 | 6.86% |
| Small business | $33,691,500 | 9.31% |
| Medical | $23,958,800 | 7.4% |
| House | $21,889,925 | 4.46% |
| Car | $18,165,525 | 5.39% |
| Moving | $11,120,725 | 8.53% |
| Vacation | $9,354,200 | 6.92% |
| Renewable energy | $1,213,675 | 6.56% |
| Wedding | $232,875 | 8.59% |
| Origination year | Not-Fully-Paid amount |
|---|---|
| 2012 | $8,011,000 |
| 2013 | $53,897,825 |
| 2014 | $180,481,175 |
| 2015 | $206,154,925 |
| 2016 | $566,835,950 |
| 2017 | $525,088,150 |
| 2018 | $743,363,125 |
| 2019 | $796,720,850 |
Method and data quality
I preserved the loan-level grain, documented status definitions and built reusable tables before designing the dashboard. Each headline result can be traced back to SQL and its source fields.
Review data types, nested application fields and source-table coverage.
Check row counts, distinct loan IDs, nulls and geographic mappings.
Create explicit status flags and document the amount denominators.
Build reusable marts with JOINs, aggregation, LAG, NTILE and ranking.
Connect Looker filters, cross-filtering and threshold formatting.
BigQuery evidence
Schema inspection established the available fields, nested application structure and data types before transformation logic was written.
The image will appear when the corresponding site asset is available.
Window functions support cohort comparisons and descriptive review priorities. The SQL archive includes source checks, transformations and presentation outputs.
CREATE OR REPLACE TABLE fintech.loan_risk_mart AS
SELECT
l.loan_id,
l.customer_id,
l.loan_status,
l.loan_amount,
l.state,
sr.subregion,
sr.region,
l.int_rate,
CAST(l.issue_year AS INT64) AS issue_year,
COALESCE(NULLIF(TRIM(l.application.purpose), ''), 'Unknown') AS purpose,
l.loan_status != 'Fully Paid' AS is_outstanding,
l.loan_status IN (
'Late (16-30 days)',
'Late (31-120 days)',
'In Grace Period'
) AS is_delinquent,
l.loan_status IN ('Charged Off', 'Default') AS is_loss
FROM fintech.loan AS l
LEFT JOIN fintech.state_region AS sr
ON l.state = sr.state;
Findings and proposed monitoring
The result is a documented review framework. These are proposed analytical next steps, rather than evidence of changes implemented by a lender.
The $3.08B status-based amount exceeds the illustrative $3.00B level.
Use connected status, state and purpose views to explain the signal before proposing a response.
California has the largest amount; New York and New Jersey are Priority 1 in the combined quartile framework.
Review the top-five concentration alongside both exposure and loss-classified rates, rather than ranking priority by amount alone.
Debt consolidation is the largest segment; small business has the highest loss-classified rate, at 9.31%.
Treat concentration and adverse-status rates as different questions. Compare like-for-like denominators and cohort maturity.
The 2019 cohort contributes $796.72M to the not-Fully-Paid amount in this snapshot.
Add repeated performance observations and repayment data before assessing migration, deterioration or forecasting.
Scope and interpretation
A descriptive analysis of a training snapshot, with transparent definitions and a clear boundary between findings and proposed extensions.
It uses a training dataset and does not represent a live lender portfolio.
It is not a regulatory capital limit or a universal industry benchmark.
The source supplies loan_amount rather than verified remaining principal. Charged Off and Default amounts do not account for recoveries.
The 2012–2019 cohorts have different ages. Repeated snapshots and repayments are needed for migration, loss development and forecasts.
Project evidence
Review the exported results and SQL alongside the original dashboard evidence. The definitions on this page clarify the terminology used in the project files.
Portfolio summary, origination cohorts, purpose rates, state priorities, regional status mix and synthetic borrower detail.
SQL · BigQuery scriptsSource validation, geographic mapping, risk-mart creation, executive summaries and analytical queries.
PDF · Executive summaryThe original project narrative, findings and recommendations. Read “outstanding” using the status-based definition explained above.