Loan Tape
For lenders seeking to raise debt capital or acquire term sheets from capital providers, putting together a Loan Tape is usually the first step.
This SQL query is designed to generate a summary of outstanding balances, repayment rates and delinquency on each account, which you would then JOIN with information on customer characteristics. The latter could live inside your CRM or in Canopy (if set up correctly).
This query compiles data from various tables to provide valuable insights into account and payment-related metrics. The report is structured to assist with financial decision-making and monitoring, particularly in a lending or financial management context.
Fields
CUSTOMER_ID
: This is the ID used to identify the account in your system.STATUS
: This field uses a CASE statement to determine the status of the account based on the outstanding amount (AMOUNT_OUTSTANDING). It assigns labels such as 'Paid in Full,' 'Delinquent,' 'Current,' or the account's actual status.ACCOUNT_STATUS
: This field represents the account's status.ACCOUNT_SUBSTATUS
: This field represents the substatus of the accountCOHORT
: Extract the month from the effective_at timestamp.PAYMENT_FREQUENCY
: This field represents the average payment frequency for the account.DAYS_PAST_DUE
: This field calculates the number of days past due for the account.TOTAL_REVOLVING_FUNDS
: This field calculates the total revolving fundsTOTAL_LATE_FEES_COLLECTED
: This field calculates the total late fees collected.MISSED_PAYMENTS
: Number of missed payments.CHARGE_OFF_DATE
: The charge-off date for accounts that have been charged off.CHARGE_OFF_AMOUNT
: The charge-off amount for accounts that have been charged off.ACCOUNT_ID
: This field represents the unique identifier for the loan.ADVANCE_DATE
: Effective date of the loan, converted to a date format.ORIGINAL_RECEIVABLE_BALANCE
: Calculates the original receivable balanceAMOUNT_OUTSTANDING
: Calculates the outstanding amount (principal)REPAYMENT_START_DATE
: Calculates repayment start date by selecting the minimum date between first_due_date from the loan_terms_statistics CTE and earliest_repayment from the loan_payment_statistics CTE.LAST_REPAYMENT_DATE
: Calculates the last repayment date by selecting the maximum date between last_due_date from the loan_terms_statistics CTE and latest_repayment from the loan_payment_statistics CTE.TERM
: Represents the total number of terms associated with the loanREMAINING_TERM
: Calculates the remaining terms of the loanEXPECTED_PAYOFF_DATE
: Expected payoff date of the loanTOTAL_PRINCIPAL_COLLECTED
: Calculates the total principal collectedTOTAL_PRINCIPAL_OFFSET
: Calculates the total principal offsetTOTAL_FEE_COLLECTED
: Calculates the total fee collectedTOTAL_FEE_OFFSET
: Calculates the total fee offset
Main goals
- The primary objective of this query is to create a detailed financial report encompassing various aspects of account and payment data.
- It calculates and presents critical financial metrics, such as delinquency status, charge-off dates, payment frequencies, and more.
- This report aids in assessing the financial health of accounts and tracking important financial events.
SQL Template
/*
This payment_split_json_records CTE starts by extracting individual JSONB array elements from
the line_item_relationships column in the line_items table. It's specifically interested in
line items of types 'PAYMENT' or 'DEBIT_OFFSET' that have a status of 'VALID'.
*/
WITH payment_split_json_records AS (
select
jsonb_array_elements(line_item_relationships) field,
valid_at as effective_at,
external_line_item_id as payment_external_line_item_id,
account_id,
line_item_type
from
data_direct.line_items
where
line_item_type IN ('PAYMENT', 'DEBIT_OFFSET')
and line_item_status = 'VALID'
),
/*
This CTE transforms the JSONB array elements from the previous CTE into structured transaction
data. It calculates several attributes, including original amount, transaction ID, paid-down
client ID, type, parent external line item ID, and loan ID based on the type of line item.
*/
splits_as_transactions AS (
select
'PAYMENT_SPLIT' as line_item_type,
payment_external_line_item_id,
(psjr.field ->> 'split_amount_cents') :: int as original_amount_cents,
(psjr.field ->> 'line_item_client_id') as transaction_id,
(psjr.field ->> 'paid_down_line_item_client_id') as paid_down_client_id,
(psjr.field ->> 'paid_down_line_item_type') as paid_down_type,
(
psjr.field ->> 'paid_down_line_item_parent_client_id'
) as paid_down_parent_external_line_item_id,
CASE
(psjr.field ->> 'paid_down_line_item_type')
WHEN 'AM_INTEREST' THEN (
psjr.field ->> 'paid_down_line_item_parent_client_id'
)
WHEN 'LOAN' THEN (psjr.field ->> 'paid_down_line_item_client_id')
END AS loan_id,
effective_at,
account_id,
line_item_type AS original_payment_type
from
payment_split_json_records psjr
),
/*
This CTE filters and selects only those records with an original_payment_type of 'PAYMENT',
effectively filtering out 'DEBIT_OFFSET' records. It focuses on payments made on accounts.
*/
collected_splits AS (
select
*
from
splits_as_transactions
where
original_payment_type = 'PAYMENT'
),
/*
This CTE calculates statistics for debit offset splits. It computes the sum of the
original_amount_cents for debit offset splits, grouping by account_id, loan_id, and
paid_down_type.
*/
offset_splits_statistics AS (
select
sum(original_amount_cents) offset_cents,
loan_id,
paid_down_type
from
splits_as_transactions
where
original_payment_type = 'DEBIT_OFFSET'
group by
account_id,
loan_id,
paid_down_type
),
/*
This CTE calculates statistics related to loan payments. It determines the latest and
earliest repayment dates for loans, grouping by loan_id.
*/
loan_payment_statistics AS (
select
loan_id,
max(effective_at) latest_repayment,
min(effective_at) earliest_repayment
from
collected_splits sat
where
loan_id is not null
group by
loan_id
),
/*
This CTE calculates statistics for late fee payments. It computes the sum of
original_amount_cents for payments associated with late fees, grouping by account_id.
*/
late_fee_payment_statistics AS (
select
account_id,
SUM(original_amount_cents) collected
from
collected_splits
where
paid_down_type = 'MANUAL_FEE'
group by
account_id
),
/*
This CTE calculates statistics related to loan terms and payments. It calculates various
metrics, including the number of missed payments, terms due, and others, for each loan.
*/
loan_terms_statistics AS (
select
external_line_item_id as loan_id,
min(cycle_exclusive_end) as first_exclusive_end_date,
max(cycle_exclusive_end) as last_exclusive_end_date,
max(min_pay_due_at) as last_due_date,
min(min_pay_due_at) as first_due_date,
sum(
case
when paid_on_time = false then 1
else 0
end
) missed_payments,
sum(
case
when clock_timestamp() > min_pay_due_at then 1
else 0
end
) terms_due,
count(1) total_terms_count
from
data_direct.am_forecast_public
group by
external_line_item_id
),
/*
This CTE calculates statistics related to account-level payments, including the count of
payments made in each month.
*/
account_payment_statistics AS (
select
account_id,
count(1) AS payments_in_month,
EXTRACT(
MONTH
FROM
payments.effective_at
)
from
data_direct.line_items payments
where
payments.line_item_type = 'PAYMENT'
AND payments.line_item_status = 'VALID'
group by
payments.account_id,
EXTRACT(
MONTH
FROM
payments.effective_at
)
),
-- This CTE calculates the average monthly payment frequency for each account.
account_monthly_payment_freq AS (
select
account_id,
avg(payments_in_month) avg_payment_in_month
from
account_payment_statistics
group by
account_id
),
-- This CTE retrieve all available loan
all_loans AS (
SELECT
*
FROM
data_direct.line_items
where
line_item_type = 'LOAN'
and line_item_status = 'VALID'
),
-- This CTE retrieve all available accounts
all_accounts AS (
SELECT
*
FROM
data_direct.accounts
),
/*
This CTE combines various loan-related data to create a comprehensive view of each loan's status,
terms, and collected amounts.
*/
loan_tapes AS (
select
external_line_item_id AS LOAN_ID,
account_id,
effective_at :: date AS ADVANCE_DATE,
original_amount_cents / 100.0 AS ORIGINAL_RECEIVABLE_BALANCE,
principal_cents / 100.0 AS AMOUNT_OUTSTANDING,
LEAST(lts.first_due_date, lps.earliest_repayment) :: date AS REPAYMENT_START_DATE,
GREATEST(lts.last_due_date, lps.latest_repayment) :: date AS LAST_REPAYMENT_DATE,
lts.total_terms_count AS TERM,
lts.total_terms_count - lts.terms_due AS REMAINING_TERM,
lts.last_due_date :: date AS EXPECTED_PAYOFF_DATE,
(
loans.original_amount_cents - loans.principal_cents
) / 100.0 AS TOTAL_PRINCIPAL_COLLECTED,
COALESCE(ABS(osls.offset_cents) / 100.0, 0) AS TOTAL_PRINCIPAL_OFFSET,
loans.total_interest_paid_to_date_cents / 100.0 AS TOTAL_FEE_COLLECTED,
COALESCE(ABS(osfs.offset_cents) / 100.0, 0) AS TOTAL_FEE_OFFSET
from
all_loans loans
left join loan_payment_statistics lps ON lps.loan_id = loans.external_line_item_id
left join loan_terms_statistics lts ON lts.loan_id = loans.external_line_item_id
left join offset_splits_statistics osls ON osls.loan_id = loans.external_line_item_id
and osls.paid_down_type = 'LOAN'
left join offset_splits_statistics osfs ON osfs.loan_id = loans.external_line_item_id
and osfs.paid_down_type = 'AM_INTEREST'
),
/*
This CTE calculates delinquency metrics, including the number of days past due,
missed payments, and charge-off dates, based on the delinquency_report and statements tables.
*/
delinquency_metrics AS (
SELECT
account_id,
delinquent_as_of_date,
dr.days_past_due,
count(1) over (partition by account_id) missed_payments,
rank() over (
partition by account_id
order by
effective_at desc
) ordering -- if the account has been delinquent for delinquent as of + 180 days, it should be charged off
,
CASE
WHEN delinquent_as_of_date IS NOT NULL
AND (delinquent_as_of_date + INTERVAL '180 days') <= clock_timestamp() THEN delinquent_as_of_date + INTERVAL '180 days'
END AS charge_off_date
FROM
data_direct.delinquency_report dr
JOIN data_direct.statements s using(account_id)
WHERE
dr.delinquent_as_of_date is not null
and s.min_pay_due_at >= dr.delinquent_as_of_date
AND s.min_pay_due_at <= clock_timestamp()
)
SELECT
aa.external_account_id AS CUSTOMER_ID,
CASE
WHEN lt.AMOUNT_OUTSTANDING = 0 THEN 'Paid in Full'
WHEN aa.account_status_subtype = 'DELINQUENT' THEN 'Delinquent'
WHEN aa.account_status = 'ACTIVE' THEN 'Current'
else aa.account_status
END AS STATUS,
aa.account_status AS ACCOUNT_STATUS,
aa.account_status_subtype AS ACCOUNT_SUBSTATUS,
TO_CHAR(aa.effective_at, 'month') AS COHORT,
ampf.avg_payment_in_month AS PAYMENT_FREQUENCY,
COALESCE(dr.days_past_due, 0) AS DAYS_PAST_DUE,
aa.credit_limit_cents / 100.0 AS TOTAL_REVOLVING_FUNDS,
COALESCE(ABS(lfps.collected / 100.0), 0) AS TOTAL_LATE_FEES_COLLECTED -- using the delinquency report, when the account has a delinquent date,
-- we can capture how many statements with due dates that have passed since the delinquent date
-- to measure the number of missed payments
,
(
CASE
WHEN dr.delinquent_as_of_date IS NULL THEN 0
ELSE dr.missed_payments
END
) AS MISSED_PAYMENTS,
(
CASE
WHEN dr.charge_off_date IS NOT NULL
AND aa.account_status_subtype = 'CHARGE_OFF' THEN dr.charge_off_date
END
) AS CHARGE_OFF_DATE -- When the account the MCA belongs to has been charged off, then
-- the outstanding MCA balance should be charged off
,
(
CASE
WHEN dr.charge_off_date IS NOT NULL THEN lt.AMOUNT_OUTSTANDING
END
) AS CHARGE_OFF_AMOUNT,
lt.*
FROM
loan_tapes lt
join all_accounts aa on aa.account_id = lt.account_id
join account_monthly_payment_freq ampf on ampf.account_id = aa.account_id
left join delinquency_metrics dr on dr.account_id = aa.account_id
and dr.ordering = 1
left join late_fee_payment_statistics lfps on lfps.account_id = aa.account_id
order by
aa.external_account_id;
-- REPAYMENT_START_DATE
-- The earliest of two values. First payment made towards the Loan (MCA),
-- or the due date of the first amortized due date of the Loan (MCA)
-- LAST_REPAYMENT_DATE
-- The latest of two values. Last payment made towards the Loan (MCA),
-- or the due date of the last amortized due date of the Loan (MCA)
Sample Data
customer_id | status | account_status | account_substatus | cohort | payment_frequency | days_past_due | total_revolving_funds | total_late_fees_collected | missed_payments | charge_off_date | charge_off_amount | loan_id | account_id | advance_date | original_receivable_balance | amount_outstanding | repayment_start_date | last_repayment_date | term | remaining_term | expected_payoff_date | total_principal_collected | total_principal_offset | total_fee_collected | total_fee_offset |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0150fb79-1a8e-4fe0-92f1-a7e5ad6bb0d0 | Paid in Full | CLOSED | PAID_OFF | april | 1.00000000000000000000 | 0 | 10000.0000000000000000 | 0 | 0 | can_319111 | 379b1d4d-2246-462a-b1f6-b6aed3f71428 | 2023-04-13 | 10000.0000000000000000 | 0.00000000000000000000 | 2023-04-14 | 2023-08-19 | 4 | 0 | 2023-08-19 | 10000.0000000000000000 | 0 | 0.00000000000000000000 | 0 | ||
10f61e25-f3dd-46cd-90d8-c7bfbe648ea8 | Current | ACTIVE | march | 1.00000000000000000000 | 0 | 100000.000000000000 | 0 | 0 | can_202207 | 59c7ca8a-8637-4e25-85ba-02f9ef7140d2 | 2023-03-03 | 1000.0000000000000000 | 1000.0000000000000000 | 2023-04-26 | 2023-04-26 | 1 | 0 | 2023-04-26 | 0.00000000000000000000 | 0 | 0.00000000000000000000 | 0 | |||
1f2766cd-6c0e-492d-9ca0-53623663082f | Delinquent | SUSPENDED | DELINQUENT | april | 1.00000000000000000000 | 36 | 2000.0000000000000000 | 0 | 2 | can_319987 | 87b80965-7472-4cdb-a36c-c71947ab8baf | 2023-04-20 | 2000.0000000000000000 | 1816.6700000000000000 | 2023-06-14 | 2025-05-14 | 24 | 20 | 2025-05-14 | 183.3300000000000000 | 0 | 0.00000000000000000000 | 0 | ||
2dea2490-9daf-4a82-8249-987b11ea6cbf | CLOSED | CLOSED | CHARGE_OFF | february | 1.00000000000000000000 | 153 | 100000.000000000000 | 0 | 2 | "can_201875 | 73bb7776-91b6-40f9-8ff0-6e06a6084b1f | 2023-02-24 | 400.0000000000000000 | 400.0000000000000000 | 2023-03-19 | 2023-06-19 | 4 | 0 | 2023-06-19 | 0.00000000000000000000 | 0 | 0.00000000000000000000 | 0 | ||
2ea7e969-5ecf-4f50-9fdb-b8955507babc | Current | ACTIVE | march | 1.00000000000000000000 | 176 | 100000.000000000000 | 0 | 1 | can_202214 | a9886d3f-db37-4d62-bca6-d929b6db1494 | 2023-03-01 | 10000.0000000000000000 | 10000.0000000000000000 | 2023-03-27 | 2024-02-25 | 12 | 6 | 2024-02-25 | 0.00000000000000000000 | 0 | 0.00000000000000000000 | 0 |
Updated 2 months ago