Clinic operations analysis

Shifa Clinics is a fictional group of six outpatient clinics in Riyadh, Jeddah, Dammam, Medina and Abha. It offers family medicine, pediatrics, dental, dermatology, physiotherapy, ophthalmology and ENT services. The operations director has two years of booking and billing data (2024–2025) and a growing list of complaints: empty slots, crowded waiting rooms, insurers rejecting claims, and more patients paying out of pocket since one insurance contract ended.

This project is about operations, not medicine. The database holds bookings (appointments), the billable services delivered at each visit (appointment_services), invoices and insurance claim rejections, plus reference tables for clinics, physicians, service lines, services and insurers. Patients are synthetic and carry only a name, gender, birth year and city. There are no diagnoses or clinical notes, and nothing in this project is clinical advice.

Operational data is messy in very specific ways, and each task makes you handle one of them deliberately:

• Walk-ins have zero lead time and cannot "no-show".
• Rescheduled and cancelled bookings were never due.
• Some visits were never checked in or closed. Some waiting times are hours long or negative because of clock errors.
• Prices rose in 2025, so the price list is not what patients were charged.
• An invoice can be rejected more than once.
• After Basma Health's contract ended, its members were billed as self-pay.
• Some patients are registered twice.

You will profile these issues, measure each KPI with a definition you could defend to a clinic manager, and finish with a 2025 clinic scorecard.