The problem
An institution with thousands of students has no way of knowing who to call first. The data exists — grades, attendance, payments — but it lives in separate systems and nobody cross-references it. By the time someone notices a student stopped showing up, a whole semester has gone by.
The flow
Grades, attendance and payments
Airtable
Runs every Monday
n8n · Schedule
A person steps inNormalises and matches by student
n8n · Code
Scores risk from five factors
n8n · Function
Applies intervention rules
n8n · Switch
Stores the history
Supabase
Alerts whoever is responsible
Email · WhatsApp
Follow-up dashboard
Airtable Interface
Tools
- Airtable
- n8n
- Google Sheets
- Email and WhatsApp
- Supabase
What happens when something breaks
A flow that only works with perfect data is no use. These are the cases that were anticipated and what the system does in each one.
A student has no attendance record for that term
The factor is left out of the average instead of counting as zero. Missing data is not bad data, and treating it as zero would invent risk where there is none.
The source returns an empty file or the connection fails
The flow writes nothing, keeps the previous week’s calculation and notifies the technical owner. Alerts are never sent on incomplete data.
A student appears twice under different ID numbers
It is caught by name and programme, flagged for human review and held back from the send until resolved. Records are never merged automatically.
The same student would trigger an alert four weeks running
There is a two-week quiet period per student and per rule. Without it, whoever receives the alerts stops reading them within a month.
Measurable result
2 indicators are still being measured: at-risk students identified before dropping out, hours of manual review avoided per semester. They will be published with their source and method. I would rather leave the gap visible than put up a number I could not defend in an interview.
Demo
A cohort of 180 students, loaded and assessed. The system works out each one’s dropout risk from five declared factors, sorts it into four bands and applies 4 intervention rules.
- Students assessed
180
the whole cohort
- Require action
34
19 % of the cohort
- Critical risk
9
immediate intervention
Cohort map
Each dot is a student. The lower and further left, the higher the risk. Hover with the mouse, or enter the map with the tab key and move through the dots with the arrows.
Point at a dot to see the detail.
Distribution by risk band
- Low136 · 76 %
- Medium10 · 6 %
- High25 · 14 %
- Critical9 · 5 %
Low up to 29, medium up to 54, high up to 74, critical from 75.
Students requiring action, by programme
- Business Administration12 of 51
- Accounting10 of 49
- Systems Engineering6 of 43
- Psychology6 of 37
The programme name always accompanies the colour: the data never depends on telling shades apart.
Interventions the system proposes
- Immediate action by the retention committee9 students
Retention committee · triggers if riesgo ≥ 75.
- Direct contact and a plan to catch up on classes24 students
Programme management · triggers if asistencia ≤ 0.6.
- Assign academic tutoring in the failed subjects22 students
Academic coordination · triggers if reprobadas ≥ 3.
- Refer to financial aid and review the payment plan25 students
Student welfare · triggers if mora = yes and riesgo ≥ 50.
Priority list
The twenty highest-risk cases. Pick one to see how it was worked out.
| Student | Programme | Risk | Band |
|---|---|---|---|
| Accounting | 91 | Critical | |
| Business Administration | 91 | Critical | |
| Business Administration | 90 | Critical | |
| Accounting | 86 | Critical | |
| Business Administration | 85 | Critical | |
| Business Administration | 83 | Critical | |
| Business Administration | 77 | Critical | |
| Accounting | 76 | Critical | |
| Accounting | 75 | Critical | |
| Psychology | 73 | High | |
| Business Administration | 73 | High | |
| Accounting | 72 | High | |
| Business Administration | 71 | High | |
| Accounting | 70 | High | |
| Psychology | 70 | High | |
| Psychology | 69 | High | |
| Accounting | 69 | High | |
| Business Administration | 69 | High | |
| Psychology | 68 | High | |
| Accounting | 68 | High |
How it was worked out
Santiago Naranjo E-1008
91out of 100 · critical risk
- Academic performance (1.84)+30 of 30
- Attendance (52 %)+23.6 of 25
- Failed subjects (3)+20 of 20
- Overdue balance (yes)+15 of 15
- Credit progress (51 %)+2.7 of 10
Sum of contributions = 91. No remainder, no hidden adjustment.
Immediate action by the retention committee
riesgo ≥ 75 · has 91
Direct contact and a plan to catch up on classes
asistencia ≤ 0.6 · has 0.52
Assign academic tutoring in the failed subjects
reprobadas ≥ 3 · has 3
Refer to financial aid and review the payment plan
mora = true · has true · riesgo ≥ 50 · has 91