Student dropout and retention

Source

Academic system, cancellations and finance

Rule

Latest snapshot, closed status, amount from the cancellations table

Outcome

Dropout, re-enrollment, lost revenue and retention by cohort

Activation

Academic dashboard, finance dashboard and AI agent

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.

Business questions it answers

Ask your agent these questions once the case is live.

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

Where to activate

Academic dashboard

Active students, dropouts, reasons and age group by campus and service, in the latest snapshot.

AI agent via MCP

Coordinators ask about dropouts and transfers and get the record date stated as such.

Risk alert

Weekly list of cancellations and non-re-enrollments by campus so coordinators can act before the enrollment campaign ends.

Ready to build?

The snapshot rule and the status list solve most of it. Lost revenue and LTV require finance data. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

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.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Counting the RA (student ID number) in every snapshot of the semester.

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.

Active = any student with a record.

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.

Counting a service transfer as dropout.

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.

Answering that the student dropped out on the day the dropout was entered.

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.

Summing net sales to find out how much was canceled.

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.

Treating a null refund as zero and adding it to the others.

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.

Counting every invoice in the month as recurring revenue.

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.

Counting the lost renewal and the product card as two churns.

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.

Summing only the installments that exist in finance.

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.

Premature = left before the average lifetime.

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.

Student status: what counts

In the latest snapshot of the academic period.

New enrollmentActive, new student

Re-enrollment (including confirmed through the portal)Active, returning student

Dropped outExit: stopped attending

CanceledExit: contract terminated

Active in the previous period and missing from the new oneNon-re-enrollment

Same RA with another level, class or serviceTransfer, not an exit

Date in the dropouts sourceRecord date, not dropout date

Definitions

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 that already happened

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.

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.

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.

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.

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.

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.

Count the refund in the month of the sale and state the rule.

Acceptance test

The case is done when every item passes.

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.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

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:
- <client>: campus <name in the system> is campus <institutional name>.
- <client>: periods <list> are excluded from semester comparisons.
- <client>: service derived from level: <level> = <service>; all others = <default service>.

Build the "Student dropout and retention" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/educacao/evasao-cancelamento-retencao-aluno.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

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.