Objectives:

• Identify trends and patterns in no-show appointments.
• Analyze demographic and scheduling factors contributing to no-shows.
• Propose actionable solutions to improve appointment adherence.
• Increase resource utilization and optimize scheduling efficiency.

TOOLS USED -

SQL - Extract and analyze patient and appointment data

POWERBI - Clean data & design dashboards to visualize no-show trends and risk factors.

Workflow

Data Collection - Data was collected on from electronic patient records (EPR) on-

Patient demographics (age, gender, zip code)

Appointment details (date, time, type, clinic).

Reasons for No-shows

Number of No-shows.

Lead time between booking and appointment date.

Data Cleaning

PowerBI was used to -

• Standardize column names and formats.
• Remove duplicates and handle missing values.
• Create calculated fields, such as lead time and appointment day of the week.

Data Analysis with SQL

Query data to:

• Calculate no-show rates by patient demographics, time slots, location and appointment types.
• Identify correlations between no-shows and appointment lead times, location, demographics and reasons for no-shows
• Aggregate data for visualizations (revenue lost).

SQL code -

-- Analyze no-show rates by patient demographics

SELECT

Gender,

Age_Group,

Location,

AVG(No_Show) AS Avg_No_Show_Rate,

COUNT(*) AS Total_Appointments,

SUM(CASE WHEN No_Show > 0 THEN 1 ELSE 0 END) AS Total_No_Shows

FROM

hospital_appointments

GROUP BY

Gender, Age_Group, Location

ORDER BY

Avg_No_Show_Rate DESC;

-- Analyze no-show rates by appointment time slots

SELECT

LEFT(Appointment_Time, 2) AS Hour, -- Group by hour

AVG(No_Show) AS Avg_No_Show_Rate,

COUNT(*) AS Total_Appointments,

SUM(CASE WHEN No_Show > 0 THEN 1 ELSE 0 END) AS Total_No_Shows

FROM

hospital_appointments

GROUP BY

LEFT(Appointment_Time, 2)

ORDER BY

Hour;

-- Analyze no-show reasons

SELECT

Reasons_For_Missed_Appointment,

COUNT(*) AS Total_No_Shows,

ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS Percentage_Of_No_Shows

FROM

hospital_appointments

WHERE

No_Show > 0

GROUP BY

Reasons_For_Missed_Appointment

ORDER BY

Total_No_Shows DESC;

-- Analyze no-show rates by appointment types

SELECT

Appointment_Type,

AVG(No_Show) AS Avg_No_Show_Rate,

COUNT(*) AS Total_Appointments,

SUM(CASE WHEN No_Show > 0 THEN 1 ELSE 0 END) AS Total_No_Shows

FROM

hospital_appointments

GROUP BY

Appointment_Type

ORDER BY

Avg_No_Show_Rate DESC;

-- Correlation between no-shows and appointment lead times

SELECT

Lead_Time,

AVG(No_Show) AS Avg_No_Show_Rate,

COUNT(*) AS Total_Appointments,

SUM(CASE WHEN No_Show > 0 THEN 1 ELSE 0 END) AS Total_No_Shows

FROM

hospital_appointments

GROUP BY

Lead_Time

ORDER BY

Lead_Time;

-- Optional: Combine demographic and appointment type analysis

SELECT

Gender,

Age_Group,

Appointment_Type,

AVG(No_Show) AS Avg_No_Show_Rate,

COUNT(*) AS Total_Appointments,

SUM(CASE WHEN No_Show > 0 THEN 1 ELSE 0 END) AS Total_No_Shows

FROM

hospital_appointments

GROUP BY

Gender, Age_Group, Appointment_Type

ORDER BY

Avg_No_Show_Rate DESC;

Data Visualizations with PowerBI

1. No-Show Rates by Patient Demographics

• Parameters: Gender, Age_Group, Location, Avg_No_Show_Rate, Total_No_Shows.
• Visualization:
• Bar Chart: No-show rates (Avg_No_Show_Rate) by Gender and Age_Group.
• Stacked Column Chart: Total no-shows by Location.
• Pie Chart: Distribution of no-shows across Age_Group.

