--- id: financeiros/funil-originacao-credito industry: Financial services usage: widely used effort: medium grain: Application x stage (first entry); task x assignee for the SLA --- # Credit origination funnel with SLA How many applications come in, how many reach approval and disbursement, where they get stuck and for how many business hours, by channel, partner and loan officer. ## Executive summary **For:** Banking correspondents, lending fintechs, credit assignment firms and funding platforms that run their pipeline in Pipefy, Zeev or an in-house core system. **What it solves** - The number of applications per stage matches the pipeline system, with no duplicates or test cards. - Management sees where applications get stuck, with whom and for how many business hours. - Losses and declines show up by reason, channel and partner. **Why it matters:** In lending, the pipeline is governed by documents and checks, and every idle day is an application the customer takes to another bank. The funnel by stage shows how much will turn into issued loans this month, and the SLA shows which stage and which analyst is holding up the queue. Without both views together, sales pushes for volume while the bottleneck sits in underwriting. **Business questions it answers** - How many applications reached credit approval this month, by channel and partner? - What is the conversion from qualified to signature by loan officer? - Which stage do applications get stuck in most, and for how many business hours? - Why are we losing or declining applications? - Which high-value applications are in underwriting right now? - How many loans should we issue this month at the current conversion rate? **What you need** - The pipeline system connected to Nekt (Pipefy, Zeev or a lending core system), with the phase or task history - The mapping from phase to funnel stage, by product - The team's working hours and the calendar of holidays and company-wide breaks **What you get** - A table per application with the date of first entry into each stage - A monthly funnel by channel, partner and loan officer, with losses and open inventory - An AI agent that answers questions about the pipeline with the right rules - Alerts for tasks stalled past their deadline ## Detailed ### The problem A credit application lives across several pipes, each with differently named phases, custom fields packed into an array and duplicate cards when the customer comes back through another channel. Counting by current phase loses the month's history, and joining the core system without deduplicating inflates the funnel. For the SLA, measuring in calendar days and in UTC blames the analyst for weekends and holidays. ### Business rules 1. **One row per application, one date per stage.** Pivot the phase history (firstTimeIn in Pipefy) into one date column per stage, using the first entry. Pipefy custom fields come in the fields array and must be exploded and pivoted by field_id. The month's funnel counts the application in the month it entered each stage. (Avoid: Counting by the card's current phase.) 2. **Fixed stages and a complete grid.** Map each pipe's phases to a fixed list of business stages, for example: lead, qualified, sent to credit, credit approved, legal review, signature and disbursement. Generate the complete month x channel x stage grid with zero where there were no applications, so the chart does not skip stages. (Avoid: Using each pipe's phase name directly in the chart.) 3. **Lost means the last time in Lost.** A card can move to Lost, come back and be lost again. For the loss month and reason, use the last entry into Lost (lastTimeIn), not the first. The reason comes from the main closing reason field. (Avoid: Using the first time in Lost.) 4. **Duplicates and tests come out before counting.** Remove cards whose closing reason starts with duplicidade (duplicate), in any spelling (compare lowercased and without spaces). When the same CPF (individual tax ID) has several cards in the product, keep the card with the latest activity. Test and sandbox cards also come out. (Avoid: Counting every created card as a lead.) 5. **The pipeline counts, the core only enriches.** The pipeline system is the source of the count. The core system or database comes in via LEFT JOIN from the card, after deduplication: one record per card id and one per normalized CPF (digits only), the most recent. Keep a column with how the match was made: by id, by CPF or no match. A raw CPF never goes into the final table, only hashed or masked. (Avoid: Joining core and card with an INNER JOIN or without deduplicating the core.) 6. **Channel by partner origin.** Classify the channel by origin: paid media and the company's own website are B2C; correspondents and commercial partners are B2B. For a product that only comes through brokers or partners, the B2C and B2B split does not apply. Do not store a total row in the gold table; sum in the dashboard. (Avoid: Storing a total row alongside B2C and B2B and summing everything.) 7. **Amounts as Brazilian-formatted text.** The credit amount on the card arrives as text '1.234,56'. Remove the thousands separator, replace the comma with a period and only then convert. A stage's VGV (gross credit value) is the sum of the amounts of the applications that reached it. (Avoid: Converting the text directly, which turns into null or multiplies the amount.) 8. **SLA in the team's business hours.** Time in a stage or task = business hours between start and end, within working hours (for example 9 a.m. to 6 p.m.), excluding weekends, national and local holidays and company-wide breaks, with half days where they apply (such as Ash Wednesday). A task still open uses now in America/Sao_Paulo as the end. (Avoid: A difference in calendar days calculated in UTC.) 9. **Send-backs are rework, not SLA.** Tasks closed with a result of sending the request back come out of the stage time and go into the rework rate. When the same stage is done again, open a new execution block, and a sub-block when the assignee changes, to measure each pass by whoever did it. (Avoid: Including the sent-back task in the stage's average time.) ### Pipeline phase to funnel stage Sample mapping. Each pipe and product has its own. - **New lead, contact attempt, automated response** → Lead - **Qualified, in negotiation, awaiting data** → Qualified - **Credit analysis, pre-analysis, sent to the bank** → Sent to credit - **Credit approved, approved by committee** → Credit approved - **Appraisal, legal review, pending documents** → Legal review - **Issuance and signature, contract signed** → Signature - **Registration and disbursement, completed successfully** → Disbursement - **Lost** → Loss, by last entry (`closing reason`) - **Reason starts with duplicidade (duplicate)** → Out of the funnel ### Definitions | Metric | Definition | |---|---| | Applications in stage | distinct cards with first entry into the stage within the month, by channel, partner and loan officer | | Stage-to-stage conversion | applications that reached stage B / applications that reached stage A, in the same funnel-entry month cohort | | Stage VGV | sum of the credit amount of the applications that reached the stage in the month | | Days to signature | calendar days between card creation and first entry into signature | | Operational inventory | cards in an open stage right now, by assignee (pre-sales in the early stages, loan officer from underwriting to registration), with average aging and follow-up status | | Business time in stage | business hours between entering and leaving the stage or task, excluding sent-back tasks | | % on time | tasks completed within the stage SLA / tasks completed in the period, by stage and analyst | | Decline rate by reason | applications declined by reason (employer not on the list, income below the minimum, failed score) / applications analyzed in the period | ### Pitfalls - **Loss reason counted twice.** One pipe had two closing reason fields. Using both counted the same loss twice in the lost applications report. How to avoid: Use only the main reason field. - **An owner field that is almost never filled.** In one product, the referrer field was filled on about 6% of cards, and conversion by person came out almost entirely without an owner. How to avoid: Measure the fill rate and use the reliable role, such as the loan officer. - **Team hardcoded in the code.** Lists of team emails and operator ids inside the transformation went stale with every hire, and the SLA silently lost people. How to avoid: Keep the team in a connected table, with start and end dates. - **Task replaced mid-year.** A pipeline task was replaced by another one from a certain date, and the stage's historical series dropped by half. How to avoid: Map the old and the new task to the same stage, with effective dates. - **The event's pipeline is not the current one.** In the CRM, the pipeline recorded on the stage change event was not always the deal's current pipeline, and applications showed up in the wrong funnel. How to avoid: Read the deal's pipeline, not the event's. - **Product without CPF.** One product had no CPF on the card, and enrichment from the core system failed silently. How to avoid: Flag those cards as no match and report the match coverage. ### Acceptance test - [ ] For a closed month, applications per stage match the count of cards in the pipeline system with first entry in the month, already without duplicates and tests. - [ ] Every month x channel x stage combination exists in the gold table, with zero when there were no applications. - [ ] Each CPF appears only once per product, and no raw CPF appears in the final table. - [ ] A task started Friday at 5 p.m. and completed Monday at 10 a.m. measures 2 business hours (9 a.m. to 6 p.m. working hours, no holiday). - [ ] Sent-back tasks do not count toward the stage's average time and appear in the rework rate. ### Context document ```text Origination funnel and pipeline SLA - Source of the count: the pipeline system (). The core system only enriches via LEFT JOIN, deduplicated by card id and by normalized CPF (individual tax ID). - Each application counts in the month of its first entry into each stage. Fixed stages: lead, qualified, sent to credit, credit approved, legal review, signature, disbursement. - Lost: use the last entry into Lost and the main closing reason field. - Exclude cards whose reason starts with duplicidade (duplicate) and test cards. One card per CPF per product, the one with the latest activity. - Channel: paid media and website = B2C; partners and correspondents = B2B. There is no total row in the table; sum B2C and B2B. - The credit amount comes as Brazilian-formatted text: normalize it before summing. - Stage and task time in business hours, working hours <9 a.m. to 6 p.m.>, excluding weekends, holidays and company-wide breaks, time zone America/Sao_Paulo. An open task uses now as the end. - Sent-back tasks do not count toward the SLA; they count as rework. - Never show a full CPF. Use the hash or the masked CPF. Per-client exceptions: - : the mapping from pipe phases to funnel stages is in . - : SLA for stage = business hours. - : product has no B2C channel; partners are brokers. ``` ### Variations - **Loan issuance projection:** Most recent month's leads x average ticket of signed applications x conversion scenarios (pessimistic, base and optimistic). - **Weighted forecast:** Fixed probability by card temperature (approved by committee, hot, warm, cold); a card without a temperature takes the lowest. - **Claims and support:** The same business-hours SLA for claims awaiting an operations response and tickets by partner.