--- id: financeiros/status-credito-no-crm industry: Financial services usage: widely used effort: low grain: Destination record (contact, deal, card or installment) x send date --- # Credit status back to the CRM Each contract's status, installments, delinquency and collections showing up in the CRM, the email cadence and WhatsApp, with the same number as the core system and no exported spreadsheet. ## Executive summary **For:** Lending fintechs, funding platforms and investment offices where sales, CS and collections work in the CRM and on WhatsApp. **What it solves** - The loan officer sees in the CRM whether the contract is current, overdue or paid off, with the same number as the core system. - The email cadence and messaging fire from the real status, not from an exported list. - Paid-off, overdue and sent-to-collections contracts become lists that update themselves. **Why it matters:** Portfolio data lives in the core system and the team lives in the CRM. Without the write-back, someone exports a spreadsheet every week, the CRM falls days behind the core and the loan officer offers new credit to someone who is overdue or chases someone who has already paid off. With the status back in the CRM, every cadence and every outreach starts from the same number as the close. **Business questions it answers** - Which of the loan officer's customers have an overdue installment today? - Which contracts were sent to the collections agency this month? - How many contracts went overdue on the very first installment? - Who has already paid off and can receive a new credit offer? - Which of the advisor's clients have a negative balance right now? - Who had their disbursement completed and has not yet received the NPS survey? **What you need** - The core system or pipeline connected to Nekt, with contracts and installments - The destination connected as an output (CRM, email or WhatsApp tool) and the field that stores the Nekt id - The list of fields the team wants to see and the format each destination accepts **What you get** - An export table per destination, one row per record, already in the accepted format - Contract status, delinquency and collections updated in the CRM at every load - Tags and lists for the email cadence and for surveys - Ready-made messages per recipient on WhatsApp ## Detailed ### The problem The contract lives in the core system and the relationship lives in the CRM, with different keys and formats that do not talk to each other. Ids arrive as '123.0', dates in a format the CRM rejects, numbers with a period where the spreadsheet expects a comma. Sending the status without a priority rule shows 'overdue' for a contract already paid off, and testing against the production destination creates duplicate contacts across the whole base. ### Business rules 1. **The key is the destination's id, cleaned.** Each row carries the id the destination recognizes: lead or contact id in the CRM, card_id in Pipefy, deal id in Pipedrive, email or CPF (individual tax ID) where the destination only accepts that. Ids read as numbers arrive as '123.0': convert to text and remove the suffix. CNPJ (company tax ID) and CPF as digits only, with leading zeros (14 and 11 characters). (Avoid: Sending the id as a decimal number or the CNPJ without its leading zeros.) 2. **One row per destination record.** Decide the object before building: the contract goes to the deal, the installment to an installment object, the customer summary to the contact. If the contact has several contracts, send the contact a summary (most recent contract, highest delinquency, total outstanding) and the detail to the contract object. When a customer and a lead exist for the same person, the customer takes precedence. (Avoid: Sending one row per contract to the contact and letting the last one overwrite the others.) 3. **Status in order of priority.** A contract with an outstanding amount of zero is Paid off, regardless of the delinquency bucket the core still carries. Otherwise, the current delinquency bucket applies, the maximum delinquency over the contract's life goes in a separate field, and FPD goes as yes or no, never as 1.0. (Avoid: Sending the core's delinquency bucket for a contract already paid off.) 4. **The same schedule as the close.** Days past due, bucket and FPD sent to the CRM come from the same table as the aging and the PDD (loan-loss provision), not from a new calculation in the export. That way the number the loan officer sees matches the risk committee's. (Avoid: Recalculating days past due inside the export query.) 5. **Destination format only in the output layer.** Each destination has its own format: one CRM accepted dates only as dd-mm-yyyy, HubSpot's date property expects yyyy-mm-dd, Brazilian spreadsheets want a decimal comma. Format in an export view per destination and keep the portfolio table with real types (date, number). (Avoid: Storing dates as dd-mm-yyyy text in the portfolio table to serve the CRM.) 6. **Explicit eligibility for each list.** Every list that feeds a cadence has a written rule. A real pre-approved example: a proposal with a partner response that was not declined and has already left pending documents and under review. An application without a partner response goes to an exception list for someone to review, never to the cadence. (Avoid: Sending every application not marked as declined to the cadence.) 7. **Dated, standardized tags.** Segmentation tags follow a fixed lowercase pattern, with spaces replaced by underscores: origin, campaign with date (for example, nps__) and a year tag. Only those who meet the condition get in, such as a completed disbursement for the NPS survey. (Avoid: Tags with spaces and capitalization varying by send.) 8. **A ready-made message per recipient.** For WhatsApp, combine all of a recipient's rows (the advisor or the loan officer) into a single message, one sentence per customer, with default text for empty fields ('(no name)') and formatted amounts. The recipient is the contact id in the messaging tool, filtered by the team tag. (Avoid: One message per customer to the same advisor.) 9. **Sandbox separate from production.** Every export starts as a sandbox version, which writes to a test account or pipeline, and only then gets a production version. Deal creation points to the pipeline and stage by id, looked up beforehand, and each send writes a log so the same record is never created twice. (Avoid: Testing the export directly in the production CRM, with the pipeline chosen by name.) ### Contract fields that go back to the CRM A real example from a lending fintech. Each field comes from the portfolio table, not from the export. - **Lead id in the CRM, without '.0'** → Record key (`id`) - **CCB (bank credit note) number as text** → Contract (`numero_ccb`) - **Installments paid and amount paid** → Payments (`qtd_parcelas_pagas`) - **Today and the maximum over the contract's life** → Days past due (`dias_atraso, atraso_max`) - **Paid off when the outstanding amount is zero** → Delinquency bucket (`faixa_atraso`) - **yes or no** → FPD (`is_fpd`) - **Outstanding balance on the date** → Outstanding amount (`valor_aberto`) - **Agency and send date, dd-mm-yyyy** → Outsourced collections (`escritorio_cobranca`) - **Origin, campaign with date, year** → Tags (`text list`) ### Definitions | Metric | Definition | |---|---| | Days past due | reference date minus the due date of the oldest open overdue installment, per contract, from the portfolio table | | Maximum delinquency | the highest number of days past due the contract has ever had, since origination | | Bucket sent | Paid off when the outstanding amount is zero; otherwise the policy's delinquency bucket on the reference date | | FPD | a contract whose first installment came due and was not paid; sent as yes or no | | Outstanding amount | sum of the balance of the contract's unpaid installments on the reference date | | Sent to collections | a contract with the collections agency and send date filled in, by send month | | Destination lag | hours between the core's last update and the write to the destination, per load | | Match coverage | eligible records with a destination id found / eligible records, per send | ### Pitfalls - **Id with '.0'.** The lead id came from the core file as a decimal number. The CRM did not recognize '123.0' and created new contacts instead of updating the existing ones. How to avoid: Convert the id to text and remove the suffix before sending. - **Paid off showing as overdue.** The core kept the last delinquency bucket after payoff, and the CRM showed a paid-off customer as delinquent to the loan officer. How to avoid: A zero outstanding amount becomes Paid off before any bucket. - **Date silently rejected.** The date was sent in the database's default format and the CRM, which expected dd-mm-yyyy, left the field empty without any error. How to avoid: Test the format of each date field in the sandbox destination. - **CNPJ without zeros.** The CNPJ read as a number lost its leading zeros, and the match with the customer records failed for companies whose CNPJ starts with zero. How to avoid: Store tax IDs as text, digits only, padded with zeros. - **Empty partner response.** Some proposals had no partner response recorded. Filtering only by not declined sent people who were never evaluated to the pre-approved cadence. How to avoid: No response goes to the exception list. - **Deal created in the wrong pipeline.** Deal creation used the default pipeline, and the records landed outside the pipeline and stage the team works in. They had to be moved one by one. How to avoid: Look up the pipeline and stage ids and create directly in them. ### Acceptance test - [ ] In a sample of 50 contracts, days past due, bucket, FPD and outstanding amount in the CRM equal those in the portfolio table on the same date. - [ ] No id sent ends in '.0' and every CNPJ has 14 digits. - [ ] No contract with a zero outstanding amount appears with a delinquency bucket in the destination. - [ ] Reprocessing the export creates no new record in the destination. - [ ] The number of records sent equals the number of eligible records with an id found, and the match coverage appears in the log. ### Context document ```text Credit status in the CRM - Destinations: , , . Each destination has its own export view; the portfolio table has real types. - Key: destination id as text, without '.0'. CPF (individual tax ID) with 11 and CNPJ (company tax ID) with 14 digits, numbers only. - Status: zero outstanding amount = Paid off. Otherwise, the policy's delinquency bucket. Maximum delinquency and FPD (yes/no) in their own fields. - Days past due, bucket and FPD come from the close's portfolio table, never from a calculation in the export. - Date format per destination: = dd-mm-yyyy; HubSpot = yyyy-mm-dd. Decimal comma only in spreadsheets. - Pre-approved: partner response not declined and out of pending documents and under review. No partner response goes to exceptions. - Tags: lowercase, spaces become underscores, date yyyymmdd in the campaign name. - WhatsApp: one message per recipient, one sentence per customer. - Sandbox writes to ; production to . Pipeline and stage by id. Per-client exceptions: - : the CRM contact receives the summary; contracts and installments go to object . - : collections agency and send date come from . - : the NPS survey only goes to those whose disbursement was completed more than days ago. ``` ### Variations - **Installments as their own object:** One row per installment with due date, payment, amount and days past due, so the CRM shows the customer's statement. - **Negative balance for the advisor:** Investment offices: accounts with a negative balance, amount and idle days, in one message per advisor. - **ERP records from the pipeline:** Customer, tags, project, salesperson and payables and receivables created in the ERP from the approved card, with a sandbox run first.