Tools: PowerBI, GoogleBigQuery, MS Excel
Project Overview
As the lead data analyst for the Telemedicine Platform Data Analysis Project, I partnered with the MediConnect Operations Team to address the challenges faced by their telemedicine platform in the Philippines. MediConnect connects patients with healthcare professionals through online consultations, offering an efficient and accessible healthcare solution. However, the platform has been experiencing high appointment cancellation rates and negative post-appointment sentiments, which hinder the overall patient experience and impact the effectiveness of their services.
Insight Deep- Dive
Overall Findings and Key Strategy from EDA
Pre-Experience
Cancellations represent not only a direct loss in revenue but also a missed opportunity to maintain patient care. One dissatisfied patient can lead to immediate revenue loss and, more critically, long-term reputational damage, which impacts future growth.
The Opportunity
Given that the average spend per patient is approximately 2K+ pesos, the cancellation rate and its subsequent effects present a significant revenue opportunity. Each cancellation represents a lost chance to capture revenue and deliver care. With a 34% cancellation rate across 23K + appointments, the potential revenue loss amounts to 16M + pesos. Addressing this issue would not only improve finances but also enhance patient retention and acquisition in the long run.
Main Cancellations Reason
-
Multiple Bookings: The most prominent reason, with 3.2K+ out of 7.9K+ cancellations attributed to patients booking multiple appointments simultaneously.
-
No Reason Indicated: A significant number of canceled appointments lack a stated reason, indicating gaps in the feedback process.
-
Overdue Pending Approval: These cancellations suggest inefficiencies in the booking or approval system.
Pareto Analysis
The three main reasons account for a large proportion of cancellations (~80%).
By focusing on these three key reasons, MediConnect can address the bulk of cancellation related problems.
Cancellation Trend Over 3 years
Cancellation trends have fluctuated, with a decrease in 2021 followed by an increase in 2022. The average annual cancellation rate is 1.8K, indicating a persistent challenge in appointment retention.
Opportunity Cost Calculation
Post Experience: How Patients Feel About Their Experience with MediConnect
Analyzing post-appointment feedback from 11K+ completed appointments, we categorized sentiments into positive, neutral, and negative experiences, helping to pinpoint key areas for improvement.
Key Findings
-
55% of appointments (6.1K cases) resulted in positive experiences, indicating that the platform is delivering value to most users.
-
20% (2.2K cases) were classified as neutral or mixed feedback, suggesting areas where patient expectations were not fully met but were not severe enough to be classified as negative.
-
25% (2.8K cases) of completed appointments received negative feedback, revealing key challenges in the telemedicine experience.
Negative Breakdown Subcategories
-
Doctor-Related Issues (1,530 cases, 55% of negative feedback)
-
Doctor No-Show (978 cases) was the top complaint, leading to patient frustration and missed consultations.
-
Unsatisfactory Doctor Experience (552 cases) highlighted concerns about rushed consultations, unclear medical advice, and poor communication.
-
-
Follow-Up Concerns (796 cases, 29% of negative feedback)
-
Patients struggled with unclear next steps, difficulty booking follow-ups, and lack of treatment continuity.
-
-
System-Related Issues (438 cases, 16% of negative feedback)
-
Connectivity Issues (173 cases,) and Bad Audio/Call Timeout (135 cases) led to poor consultation quality.
-
Other system-related complaints (130 cases) included app crashes, slow response times, and payment failures.
-
Impact:
These issues not only affect short-term patient satisfaction but also have long-term implications, including:
-
Negative word-of-mouth
-
Reduced Patient Lifetime Value: Lower retention and repeat bookings.
Proactively addressing these issues will not only improve the patient experience but also build trust and loyalty, critical to MediConnect long-term growth.
Three Phase Recommendation
Based on the findings, we propose a three-phase strategy for MediConnect to address cancellations, improve patient satisfaction, and increase revenue:
Phase 1: Improve the Pre-Appointment Experience
-
Booking Confirmation System: Implement a system that confirms patient bookings before they are finalized to reduce cancellations due to unconfirmed appointments and multiple bookings.
-
Waitlist Feature: Introduce a waitlist option for patients to join if their preferred doctor is unavailable, reducing cancellations caused by scheduling issues.
-
Prevent Incomplete Bookings: Ensure patients provide all necessary information before submitting a booking by adding prompts for missing fields.
Phase 2: Enhance the Post-Appointment Experience
-
Tiered Penalty System: Implement a fair, transparent penalty system for doctors who fail to show up for scheduled appointments.
-
Structured Feedback Requests: Create a more formalized process for collecting and reviewing patient feedback, focusing on areas such as doctor performance and system issues.
Phase 3: Strengthen Data Collection
-
Predefined Data Options: Use drop-down menus for key fields in the booking process to reduce data entry errors and ensure consistency.
-
Real-Time Data Validation: Implement real-time checks to flag incomplete or incorrect data during the booking process.
-
Review Prompt: Encourage patients to review their appointment details before submission to ensure accuracy and reduce booking errors.
Data Collection
The client provided the data, which was subsequently imported into the Google BigQuery database under "mediconnect-proj-01" project, utilizing the "telemed" schema.
The client’s database contains various tables that house critical information about patients, appointments, practitioners, services, and financial transactions. For this project, we focused on the following key tables to perform our analysis:
-
appointments
-
ratings
-
practices
-
specialization
-
credit_transactions
-
practitioner_specialization
Data Cleaning Process
The dataset originally contained 43K + records, of which 23K+ represented active patients who made appointments. Within this subset, 11K+ appointments were completed, and we focused our deep dive analysis on these to understand both cancellation trends and post-experience sentiments. Here’s a detailed breakdown of the cleaning process and key findings from our exploratory analysis:
Data Filtering: Prioritizing Relevant Records
Given the large dataset, the first step was to filter for active appointments, ensuring we focused only on records that reflected actual patient interactions. By narrowing down to key appointment statuses—Complete, Canceled, Patient No Show, Approved, Pending, Dr No Show—we excluded irrelevant records and reduced the noise for meaningful analysis.
This left us with 23K++ active appointments—our primary focus.
Add Derived Columns:
Standardizing Feedback Remarks
Created standardized categories for qualitative analysis. Scanned feedback for common keywords and categorized them.
Addressing Multiple Bookings (Cancellation)
During the data cleaning process, we identified instances of multiple booking entries for the same patient and appointment time. Upon investigation, we determined that these entries were not errors but rather a result of the system's functionality, which allows patients to create multiple bookings. This discovery is a critical piece of information, as it reveals a potential area for system optimization and patient behavior analysis.
Revenue Loss (Refer to Opportunity Cost Calculation)
Calculated based on cancellation rates and the average spend per patient, which was determined by averaging completed appointment payment amounts. This metric allowed us to quantify the financial impact of cancellations.
Exported the dataset as a .csv file for use with external visualization tools like Excel and Power BI, facilitating data exploration and presentation.
