One in five patients miss their appointment. The data shows why — and the redesign fixes the root cause.
An end-to-end study of a Brazilian municipal clinic network: ten SQL queries on 110,527 appointments isolate the drivers of no-shows, a BPMN redesign of the front-desk process attacks each driver, and a traceability matrix wires every process change back to a number in the data.
The story in one breath
A What the data said
Ten SQL queries against 110 K appointment records isolated five drivers of no-shows. The single strongest signal is booking lead time — same-day visits miss 4.6 %, 31+-day visits miss 31.4 %.
Under 1 % of patients account for 16 % of all misses — invisible to the current process.
B What the redesign does
A digital-front-door BPMN replaces the paper form, batches the phone-call insurance check with a SUS-Conecte API call, and adds a three-touch reminder cadence with tap-to-confirm/reschedule.
A booking-time risk score routes the high-risk 1 % to a care-coordinator queue. Every BPMN element is wired to a numbered requirement.
↻ The patient's journey, redesigned
Ten queries. Five findings. One smoking gun.
Charts below are computed from the full 110,527-row dataset. Each chart corresponds to a numbered query in sql/queries/.
Q4 Lead time vs no-show rate — the single strongest predictor
Booking more than two weeks out makes a no-show ~7× more likely than a same-day booking.
day
days
days
days
days
days
Q1 No-show rate by age band
Q3 No-show by day of week
Saturday spikes; mid-week is the sweet spot.
Q6 The 0.9 % club — frequent missers
Under 1 % of repeat patients generate 16 % of every empty exam-room slot.
Right column = share of patients · bar = share of total misses
Q5 SMS reminder — does it actually work?
Naive view (bias-loaded):
Like-for-like (≥3 day lead time only):
⚠ Insight: the SMS as currently sent only buys ~1.5 pp — within noise. Single late ping ≠ engagement.
Q9 Age × gender heatmap
Darker red = higher no-show rate. Young adult men are the worst-attending cohort.
Q2 Worst-served neighbourhoods
→ All peripheral, transport-limited areas. Voucher pathway target.
SQL Example query — Q4 lead time
WITH bucketed AS ( SELECT CASE WHEN lead_time_days < 1 THEN '0 — same day' WHEN lead_time_days < 4 THEN '1-3 days' WHEN lead_time_days < 8 THEN '4-7 days' WHEN lead_time_days < 15 THEN '8-14 days' WHEN lead_time_days < 31 THEN '15-30 days' ELSE '31+ days' END AS lead_bucket, no_show FROM v_appointment_facts WHERE lead_time_days >= 0 ) SELECT lead_bucket, COUNT(*) AS appointments, ROUND(100.0 * AVG(CASE WHEN no_show THEN 1 ELSE 0 END), 2) AS rate_pct FROM bucketed GROUP BY lead_bucket ORDER BY MIN(lead_bucket); -- result: monotone climb from 4.6% (same day) to 31.4% (31+ days)
The current process — where the 76 minutes go
Coral cells are friction points. Bright red are the four anchors the redesign targets.
⏱ Cycle-time breakdown
! Four structural anchors
- 1. Insurance verification is a batched, synchronous phone call
- 2. Paper form is the source of truth for ~6 hours
- 3. No risk signal travels with a booking
- 4. Reminder is open-loop — no confirm path
The redesign — every coral cell becomes teal
Teal = digital · purple = human (with system support) · blue = system. Same actors, very different choreography.
↻ Five things that changed
- 1. Risk score lives at booking, not in retrospect
- 2. Insurance check is an API call (<1 s) not a phone call (23 min)
- 3. Online pre-reg replaces the paper form
- 4. Reminders are a 3-touch cadence with tap-confirm
- 5. No retyping. End-of-shift typist role sunset
📉 Cycle-time after redesign
Eight gaps. Each one passed two tests.
To be on this list a gap had to (1) explain a row of the SQL findings, and (2) point to a specific BPMN element as the fix.
Every BPMN element wired to a numbered requirement
This is the document an auditor or PMO reads first.
| Gap | Requirement | Type | BPMN element | Acceptance test |
|---|---|---|---|---|
| G1 | R1.1 | Functional | D1 → D2 | 95% of bookings have a completed pre-reg payload at T-1 day |
| R1.2 | Non-functional | D2 | Lighthouse a11y ≥ 95; PT + EN | |
| G2 | R2.1 | Functional | D3 | p95 latency ≤ 2 s in load test |
| R2.2 | Functional | G2 → D4 | 100% of API failures routed to async queue | |
| G3 | R3.1 | Functional | S1, G1 | Model ROC-AUC ≥ 0.72 on held-out month |
| R3.2 | Functional | G1 → S2 | 100% outreach attempts logged within 24 h | |
| R3.3 | Non-functional | background job | Quarterly retrain in audit log | |
| G4 | R4.1 | Functional | R1 → R2 → R3 | All three sent for 100% of eligible bookings |
| R4.2 | Functional | R1-R3 | Tap → slot reappears in pool within 30 s | |
| G5 | R5.1 | Functional | N2 | Engine output present for 100% of triage events |
| R5.2 | Functional | N3 | Override is 1 click; reason captured ≥ 95% | |
| G6 | R6.1 | Functional | A2 | Manual retype rate ≤ 5% of fields |
| R6.2 | Operational | (role retired) | EOS step absent in To-Be SOP | |
| G7 | R7.1 | Functional | extends R1 | Voucher tap-through ≥ 25% in pilot |
| G8 | R8.1 | Operational | (out-of-flow) | Weekly dashboard live; reviewed in ops huddle |
| R8.2 | Operational | (out-of-flow) | A/B record book current |
Conservative bounds. Auditable origin for every number.
Each metric below ties to a row of the SQL findings and a BPMN element in the To-Be.
-33% from cadence (R1-R3) + risk routing (S1, S2). Mid-range of published meta-analyses (25-50%).
API call (D3) replaces synchronous phone batch. Exception queue (D4) handles failures async.
-71%. The dominant cuts: zero-out paper form, API verification, in-room triage confirm.
Role retired by A2. Existing clerk redeployed to the exception queue.
(no-show delta × visit value) + (verification + typist savings). Net of platform run-cost. Tested in 8-week pilot before network-wide rollout.