View this page on GitHub – ChatGPT – “Commands” link bv loan: paste loan related text string bv app status: paste a listing of application statuses bv application: paste application table related text string bv wiki page: paste my personal wiki page (this page) – 0-9 – link 3 Business Days select * from dw_reporting_meta.add_business_days(current_date,3) -- Adds 3 business days to a given date – A – link (call) abandoned: from an Ops perspective an abandoned call is inbound call that has been placed in a call queue but was ended prior to reaching an agent Accelitas: Bank Account Validation vendor https://www.accelitas.com (?) DA: heard that the relationship with Accelitas is ending soon (~mid May, 2025) Accrued Interest Not Yet Paid DA: I have seen this definition: payoff - principal_balance + interest_accrued_today (all in loan_status_archive) ACH: Automated Clearing House ACH Daily Funding Report: dw_reporting_views.ach_daily_funding_report looks like this report/table feeds Looker dashboards ACH File Table: assume table that persist processed ACH file contents. Can join to this table to close in on loans that are officially originated/funded dw_reporting_bsf_ach.achfilerecord ACH Transactions ACH Credits: “Push” of funds from the Originator’s account at the ODFI to the Receiver’s account at the RDFI. A common ACH credit application is direct deposit of payroll. Originator initiates an Entry to push funds into a Receiver’s account Receiver’s account is credited Originator’s account is debited (this is considered the offset/settlement Entry) ACH Debits: ”Pull” of funds from the Receiver’s account at the RDFI to the Originator’s account at the ODFI. A common ACH debit application is payment of an insurance premium. Originator initiates an Entry to pull funds from a Receiver’s account Receiver’s account is debited Originator’s account is credited (this is considered the offset/settlement Entry) ACH Return File Table: assume ACH instructions that have failed with an ACH return code dw_reporting_bsf_ach.returnfilerecord ACH Settlement File: (Per Krithika) each record represents a NACHA file sent for the day (for a particular ACH transmission - Braviant has multiple transmissions per day for various reasons) Krithika: The process runs at 10:30 and 10:35 file_type = 1 is the sunup -> ccb cash flows file_type = 2 is the bcs –> ccb cash flows ACH Statuses: ChatGPT Info DA: output by ChatGPT and thought it may be informative here Here’s a side-by-side table showing the main ACH payment statuses and how Pending, Posted, Failed, and Returned fit into the lifecycle: Status When It Happens Description Common Causes ACH Network Involvement Outcome Pending Before processing begins or while in transit through the ACH system Payment has been initiated but not yet settled Normal waiting period for processing, bank cut-off times, weekends/holidays Yes (once submitted) Will typically move to Posted, Returned, or Failed Posted After settlement at the RDFI (credit/debit posted) Funds have been posted to the receiver’s account; still within possible return/reversal windows Successful processing Yes Payment appears complete, but could later be Returned or Reversed within network timeframes Failed Before the ACH network accepts the entry for processing Transaction could not be submitted or validated Invalid routing/account format, missing authorization, originator/system validation error No (stopped before entering ACH clearing) Payment never leaves the originator; no ACH entry created Returned After the entry is submitted (and possibly posted), but rejected by the RDFI RDFI sends the entry back to the ODFI with a NACHA return code Insufficient funds (R01), account closed (R02), no account (R03), unauthorized consumer debit (R10/R11/R07), etc. Yes Funds are sent back to the originator; official ACH return code logged; original entry effectively undone Reversed Originator/ODFI discovers an erroneous original entry (typically within 5 banking days of settlement) ODFI/Originator sends a reversing entry (marked “REVERSAL”) to correct an error; Receiver should be notified Duplicate file/entry, wrong account, wrong amount, wrong effective date Yes (originator-initiated reversing entry) If reversal posts, it offsets the original (net ≈ 0); if it can’t post, the RDFI may return the reversal Acronyms: came across this archived wiki page for acronyms. Acronyms ACW: After Call Work Time Advancements: dw_reporting_bsf_identity.loan_advancement_transaction ANI: From LLM In the context of call center operations, ANI stands for “Automatic Number Identification.” It is a technology used by telecommunications companies and other organizations that handle large volumes of phone calls to automatically identify incoming or outgoing numbers dw_reporting_five9.call_log.ani: the number calling inbound to the call center Application: dw_reporting_views.fact_application: current state application “fact” table - will be replaced by dw_core.fact_application dw_core.fact_application: new version of fact_application and will reference various dimension tables dw_reporting_bsf_origination.application: BSF origination schema for application data. this should be data synced/refreshed from LoanPro dw_reporting_objects.application_entity: not sure what this schema is but another application data source includes application_status and application_sub_status new dw_core.fact_applicationtable assumptions and business rules: application_withdrawn will be 1 if the app is withdrawn during the app lifecycle and downstream if the app loan is withdrawn or not funded successfully dw_reporting_views.dim_duplicate_application_flg: indicates duplicate applications Application Source: BSF listing: dw_reporting_bsf_common.application_source_type Leads (assume at this time, this is only PingTree type leads) Affiliates Refinance Longform Reapply Offerwall Application Status & Sub-Status see cacher page here (title: Useful DB Snippets) for sql and listing: link Application Validation: dw_reporting_bsf_origination.application_validation: materialized view that holds errors thrown during the processing of the application Nic told me about this Not sure what processing we’re talking about. Need to get more details APR: Annual Percentage Rate dw_reporting_bsf_identity.loan.apr: I think this is one way to get the APR Average Contacts per Customer: from ChatGPT The average number of interactions (calls, emails, chats, etc.) a customer has with the call center over a specific period of time Average Speed of Answer (ASA): from ChatGPT ASA measures the average amount of time it takes for a call to be answered by an agent after the call enters the queue (Total Waiting Time for Answered Calls) / (Number of Answered Calls) Asserted & Effective Bi-Temporal Columns Used in various tables that write multiple row (or row instances) over time IMPORTANT: some context from Tejas effective: provides a sense of history (when was this record in effect from a time perspective) asserted: provides a sense of if this record was valid (good) or not (bad) DA: so assume if I wanted a historical record of valid rows I would NOT filter out by effective but filter out by asserted This method to compare bi-temporal columns seems to be much more efficient: upper(l.asserted) = 'infinity' and upper(l.effective) = 'infinity' OR now()<@ l.asserted and now()<@ l.effective At any given time only 1 row should be valid From KG: if you want data as of end of day 12/11, it has to be ‘2023-12-12 05:00:00’::timestamptz <@ l.asserted – B – link Bank Account Transactions: From Lara: you can check bank transactions in adhoc/finance schema in this table finance.accounting_bank_account_transactions re: Finance schema see also finance.fft_clean table: does fft = Finance Fact Transaction? Basepoint: assume this is Basepoint Capital. also assume Basepoint participates in Braviant loans batch jobs cron jobs? See Confluence jobs: https://balancecredit.atlassian.net/wiki/spaces/BSF/pages/779617231/Batch+Job+Processes BAU: assume this means Business As Usual BAV: Bank Account Verficiation Currently using DecisionLogic but now migrating over time to EDGE BC: Balance Credit (Note**: SunUp Financial, LLC is a lending entity for BC lending products) BCS: assume BCS is short for B Credit Solutions B Credit Solutions, LLC: Braviant legal entity that buys back loans from CC Bank triggered by certain events (assume one event is a charge off) some notes: also see this confluence page B-Credit Solutions(this is us) is a Guarantee Company that will purchase the loan when it meets the Default Definition of the program(different concept as Delinquency) and becomes the Owner of the loan. When the B-Credit Solutions, the Guarantee Company becomes the owner of the loan, SunUp, the Sub-Servicer transfers the loan tape to SunUp, the Collections Company, who will conduct Collections for the Guarantee Company(B-Credit Solutions). The Servicer(TLS) and Sub-Servicer no longer have any obligations to the loan. Bi-Temporal Columns: see Asserted & Effective Bi-Temporal Columns blacklist: list of bad bank routing numbers BSF db: origination.badbank also see: graylist on this page select bad.product, bad.bank_name, bad.routing_number from origination.badbank bad where now()<@bad.asserted and now()<@bad.effective order by bad.product, bad.bank_name, bad.routing_number; BK: Bankruptcy NOTE: The Ops Team refunds (? Advancement | Overpayment) payments that were submitted after the official Bankruptcy date has been declared (where to do we store this date?) BSF: Braviant Service Framework bsf-prod-reporting.tech.braviant.com: BSF Prod Reporting database – C – link C & D: Cease and Desist CartStack: CartStack is a software that helps businesses recover abandoned carts and lost customers. It offers a variety of features, including: Abandoned cart emails: Automatically sends targeted emails to customers who left items in their cart SMS reminders: Automatically sends SMS text messages to customers who left items in their cart Push notifications: Automatically sends web push notifications to customers who left items in their cart Exit Intent Popups: Displays targeted messages to customers who are attempting to abandon their cart Real-time tracking: Captures email addresses, phone numbers, and other contact data as it’s typed on any page Advanced reporting: Tracks and measures engagement with emails to accurately attribute recovered sales Custom domain sending: Allows users to send emails from CartStack’s trusted domains or their own custom domain CCB: Capital Community Bank CDD & EDD: Customer Due Diligence and Enhanced Due Diligence part of the BSA (Bank Secrecy Act) CF: Custom Field (assume custom in LoanPro) CIP Customer Identification Program Charge Off: loans are charged off when they reach 60 days past due Different “Types”/Categories of Charge Offs Accounting Charge Off: sometimes referred as a Finance or GAAP charge off loan is considered charged off when it his 61 days past due and stays charged off even if days past due drops under 61 days from an Ops perspective, we still attempt to work/collect on the loan Ops Charge Off: loan is considered charged off from an operational perspective (ie. we cease to drive income on the loan) indicated by various Closed substatuses: Debt Sale, Discharged, Settlement (is this Debt Settlement?) Bank Charge Off: charged off from a CC Bank perspective. This occurs at a buyback transaction Can find this information in a multiple places: dw_reporting_views.fact_application: Fact Application table loan_num: the loan_id field loan_charge_off_date: the date the loan was deemed charged off (must be a process that triggers this) loan_charge_off_principal_amount: the principal amount charged off (only remaining principal amount is charged off) Gross Charge Off: assume this is the principal amount balance at the time of charge off Net Charge Off: assume this would include payments that happen to come in after the charge off date (e.g. customer continues to pay after that date) Loan Charge Off Dates dw_reporting_views.loan_charge_off_dates: view charge off date and charge off principal amount by loan id also see Purchase Date below Chorus Credit: assume old product line that has been or is being phased out NOTE: Chorus Credit loan data reside in these schemas: dw_reporting_chorus_lp dw_reporting_chorus_view Clarity: an Experian company that provides visibility into underbanked, near‑prime, and subprime consumers by collecting alternative financial data Types of Clarity product offerings: Credit Reporting & Scoring Risk Management & Fraud Tools Identity Verification & Behavioral Insights Analytics & Integration Services Pertinent Clarity scores: CCR Score (Clarity Credit Risk Score): Predicts the likelihood of credit risk (i.e., charge-off, default, or serious delinquency) CCD Score (Clarity Credit Demand Score): Predicts the likelihood that a consumer will seek and obtain new credit in the near future Other Terms: CFA: Clear Fraud Attributes CFI: Clearance Rate: refers to the percentage of delinquent accounts or outstanding debt that is resolved or “cleared” from the collections process within a specific period. This includes accounts that are brought back to current status (cured), fully paid off, settled for a reduced amount, or written off as uncollectible Clearing: (general banking industry definition) Clearing is all of the steps involved in transferring funds ownership from one party to another except for the final step, which is settlement. web link Closed Date: assume loan closed date dw_reporting_lp.loan_settings_entity.closed_date Collections High Level Overview (background from Jordan D.) We begin collections at 1 DPD, with both calls and emails/SMS Calls are made by our dedicated collections team (15 agents) with BGO (collections and call center outsource partner) in Manila Emails and SMS are managed by Remitter and their Machine Learning playbook dictates the number of emails/SMS and their timing Remitter also hosts their own collections and settlement portal that we utilize All delinquent accounts are also scored every 2 weeks on Equabli’s propensity to pay model We utilize these scores to dictate call volume, and email/SMS messaging As well as settlement offers (when we get there) Customers continue to get a variety of calls and emails/SMS through debt sale (150 DPD) At 90 DPD customers are also provided a settlement offer The level of this settlement offer can vary from 30%-70% off The level of discount is determined via the customer’s Equabli propensity to pay score Currently we are not able to offer settlements any earlier than this, as CCB still owns the loan until 91 DPD and we do not have any agreement in place to be able to offer settlement offers while they own the loan (we are currently exploring this with CCB) Additionally, the Right to Cure Notice is sent at 91 DPD, and the Called Due Notice is sent 30 days after that (if the loan has not cured). Both of these must be complete to include the loan in debt sale. We are currently exploring moving up these notices. Once a loan makes it to 150 DPD, and is not on a payment plan or in another status that prevents its sale (Bankruptcy, Debt Management, etc.) they are included in the monthly Debt Sale Connections (Provenir): connections are used by your application to connect to external data (e.g. web service, Salesforce, database, message queue, file adapter) Containment from ChatGPT containment speaks to how effective call center operations handles customer inquiries without agent intervention (e.g. via an IVR) Containment: refers to the successful resolution of a customer inquiry without involving a live agent Containment Volumes: represent the total number of interactions that were successfully handled by a self-service system without escalation to an agent Containment Points: specific steps or areas within a self-service flow (e.g., IVR menu, chatbot sequence) where the containment happens Contract Date: assume origination date dw_reporting_lp.loan_setup_entity.contract_date this is the date on which interest starts accruing (see/as opposed to the Funding Date) assume this is equivalent to effective_date in other tables/contexts Cost Per Fund (CPF): What you pay to acquire a customer (funded customer?) For DM (Direct Mail): the cost to buy a prospective customer’s name/address and the cost to print and send the customer letter For Offer Wall Leads: pay a percentage of the loan’s funded principal For Ping Tree Leads: pay $X to purchase a customer lead Cost Per Fund (CPF) | General Notes on Costs: For different channels, costs break down into marketing and data costs example of marketing costs are USPS mailing costs for Direct Mail mailings example of data costs is data service provider spending (e.g. Emailage, Clarity, etc.) +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ | APP GENERATION PATH | LEAD/MARKETING COST | DATA COST | | | DATA SOURCE | DATA SOURCE | +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ | Pingtree Lead Path | dw_reporting_views.fact_leads_underwriting | dw_reporting_views.leads_data_cost | | | | (Need to adjust total leads data cost needs to include DL cost. Grant is working on it.) | +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ | Offerwall Lead Path | dw_core.fact_leads_offerwall | dw_core.fact_leads_offerwall | +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ | Direct Mail Path | dw_core.cost_per_letter_lookup | Need to identify data tables for these data cost types: | | | dw_core.dm_campaign_summary | | | | | Iovation | | | | Emailage | | | | Decision Logic | | | | EDGE | | | | Clarity | | | | | | | | (Tejas to identify) | +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ | Retention & Refi Paths | dw_core.retention_letter_summary | Need to identify data tables for these data cost types: | | | (Need to add letter cost) | | | | | Iovation | | | | Emailage | | | | Decision Logic | | | | EDGE | | | | Clarity | | | | | | | | (Tejas to identify) | +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ | Organic Path | Not applicable | Need to identify data tables for these data cost types: | | | | | | | | Iovation | | | | Emailage | | | | Decision Logic | | | | EDGE | | | | Clarity | | | | | | | | (Tejas to identify) | +------------------------+--------------------------------------------+--------------------------------------------------------------------------------------------+ Control File or Clarity Control File From an old Vincent wiki page: A control file is constructed with several Clarity Credit Reports that BalanceCredit purchase for a specific underwriting flow See: https://balancecredit.atlassian.net/wiki/spaces/PD/pages/469696527/Clarity+Control+File CSO: Credit Service Organization a type of business that helps consumers improve their credit rating, obtain credit, and manage debt. These services can include: Credit Repair Services Debt Management Plans Credit Counseling Loan Brokering: Braviant’s main role(?) Assume CSO loans are TX loans with an original owner of Redpoint looks there are some TN Redpoint loans as well sql gist Identify CSO?: dw_reporting_bsf_uwp.loan_contract_calculation.installment_loan_type = CSO Business Model CSO loans have low interest rates… … but large fees (escrow fees in Braviant parlance, e.g. payment_e) assume fees are paid to the lender upfront AND then Bravian collects that payment over time as the “escrow component” of each installment payment Example CSO Loan loan_id: 658288 (not a refi loan) TIL: Amount Financed (goes to customer): 1,500.00 CSO Fee on Application: 1,053.45 (part of finance charge) Principal Amount: (amount to customer = 1,500.00) + (prepaid finance charge = CSO fee = 1,053.45) = 2,553.45 Schedule payments: charge_amount = 523.70; charge_p = (varies according to amortization); charge_i = (varies according to amortization); charge_e = CSO per payment fee = 210.69 Credit Transactions: credits applied in LoanPro during servicing (see: Loan Credit Transaction) Credit Rate: DA: from the perspective of a Balance Credit loan, the credit rate is stored in these fields (elsewhere as well) dw_core.loan_tape.ccr_v1_score dw_core.loan_tape.ccr_v2_score other context: DA: I am not totally clear on the distinction between v1 and v2. I think v1 is the traditional Clarity credit score and v2 is an enhanced score that leverages additional data points and represents a more real time assessment Cure Rate: from Grok the percentage of delinquent accounts that are brought back to a current or non-delinquent status within a specific period. It measures the success of collections efforts in getting borrowers to make sufficient payments to “cure” their delinquency, typically by paying all past-due amounts or entering a repayment plan that restores the account to good standing current default: DA: doesn’t appear to be a standard industry term… so I assume it means currently in the state of default (?) Current Owner database column that I assume displays the current owner of a loan through the loan lifecycle (e.g. B Credit Solutions, LLC , CC Bank Direct, Landmark Strategy Group, LLC, Redpoint, SPV3, SunUp Financial) dw_reporting_bsf_identity.loan.current_owner Custom Fields: or Loan Custom Fields. Looks like custom field values set in LoanPro by Agents (or processes). DA: looks like a shitload of fields including multiple dates: e.g. debt_sale_date Table: dw_reporting_views.loan_settings_custom_fields Customer Entity: dw_reporting_lp.customer_entity customer records see other entity tables: dw_reporting_lp.address_entity Customer Type: DA: this seems to be generated value (comprised of a couple source data elements) that describe the different flavors of customers. This is from Jeff G. and how he uses it. Some may be added as we incorporate new types of sources New - DM New - Expired DM New - Other New - Lead Offerwall - Lead New - Refinance Previous - Refinance Previous – D – link Data Dictionary: DA: in search of a/a definitive Braviant data dictionary Looker Data Dictionary pages: Looker link looks like Looker stands up Data Dictionary like pages. Could be very helpful DataOpsSupport: this is a Jira label to apply to DMS tickets to indicate an ops like request in the DMS world Date / Dates: dw_core.dim_date: check out this table which is a dimension table that lists out dates across many past and future years along with informative meta data (e.g. is the date a holiday, a weekday, etc.) NOTE: you have snippet of this database on your machine in a Duckdb: /Users/dan.anderson/Documents/duckdb/braviant_dates_db.duckdb Days Past Due: dw_reporting_lp.loan_status_archive.days_past_due DE: in Provenir assume this means Decision Engine(?) Debt Sale Date: date where SunUp has sold delinquent/bad loans to third parties can be seen in: dw_reporting_views.loan_settings_custom_fields.debt_sale_date looks like both Bank and Non Bank loans can incur a Debt Sale (balancecredit, ccbankinstallment, ccbankdirect, balanceloc) Delinquent: assume when a loan is days_past_due > 0, it is delinquent (e.g. dw_reporting_bsf_identity.loan.days_past_due) DFC: Dynamic Flow Controller (call) disposition: a type or bucketing on inbound calls reflecting their focus: originations: customer does not yet have a funded loan csr: customer has a funded loan but not yet in collections collection: customer has a loan and is in collections escalation: customer has escalated the call from Tier 1 to Tier 2 DL: Decision Logic From the Decision Logic website: “Our service provides the ability to verify a borrower’s identity, bank account number and balance in real-time. It also provides access to up to 365-days of borrowers’ bank account transaction history with a layer of advanced analytics that lead the industry. This automated real-time process empowers you to make fast and accurate decisions critical to the success of your business.” DLH: assume Data Lake House DM: Direct Mail NOTE: live_fl is a flag that indicates that a campaign is “live” and in effect DMC: Debt Management Company DNIS: from ChatGPT Dialed Number Identification Service. It is a telephony service that identifies the number that the caller dialed, allowing call centers and phone systems to determine which inbound number triggered the call — useful when multiple numbers route to the same system Document Data Table: looks like a table that holds attributes related to customer (borrower) documents handled, reviewed, or acknowledged during the loan life cycle Examples: ACHAgreement Consent To Electronic Communication Patriot Act dw_reporting_bsf_core.document_data: document data including json dw_reporting_bsf_common.document_type: lookup table relating document_type_id to an english name join to dw_reporting_bsf_origination.document (?) (call) dropped: from an Ops perspective, an inbound call that was ended prior to being placed in a queue Due Date: Dan: re: payment due date assume this is the day after the scheduled period end select entity_id, entity_type, max(period_end) as last_period_end, max(period_end) + 1 as schd_loan_date from dw_reporting_lp.loan_active_time_tx group by entity_id, entity_type limit 1000 DPD: Days Past Due DSPO: Days Since Last Payoff - number of days since a customer’s last loan was paid/closed Draw: assume this refers to a draw on a Line of Credit (LOC) account/product – E – link EBN: assume this refers to Electronic Bankruptcy Notice website maintained by US federal courts. See website. May be important in the CCB Bank Direct project EBS: Electronic Bank Statement CDD & EDD: Customer Due Diligence and Enhanced Due Diligence part of the BSA (Bank Secrecy Act) EDGE or EDGEScore: hosted service that is intended to replace DecisionLogic for BAV and/or income verfication(?) see: https://www.edgescore.com/ their spin is that balances and daily bank activity is a better indicator than more(?) static credit scores and bank account balances Effective & Asserted Bi-Temporal Columns Used in various tables that write multiple row (or row instances) over time At any given time only 1 row should be valid From KG: if you want data as of end of day 12/11, it has to be ‘2023-12-12 05:00:00’::timestamptz <@ l.asserted EFX: Assume it means Equifax (consumer credit reporting agency) (LexisNexis) Emailage: from Grok | is a fraud prevention and risk assessment solution that uses email intelligence as a core identifier to detect and prevent fraudulent activities. It analyzes email address metadata, along with other data points like IP addresses, names, phone numbers, and billing/shipping addresses, to generate predictive risk scores. These scores help businesses verify consumer identities, approve transactions faster, and reduce fraud while maintaining a seamless user experience sounds like Emailage is very expensive and Bravaint may try to replicate its value through other data sources Email Penetration: assume we considered an email as “penetrated” it was delivered and no bounceback occurred Emails are sent using multiple services: Five9 Acoustic LoanPro Remitter Sendgrid EV: Enhanced Verification customer flow where the user has go through enhanced verification to have an agent (?) verify required information (pay stubs? bank acount? not sure here) see table: dw_reporting_bsf_origination.ev_result see common lookup tables for context dw_reporting_bsf_common.ev_change_reason dw_reporting_bsf_common.ev_resolution_method dw_reporting_bsf_common.ev_status dw_reporting_bsf_common.ev_type EXP: Experian (credit bureau) – F – link Fact Application Table: special “fact” table storing data element for applications. Heavily used for reporting and Looker views (?) dw_reporting_views.fact_application: main table NOTE: the loan_num is the loan id field dw_reporting_views.fact_application_new: assume this is a new table that has not yet been moved into production Fact Installment Table: special “fact” table storing installment and installment payment data for a loan. Assume like other fact tables, used for downstream reporting dw_reporting_views.fact_installment: a materialized view From a code comment: The purpose of fact_installment is to provide a payment-by-payment summary of each loan, with payment amounts, initial and current default performance, and due dates Wiki page with some details: wiki page FCRA: Fair Credit Reporting Act. From Grok: The FCRA is a U.S. federal law enacted in 1970 that regulates the collection, dissemination, and use of consumer credit information. It ensures the accuracy, fairness, and privacy of information in credit reports, which lenders often rely on during loan origination and collections FCRA Alert: from local LLM Model also known as a fraud alert or sometimes a consumer notice, is a notification placed on a consumer’s credit report that alerts creditors and other businesses to take extra precautions before extending credit. It’s a safeguard against identity theft and fraudulent activity FCRA Pass Rate: from local LLM Model (also sometimes referred to as a “Compliance Pass Rate” or “Adverse Action Verification Pass Rate”) is a metric that measures the percentage of adverse action decisions (loan denials, credit limit reductions, etc.) where a lender has properly verified the information used to make that decision, specifically when relying on consumer reports DA: assume we must take care and action to confirm identity when an alert is in place New Fact Installment Table: new version of the fact_installment table: dw_core.fact_installment built out as a part of dev project in late 2022 NOTE: this table is being populated (ETL/data propagation) Fair Value: (general accouting defintion): the practice of measuring your business’s liabilities and assets at their current market value Finance, Accounting, Capital, Treasury and Tax (FACTT): The FACTT Team. Now includes the BI team (currently just Michael Yakymov?) Finance Aging Table: Finance schema table that contains loan “aging” data (payment perspective) finance.aging: don’t have access to this schema as yet First Payment Date: assume loan closed date dw_reporting_lp.loan_setup_entity.first_payment_date First Payment Default: assume date in which a first payment default has been determined dw_reporting_bsf_identity.prepped_loan_additional_info_data.first_payment_default (materialized view) Code Logic: the FPD (assume that’s an indicator of a First Payment Default) is determined (DA: sort of inductively) by: adding up all the payments on a loan that have been applied prior to the first payment due date if that sum is less than the payment amount due then FPD = 1 (is true) ELSE FPD = 0 (is false) Five9 Fact Table? dw_reporting_views.fact_five9: DA: seems like a “fact” table for Five9 calls. Looks like an active table has columns such as disposition, call_id, queue_wait_time FL Stamp Tax Calculation: the Florida Stamp Tax calculation is: ( 0.35 + cash_to_customer + ( 0.35 * cash_to_customer / 100 ) ) where cash_to_customer is just the original loan amount prior to adding on the FL stamp tax See: https://balancecredit.atlassian.net/wiki/spaces/PD/pages/1494548559/FL+Documentary+Stamp+Tax Flow Rate: from Grok rate at which delinquent accounts or debts move through different stages of the collections process. It typically measures the percentage of accounts that “flow” from one delinquency bucket (e.g., 30 days past due) to the next (e.g., 60 days past due) or from delinquency to resolution (e.g., paid, settled, or charged off) over a specific period (LexisNexis) FraudPoint: solutions detects fraudulent applications by using advanced analytics that leverage comprehensive and dynamic identity and digital intelligence Function vs. Procedure: in Postgres the primary difference is that a function returns a result and a procedure does not Funding Date: assume this is the date that a loan is considered funded from an origination perspective this is 1 day before the originated loan’s contract (effective) date not sure the Funding Date can be on a weekend day FT: Factor Trust (now owned by TransUnion), credit bureau from which Braviant gets data feeds – G – link get_business_date: database function that returns the next business day (so takes into account weekends and holidays) get_business_date(current_date) would return today get_business_date('2023-07-04') would return ‘2023-07-05’ so the next applicable non-holiday graylist: list of bank routing numbers on the “graylist”; assume applications referencing these routing numbers trigger more scrutiny (DA: Enhanced Verification?) KG says the graylist is here: origination.ev_bank in the BSF database also see: blacklist on this page GNS: not sure that this stands for… maybe Gross Net Spend from Michael Yakymov’s documenation: payment successfully cleared on all payment due divided by $ payment attempted for all MTD due dates see: https://balancecredit.atlassian.net/wiki/spaces/AnalyticsTeam/pages/4453138433/Enterprise+Dashboard+KPI – H – link Hudson Cove: assume this means Hudson Cove Capital Management which I think is a Braviant partner to whom we sell loan or loan participation https://hudsoncovecapital.com – I – link IL PS: Illinois Paystub Interest Rate or contract rate - NOT APR (Annual Percentage Rate) Note APR includes other costs, interest rate is the strict cost to borrow money Grant fetched the contract rate/interest rate from this table (DA: not familiar with this table) dw_reporting_bsf_uwp.loan_contract_calculation.actual_interest_rate From an example TIL document (loan_id = 630195) Interest will accrue on a daily basis on the unpaid Principal Amount. Beginning on the Loan Effective Date set forth above, you begin calculating interest based on the number of days my loan has been outstanding, assuming a 365 day year, until I pay off the Loan in full. My daily interest rate is equal to my Contract Rate (listed at the top of this page) divided by 365. Daily Accrued Interest = principal_balance * contract_rate (not APR) / 365 days – K – link KO: Knockout – L – link Leads: “Leads Schema”: dw_reporting_bsf_leads Lead table: dw_reporting_bsf_leads.lead l dw_reporting_bsf_leads.lead_provider_lookup: table used to look up leads providers link an application to a lead source type (ie. the overall application source type): dw_reporting_bsf_origination.application.application_source_type_id application source type enumerated values view: dw_reporting_bsf_common.application_source_type link application_source_type_id to application_source_type a code change that looks like an example of linking applications to leads: github code PingTree: online leads platform; seems like a marketplace where you purchase/bid for customer leads assume we are refocusing on this as a source (mid 2024) Ramon to start driving this currently PingTree is the only leads type channel (assume this will change/grow) Nic: They would come in with an origination.application application_source_type_id = 2 LMS: Loan Management System Loan: dw_reporting_bsf_identity.loan some important columns: loan_status loan_sustatus loan_effective_date loan_payoff_date LoanPro | Loan Transaction Table: loan_tx From ChatGPT: What it is: the loan transaction ledger — 1 row per transaction that affects a loan (payments, charges/fees, credits, adjustments, reversals, etc.). Where this maps in the API/UI: it corresponds to “loan transactions” you can pull via the LMS API (e.g., Get single loan transaction), and to the Transactions/Payments you see in a loan’s history. ([LoanPro Developers][1], [help.loanpro.io][2]) Typical fields you’ll see: loan id, transaction type, amounts (and allocation by principal/interest/fees), apply/effective date, post timestamp, user/process that created it, reversal/parent links. Use it for: cash/ledger truth (what posted when, how it allocated), rollups (collected cash, fee income), and downstream reconciliation. dw_reporting_lp.loan_tx: assume this holds transactions for a loan (and probably other “objects” including refunds [Advancements for overpayments]). use entity_id to search for the loan_id value NOTE: associate dw_reporting_lp.loan_tx tx to dw_reporting_lp.payment_entity pe on pe.id = tx.payment_id the loan_tx table includes these types of transactions: +--------------------+ |type | +--------------------+ |advancement | |apdAdjustment | |credit | |dpdAdjustment | |fee | |forecastedPayment | |modification | |origination | |payment | |scheduledPayment | |suspendInterest | +--------------------+ LoanPro | Loan Active Time TX Table: loan_active_time_tx From ChatGPT: What it is (in practice): a time-series/derived “active time” stream that records changes or accrual of time-in-state for loans (e.g., time in “Active”, “Delinquent X”, “Charged-off”, or other status buckets). It’s used to support “days in status,” delinquency history, and daily snapshot–style reporting. Why it exists: LoanPro maintains day-by-day and status-by-status history for analytics (e.g., Daily Snapshot / delinquency history reports). In the database export, this shows up as a transaction-like table of state/time changes rather than money movements. ([help.loanpro.io][3]) Use it for: cohorting and KPIs that depend on duration (e.g., average days to charge-off, days current vs. delinquent, compliance “time in bucket” rules), and to reconcile differences between “point-in-time” vs “as-of” views. dw_reporting_lp.loan_active_time_tx: looks like the set of both forecasted and scheduled payments for a loan entity_id: assume that is the loan_id field type: forecastedPayment or scheduledPayment e.g. query to fetch scheduled payments select * from dw_reporting_lp.loan_active_time_tx latx where latx.entity_type = 'Entity.Loan' and latx.type = 'scheduledPayment' limit 100 Loan Amount assume this means the initial loan amount in the customer’s contract (ie. original loan amount) dw_reporting_lp.loan_setup_entity.loan_amount Loan Credit Transaction assume table lists out credit transactions for a particular loan dw_reporting_bsf_identity.loan_credit_transaction (link) also see: dw_reporting_lp.loan_credit_entity looks like the title is a freeform text field that someone (an agent? automated process) fills out categories of credit transactions from table: dw_reporting_lp.credit_category_entity: Balance Transfer Discharge Fee Waiver Legal Settlement Other Payoff Adjustment Processing Error Adjustment Promotions Rescission Adjustment Settlement Adjustment System Adjustment see example SQL to sum up credits for a loan for a particular date gist see another example SQL to find credits associated for a loan gist see list of credit categories gist Loan ID dw_reporting_lp.loan_setup_entity.loan_id LoanPro: the loan servicing web application used by Braviant (mostly Ops and Agents but others as well) IMPORTANT: handy report: a loan’s HISTORICAL ARCHIVE looks like loan_status_archive in web page format for a loan… use to understand loan changes over time navigate to.. search on a loan (e.g. 594200) click on Reports click on Historical Archive Loan Rate: loan_rate the loan’s contractual interest rate which is displayed on the loan TIL. This not the loan APR In BSF you can find this here (assume other places): loanpro.loan_setup_entity,loan_rate Loan Status Entity: looks like a lookup table (enumerated types) describing loan statuses (those are not soft deleted) Pending App Closed Approved Active Closed Loan Status & Sub Status: look up tables for loan status and substatus dw_reporting_lp.loan_sub_status_entity 4 Active 2 App Closed 3 Approved 5 Closed 1 Pending dw_reporting_lp.loan_status_entity Loan Status Archive (table) NOTE: KG SAYS THAT CLOSED LOANS FALL OF THIS TABLE AFTER A FEW DAYS. KEEP THIS MIND. MAY BE BETTTER TO USE dw_reporting_bsf_identity.loan for some queries dw_reporting_lp.loan_status_archive “lsa” For SC state reporting, dw_reporting_lp.loan_status_archive.payoff as of a date to include the payments applied up to and including that date looks like a daily snapshot of key data by loan Loan Tape dw_core.loan_tape: core DPE table dw_core.loan_tape_snapshot_ip view used to generate snapshots for Jordan O.? From Tejas: loan_tape_snapshot_ip was a temp object created to combine 2022/1/31 - 2025/3/31 snapshots dw_core.loan_tape_snapshot_2025_03_31 materialized view; assume this is a particular instance of a Loan Tape snapshot (?) Loan Term DA: determine the loan term in days (dw_reporting_lp.loan_setup_entity.orig_final_payment_date - dw_reporting_lp.loan_setup_entity.contract_date) as loan_term_in_days the loan_term attribute that is in various loan tables (e.g. BSF identity.loan.loan_term) is the loan interest rate aka the loan contract rate (which is displayed on the TIL) Loan Transaction Tables dw_reporting_lp.loan_tx: assume this holds transactions for a loan (and probably other “objects” including refunds [Advancements for overpayments]). use entity_id to search for the loan_id value NOTE: associate dw_reporting_lp.loan_tx tx to dw_reporting_lp.payment_entity pe on pe.id = tx.payment_id the loan_tx table includes these types of transactions: +--------------------+ |type | +--------------------+ |advancement | |apdAdjustment | |credit | |dpdAdjustment | |fee | |forecastedPayment | |modification | |origination | |payment | |scheduledPayment | |suspendInterest | +--------------------+ Loan Transaction (Loan Tran / Settlement) Tables From Grant: Holds all of the prior days transactions (Payments, credits, buybacks, advancements, fundings, funding returns, payment returns, system adjustments, credit returns, adv returns, rescissions, buyback payment returns, and accrued interest) Github Code assume these are the Loan Tran tables dw_reporting_views.cc_bank_loantran_audit: main Loan Tran table? dw_reporting_views.cc_bank_loantran_daily: looks like the last daily snapshot of cc_bank_loantran_audit (so it just has one day’s worth of data) LOC: Line of Credit Looker Developer Mode Get into a particular branch Log into Looker Toggle into Developer Mode (lower left hand corner) Click the Develop menu item Click on the enterprise_dw project Click on the default branch (upper left hand corner) Click on the Pull Remote Changes button (upper right hand corner) (I think you now just use Looker normally and should be using the specified branch code?) Lock Box: from Investopedia Lockbox banking is a service provided by banks to companies for the receipt of payment from customers. Under the service, the payments made by customers are directed to a special post office box instead of going to the company. The bank goes to the box, retrieves the payments, processes them and deposits the funds directly into the company’s bank account. LP: LoanPro – M – link Maturity Date: contractual (TIL?) maturity date of the loan: dw_reporting_lp.loan_setup_entity.orig_final_payment_date Maxdecisions: DA: looks like a predictive modeling service vendor DA: how does Braviant use them? https://maxdecision.com/ MOB Month on Book: seems like a bucketing or cohort categorization driven by the months that a loan is on the books starting with Month 0 if a loan originates on July 3 and it is now Oct 31 (end ot the month of October) then the loan is categorized as MOB 3 Jul: MOB 0 Aug: MOB 1 Sep: MOB 2 Oct: MOB 3 MUP: (DA: see this in Ops payments data) From Chatgpt: Manual Update Payment | a payment-related action that is manually posted or adjusted by an operations or servicing agent—outside of normal automated payment flows For example: Manually posting a payment due to technical issues Adjusting or correcting a payment record Applying a payment to a specific account or schedule after receiving offline funds (e.g. mailed check, money order) Reversing or reassigning misapplied funds – N – link NCP: NCP Finance, company currently used a middle man between SunUp Financial and CC Bank. That relationship will end and SunUp will eventually act direct to CC Bank NCP Table: table that houses the “GL” like records produced by the NCP/TLS CC Bank settlement process used for ccbankinstallment product loans dw_reporting_views.bank_ncp_settlement ccbankdirect loans have the analogous GL records stored here: dw_reporting_views.ccbd_transaction_history_audit NOA: Notice of Assignment: mandatory notification to the customer that their loan has been sold NOAA | Notice of Adverse Action: from Grok s a written communication that lenders are required to provide to a borrower when an adverse decision is made regarding their credit application. This could include denying a loan, offering less favorable terms than requested, or taking other negative actions such as reducing a credit limit or closing an account The NOAA, mandated by regulations like the Fair Credit Reporting Act (FCRA) and the Equal Credit Opportunity Act (ECOA) in the United States, must include: The specific reasons for the adverse action (e.g., low credit score, high debt-to-income ratio). The name and contact information of the credit reporting agency (if a credit report was used). Information about the borrower’s right to obtain a free copy of their credit report and dispute inaccuracies. A statement of the borrower’s rights under the ECOA, such as non-discrimination protections. -- SQL to extract the NOAA reasons for a given application_id SELECT a.application_id, nr.noaa_id, nr.noaa_reason_id, nrc.noaa_reason FROM dw_reporting_bsf_origination.application a JOIN dw_reporting_bsf_origination.noaa n ON n.application_id = a.application_id AND now() <@ n.asserted AND now() <@ n.effective JOIN dw_reporting_bsf_origination.noaa_reason nr ON nr.noaa_id = n.noaa_id LEFT JOIN dw_reporting_bsf_common.noaa_reason nrc ON nrc.noaa_reason_id = nr.noaa_reason_id WHERE a.application_id = 2669566 and now()<@a.asserted and now()<@a.effective order by nr.noaa_reason_id; -- <<<< assume this is the logical sort order Net NPV: NPV - CFP (net present value minus the cost to fund) NOI: Notice of Incompleteness from ChatGPT A formal disclosure required under ECOA / Regulation B that notifies a loan applicant their credit application is missing essential information. It specifies what documentation or details are needed and sets a deadline for submission. If the applicant does not provide the requested items by the deadline, the lender may treat the application as withdrawn or denied without issuing a Notice of Adverse Action. Notes, Agent Notes, System Notes: tables that contain system and Agent notes dw_reporting_lp.note_entity dw_reporting_lp.system_note_payments see other %note% tables in dw_reporting_lp NPV: Net Present Value for a loan – O – link Occupancy: or Agent Occupance; from ChatGPT the percentage of time that an agent is actively engaged in handling customer interactions (e.g., calls, chats, emails) compared to the total time they are available to work Occupancy (%) = (Talk Time + After-Call Work Time) / (Total Available Time) × 100 ODFI: Originating Depository Financial Institution In ACH payment processing (see informative web page) agrees with Nacha or the Federal Reserve to transmit ACH transactions on behalf of an originator bank In banking terms, it’s the entity that helps initiate the original transaction. offer wall (lead generation): from Grok lead generation strategy involves presenting potential borrowers with a digital platform or interface (the “offer wall”) that displays multiple loan offers or financial products from various lenders or partners. This approach is commonly used by lead generation companies, affiliate marketers, or financial service platforms to connect consumers with lending options tailored to their needs or credit profiles How It Works: Consumer Interaction: A consumer visits a website, app, or platform (often through ads, search engines, or affiliate links) seeking a loan or financial product. Data Collection: The consumer provides basic information, such as their name, contact details, income, credit score range, or loan amount needed, typically through a short form. Offer Display: Based on the provided information, the platform dynamically generates an “offer wall”—a curated list of loan offers from multiple lenders or financial institutions. These offers may include personal loans, payday loans, credit cards, or other financial products. Consumer Selection: The consumer reviews the offers, which often include details like loan amounts, interest rates, terms, or lender names, and selects one or more offers to pursue. Lead Transfer: When the consumer clicks on an offer, their information (the “lead”) is sent to the respective lender or partner for further processing. The lead generator earns a commission for delivering the lead, often on a cost-per-lead (CPL) or cost-per-action (CPA) basis. Current Braviant Offerwall Partners: Engine, SuperMoney Partners serve as brokers to lead providers identified as source IDs or source UUIDs DA: assume source UUID is stored here?: dw_core.fact_leads_offerwall.offerwall_channel_source_id Offers: offers sent out to previous customers (Refi Offers, Retention), DA: others? dw_core.monthly_offer_summary: (DPE table) from Tejas: Table contains Retnetion Email data sent to these customers on a monthly basis and offers information on sent/opened/clicked counts Offer Page: DA: assume this is the page displayed to the user which represents a loan offer (not necessarily what their loan will exactly look like?) BSF origination.balance_pricing table contains the pricing/details associated with an application owner: dw_reporting_views.loan_settings_custom_fields.owner assume the entity that currently owns the loan see Loan Custom Fields OWR: DA: I think the QA team uses this acronym for “Offerwall Retention” line of business/application generation path Overpayment: snippet to calculate overpayments by loan and loan_tran transaction date gist – P – link Paid Off Date: or Payoff Date. Date in which the loan is paid off. some tables where this exists (DA: others exist I’m sure) dw_reporting_bsf_identity.loan.loan_payoff_date dw_core.fact_installment.loan_paid_off_date Participation vs Pledging: IMPORTANT ChatGPT pulled this out my meeting notes: 2025-09-15_1300_Participation-Pledging-Training.md Participation = buying the economic slice of the loan ~Day 3 after origination, the bank keeps 5% and we “participate” in 95%. We pay the bank ~95% of principal plus three business days of interest and a premium. From then on, we take 95% of payments (and 95% of the risk) while the bank still legally owns the note until any buyback. This is driven by the participation/settlement processes and files that split cash 95/5. Pledging = using that slice as collateral to borrow After we hold that 95%, we pledge it into an SPV (e.g., Base Point SP1 or Hudson Cove SP2). That transfers our 95% interest to the facility so we can draw cash to fund the participation payments and future lending. Once pledged, collections on those loans flow to the SPV. We later un-pledge (e.g., at bankruptcy or ~90 DPD) so the loan can be bought back/sold to a debt buyer Participation: process by which entities own parts of a Braviant loan and (I assume) receive part of the customer’s principal and interest payment stream Example: CC Bank originates Bank loans and then on the third business day sells 95% of the loan back to a Braviant entity and keeps a 5% participation. future events (e.g. chargeoff) can adjust that participation Tejas can provide help from a tech perspective on Pledging Old Participation Product Requirements: may provide some helpful context LucidChart - Context on Participation See how participation changes participation event: configuration table Participation Business Rules: observations on potential business rules(?) Pending Rescission Loans: if a loan is Pending Rescission on the third business day, a loan participation event will not occur Participation “Effect”: participation essentially takes effect at the end of the participation date What does this mean? Means that payments applied on the participation date should be allocated only to CCB (who is the sole owner prior to participation) Relevant EDW tables (NOTE: assume the operating tables are in BOSS) dw_reporting_boss_core.participating_entity: list of business entities that participate in loans (lookup table?) B Credit Solutions, LLC|(id = 3) B Credit Solutions - SPV1|(id = 4) CC Bank|(id = 1) Debt Management Partners, LLC|(id = 5) Landmark Strategy Group, LLC|(id = 6) National Debt Holdings, LLC|(id = 8) NCP|(id = 2) Peritus Portfolio Services LLC|(id = 7) dw_reporting_boss_participation.participation_allocation_event_type: enumerated tables describing events that drive allocations Origination|(id = 1) Participation Transfer: CCB -> NCP|(id = 2) Participation Transfer: NCP -> BCS|(id = 3) Buyback Purchase|(id = 4) Participation Pledge|(id = 5) Debt Sale|(id = 6) Participation Unpledge|(id = 7) Debt Sale Buyback|(id = 8) dw_reporting_boss_participation.participation_allocation: assume table that describes the current participation allocation of a particular loan (at a particular point in time) this is a bitemporal table so some allocation records are in effect and some are inactive SQL to fetch the the participation allocation for a loan_id at a particular date select * from dw_reporting_boss_participation.participation_allocation where loan_pro_loan_id = 612677 and '2023-06-07'::timestamptz <@ asserted and '2023-06-07'::timestamptz <@ effective dw_reporting_boss_participation.participation_allocation_transition: looks like this table describes the transition in participation for a given allocation event dw_reporting_boss_participation.participation_transaction: describes a participation transaction Payment Date: various payment dates dw_reporting_bsf_identity.loan.last_payment_apply_date dw_reporting_bsf_identity.loan.next_payment_due_date payment_e: column multiple tables; part of a payment that is considered part of an escrow payment (?) in table: dw_reporting_lp.payment_entity Payment Frequency dw_reporting_bsf_common.payment_frequency: enumerated list of payment frequency types (e.g. Bi-weekly) also see: dw_reporting_lp.loan_setup_entity.payment_frequency Payment Method dw_reporting_bsf_origination.application.payment_method_id: assume this table is where I should find the payment_method associated with a particular loan (loan’s application) dw_reporting_bsf_common.payment_method: listing of payment methods ACH (payment_method_id = 1) RCC (payment_method_id = 2) DirectPay (payment_method_id = 3) dw_reporting_lp.custom_payment_method_entity: list of custom payment methods referenced in LoanPro (e.g. Check, EFT, MoneyGram, etc.) SQL from Tejas: select * from dw_reporting_lp.custom_payment_method_entity where active = 1 and deleted = 0 NOTE: Grant says the table above is for something else. The ids he uses is: ACH (payment_method_id = 4) Debit (payment_method_id = 3) Check (payment_method_id = 2) (Loan) Payment Schedule: dw_reporting_views.loan_payment_schedule: table that appears (?) to hold the formal schedule of payments driven by a loan’s amortization schedule is used when refreshing dw_reporting_views.fact_installment Payment Transaction dw_reporting_bsf_identity.payment_transaction: payment transaction table? See example SQL to query successful transaction gist Payment - Unsuccessful Transaction: a type of transaction that is tagged as ACH in LoaPro but for some reason do not show up in the ACH file I think this occurs for events like a bad routing number was used Grant: “When they occur, ops gets notified by this looker report” Grant: “the amount of non-ach payments dont apply to the balance of the loan. so when it gets reversed thats why your balance becomes corrected again” NOTE: this table will contain deleted payment transactions. for these cases: payment_transaction_id is null Payments, Cleared: from KG, information on determining if a payment is cleared: Debit card paymen: apply date <= today and is not deleted and not reversed ACH payment with apply date <= today - 3 and is not deleted and not reversed NOTE: is the -3 days business or calendar days? (Confirm with Tejas?) check out the Date / Date entry on this page above; that table may be helpful Check payment with apply date <= today and is not deleted and not reversed PBR: Product Backlog Review - recurring meeting that Nic has to review backlog epics(?). Not sure exactly what we do in this meeting Per Diem Daily interest accrual dw_reporting_lp.loan_status_archive.perdiem dw_reporting_bsf_origination.application.product Perform: Postgres command, same as select but perform throws away the result looks like perform can trigger a function or procedure (and thow away the result if applicable?) ping tree (lead generation): from Grok is a lead generation system used to distribute and sell leads (potential borrowers’ information) to lenders or brokers in real-time. It operates as an automated, hierarchical process that “pings” or tests the lead against multiple buyers’ criteria to maximize the value of the lead How it Works: Lead Submission: A consumer submits personal and financial information (e.g., name, income, credit score) through a website or form, creating a lead. Pinging Process: The lead is sent to a ping tree, which instantly evaluates it against the criteria of multiple lenders or buyers. These criteria might include credit score, loan amount, or geographic location. Tiered Bidding: Buyers are organized in a priority “tree” based on their bid price or relationship with the lead generator. The lead is first offered to the highest-priority buyer. If they accept, the lead is sold. If not, it “pings” down to the next buyer in the tree, and so on. Real-Time Auction: The process happens in milliseconds, ensuring the lead is sold to the highest-paying or most suitable buyer quickly. Lead Delivery: Once a buyer accepts the lead, the consumer is typically redirected to that lender’s application process. Pledging: process by which a loan allocation (how much?) is transferred from B Credit Solutions (BCS) to another Braviant entity (ie. BCS - SPV1) NOTE: looks like pledging is only done (given comment above and a db query of dw_reporting_boss_pledging.pledged_loans) to Bank loans (ccbankdirect, ccbankinstallment) - IS THIS ACCURATE? Tejas can provide help from a tech perspective on Pledging Pledging functionality is in the BOSS world and db and that data gets ETL’ed/sync’ed to EDW (see dw_reporting_boss_pledging schema) Tables that describe the pledging activity dw_reporting_boss_pledging.pledged_loans: lists out pledged loans see PTOB-520 for an example of code leveraging pledging data Eligibility at a high level the process to determin what loans are eligible to be pledged (?) Lara’s SQL to determine loan eligibility for SPV3: Cacher link PM Prior Month: just saw this in a deliverable and didn’t know what it was it may not be an official acronym PMML: Predictive Model Markup Language from Grok: is an XML-based standard for representing and exchanging predictive models between different systems and platforms. It allows models built in one environment (e.g., R, Python) to be deployed and executed in another (e.g., a production server) without requiring code rewrites. PMML supports various machine learning algorithms, including regression, neural networks, and decision trees, enabling interoperability and model portability across tools (Provenir): DA: looks like Provenir usess PMML to enable users to leverage/install their predictive models LoanPro Portfolio ID: unique identifier used upstream in LoanPro dw_reporting_lp.loan__portfolio: looks like mapping of loan_id to portfolio_id select distinct portfolio_id from dw_reporting_lp.loan__portfolio order by portfolio_id desc where deleted = 0 Pre-Funding Checks: DA: assume these are checks against a loan and loan application that are executed prior to the funding event. Assume if a loan fails a check it is excluded from funding(?) Github code: https://github.com/braviant/bsf_sqitch/blob/main/dbobjects/op_reporting/routines/pre_fund_check_select.sql Nic working document: Google Sheet Prepayment: assume that means the customer paying off all or part of the loan prior to the scheduled payment date pricing: DA: assume when people refer to pricing they mean determining the APR on a loan (it is essentially the pricing of a loan) [???] 10 point APR pricing discount: retention customers get a 10 point APR price discount (on their APR). DA: assume this applies just to ccbankdirect loans (but confirm this) Procedure vs. Function: in Postgres the primary difference is that a function returns a result and a procedure does not Product dw_reporting_bsf_origination.application.product these are the current “products”; there will be a new one when NCP is removed from the Braviant operating relationship balancecredit balanceloc ccbankinstallment choruscredit dw_reporting_bsf_common.product: enumerated list of product types PTI: Payment To Income Purchase Date: purchase_date: date that a loan was bought back (buy back / buyback) from the bank can be found in dw_reporting_bsf_identity.loan_additional_info.purchase_date dw_reporting_bsf_identity.loan_additional_info lai – R – link RBPN: Risk Based Pricing Notice: maybe some background link RCC: Remotely Created Check RDFI: Receiving Depository Financial Institution (NACHA system) Informative page REFI: Refinance Refinance Offers See materialized view dw_reporting_views.refinance_offers for refinance offers (?) assume base_loan_id is the loan being refinanced assume refi_loan_id is the new refinanced loan ? assume you need to check out the refinanced loan to make sure it has a status of Active or Closed (make sure it was funded) Refinancing / Refis: my crude take on what happens Check out the example in dw_reporting_lp.loan_tx (Loan Transaction table) Refinanced Loan: 568975 New Loan: 616930 Basic Process 568975’s outstanding balance ($1,119.75) is “paid off” the paid off balance ($1,119.75) is applied to the new loan 616930 the new loan is funded ($1,280.25) at origination time see: select * from dw_reporting_views.cc_bank_loantran_audit where loan_id = 616930 for a loan with a initial balance of $2,400 = $1,119.75 + $1,280.25 DA (?): the components of a refi loan balance: Total Loan Amount = $2,400 Base Loan Payoff = $1,119.75 Cash to Customer = $1,280.25 Check if a Loan is a Refinance DA: a way (probable one of multiple ways) to determine if a loan is a refi / refinance check the if the application source type id = 4 case when a.application_source_type_id = 4 then 'Yes' else 'No' end as refi_flag Refund: currently refunds are cash payments made via papercheck (processed through a service called Smart Payables[?]) Assume refunds are Advancements in the LoanPro world one place to query: finance.fft_clean fft where transaction_type = Advancement Refund & Reimbursement Tracker: a Google Sheet that tracks refunds and reimbursements processed by the Ops Team Google Sheet Link Sounds like this was a processed partially owned by Accounting but now owned by Ops start to finish Reject Reasons: common BSF tables listing regret reasons common.reject_group common.reject_reason: the reject_group_id value groups the reject reasons by process (e.g. Verification) common.noaa_reason common.reject_noaa_reason_xref -- from Krithika: relating reject reasons to NOAA reason codes (adverse action) select rr.reject_reason_id, rr.reject_reason, nr.noaa_reason from common.reject_reason rr join common.reject_noaa_reason_xref x on rr.reject_reason_id = x.reject_reason_id join common.noaa_reason nr on nr.noaa_reason_id = x.noaa_reason_id; Retention: maketing / targeting (?) former customers who have at least one past loan (status is Closed) they can targeted via direct mail (DM), email DA: what about Offerwall? (can we tell if an Offerwall customer is a former customer) revenue: DA: looks like revenue calculated as the total sum of payments (assume those are loan payments as opposed to feeds) there is a Revenue % threshold (140%) that is used to determine if a previous customer (with a paid off loan) can receive an APR (pricing discount) this is calculated as: total of payments / amount financed * 100% [from Nic] Reverse Status Archive dw_reporting_lp.loan_reverse_status_archive a status of a loan’s payments that goes back in time and represents payment reversals to apply to the actual due date (why? beacause there is a lag of ~3 days to get notified of a reversal, this db table goes back an applies reversal as if the notification was immediate) Risk Grades: grades assigned to an application (or person?) that drives underwriting decisions? AAA AA A B C D DA: How do we use these risk grades? Roll Rate from Grok measures the percentage of delinquent accounts that “roll” from one delinquency bucket to a more severe one (e.g., from 30 days past due to 60 days past due) over a given time frame. It reflects the progression of delinquency and is used to assess the effectiveness of collections strategies From Investopedia: The roll rate is the percentage of credit card cardholders that roll from one category of delinquency to the next. * For instance, you can measure the percentage of cardholders who roll from 60-days overdue to 90-days overdue. RPC | Right Party Contact: from Grok refers to a successful interaction or communication with the intended individual or entity in a specific context, such as debt collection, telemarketing, customer service, or survey outreach. It means reaching the correct person who is authorized, responsible, or targeted for the purpose of the contact, as opposed to contacting the wrong person or a non-decision-maker Logic (from Jordan D./Ops?) that determines a call is an RPC call: case when talk_time_sec > 0 then 'RPC' else 'No RPC' end as rpc_status RPS | Right Party Setup: a payment setup triggered by Right Party Contact. That is the right party was successfully contacted and a payment was setup (assume immediatly transacted or scheduled). RTC | Right to Cure Right to Cure notice. Notice to a customer they are in default or delinquency and they have a right to cure the default Assume this notice is triggered by certain events and has to adhere to federal/state regulations RTF: assume this means Ready to Fund DA:? – S – link Sales Finance Contract (SFC) from ChatGPT. DA: assume this is not part of Braviant’s current business In the consumer installment loan industry, a “sales finance contract” (SFC) refers to a legal agreement between a borrower (often a consumer) and a lender, typically used in the context of financing the purchase of specific goods or services. Scheduled Payments: see Loan Active Time TX Table Scorecard (Provenir): A Scorecard is a set of predictive variables that help determine the likelihood or odds of a certain outcome and are typically used to predict risk, revenue, fraud, and attrition Provenir Scorecards consist of different characteristics or data points, which define the source of the data. Each characteristic has one or more attributes that specify a certain weight for data values. These weights are added up to generate a score. You may also specify reason codes, which provide information about why a certain score was attained. The output of a scorecard is the final score SCRA - The Servicemembers Civil Relief Act drives loan servicing treatment and notifications SDN: Specially Designated Persons & Blocked Persons list (SDN List) Sendgrid Tables: selected Sendgrid tables dw_reporting_bsf_email_service.sendgrid_email_log: looks like it logs email sending events dw_reporting_views.sendgrid_event_collection_view: looks like a table that logs Sendgrid events +----------+ |event_name| +----------+ |bounce | |click | |deferred | |delivered | |dropped | |open | |processed | |spamreport| +----------+ Setup: from an Ops perspective a setup occurs when the Ops team triggers a call, makes contact, and sets up a payment Settlement: (general banking industry definition) Settlement involves the finalization of a payment, so that a new party takes possession of transferred funds web link Daily Settlement Total: NOTE: some of these values may be (-) in sign in the loan_tran table NOTE see Grant’s code here Add In: Scenarios: Payment Payment Return Refund Credit Credit Return System Adjustment Agent Error Overpayment Advancement Return Check Return - Refer to Maker Advancement Events: Payment - Unsuccessful Transaction Payment Return - Unsuccessful Transaction Subtract Out: Scenarios: Funding Return Rescission Origination Transaction Code: WO SSN NOTE: ssn_7 is the last 7 characters of the SSN value You can find SSN for an application in this DPE (dw_core) table: dw_core.dim_applicant Stay Rate: from Grok percentage of delinquent accounts that remain in the same delinquency status or bucket (e.g., 30 days past due, 60 days past due) over a specific period, without progressing to a worse delinquency stage, curing to current status, or being resolved (e.g., paid off or charged off) A high stay rate in early delinquency buckets might indicate stalled collections efforts, where accounts are neither improving nor worsening sunup-ecs-stg.tech.braviant.com: BSF Stage environment database Suppression: DA: assume this applies to keeping people from receiving DM offers (suppressed from the Darwill mailings process) See Confluence page: https://balancecredit.atlassian.net/wiki/spaces/AnalyticsTeam/pages/2909339706/DM_Final_Suppression_File_+date See Looker report of suppressed individuals: https://braviantholdings.looker.com/looks/1175 Suspense Account: general business definition a suspense account is a bookkeeping account where funds are categorized temporarily for various reasons e.g. funds are received for an ambiguous reason and the company needs more clarification to accurately categorize funds e.g. borrower makes a partial payment and the company temporarily holds the payment until the balance of the full payment is received see: Investopedia article – T – link TBS: Traditional Bank Statement TLS: TLS (Part of NCP) is a Servicer for the CC Bank for its program. It coordinates and maintains Program info in its Loan Info System(LIS) Assume TLS will go away as the Bank Direct program ramps up (just applicable to loans orginated prior to Bank Direct) Transaction Codes (tran_codes): code describing different transaction types ADV: advance ADV1: advance (not sure what’s the difference) CRE: credit PART: participation PAY: payment RADV: advance return REFI: refinance VADV: voided advance (example: ACH exception due to an incorrect bank routing number) VPAY: voided payment WO: write off (example: bankruptcy) Trial Balance Tables A trial balance is a bookkeeping worksheet in which the balances of all ledgers are compiled into debit and credit account column totals that are equal dw_reporting_views.cc_bank_trialbalance_dpd_audit dw_reporting_views.cc_bank_trialbalance_dpd_daily Dan: I think the trial balance “tallies” up these columns: principal_balance days_past_due accrued_interest From Grant (current settlement process) Should have a record for each ‘open’ loan, showing its running balance. Aka what the outstanding balance is for each loan through ccb. Once they are paid off or otherwise they should drop off the report. Key column is bsf_loan.remaining_loan_balance (same as lp.loan_status_archive.principal_balance for a date) TU: Transunion – U – link UPL: Unsecured Personal Loans Seems like a FinTech acronym UW: Underwriting uwp: e.g. uwp database schema | “underwriting and pricing” – V – link Vintage or Loan Vintage: bucketing loans by the calendar month (e.g. 2024-05) they were originated. Use those buckets to calcuulate metrics to monitor loans in a more meaningful way (e.g. a loan just originated has different characteristics than a loan half way through its term) VOI: Verification of Income Vendor EDGE (https://www.edgescore.com) see EDGE above DA: assume this is a term in general use (not just by EDGE) EDGE has there own flavor of VOI Core data elements which we can use for decisioning See EDGE API docs: https://docs.edgescore.com/docs/verification-of-income-voi-core – W – link WOAC: assume (?) Write Off Accrued View this page on GitHub

Docs