Where do outpatient no-shows come from?

Real public data. Analysis of 110,521 real outpatient appointments (Kaggle, CC BY-NC-SA 4.0). The interpretation and recommendations are my own.

Key findings

• 20.2% of 110,521 booked appointments were no-shows. The rate climbs from 4.6% for same-day bookings to 33.0% for bookings made 31 or more days ahead.
• Bookings made 8+ days ahead are 36% of volume but 57% of all no-shows, so a single reminder or reconfirmation step reaches most of the problem.
• SMS reminders look harmful in the raw data (27.6% vs 16.7%) only because they were never sent for bookings made 0 to 2 days ahead. Within each lead-time band, recipients no-showed 2.8 to 7.3 points less (observational).

Context

An empty appointment slot is lost capacity: the clinician is paid, the room is staffed, and another patient could have been seen. In this dataset about one booked appointment in five ended in a no-show. The operational question is which no-shows are predictable and which intervention is worth testing first.

I framed the work as a short operations brief for a multi-clinic network: where do no-shows concentrate, is the SMS reminder working, and what would we change and how would we know it worked?

Approach

I loaded the file into SQLite and answered each question with a query. Every figure below is produced by the SQL shown beside it; the queries ran on the real database and the results are rendered into this page.

• No-show: the patient did not attend the booked appointment.
• Lead time: calendar days between the day the appointment was booked and the appointment day. The source has no time of day for the appointment, so days are the finest grain.
• Cleaning: 6 of 110,527 rows removed (1 negative age; 5 booked after the appointment date), leaving 110,521.

Descriptive analysis only: grouped rates and shares, then a stratified comparison to test whether the SMS pattern survives control for lead time. I did not fit a predictive model, and nothing here is a causal estimate.

Analysis

1. Lead time is the dominant pattern

The no-show rate rises with every step in lead time, from 4.6% for same-day bookings to 33.0% for bookings made more than a month ahead (Fig. 1). Same-day bookings are partly low by construction, because the patient is already seeking care that day; the gradient across the other bands is the actionable part.

Appointments booked 8 or more days ahead are 36% of bookings but 57% of no-shows (Fig. 2).

SQL for Figs. 1 and 2 (run on the real database):

WITH base AS (
  SELECT no_show, sms_received, age,
         CAST(julianday(appointment_date)
            - julianday(substr(scheduled_at, 1, 10)) AS INT) AS lead_days,
         CAST(strftime('%w', appointment_date) AS INT) AS dow
  FROM   appointments
  WHERE  age >= 0                                      -- 1 impossible age removed
    AND  appointment_date >= substr(scheduled_at, 1, 10) -- 5 booked after the visit removed
)
SELECT CASE WHEN lead_days = 0  THEN 'Same day'
            WHEN lead_days <= 2 THEN '1-2 days'
            WHEN lead_days <= 7 THEN '3-7 days'
            WHEN lead_days <= 14 THEN '8-14 days'
            WHEN lead_days <= 30 THEN '15-30 days'
            ELSE '31+ days' END
         AS lead_band,
       COUNT(*) AS appointments,
       SUM(no_show) AS no_shows,
       ROUND(100.0 * AVG(no_show), 1) AS no_show_pct,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM base), 1) AS pct_of_bookings,
       ROUND(100.0 * SUM(no_show) / (SELECT SUM(no_show) FROM base), 1) AS pct_of_no_shows
FROM   base
GROUP  BY 1
ORDER  BY MIN(lead_days);

Result:

Explore the numbers

Switch the breakdown below: hover a bar for the exact rate and volume. Every value comes from the SQL above and below, run on the real database.

2. The SMS reminder: a confounder, then the real signal

Taken at face value the SMS looks counter-productive: patients who received one no-showed more (27.6% against 16.7%). The reason is how the reminder was deployed. It was only ever sent for bookings made three or more days ahead; none of the 50,500 bookings made 0 to 2 days ahead received one. Those are the safest bookings, and they all sit in the no-SMS group, which drags its average down.

WITH base AS (
  SELECT no_show, sms_received, age,
         CAST(julianday(appointment_date)
            - julianday(substr(scheduled_at, 1, 10)) AS INT) AS lead_days,
         CAST(strftime('%w', appointment_date) AS INT) AS dow
  FROM   appointments
  WHERE  age >= 0                                      -- 1 impossible age removed
    AND  appointment_date >= substr(scheduled_at, 1, 10) -- 5 booked after the visit removed
)
SELECT CASE WHEN lead_days = 0  THEN 'Same day'
            WHEN lead_days <= 2 THEN '1-2 days'
            WHEN lead_days <= 7 THEN '3-7 days'
            WHEN lead_days <= 14 THEN '8-14 days'
            WHEN lead_days <= 30 THEN '15-30 days'
            ELSE '31+ days' END
         AS lead_band,
       COUNT(*) AS appointments,
       SUM(sms_received) AS sms_sent
FROM   base
GROUP  BY 1
ORDER  BY MIN(lead_days);

Comparing like with like (same lead-time band) reverses the sign: in every band, recipients no-showed 2.8 to 7.3 percentage points less (Fig. 3). Because SMS was not randomly assigned this is an association, not proof the message works; it is a hypothesis to test properly (see Recommendations).