2. No-Show Rates by Appointment Time Slots

• Parameters: Hour, Avg_No_Show_Rate, Total_No_Shows.
• Visualization:
• Line Chart: No-show rates (Avg_No_Show_Rate) by Hour.
• Heatmap: No-show counts (Total_No_Shows) by Hour and Appointment_Type.

3. Reasons for Missed Appointments

• Parameters: Reasons_For_Missed_Appointment, Total_No_Shows, Percentage_Of_No_Shows.
• Visualization:
• Bar Chart: Total no-shows by reasons.
• Pie Chart: Percentage of no-shows by reasons.

4. No-Show Rates by Appointment Types

• Parameters: Appointment_Type, Avg_No_Show_Rate, Total_No_Shows.
• Visualization:
• Clustered Bar Chart: No-show rates (Avg_No_Show_Rate) for each Appointment_Type.
• Treemap: Total no-shows grouped by Appointment_Type.

5. Correlation Between No-Shows and Appointment Lead Times

• Parameters: Lead_Time, Avg_No_Show_Rate, Total_No_Shows.
• Visualization:
• Scatter Plot: Lead time (Lead_Time) vs. no-show rate (Avg_No_Show_Rate).
• Line Chart: Total no-shows by Lead_Time.

6. Combined Analysis (Demographics + Appointment Type)

• Parameters: Gender, Age_Group, Appointment_Type, Avg_No_Show_Rate.
• Visualization:
• Stacked Bar Chart: No-show rates by Appointment_Type split by Gender.
• Matrix: No-show rates grouped by Age_Group and Appointment_Type.

Insights Gleaned from the data Analysis -

1. Demographics Most Represented in No-Shows:

• Gender: Female patients had slightly higher no-show rates compared to other genders.
• Age Group: The 19–35 age group showed the highest no-show rates, likely due to work or other commitments
• Time Slots with the Most No-Show Rates:
  
• The late morning hours (10 AM–11 AM) had the highest average no-show rates. This could be due to overlapping commitments or delays in starting the day
• Top 5 Reasons for No-Shows:
  
• The most common reasons for missed appointments were:
1. Transport issues
2. Work commitment
3. Emergency
4. Miscommunication
5. Childcare issues
• Appointment Type with the Most No-Shows:
  
• Routine checkups accounted for the highest number of no-shows. These appointments are often perceived as less urgent, leading to deprioritization.
• Correlation Between No-Shows and Appointment Lead Times:
  
• Appointments with longer lead times (e.g., 3 weeks or more) experienced higher no-show rates. Patients often forget about these appointments or have scheduling conflicts closer to the date.
• High-Risk Groups:
  
• High-risk groups include:
• Females in the 19–35 age group, particularly those from rural or suburban areas.
• Patients with routine checkups scheduled far in advance.
• Overall Financial Impact of No-Shows:
  
• The total financial loss caused by missed appointments is $134,410. This highlights a significant operational and financial burden on the hospital.

Strategies to mitigate the issues identified in the Analysis

Tailored Reminder Systems - Implement SMS, email, or automated call reminders specifically targeted at the 19–35 age group, with multiple reminders (e.g., one week, three days, and one day before the appointment).

Education Campaigns - Highlight the importance of attending appointments, especially routine checkups, through social media or direct communication.

Analyze high-risk time slots (e.g., 10 AM–11 AM) and reduce the number of appointments during those hours, shifting patients to lower-risk slots.

Allow for minor delays (e.g., a 15-minute grace period) during high no-show times, which could help mitigate missed appointments caused by lateness.

Provide virtual options for routine checkups, making it easier for patients to attend.

Combine routine checkups with other health services (e.g., diagnostic tests or specialist visits) to increase perceived value.

Require a refundable deposit for high-value appointments, refundable only upon attendance or rescheduling in advance.

Offer loyalty points, discounts, or incentives for patients who consistently show up for their appointments.

Implementation Prioritization:

1. Start with reminder systems and flexible scheduling, as these are cost-effective and impactful.
2. Telehealth options for more systemic changes.
3. Gradually introduce financial incentives and shorter lead times to optimize efficiency and reduce losses.