Data analytics case study · Portfolio risk

Loan portfolio risk and concentration analysis

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.

Portfolio control map Review triggered
Status-based amount against an illustrative threshold

The calculated amount exceeds the illustrative $3.00B threshold. Concentration and status measures provide the next level of investigation.

Not-Fully-Paid loan amount $3.08B
$0 $3.00B monitoring level
Top-five states41.81%Geographic concentration
Current status87.89%Share of not-Fully-Paid amount
Purpose loss-classified rate9.31%Small-business loans
The amount sums original loan values for all statuses other than Fully Paid, including Charged Off and Default. It is not a remaining-principal measure.
Not-Fully-Paid loan amount$3.08B

Above the illustrative $3.00B internal monitoring level.

Top-five state share41.81%

Share of the not-Fully-Paid amount held by the five largest states.

Largest purpose exposure$1.83B

Original loan amount in debt consolidation, excluding Fully Paid loans.

Highest purpose loss-classified rate9.31%

Charged Off or Default amount ÷ total amount issued to small-business loans.

Looker Enterprise

From portfolio overview to loan detail

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.

Looker dashboard showing outstanding portfolio exposure, loan status and state concentration
Dashboard evidence

The image will appear when the corresponding site asset is available.

Original Looker view: status-based loan amount, status mix and state concentration. Built from reusable BigQuery analytical tables.

Risk lenses

Three lenses on portfolio risk

The status view uses original loan amounts rather than remaining balances. Charged Off and Default are included in the denominator.

Current status holds the largest share

87.89% of the not-Fully-Paid amount is classified Current. Adverse statuses remain visible separately; their amounts do not measure net financial loss.

Largest displayed share 87.89%

Select a row to explore its meaning. Bar lengths compare amounts or shares within the selected view.

Read the figures and definitions as tables

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.

Portfolio measures and definitions
MeasureValueDefinition
Loan records270,299Distinct loan IDs in the validated training snapshot
Total amount issued$4,166,072,400Original loan_amount across all statuses
Not-Fully-Paid amount$3,080,553,000Original loan_amount excluding Fully Paid, including Charged Off and Default
Loss-classified amount$279,669,425Original loan_amount for Charged Off or Default
Portfolio loss-classified rate6.71%Loss-classified amount ÷ total amount issued
Top-five state share41.81%Five largest state amounts ÷ all not-Fully-Paid amounts
Jurisdictions represented51Distinct mapped jurisdictions in loan records; mapping table contains 52 entries
Status shares of the not-Fully-Paid amount
Loan statusShare
Current87.89%
Charged Off9.07%
Late (31–120 days)1.72%
In Grace Period0.90%
Late (16–30 days)0.42%
Default0.01%
Eight largest state amounts and combined review priority
StateNot-Fully-Paid amountShareLoss-classified ratePriority
California$419,531,27513.62%6.93%Priority 2
Texas$268,687,1258.72%6.63%Monitor
New York$247,618,6508.04%7.44%Priority 1
Florida$223,337,2757.25%7.06%Priority 2
Illinois$128,796,6004.18%6.14%Monitor
New Jersey$119,291,4503.87%7.63%Priority 1
Ohio$102,053,1253.31%7.09%Priority 2
Georgia$101,972,6253.31%5.24%Monitor
Loan-purpose amounts and loss-classified rates
PurposeNot-Fully-Paid amountLoss-classified rate
Debt consolidation$1,832,403,6257.26%
Credit card$743,918,2255.42%
Home improvement$194,797,1256.32%
Other$134,245,8256.49%
Major purchase$55,560,9756.86%
Small business$33,691,5009.31%
Medical$23,958,8007.4%
House$21,889,9254.46%
Car$18,165,5255.39%
Moving$11,120,7258.53%
Vacation$9,354,2006.92%
Renewable energy$1,213,6756.56%
Wedding$232,8758.59%
Origination cohorts in one portfolio snapshot
Origination yearNot-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

From source checks to the dashboard

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.

  1. 01

    Inspect

    Review data types, nested application fields and source-table coverage.

  2. 02

    Validate

    Check row counts, distinct loan IDs, nulls and geographic mappings.

  3. 03

    Define

    Create explicit status flags and document the amount denominators.

  4. 04

    Model

    Build reusable marts with JOINs, aggregation, LAG, NTILE and ranking.

  5. 05

    Explore

    Connect Looker filters, cross-filtering and threshold formatting.

BigQuery evidence

Inspect the source schema

Schema inspection established the available fields, nested application structure and data types before transformation logic was written.

BigQuery schema showing loan fields and the nested application record
BigQuery evidence

The image will appear when the corresponding site asset is available.

Validated grain
270,299 loans
Geographic coverage
51 jurisdictions represented
Mapping reference
52 mapping entries
Analytical delivery
7 reusable tables

Window functions support cohort comparisons and descriptive review priorities. The SQL archive includes source checks, transformations and presentation outputs.

Representative BigQuery SQLInspect the loan-level risk logic
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

Turn findings into review questions

The result is a documented review framework. These are proposed analytical next steps, rather than evidence of changes implemented by a lender.

EXP

Investigate the threshold

The $3.08B status-based amount exceeds the illustrative $3.00B level.

Proposed next step

Use connected status, state and purpose views to explain the signal before proposing a response.

GEO

Separate scale from priority

California has the largest amount; New York and New Jersey are Priority 1 in the combined quartile framework.

Proposed next step

Review the top-five concentration alongside both exposure and loss-classified rates, rather than ranking priority by amount alone.

SEG

Compare volume and rate

Debt consolidation is the largest segment; small business has the highest loss-classified rate, at 9.31%.

Proposed next step

Treat concentration and adverse-status rates as different questions. Compare like-for-like denominators and cohort maturity.

TIME

Read years as origination cohorts

The 2019 cohort contributes $796.72M to the not-Fully-Paid amount in this snapshot.

Proposed next step

Add repeated performance observations and repayment data before assessing migration, deterioration or forecasting.

Risk-monitoring architectureA traceable review path
Executive signalStatus-based amount
Diagnostic layerGeography, status, purpose and vintage
Management actionTriggers, review and targeted controls

Scope and interpretation

Scope and interpretation

A descriptive analysis of a training snapshot, with transparent definitions and a clear boundary between findings and proposed extensions.

Training contextTraining data, practical analytical delivery

It uses a training dataset and does not represent a live lender portfolio.

Threshold meaningAn illustrative $3.00B review threshold

It is not a regulatory capital limit or a universal industry benchmark.

Definition sensitivityOriginal loan amounts, classified by status

The source supplies loan_amount rather than verified remaining principal. Charged Off and Default amounts do not account for recoveries.

Production extensionOrigination cohorts, not a balance time series

The 2012–2019 cohorts have different ages. Repeated snapshots and repayments are needed for migration, loss development and forecasts.

Continue exploring

Explore more data work

Explore the wider analytics portfolio or review the experience, tools and credentials behind this project.

Contact

Need a clearer answer from complex data?

I bring careful validation, clear definitions and practical reporting to analytical questions. Let’s discuss your data and the decisions it needs to support.