Finance
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.
Growth counts approved loans, risk counts losses, and nobody can say which channels bring borrowers who actually repay.
Paste this into your AI assistant. It builds the Finance model's Data Marts in your data warehouse from your lending and marketing data, and shows which acquisition channels bring borrowers who stay profitable after late payments and write-offs.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /data-models/finance.md. The task: build the Finance model with its Customer, Loans and Repayments Data Marts, then show me which acquisition channels bring the most profitable borrowers once late payments and write-offs are counted. Tag the links you give me with utm_campaign=ai-task and utm_term=m1. Use what you know about my context and goals
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: Loans holds every application and what was funded, and Repayments shows how each funded loan is being paid back.
How this business is managed
- Credit Risk — approves applicants in line with their risk and keeps missed payments and write-offs within appetite.
- Finance — keeps what lending earns well above what deposits cost, and grows new lending to plan.
- Product — turns sign-ups into funded customers who use their accounts every month.
Key metrics
- On-time payments — Repayments · On-Time Payment Rate by Repayments · Due Date.
- Delinquency — Repayments · 30+ DPD Rate (loans).
- Net interest — Balances (monthly) · Interest Earned less Balances (monthly) · Interest Paid, by Balances (monthly) · Month.
- Origination — Loans · Funded Loans and Loans · Funded Amount by Loans · Funded At.
Key Data Marts
- Loans — every application, the decision, the amount funded and its APR.
- Repayments — every instalment, and how late it was paid.
- Balances (monthly) — what each account earns or costs the business, month by month.
- Customer — who the borrowers are, where they came from and their risk band.
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?
To connect live data to your data model, plug in the data sources you need
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. Whether a balance is lending or deposits comes from the account's product.
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; the missed instalments that put each loan here are in Repayments.
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. |
recovered_per_action | FLOAT | Recovered per Action | Calculated Field. Dollars recovered per collections action: total recovered over the number of actions taken. Actions that reached nobody or recovered nothing stay in the count, so this is the yield of the effort, not of the successes. |
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; null for an application that was never funded. |
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. |
funded_loans | INTEGER | Funded Loans | Calculated Field. Number of loans actually funded (money paid out). Approved applications the borrower never drew down are not counted. |
approval_rate | FLOAT | Approval Rate | Calculated Field. Share of decided applications approved, as a fraction (0.74 = 74%): approved over approved plus declined. Withdrawn applications never got a decision and are left out of both sides. |
avg_loan_size | FLOAT | Average Loan Size | Calculated Field. Average funded amount per funded loan, in dollars. Total funded over Funded Loans; applications that were declined, withdrawn or never drawn down do not dilute it. |
weighted_avg_apr | FLOAT | Weighted Avg APR (funded) | Calculated Field. APR weighted by funded dollars, as a fraction (0.17 = 17%) — the contract rate at funding, before losses. Covers every loan ever funded, including paid-off and charged-off ones; it is not interest earned over average balance. Declined and unfunded applications carry a quoted APR but no dollars, so they do not move it; a plain average of APR would count them. |
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 | Product family: BNPL = buy now, pay later; card = credit card; loan = instalment loan (personal or auto); deposit = savings or checking account. |
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. What was done to recover a loan that fell behind is in Collections.
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). |
on_time_payment_rate | FLOAT | On-Time Payment Rate | Calculated Field. Share of due instalments paid in full on time, as a fraction (0.97 = 97%). Instalment products only (BNPL, instalment loans); credit cards carry no schedule. Counts instalments due by today paid in full on or before the due date; a partial payment counts as not on time. |
delinquency_rate_30 | FLOAT | 30+ DPD Rate (loans) | Calculated Field. Share of loans that went 30+ days past due, as a fraction (0.05 = 5%). Instalment products only (BNPL, instalment loans); credit cards carry no schedule. Counted per loan, not per instalment: a loan with several late instalments counts once; loans with nothing due yet are left out. |
dpd_bucket | STRING | DPD Bucket | Calculated Field. Delinquency bucket of the instalment: Not yet due, Current, 30-59, 60-89 or 90+ days past due. Instalment products only (BNPL, instalment loans); credit cards carry no schedule. A row-level formula that behaves as a dimension: group and filter by it. |
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.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.