CRM: deals, pipelines, stages, loss reasons and tasks
Entry by creation date, sale and loss by close date
Funnel, losses by stage, cycle time and forecast against the target
Leadership dashboard, AI agent and email report
What it solves
- Leads and sales stop contradicting each other: each metric uses the right date, entry or close.
- Leadership knows at which stage the funnel loses the most deals and why.
- The monthly forecast accounts for qualification and the historical loss rate of each stage, and shows whether the target will be met.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- CRM connected to Nekt (deals, pipelines, stages, loss reasons and, if available, stage history and tasks)
- The list of pipelines that are new sales and the school's monthly target
- The qualification rule used in the forecast (for example, the deal score)
What you get
- A table with one row per deal: pipeline, canonical stage, status, entry and close dates, amount, loss reason and cycle time
- Dashboard with leads, open pipeline, sales, losses by stage and target projection
- An AI agent that answers questions about the funnel without calling a prospect an enrolled student
- Daily or weekly email report with new deals and pending items
Where to activate
Leads, pipeline, sales, losses by stage and target projection, with each metric's date stated explicitly.
Coordinators ask about prospects and deals and get answers that never confuse a deal with an enrollment.
Previous day's new deals by pipeline, and weeks running Sunday to Saturday in America/Sao_Paulo.
One source, but it requires agreeing on pipeline scope, stage map and forecast rule with the school. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
The CRM stores one row per deal with several dates, and each question needs a different one. Counting sales by creation date or leads by close date produces a funnel that does not match the CRM's own report. On top of that come pipelines that are not new sales, stages named after enrollment that are not enrollment, deals duplicated by joins with contacts and a forecast that treats every open deal as a sure sale.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
Leads in the funnel, open pipeline and distribution by stage use the entry date (`created_at`, `add_time`). Sales, revenue, losses, conversion and cycle time use the close date (`closed_at`, `won_time`, `lost_time`). Convert everything to America/Sao_Paulo before truncating to the day.
Only new-sales pipelines are included. In-company sales, waiting list, renewal, CS, partnership and event pipelines stay out of the leadership dashboard. Store the pipeline type as a column in the base table and filter in the final layer, instead of hiding the filter. Confirm the real pipeline by how it is used: in one case, the new-sales pipeline had a test name.
A deal has several contacts, products and tasks. After any join, count the distinct deal id. Contacts, tasks and products are counted by their own id.
Use the native status: won (`won`), lost (`lost`), in progress (`ongoing`) and paused (`paused`). Closed = won + lost. Open = in progress + paused. Deleted deals (`deleted`) are excluded; in Pipedrive, join `deals` with `deals_archived` so you do not lose history.
A stage or field called Enrollment completed is a sales classification. Official enrollment comes from the academic system. A CRM contact may be a mother, father or guardian, not the student. In answers, talk about deals, prospects and sales.
Where the funnel loses = lost deals at the stage / total lost deals in the close month. Do not compute a loss rate within the stage: wins only happen at the last stage, so the rate becomes 100% at every other stage.
Only in-progress deals in the proposal and negotiation stages are included. Weighted value = amount × qualification probability × (1 - stage loss rate over the last 3 months). The probability for each score is the school's rule (for example, 80%, 60% and 40%); scores without a rule count as unqualified, not as 100%.
A paused deal counts as open in the pipeline, but does not enter the month's forecast. Awaiting payment is also a stage: in B2B sales, closing can take about 14 days until the client's purchasing department pays.
Average cycle = sum of cycle days / number of wins with cycle filled in. The cycle starts at the card creation date recorded by an automation when one exists, because the native date changes when the deal is duplicated or recreated.
Stage names come with emoji, spaces and variations across pipelines. Remove emoji and spaces and map each stage of each pipeline to a canonical phase (lead, contact, qualification, proposal, negotiation, closing) through a reference table, not through a rule repeated in every query.
Status and stage: what each one counts
Native deal status and canonical stage phase.
Definitions
Pitfalls that already happened
At one school, the new-sales pipeline used by SDRs and closers was named like a test pipeline, and anyone who filtered by the expected name got zero sales.
Validate the pipeline scope with the sales team and record the list in the context document.
In CRMs like Clint, the card only stores the current source and pipeline. Lost deals are moved to a win-back pipeline and the original entry disappears.
Use the stage history (`deal_history`) to rebuild the journey and measure time in stage by median, never by sum or average of averages.
Lost deals still had open tasks and inflated each salesperson's overdue list.
Overdue task = open status, due date before today in America/Sao_Paulo and deal still open; a weekly routine can clean up tasks on lost deals.
Prospecting and connection dates came in custom fields as `YYYY-MM-DD HH:MM` text, sometimes with a single-digit hour, and Spark's strict parser broke the load.
Use lenient parsing (in Spark, `timeParserPolicy` LEGACY) and treat invalid text as null.
In a sales ERP, the funnel status set by the consultant said sale on leads that never became a contract.
Converted = an actual sale originated from the lead exists, not the status chosen by the salesperson.
The CRM's PJ or PF field (company or individual) was filled in on few deals, and Not informed became the largest slice.
Use the answer from the qualification questionnaire as a fallback, matched by the contact's email, and show how many came from the fallback.
Acceptance test
The case is done when every item passes.
- For a closed month, sales and revenue match the CRM's native report filtered by the same pipelines and by close date (zero difference in count).
- The final table has one row per deal: total rows = total distinct ids.
- In each month, the stage loss shares add up to 100%.
- No paused deal appears in the forecast, and the weighted forecast is less than or equal to the in-progress amount in the proposal and negotiation stages.
- No deal from an excluded pipeline (in-company, waiting list, renewal, CS, partnerships) appears in the leadership dashboard.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Enrollment sales funnel - Scope: only the new-sales pipelines <list of pipelines>. In-company, waiting list, renewal, CS, partnerships and events stay out of the leadership dashboard. - Leads in the funnel, open pipeline and distribution by stage: entry date (created_at). Sales, revenue, losses, conversion and cycle time: close date (closed_at). Time zone America/Sao_Paulo. - Status: won = sale, lost = loss, ongoing and paused = open. Paused deals do not enter the forecast. - Count deals with a distinct count of the deal id, never a row count after joining contacts, products or tasks. - A CRM stage or field named Enrollment completed is a sales classification, not official enrollment. A CRM contact may be a guardian, not the student. - Loss by stage = lost deals at the stage / lost deals in the month. Do not use a loss rate within the stage. - Forecast = amount x qualification probability x (1 - stage loss over the last 3 months), only in-progress deals in the proposal and negotiation stages. - Average cycle = sum of days / wins with cycle filled in. - Overdue task = open status and due date before today in America/Sao_Paulo. - State the date of the last load in every answer with a time range. Null is not zero. Client exceptions: - <client>: monthly target of R$ <value>; probability by score: <score> = <x>%. - <client>: the real new-sales pipeline is <pipeline name>, despite its name. - <client>: B2B sales stay in Closing for about <n> days until payment.
Build the "Enrollment funnel with forecast and targets" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/educacao/funil-comercial-crm-educacao.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.
Variations
An SDR pipeline separate from each campus's pipeline or from PJ and PF (company and individual) segments, with handoff from the SDR to the campus.
Conversations, messages and tasks by salesperson, counting only named human operators; events and notes are not conversations.
Monthly target by product and source from a spreadsheet, compared with actuals from the CRM.