--- id: varejo/funil-vendedor-crm industry: Retail and e-commerce usage: common effort: medium grain: Deal (one per opportunity) and stage passage; aggregates salesperson x month --- # Salesperson funnel in the CRM Opportunities, wins, losses and response time for each salesperson and store in the CRM, with the wins that became an invoice in the ERP, and without counting after-sales, satisfaction surveys or lead closure as a sale. ## Executive summary **For:** Store chains and e-commerce businesses with salespeople or SDRs who log opportunities and quotes in the CRM and invoice in the ERP. **What it solves** - Each salesperson's conversion counts as a win everything that was sold, not only the deals that went through the satisfaction survey. - After-sales, delivery, exchanges and collections leave the sales funnel and stop distorting the win rate. - Each salesperson's value is what became an invoice in the ERP, linked to the CRM deal. **Why it matters:** In consultative retail, the CRM holds the effort and the ERP holds the sale. Each side alone misleads: the CRM has final stages that are not sales and values typed by the salesperson, and the ERP often does not know who sold. Without the bridge, the ranking rewards whoever fills in the CRM, not whoever invoices, and management cannot tell whether the problem is in response, qualification or closing. **Business questions it answers** - What is each salesperson's conversion this month, and how much did they actually invoice? - How many leads came in through the SDR and how many were handed to a salesperson? - What is the team's first response time, by hour of day and by owner? - How many deals does each store have in negotiation today and what are they worth? - Which deals won in the CRM have not yet become an invoice in the ERP? - Why are we losing deals: price, no reply or product? **What you need** - CRM connected to Nekt with deals, stage history, stages and users - ERP connected with orders and invoices, and a field that links the order to the deal - The list of sales pipelines, win stages and system accounts, agreed with management **What you get** - A table per deal with pipeline, stage, category, owner, store and dates - Indicators by salesperson and month: opportunities, wins, conversion, invoiced value and ticket - Daily store card with yesterday's sales and deals in negotiation by salesperson - An AI agent that answers about the team with the right win rule ## Detailed ### The problem The retail CRM mixes sales, after-sales, collections and partner relationship pipelines in the same place. The final stage of one pipeline may be the satisfaction survey, and of another, the closure of the SDR's service. The deal value is typed by the salesperson, and the ERP, which has the sale, sometimes returns a generic salesperson through the API. Whoever counts the last stage as a win, or adds all pipelines together, delivers rates of 20% or 97% for the same team. ### Business rules 1. **A win is the sum of the sales stages.** Classify every stage of every pipeline in a reference table: in progress, won, lost or postponed. In pipelines where the deal goes through won to be invoiced, won invoiced and then a final satisfaction survey stage, all three count as a commercial win. The CRM semantics help (in Bitrix24, STAGE_SEMANTIC_ID S for won, F for lost, P for in progress), but check the name: a final stage is not always a sale. (Avoid: Counting as a win only stage C:WON, which was the completed survey.) 2. **Only sales pipelines go into conversion.** Filter by pipeline (in Bitrix24, CATEGORY_ID). After-sales and service pipelines (exchange, delivery, complaint, warranty), scheduling, collections and partner programs are not sales and stay out of conversion, even if they have success stages. A deal created directly in an operational pipeline is not a lead. (Avoid: Computing the win rate with all CRM pipelines together.) 3. **Choose the entity by fill rate.** Before modeling, measure fill rates: in many operations the service lives in deals, and the leads entity has abandoned SDR and status fields, filled close to zero. The custom SDR field may also be empty; in that case the owner is ASSIGNED_BY_ID. Write the choice in the context document. (Avoid: Building the funnel on the leads entity because the name sounds right.) 4. **SDR and salesperson are stages of the same deal.** When the lead enters an SDR pipeline and, once qualified, is transferred to a sales pipeline, it is the same deal. SDR origin = the deal has any SDR pipeline stage in its history. Handoff = it is in or went through the sales pipeline coming from the SDR. The SDR's final stage (lead closure) is not a sale: the sale is measured in the sales pipeline. Count each deal once in the total. (Avoid: Adding deals from both pipelines and counting the SDR closure as a win.) 5. **Real owner, no system accounts.** The salesperson is the deal owner in the CRM, resolved through the users table, without grouping accounts into names. Administrator, marketing, performance, integration and design accounts stay out of the ranking through a list of ids maintained by management; their deals appear in the total as System. If the CRM does not export names, keep an id-to-name mapping. (Avoid: Grouping several accounts into one name or leaving the administrator in the ranking.) 6. **The CRM measures the funnel, the ERP measures the sale.** The CRM is the source of opportunities, stages, origin and activity. The ERP is the source of realized sales. Link the won deal to the order or invoice through an explicit field (deal id saved on the order, quote or cart number), using the composite store and document key. The deal value in the CRM stays as a reference; invoiced value is the sum of the linked sales invoices. A win without an invoice after the agreed delivery period becomes a pending item. (Avoid: Using the CRM deal value as the salesperson's revenue.) 7. **CRM salesperson linked to ERP salesperson with validity dates.** Keep a table of CRM id, ERP salesperson, store, start and end. Some ERPs return, through the API, a generic salesperson on every order: then the CRM owner is the only source of who sold. Without a registry, the month's assignment can be inferred as the store where the salesperson invoiced the most that month. A month with no sales in a store does not mean vacation or dismissal. (Avoid: Ranking salespeople by the ERP salesperson field without checking that it is filled in.) 8. **Response time in business hours.** First response time = from entry into the pipeline (first record in the stage history) to the first move out of the entry stages, counting only the declared business hours (for example, 8 am to 6 pm, Monday to Friday, America/Sao_Paulo). Convert times once: Bitrix24 stage history comes in UTC and open channel sessions come in UTC+3. Report the median, because the average rises with leads that arrive at night. (Avoid: Average wall-clock time, with nights and weekends included.) 9. **The cohort is the creation date.** Incoming opportunities and conversion are counted by the deal's creation date (DATE_CREATE in America/Sao_Paulo), which matches the CRM export. The entry date in the history differs for some deals and creates daily differences. Recent cohorts are still open: show the current month's conversion as partial. (Avoid: Counting entries by the first row of the stage history.) 10. **Logged activity is not outbound effort.** In WhatsApp integrations with the CRM, the customer's incoming message becomes an activity, but the reply the team sends through the chat does not. Automatic tasks also become activities. Activity counts measure incoming demand; the effort signal is first response time and stage progress. (Avoid: Ranking the team by number of activities.) ### How each stage enters the salesperson funnel Reference table by pipeline and stage, maintained with sales management. - **New opportunity, qualification, negotiation** → In progress (`STAGE_SEMANTIC_ID = P`) - **Won, to be invoiced** → Commercial win, no invoice yet - **Won, invoiced (confirmed by the ERP integration)** → Commercial win with invoice - **Finished after satisfaction survey** → Commercial win (already passed through won) (`C:WON`) - **Lost** → Loss, with reason (`STAGE_SEMANTIC_ID = F`) - **Future opportunity** → Postponed, out of the month's rate - **Lead closure in the SDR pipeline** → End of SDR service, not a sale (`C:WON`) - **After-sales, delivery and exchange pipelines** → Out of the sales funnel (`CATEGORY_ID`) - **Collections and partner pipelines** → Out of the sales funnel (`CATEGORY_ID`) ### Definitions | Metric | Definition | |---|---| | Opportunities created | deals in sales pipelines by creation date in America/Sao_Paulo, by salesperson, store and month | | Commercial wins | deals in the cohort that are in or went through a stage classified as won | | Conversion rate | commercial wins / opportunities created in the same cohort; partial while the cohort is open | | Wins with invoice | commercial wins linked to at least one sales invoice in the ERP | | Invoiced value | sum of ERP sales invoices linked to the salesperson's wins, by invoice date, only items with a sales CFOP (tax operation code) | | Salesperson average ticket | invoiced value / wins with invoice, recomputed for each slice | | Handoff rate | deals with SDR origin that reached the sales pipeline / deals with SDR origin, by creation month | | First response time | median, in business hours, between entry into the pipeline and the first move out of the entry stages | | Pipeline in negotiation | daily snapshot of deals in qualification or negotiation by salesperson and store: count and declared value | ### Pitfalls - **Conversion below half.** The win rate used only the final satisfaction survey stage, which was rarely filled in. Adding won to be invoiced and won invoiced, real conversion for the period was close to 52%. How to avoid: Classify all sales stages as won in the reference table. - **SDR with a 97% win rate.** About 97% of SDR pipeline deals ended in the stage marked as won, which was the closure of the service, and the SDR showed up as the company's best salesperson. How to avoid: Measure the SDR by response, qualification and handoff; wins only in the sales pipeline. - **Administrator at the top of the ranking.** Administrator, marketing and integration accounts owned thousands of imported deals and showed up ahead of the salespeople. How to avoid: List of system ids out of the ranking and in the total as System. - **Empty owners.** An old rule grouped several CRM accounts into a few SDR names. The distribution was distorted and part of the deals ended up with no owner. How to avoid: Use the real owner by id; grouping only as an extra view, if management asks. - **Generic salesperson in the ERP.** The ERP returned through the API the same salesperson on every order of a chain, and the revenue ranking put all sales under one name. How to avoid: Take the salesperson from the deal owner in the CRM and link the order to the deal. - **A team that seemed to only receive.** Almost all activities were incoming, because chat replies did not generate records, and the dashboard concluded that the team did no outbound contact. How to avoid: Use first response time and stage progress as the effort signal. ### Acceptance test - [ ] For a closed period, opportunities created by day, owner and stage match the CRM export one by one. - [ ] No deal from after-sales, collections or partner pipelines appears in the sales funnel, and no system account appears in the ranking. - [ ] Commercial wins = sum of stages classified as won, and the percentage of wins with an invoice is reported next to it. - [ ] For a store and a month, the sum of the salespeople's invoiced value plus the Not linked to CRM row equals the store's net sales in the ERP. - [ ] The first response time of a deal that came in on Friday at 5:50 pm and was moved on Monday at 8:10 am is 20 minutes. ### Context document ```text Salesperson funnel in the CRM (retail) - Sales pipelines (CATEGORY_ID): . After-sales, delivery, exchange, collections, scheduling and partners stay out of conversion. - Commercial win = stages classified as won in the reference table (won to be invoiced, won invoiced and finished after survey). Never only the last stage. - Lead closure in the SDR pipeline is not a sale. The SDR is measured by response, qualification and handoff. - SDR origin = any SDR pipeline stage in the history. Handoff rate = handed off / SDR origin. - Salesperson = deal owner (ASSIGNED_BY_ID). System accounts stay out of the ranking. - Opportunities and conversion by creation date (DATE_CREATE, America/Sao_Paulo). The current month's cohort is partial. - Invoiced value comes from the ERP: sales invoices linked to the won deal, composite key store + document. The CRM deal value is only a reference. - First response time: median in business hours , from the first record in the history to the first exit from the entry stages. - Activity count measures incoming demand, not team effort. - The salesperson x store link has validity dates; never apply the current store to the past. Client exceptions: - : win stages of pipeline = , , . - : ERP returns a generic salesperson; the salesperson comes only from the CRM. - : SDR pipeline hands off to pipelines . ``` ### Variations - **Proposals in the ERP:** At car dealerships, the funnel lives in the management system: proposals, approved, approved by financing, sold, canceled and returned by salesperson and month, with average discount per proposal. - **Referring professionals:** When the person who refers (painter, architect, installer) is not the buyer, the sale is attributed to the buyer and to the commissioned professional in separate columns, matching the CRM record with the ERP. - **Service on open channels:** One row per conversation session with routed channel, operator, time to acceptance and rating, for service SLA and productivity.