Finance Data Model
From acquisition and identity verification through loan origination to repayments and collections, with each borrower's risk tier shaping approval, pricing and eventual write-offs.
A consumer-lending business modeled end to end — from how customers are acquired and verified, through account funding and loan origination, to repayments, missed payments, and collections. Each customer carries an internal risk tier that shapes their whole journey: who gets approved, at what interest rate, how likely they are to fall behind, and how much the business ultimately writes off. Together the tables let you follow both the money and the risk across the entire loan book.
Example Questions
- Which acquisition channels bring in the most profitable borrowers once
late paymentsand write-offs are taken into account? - How much of the money lent out is the business likely to lose, and is that improving or worsening for recent
loans? - Do higher-risk
customerswho pay on time make up for the ones who default — is the pricing worth the risk?
Accounts
Every product a customer has opened — the funded relationships that turn a sign-up into an active customer. Tracks how each holding was activated, its current balance, and whether it is still active, dormant, frozen, closed, or written off. This is where you see who actually became a funded customer and how healthy those relationships are.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
account_id | STRING | Account ID | PK. Unique account identifier. |
customer_id | STRING | Customer ID | Owning customer. FK to Customer |
product_id | STRING | Product ID | Product held in this account. FK to Product |
opened_at | DATE | Opened At | Date the account was opened. Only customers with kyc_status = passed open accounts. |
status | STRING | Status | Current account status. One of active / dormant / frozen / closed / charged_off. |
current_balance | NUMERIC | Current Balance | Current account balance. |
activated_at | DATE | Activated At | First funding / first card use; ≥ opened_at; null if never activated. |
is_active | BOOLEAN | Is Active | Pure derivation: is_active = (status = 'active'). Not drawn independently. |
Relationships
| Related data mart | On | Cardinality | Meaning |
|---|---|---|---|
| Customer | customer_id = customer_id | N:1 | The customer who holds this account. |
| Product | product_id = product_id | N:1 | The product this account was opened on. |
Balances (monthly)
A month-by-month snapshot of every account's balance and the money it earns or costs the business — interest earned on lending, interest paid on deposits, and fee income. This is the view for understanding the earning power of the book over time and how balances build up or run down.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
snapshot_id | STRING | Snapshot ID | PK. Unique identifier for the monthly balance snapshot. |
account_id | STRING | Account ID | Account the snapshot belongs to. FK to Accounts |
month | DATE | Month | Calendar month of the snapshot. |
avg_balance | NUMERIC | Average Balance | Average balance over the month. Carries month-over-month persistence per account (a random walk, not IID noise). |
interest_earned | NUMERIC | Interest Earned | Interest income the bank earns on the account. Non-zero for card / loan / BNPL products (APR × outstanding balance); ~0 for deposit. |
interest_paid | NUMERIC | Interest Paid | Interest the bank pays out to the customer. Non-zero for deposit products (deposit APY × avg_balance); ~0 for card / loan / BNPL. |
fees | NUMERIC | Fees | Fee income for the month; scales with avg_balance / activity, not an independent draw. |
Relationships
| Related data mart | On | Cardinality | Meaning |
|---|---|---|---|
| Accounts | account_id = account_id | N:1 | The account this month of balances belongs to. |
Collections
Every action taken to recover money from loans that have fallen behind — reminders, calls, restructures, and hand-offs to agencies — and what came of each. Tracks how much is recovered and how effective different approaches are once a loan is delinquent or written off. This is the last line of defense on losses.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
action_id | STRING | Action ID | PK. Unique identifier for the collections action. |
loan_id | STRING | Loan ID | Delinquent loan being worked; rows exist only for loans that reached ≥30 DPD or is_charged_off. FK to Loans |
action_ts | TIMESTAMP | Action Time | When the action was taken. |
action_type | STRING | Action Type | One of reminder / call / restructure / agency_handoff. Early-stage (reminder/call) at low DPD; late-stage (restructure/agency_handoff) once charged off. |
outcome | STRING | Outcome | One of promise_to_pay / paid / no_contact / dispute. |
amount_recovered | NUMERIC | Amount Recovered | Money recovered by this action; bounded by remaining balance. Cumulative recovery per charged-off loan stays well under 100% and decays with time since charge-off. |
Relationships
| Related data mart | On | Cardinality | Meaning |
|---|---|---|---|
| Loans | loan_id = loan_id | N:1 | The delinquent loan being collected on. |
Customer
Everyone the business has taken on as a borrower, with the details that drive every downstream decision: how they were acquired, whether they passed identity and eligibility checks, their credit standing at sign-up, an internal risk tier, and whether they went on to fund an account. The starting point for understanding who your customers are and where they come from.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
customer_id | STRING | Customer ID | PK. Unique customer identifier. |
signup_date | DATE | Signup Date | Date the customer signed up. |
kyc_status | STRING | KYC Status | passed / pending / rejected. Gates account opening — only passed customers get funded accounts. |
risk_band | STRING | Risk Band | Internal risk tier — the model's central conditioning variable. One of prime / near_prime / subprime / deep_subprime. Drives loan approval odds, APR, delinquency (DPD) and charge-off downstream. |
credit_score | INTEGER | Credit Score | Credit score at onboarding (~300–850). Consistent with risk_band (prime high, deep_subprime low). |
acquisition_channel | STRING | Acquisition Channel | Channel that brought the customer in (e.g. organic, paid_search, paid_social, referral, partner). |
region | STRING | Region | Customer's geographic region. |
is_funded | BOOLEAN | Is Funded | Activation flag — the source of truth for whether the customer ever funded an account (only KYC-passed customers can be funded). Accounts derives its activated_at from this: a customer is funded iff they have ≥1 account with a non-null activated_at (the two are kept consistent by construction). |
Loans
Every loan application and what happened to it — approved, declined, or withdrawn — through to how much was actually funded and at what rate. Captures the full underwriting funnel, the reasons applications are turned down, and the pricing applied to each borrower based on their risk. This is the origination story of the loan book.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
loan_id | STRING | Loan ID | PK. Unique loan identifier. |
customer_id | STRING | Customer ID | Borrowing customer. FK to Customer |
product_id | STRING | Product ID | Loan product applied for. FK to Product |
applied_at | DATE | Applied At | Date the loan was applied for. |
decision | STRING | Decision | approved / declined / withdrawn. Approval odds conditioned on the customer's risk_band. |
approved_amount | NUMERIC | Approved Amount | Amount approved at underwriting; null unless decision = approved. |
funded_amount | NUMERIC | Funded Amount | Amount actually funded; ≤ approved_amount; null unless funded. Approved → funded is the pull-through rate. |
apr | FLOAT | Apr | APR on the loan. Seeded from the product's rate-card apr and adjusted by a risk-based spread (higher for riskier bands). |
term_months | INTEGER | Term Months | Loan term; selected from the product's stated term, not drawn independently. |
funded_at | DATE | Funded At | Date funded; ≥ applied_at; null unless funded. |
status | STRING | Status | Current loan status. One of current / delinquent / charged_off / paid_off. |
decline_reason | STRING | Decline Reason | Adverse-action reason (ECOA/Reg B); populated only when decision = declined. One of insufficient_credit_history / debt_to_income_too_high / delinquent_credit_obligations / income_verification_failed / fraud_flag. Weighted by risk_band. |
Relationships
| Related data mart | On | Cardinality | Meaning |
|---|---|---|---|
| Customer | customer_id = customer_id | N:1 | The customer who applied for this loan. |
| Product | product_id = product_id | N:1 | The lending product applied for. |
Product
The catalog of products the business offers — deposits, cards, loans, and buy-now-pay-later — each with its headline rate and standard term. For deposits the rate is what the business pays the customer; for credit products it is what the customer is charged. This is the lookup that gives every account and loan its pricing context.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
product_id | STRING | Product ID | PK. Unique product identifier. |
name | STRING | Name | Product display name. |
product_type | STRING | Product Type | deposit / card / loan / BNPL. |
apr | FLOAT | Apr | Rate-card reference rate. Meaning depends on product_type: for deposit it is the APY paid to the customer (low, ~0.5–4%); for card / loan / BNPL it is the rate charged to the customer. Loans.apr is seeded from this and adjusted by risk-based spread. |
term_months | INTEGER | Term Months | Stated product term in months. (BNPL "Pay in 4" is modeled here as a short months-denominated term — a deliberate simplification, not real biweekly installments.) |
Repayments
The repayment schedule for every funded loan and how each installment actually played out — paid on time, paid late, or missed. Tracks how far behind each loan falls, the principal still outstanding, and the point at which a loan is written off. This is where the health of the loan book, and the losses building in it, become visible.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
repayment_id | STRING | Repayment ID | PK. Unique repayment identifier. |
loan_id | STRING | Loan ID | Loan this repayment belongs to. FK to Loans |
due_date | DATE | Due Date | Date the payment is due. The full set of rows amortizes the loan's funded_amount at its apr over term_months. |
paid_date | DATE | Paid Date | Date the payment was made; null if unpaid. |
due_amount | NUMERIC | Due Amount | Scheduled principal + interest for the installment (from the amortization schedule). |
paid_amount | NUMERIC | Paid Amount | Amount actually paid (0 / partial / full). |
days_past_due | INTEGER | Days Past Due | DPD; progresses through the 0 / 30 / 60 / 90 / 120+ ladder per loan (roll-rate mechanics), not an independent draw. Conditioned on risk_band. |
outstanding_principal | NUMERIC | Outstanding Principal | Remaining principal after this installment — enables dollar-weighted roll-rate and vintage analysis. |
is_charged_off | BOOLEAN | Is Charged Off | True only once cumulative DPD crosses the charge-off threshold (FFIEC: 120 days installment / 180 days revolving). |
Relationships
| Related data mart | On | Cardinality | Meaning |
|---|---|---|---|
| Loans | loan_id = loan_id | N:1 | The loan this instalment is scheduled against. |
Transactions
Every payment, withdrawal, transfer, and card authorization flowing through customer accounts — the day-to-day activity that shows how engaged customers are and where fraud shows up. Each record carries the amount, merchant category, channel, whether it was declined, and the fraud assessment made at the time of authorization. The pulse of everyday customer behavior.
Fields
| Column | Type | Alias | Description |
|---|---|---|---|
txn_id | STRING | Transaction ID | PK. Unique transaction identifier. |
account_id | STRING | Account ID | Account the transaction belongs to. FK to Accounts |
txn_ts | TIMESTAMP | Transaction Time | When the transaction occurred. |
txn_type | STRING | Transaction Type | One of purchase / atm_withdrawal / transfer / direct_debit / refund / fee. |
mcc | STRING | MCC | Merchant category code. Typical amount and implied interchange vary by MCC. |
amount | NUMERIC | Amount | Transaction amount. |
currency | STRING | Currency | Currency; consistent with the customer's region. |
is_declined | BOOLEAN | Is Declined | Whether declined. Overall approval ~85–95%; declines skew to low/mid fraud_score (false positives) plus high-score fraud blocks. |
fraud_score | FLOAT | Fraud Score | Model score at authorization (0–1). |
is_confirmed_fraud | BOOLEAN | Is Confirmed Fraud | Post-investigation label. Steeply correlated with high fraud_score; overall a low-basis-points share of volume. Together with fraud_score gives capture rate vs false-positive declines. |
channel | STRING | Channel | One of card_present / ecommerce / atm / online_banking / mobile. |
Relationships
| Related data mart | On | Cardinality | Meaning |
|---|---|---|---|
| Accounts | account_id = account_id | N:1 | The account the money moved through. |
- 1
Install the Import Model plugin
One plugin, installed once, in your own OWOX workspace.
- 2
Import this model
Point it at this bundle and it creates every data mart above, joins and all.
- 3
Plug in your data and destinations
Connect your own sources and send the results where your team already works.
4. Optional — customize as you wish. Rename a column, drop a mart, add your own: once it is imported it is yours, and nothing here syncs back.