Healthcare operations analytics using synthetic appointment data

Appointment operations data can help describe waiting, scheduling and recording gaps. It cannot, by itself, establish care quality, clinical appropriateness or patient outcomes. A useful analytics project keeps those limits visible while making the operational calculations reproducible.

Use synthetic records for a public portfolio. The example here contains no real patients, diagnoses, treatments or clinical attributes.

Define each timestamp's meaning

The original appointment fixture records scheduled time, arrival, service start, service end and an administrative outcome. All timestamps use one fixed local timezone without a daylight-saving transition.

For January 10, there are six scheduled records: three completed, one no-show, one advance cancellation and one unknown outcome. A seventh appointment is scheduled for January 11 and lies beyond the January 11 midnight cutoff.

For completed appointments, calculate waiting after arrival as start minus arrival, schedule delay as start minus scheduled time, and recorded service duration as end minus start. These are distinct intervals.

Calculate only intervals with the required evidence

SELECT appointment_id,
 ROUND((julianday(started_at)-julianday(arrived_at))*1440,6) AS wait_after_arrival_minutes,
 ROUND((julianday(started_at)-julianday(scheduled_at))*1440,6) AS start_delay_minutes,
 ROUND((julianday(ended_at)-julianday(started_at))*1440,6) AS service_minutes
FROM appointments
WHERE scheduled_at<'2026-01-11T00:00:00' AND status='completed'
ORDER BY appointment_id;

A1, A2 and A5 each wait fifteen minutes after arrival. Their starts are ten, ten and five minutes after the scheduled time. Recorded service durations are twenty, twenty and fifteen minutes.

An early arrival can increase waiting-after-arrival without an equally large schedule delay. Report the interval that matches the operational question rather than calling every timestamp difference “waiting time.”

from datetime import datetime
from math import isclose
from build_and_verify import database

db = database()
rows = db.execute("SELECT * FROM appointments WHERE scheduled_at<'2026-01-11T00:00:00'").fetchall()
db.close()
completed = [r for r in rows if r[5]=='completed']
waits, delays, durations = [],[],[]
for appointment,scheduled,arrived,started,ended,status,reminder in completed:
    scheduled,arrived,started,ended = map(datetime.fromisoformat,(scheduled,arrived,started,ended))
    assert arrived <= started <= ended
    waits.append((started-arrived).total_seconds()/60)
    delays.append((started-scheduled).total_seconds()/60)
    durations.append((ended-started).total_seconds()/60)
assert waits==[15,15,15] and delays==[10,10,5] and durations==[20,20,15]
assert len(rows)==6 and len(completed)==3
assert isclose(sum(delays)/len(delays),25/3)
print({'completed_wait_mean_minutes':sum(waits)/len(waits),
       'completed_start_delay_mean_minutes':sum(delays)/len(delays),
       'recorded_service_minutes':sum(durations)})

Keep incomplete outcomes in the coverage report

A6 has an arrival but no recorded start or end. That does not establish a zero-minute wait, a no-show or a completed visit. The interval is unknown and the administrative outcome needs reconciliation.

NHS England's appointment recording guidance emphasizes consistent recording of appointments, including nonattendance. This example uses its own declared teaching categories; a live project must match the applicable local data dictionary.

Report three completed records out of six scheduled records alongside the completed-only averages. The average describes those three records, not every scheduled person's experience. Incomplete records could differ systematically from complete ones.

Interpret capacity arithmetic narrowly

Suppose this fictional session has six thirty-minute planned slots, totaling 180 minutes. Recorded completed service time is 55 minutes. The ratio 55/180 ≈ 30.6% describes recorded service minutes relative to the stated slot capacity.

It is not a measure of total clinician productivity. Documentation, preparation, urgent work, breaks, simultaneous activities and missing service records are absent from the fixture. It also cannot establish whether an appointment was too short or whether staffing should change.

For a real operational investigation, reconcile the schedule with actual resource availability and recording coverage before interpreting unused slot time. Keep the analysis focused on process questions supported by the data.

Exercise: add a service start earlier than arrival and make the chronology check fail. Then add a completed record with a missing end time and route it to an exception report rather than substituting zero duration.

NeuraPath's Data Analytics with Generative AI course connects data validation with domain interpretation. A credible healthcare-operations portfolio demonstrates careful measurement without claiming clinical conclusions from administrative timestamps.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Analyze call-centre service levels with abandonment rules.
• Continue with Analyze appointment no-shows without making clinical claims.