How do you define a KPI? Write its definition sheet
See why the same data gives three different numbers, and write the definition sheet that gets everyone to calculate the same one.
Free course. Your Maestro, an AI, .
On Monday, Lucía reports that the week's no-show rate was 10.7%. An hour later, the director presents 11.5%, from the same data. Nobody made an arithmetic mistake: each of them divided by something different. A written definition sheet helps settle that discussion before calculating.
Before you see how, try something smaller. You can predict by counting the table without reading Python; the program does the same counts. Before running it, predict: no-shows out of all appointments, no-shows out of appointments not canceled, and people who missed out of distinct people.
| Appointment | Patient | Status |
|---|---|---|
| 1 | Ana | completed |
| 2 | Beto | no-show |
| 3 | Beto | no-show |
| 4 | Carla | canceled with notice |
| 5 | Dulce | completed |
| 6 | Ernesto | completed |
Six appointments from one week. Write down the three results before you see what it prints, and why you think they come out that way. The program prints its labels in Spanish; the comments in the code tell you what they mean.
Six appointments, three numbers
# Six appointments from one week at one site (made-up data).# "atendida" = seen, "no llegó" = no-show, "canceló con aviso" = canceled with notice.citas = [ {"paciente": "Ana", "estado": "atendida"}, {"paciente": "Beto", "estado": "no llegó"}, {"paciente": "Beto", "estado": "no llegó"}, {"paciente": "Carla", "estado": "canceló con aviso"}, {"paciente": "Dulce", "estado": "atendida"}, {"paciente": "Ernesto", "estado": "atendida"},]faltas = sum(1 for cita in citas if cita["estado"] == "no llegó")en_pie = sum(1 for cita in citas if cita["estado"] != "canceló con aviso")pacientes = {cita["paciente"] for cita in citas}faltaron = {cita["paciente"] for cita in citas if cita["estado"] == "no llegó"}# The labels are in Spanish: "Por cita, entre todas las agendadas" = by appointment,# out of all scheduled; "entre las que seguían en pie" = out of those still on the books;# "Por paciente" = by patient.print("Por cita, entre todas las agendadas:", faltas, "de", len(citas))print("Por cita, entre las que seguían en pie:", faltas, "de", en_pie)print("Por paciente:", len(faltaron), "de", len(pacientes))A guess is fine. What you write stays in this browser and waits in your course.
Six rows, three answers. With 402 appointments the same thing happens; you just can't see the difference at a glance anymore. If your prediction was different and you can't see why, ask your Maestro to walk through it with you.
The definition sheet: five decisions in writing
A KPI is defined when two people who've never met, working from the same data, calculate the same number. To get there, you write down five decisions:
- Numerator: what you count. "No-shows" isn't enough. If a patient shows up 40 minutes late and can't be seen anymore, is that a no-show? Write it down.
- Denominator: what you divide by. This is the decision that separates 10.7% from 11.5%. Lucía divides by the appointments still on the books, because a cancellation with notice frees a slot that can go to someone else.
- Unit: what one case is. This is also called the grain: are you counting appointments or patients? To decide where the confirmation calls go, the unit is the appointment: every lost appointment is an empty slot. For a program that follows up with patients who miss a lot, the unit would be the patient.
- Period: from when to when. A Monday-to-Sunday week, by the date of the appointment, not the day it was booked. Do not compare a partial week with complete weeks without labeling it: the rate may rise or fall as the week finishes. This dashboard uses complete weeks only.
- Exclusions: what's left out. Test appointments, internal staff appointments, a site that opened midweek. Whatever you leave out, write it down.
On top of those five go three lines that don't change the math but do change how the number gets used: the question it answers, the target, and who owns the definition when someone wants to change it.
Here's Lucía's complete definition sheet. Copy it as a template for your own KPIs:
# No-show rateQuestion it answers: which site gets the confirmation calls this week?Numerator: appointments where the patient didn't show up or call.Denominator: active appointments = scheduled appointments minus appointments canceled with at least 24 hours' notice.Unit: the appointment (a patient who misses two appointments counts twice).Period: Monday-to-Sunday week, by appointment date. Complete weeks only.Exclusions: internal staff appointments.Breakdown: by site; the whole clinic is calculated from the totals.Target: 10% or less.Owner of the definition: Lucía, operations coordinator.Case boundary: the file does not distinguish cancellations with less than24 hours' notice. Do not classify them by assumption. For real data, add thatstatus and agree whether it belongs in the numerator, denominator or anexclusion before reusing this sheet; the three current statuses do not settle it.The breakdown line says the whole clinic's rate is calculated from the totals. It looks like a detail, and it's the subject of the next page.
The unit is not the row
A common mix-up is thinking the KPI's unit is the table's row. In the clinic's data, each row sums up one site, one specialty and one week: 117 rows for the quarter. But the rate doesn't count rows: it adds up the appointments inside them. If someone calculates "the percentage of rows with high no-shows," they're measuring something else.
Whenever you open a new table, ask two separate questions: what does one row represent, and what unit does your KPI count? Sometimes they're the same; here they aren't.
Check yourself: Beto missed two appointments in the same week without calling. Using Lucía's definition sheet, how many no-shows does he add to that week's rate?
Try it
The second number on Lucía's dashboard is schedule occupancy. Write its definition sheet with the same fields: the question it answers, numerator, denominator, unit, period, exclusions and target. For each site, specialty and week, the clinic's data has the open appointment slots and the appointments scheduled.
There's no single right answer, but there is a test: if you hand your sheet to someone else with the same data, do they get the same number you do? If you're not sure what goes in the denominator, ask your Maestro.
Write the definition sheet for "schedule occupancy" on Lucía's dashboard: question, numerator, denominator, unit, period, exclusions and target.
Check yourself: Write the definition sheet for "schedule occupancy" on Lucía's dashboard: question, numerator, denominator, unit, period, exclusions and target.