WITH base AS (
  SELECT no_show, sms_received, age,
         CAST(julianday(appointment_date)
            - julianday(substr(scheduled_at, 1, 10)) AS INT) AS lead_days,
         CAST(strftime('%w', appointment_date) AS INT) AS dow
  FROM   appointments
  WHERE  age >= 0                                      -- 1 impossible age removed
    AND  appointment_date >= substr(scheduled_at, 1, 10) -- 5 booked after the visit removed
)
SELECT CASE WHEN lead_days <= 7  THEN '3-7 days'
            WHEN lead_days <= 14 THEN '8-14 days'
            WHEN lead_days <= 30 THEN '15-30 days'
            ELSE '31+ days' END AS lead_band,
       sms_received,
       COUNT(*) AS appointments,
       ROUND(100.0 * AVG(no_show), 1) AS no_show_pct
FROM   base
WHERE  lead_days >= 3          -- the only bands where SMS was ever sent
GROUP  BY 1, 2
ORDER  BY MIN(lead_days), 2;

3. Who, and when

No-show rates are highest for 13 to 24 year-olds (25.6%) and lowest for over-70s (15.6%) (Fig. 4). Monday to Friday sit between 19.3% and 21.2%: with this many appointments the differences are real but too small to justify weekday-specific rules.

WITH base AS (
  SELECT no_show, sms_received, age,
         CAST(julianday(appointment_date)
            - julianday(substr(scheduled_at, 1, 10)) AS INT) AS lead_days,
         CAST(strftime('%w', appointment_date) AS INT) AS dow
  FROM   appointments
  WHERE  age >= 0                                      -- 1 impossible age removed
    AND  appointment_date >= substr(scheduled_at, 1, 10) -- 5 booked after the visit removed
)
SELECT CASE WHEN age <= 12 THEN '0-12'
            WHEN age <= 24 THEN '13-24'
            WHEN age <= 39 THEN '25-39'
            WHEN age <= 54 THEN '40-54'
            WHEN age <= 69 THEN '55-69'
            ELSE '70+' END
         AS age_band,
       COUNT(*) AS appointments,
       ROUND(100.0 * AVG(no_show), 1) AS no_show_pct
FROM   base
GROUP  BY 1
ORDER  BY MIN(age);

WITH base AS (
  SELECT no_show, sms_received, age,
         CAST(julianday(appointment_date)
            - julianday(substr(scheduled_at, 1, 10)) AS INT) AS lead_days,
         CAST(strftime('%w', appointment_date) AS INT) AS dow
  FROM   appointments
  WHERE  age >= 0                                      -- 1 impossible age removed
    AND  appointment_date >= substr(scheduled_at, 1, 10) -- 5 booked after the visit removed
)
SELECT CASE dow WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
                WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' ELSE 'Sat' END AS weekday,
       COUNT(*) AS appointments,
       ROUND(100.0 * AVG(no_show), 1) AS no_show_pct
FROM   base
GROUP  BY dow
ORDER  BY dow;

Combining the two strongest signals isolates a high-risk segment: appointments booked 15 or more days ahead for patients aged 13 to 39 number 9,642 (8.7% of volume) and no-show at 38.8%, nearly double the average. This subgroup was found by exploring the data, so it needs validating on a later period before it is acted on.

WITH base AS (
  SELECT no_show, sms_received, age,
         CAST(julianday(appointment_date)
            - julianday(substr(scheduled_at, 1, 10)) AS INT) AS lead_days,
         CAST(strftime('%w', appointment_date) AS INT) AS dow
  FROM   appointments
  WHERE  age >= 0                                      -- 1 impossible age removed
    AND  appointment_date >= substr(scheduled_at, 1, 10) -- 5 booked after the visit removed
)
SELECT COUNT(*) AS appointments,
       SUM(no_show) AS no_shows,
       ROUND(100.0 * AVG(no_show), 1) AS no_show_pct,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM base), 1) AS pct_of_bookings
FROM   base
WHERE  lead_days >= 15 AND age BETWEEN 13 AND 39;

Recommendations

I would sequence the work by size of prize and strength of evidence: start with the intervention aimed at the concentration finding, run the SMS question as a proper experiment, and hold the subgroup idea until it is validated.

Impact and limitations

What the data supports. The lead-time gradient, its concentration (Fig. 2) and the stratified SMS pattern (Fig. 3) are measured in 110,521 real appointments.

Scenario, not a measurement. If reconfirmation recovered 20% of the 12,750 no-shows among 8+ day bookings, about 2,550 slots would be saved per 110,521 bookings and the overall no-show rate would fall from 20.2% to 17.9%. The 20% recovery rate is my assumption, and it also assumes the waitlist fills the freed slots. Recommendation 1 is the experiment that would replace it with a measured number.

Limitations

• The dataset has no cancellation field, so bookings cancelled early are absent and the lead-time gradient is partly a selection effect. Same-day bookings are low partly because the patient is already there.
• Data are from 2016 and one health system; re-check every pattern on the clinic's own bookings before acting.
• The SMS comparison is observational and stratified on lead time only; other confounders may remain. No confidence intervals were computed.
• The high-risk segment was found by exploration, not pre-specified. I also looked at prior no-show history; in this six-week window it was not decision-grade, so I excluded it.