---
id: educacao/funil-comercial-crm-educacao
industry: Education
usage: widely used
effort: medium
grain: Deal (one row per opportunity) and stage movement
---

# Enrollment funnel with forecast and targets

How many prospects came in, where the funnel loses them and how much will close this month, with every number on the right date and the forecast checked against the target.

## Executive summary

**For:** Schools, colleges, short-course providers and corporate education companies that sell enrollments or courses through a sales team working in a CRM.

**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.

**Why it matters:** In education, the enrollment campaign has a short window: finding out at the end of the month that the target will not be met is too late. A funnel counted on the wrong date, with duplicated deals or with pipelines that are not new sales, leads to wrong decisions about budget and team. And the same funnel feeds the AI agent that coordinators and educators query every day.

**Business questions it answers**

- How many prospects entered the funnel this month, by campus and by course?
- Are we going to hit this month's sales target?
- At which stage does the funnel lose the most deals, and for what reason?
- What is the average time between a lead coming in and the sale?
- Which deals have been stuck in the proposal stage for more than a week?
- How many deals have overdue tasks, by salesperson?

**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

## Detailed

### The problem

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.

### Business rules

1. **Each metric has its own date.** 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. (Avoid: Counting the month's sales as the deals created in the month.)
2. **Explicit pipeline scope.** 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. (Avoid: Adding up every CRM pipeline in the same dashboard.)
3. **Count distinct deals.** 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. (Avoid: Counting rows after joining deals with contacts or products.)
4. **CRM status defines open and closed.** 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. (Avoid: Classifying a win by the stage name.)
5. **A CRM stage is not an enrollment.** 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. (Avoid: Answering how many students were enrolled by counting won deals.)
6. **Loss by stage is share of losses.** 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. (Avoid: Lost deals at the stage / deals that passed through the stage.)
7. **The forecast weighs qualification and stage loss.** 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%. (Avoid: Adding the value of the entire open pipeline as the month's forecast.)
8. **Paused stays out of the forecast.** 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. (Avoid: Paused deals added to the forecast.)
9. **Average cycle only over deals with a cycle.** 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. (Avoid: Dividing the sum of days by all wins, including those without a date.)
10. **Stages normalized in a canonical map.** 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. (Avoid: Comparing stages by raw text across different pipelines.)

### Status and stage: what each one counts

Native deal status and canonical stage phase.

- **status won** → Sale, on the close date (`closed_at / won_time`)
- **status lost** → Loss, on the close date, with reason (`lost_reason`)
- **status ongoing** → Open, enters the forecast if in proposal or negotiation (`ongoing`)
- **status paused** → Open, out of the forecast (`paused`)
- **Lead, MQL** → Top: prospect who converted through a form or conversation
- **Contact, SAL** → Qualification: profile and ability to pay confirmed
- **Presentation, Proposal, SQL** → Proposal sent with syllabus, price and installments
- **Negotiation** → Negotiating terms and conditions
- **Closing** → Approved, awaiting payment
- **Enrollment completed (stage or field)** → Sales classification, not official enrollment

### Definitions

| Metric | Definition |
|---|---|
| Leads in the funnel | distinct deals in new-sales pipelines with entry date in the period |
| Open pipeline | sum of the amount of in-progress and paused deals, by entry date |
| Closed sales | distinct deals with won status and close date in the period |
| Sales revenue | sum of the amount of won deals, by close date |
| Conversion rate | won / (won + lost) with close date in the month |
| Stage loss share | lost deals at the stage / lost deals in the close month; adds up to 100% in the month |
| Average cycle | sum of days between entry and win / wins with cycle filled in, by close month |
| Weighted forecast | sum of amount x qualification probability x (1 - stage loss over the last 3 months), only in-progress deals in the proposal and negotiation stages |
| Target projection | revenue closed in the month + weighted forecast, compared with the monthly target; alongside, the projection at the daily run rate |

### Pitfalls

- **Pipeline with a misleading name.** 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. How to avoid: Validate the pipeline scope with the sales team and record the list in the context document.
- **Current source erases history.** 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. How to avoid: Use the stage history (`deal_history`) to rebuild the journey and measure time in stage by median, never by sum or average of averages.
- **Open task on a lost deal.** Lost deals still had open tasks and inflated each salesperson's overdue list. How to avoid: 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.
- **Date in a text field.** 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. How to avoid: Use lenient parsing (in Spark, `timeParserPolicy` LEGACY) and treat invalid text as null.
- **Consultant status is not conversion.** In a sales ERP, the funnel status set by the consultant said sale on leads that never became a contract. How to avoid: Converted = an actual sale originated from the lead exists, not the status chosen by the salesperson.
- **Customer type almost empty.** The CRM's PJ or PF field (company or individual) was filled in on few deals, and Not informed became the largest slice. How to avoid: 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

- [ ] 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.

### Context document

```text
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.
```

### Variations

- **SDR and campuses:** 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.
- **Team productivity:** Conversations, messages and tasks by salesperson, counting only named human operators; events and notes are not conversations.
- **Targets by source:** Monthly target by product and source from a spreadsheet, compared with actuals from the CRM.