--- id: educacao/evasao-cancelamento-retencao-aluno industry: Education usage: common effort: medium grain: Student x academic period x snapshot; canceled contract; customer x product x month --- # Student dropout and retention Who left, why, when and how much was lost, counting each student once and without confusing the date a record was entered with the date the student dropped out. ## Executive summary **For:** Language schools, K-12 schools, continuous programs and subscription courses that need to track who stays and who leaves. **What it solves** - Dropout, cancellation, non-re-enrollment and transfer become separate categories, counted in the right snapshot. - Canceled amount, refunds and lost revenue come from a single source, without showing zero or double counting. - Retention by cohort and LTV show whether students are leaving earlier and earlier. **Why it matters:** For a school, keeping a student costs less than acquiring a new one, and re-enrollment sets next semester's cash. A dashboard that mixes snapshots counts the same student twice; one that adds cancellations to net sales says nothing was canceled. Both the academic and the financial decision depend on the same number being right. **Business questions it answers** - How many students dropped out or canceled this semester, by campus and by reason? - What was the re-enrollment rate compared with last semester? - How much has been refunded on cancellations so far this year? - Were this month's cancellations from new students or returning students? - Which age group drops out the most? - How much recurring revenue did we lose to churn this month? **What you need** - Academic system connected to Nekt with each student's status per academic period, ideally as dated snapshots - A cancellations or dropouts table with the reason, plus finance data if you want the lost amount - The school's list of statuses: what counts as active, dropped out, canceled and transferred **What you get** - A table per student and period with the status in the latest snapshot, reason, age group and service - Dropout and re-enrollment dashboard by campus, comparing semesters - Cancellations table with canceled amount, penalty and refund, plus retention by cohort with LTV - An AI agent that answers questions about dropouts without inventing the date the student left ## Detailed ### The problem The academic system delivers repeated snapshots of the same student, one per load, and the dropouts source records when the information was entered, not when the student stopped. In finance, a cancellation comes in as a negative line and the canceled sale disappears from the net figure. Without handling this, you count students twice, date the dropout on the wrong day and report that the canceled amount is zero. ### Business rules 1. **Never mix snapshots.** The enrollments table keeps one snapshot per load date. Count each student in the latest snapshot of the academic period, or on the snapshot date requested. Comparisons between semesters use the same point in the enrollment campaign for each. The snapshot date may come in mixed formats (with and without time): convert it with a tolerant parser. (Avoid: Counting the RA (student ID number) in every snapshot of the semester.) 2. **An active student on a date is an interval.** Active on date D = start on or before D and (end after D or end empty). In fixed-term programs, add the condition that expected completion is after D. Retention between two years = students active on the year 1 date who are still active on the same date in year 2. (Avoid: Active = any student with a record.) 3. **Four different exits.** Dropout (the student stopped attending) and cancellation (the contract was terminated) are statuses in the academic system. Non-re-enrollment is a student active in the previous period who does not appear in the new one. Transfer is a change of level, class or service for the same RA in the same period: it is not an exit. (Avoid: Counting a service transfer as dropout.) 4. **Record date is not dropout date.** The dropouts source stores the moment the information was entered or changed. Use that date as the record date and say so in the answer; the actual date the student stopped is not in the source. (Avoid: Answering that the student dropped out on the day the dropout was entered.) 5. **Canceled amount comes from the cancellations table.** In the sales table, a cancellation comes in as a negative line and the net value of a canceled contract is zero. How much was canceled comes from the cancellations table, at the gross value of the canceled sale. Never add the two. (Avoid: Summing net sales to find out how much was canceled.) 6. **No formal process means null, not zero.** A cancellation without a formal process has no penalty, chargeback or refund recorded: those fields stay null. When summing, state how many contracts you are talking about or filter to those with a process. Use the total refund, not the partial ones. (Avoid: Treating a null refund as zero and adding it to the others.) 7. **Recurring revenue only from recurring items.** MRR = sum of installments by due month, recurring category only, excluding canceled invoices (including those paid and later canceled) and commissions. Tickets, sponsorships and events are excluded from MRR, churn and LTV, even when issued with the same document type as tuition. (Avoid: Counting every invoice in the month as recurring revenue.) 8. **Churn on the product card, once.** A cancellation is the lost deal in the product's CS pipeline. A lost renewal does not count on its own: churn sits on the product card. Deduplicate by person + month + product and exclude free trials. (Avoid: Counting the lost renewal and the product card as two churns.) 9. **Lost contracted revenue from the installment number.** When a contract is canceled, future installments are never generated in finance. The lost balance comes from the installment field in NNN/TTT format (current installment of total), not from summing what exists. (Avoid: Summing only the installments that exist in finance.) 10. **Fixed churn maturity scale.** Classify by tenure: early under 1 month, premature from 1 to 3 months, mature from 3 onward. Comparing against the cohort's expected lifetime classified almost all churn as premature and did not discriminate anything. (Avoid: Premature = left before the average lifetime.) ### Student status: what counts In the latest snapshot of the academic period. - **New enrollment** → Active, new student - **Re-enrollment (including confirmed through the portal)** → Active, returning student - **Dropped out** → Exit: stopped attending - **Canceled** → Exit: contract terminated - **Active in the previous period and missing from the new one** → Non-re-enrollment - **Same RA with another level, class or service** → Transfer, not an exit - **Date in the dropouts source** → Record date, not dropout date ### Definitions | Metric | Definition | |---|---| | Active | distinct students (RA) with status new enrollment or re-enrollment in the latest snapshot of the academic period | | Dropouts | distinct students with status dropped out or canceled in the latest snapshot of the academic period | | Re-enrollment rate | re-enrolled in period N / active in period N-1 in the final snapshot, by campus | | Students per class | active / distinct classes in the same snapshot | | Canceled amount | sum of the gross value of canceled sales in the cancellations table, by cancellation date | | Refund | sum of the total refund of cancellations with a formal process; state how many contracts are included | | Financial churn | MRR of the last month before the loss, for lost deals matched to finance, in the month of the loss | | Retention by cohort | customers active N months after joining / customers who joined in the cohort, with a complete-window flag | | LTV | monthly ARPU x expected lifetime (sum of the average retention curve by month of life) | ### Pitfalls - **Canceled came out as zero.** Someone asked how much was canceled and the agent summed the sales table, where each cancellation offsets the sale: the answer was zero. How to avoid: Point the agent to the cancellations table in the context document. - **Age groups in two places.** Age group cutoffs were in a lookup table and also hardcoded in a rule inside the leads table; editing the lookup changed no groups, and two dashboards used different groups. How to avoid: One age group table with order and cutoffs, read by every query. - **Scholarship holder counted by the wrong date.** The scholarship holder's record date was used as the date the scholarship was granted, and the discount showed up in semesters where it did not exist. How to avoid: Join scholarship and enrollment by RA and the same snapshot date; the snapshot date is not the grant date. - **Bulk cancellation in finance.** A batch cancellation marked already overdue installments as canceled, and the customer's last month of MRR disappeared. How to avoid: To find the last month of revenue before the loss, include canceled installments too. - **Recent cohort without a full window.** Cohorts from the last few months showed high 60 and 90 day retention because those months had not yet passed. How to avoid: Flag whether the window is complete and compare only cohorts with the same closed window. - **Refund without a date.** Hotmart has no refund date, and refunds landed in different months depending on the query. How to avoid: Count the refund in the month of the sale and state the rule. ### Acceptance test - [ ] For an academic period, active + dropouts in the latest snapshot match the academic system's status report on the same date. - [ ] No RA appears more than once per academic period in the final table. - [ ] The canceled amount for the year is greater than zero and equal to the sum of the cancellations table, not the sum of net sales. - [ ] The refund sum states how many contracts with a formal process it was calculated over, and contracts without a process stay null. - [ ] No event, ticket or sponsorship invoice enters MRR, churn or LTV. ### Context document ```text Dropout, cancellation and retention - Official source of student status: academic system. Count distinct students by RA (student ID number), as text, with leading zeros. - Never mix snapshots: use the latest one for the academic period or the snapshot date requested. - Active = new enrollment + re-enrollment. Dropouts = dropped out + canceled. Non-re-enrollment = active in the previous period and missing from the new one. A level or service transfer is not an exit. - The dropouts source records the date the information was entered, not the actual dropout date. Say so in the answer. - How much was canceled: cancellations table, gross value. Never sum the sales table, where a cancellation is a negative line and the net comes out as zero. - Cancellation without a formal process: penalty and refund are null, not zero. State the base. Use the total refund. - MRR: installments in the recurring category by due month, excluding canceled ones and commissions. Events, tickets and sponsorships excluded from MRR, churn and LTV. - Churn: lost deal in the product pipeline; a lost renewal does not count on its own; one per person, month and product. - Churn maturity: early under 1 month, premature from 1 to 3, mature from 3 onward. - Null is not zero. State the date of the last load. Client-specific exceptions: - : campus is campus . - : periods are excluded from semester comparisons. - : service derived from level: = ; all others = . ``` ### Variations - **Subscriptions and continuous programs:** NRR and GRR on start-of-month MRR, with expansion and contraction by customer and product. - **Study abroad:** Cancellation per contract with penalty, credit and refund, plus the program's operational status (departed, in progress, completed). - **Nonprofit programs:** Active participants by date and retention between academic years, by hub or chapter.