Project Overview

Business Objective

Track the full patient journey from signups → engagement → bookings → attendance and surface operational levers (e.g., reminders, overbooking, provider mix) to reduce no-shows and lift attended visits.

Dataset Overview

Dataset Description

• patients.csv — patient_id, gender, age, region, signup_date
• engagement.csv — patient_id, date, action_type (login, message, article, video, reminder_click), action_count
• appointments.csv — appointment_id, patient_id, scheduled_date, attended_flag (Y/N), provider_type (GP/Specialist/Therapist)

Row counts

• Patients: 1,200
• Engagement events: 4,837
• Appointments: 1,817

Data Modelling

Entities & Keys

• patients — PK: patient_id
• engagement — PK (composite): (patient_id, date, action_type); FK: patient_id → patients.patient_id
• appointments — PK: appointment_id; FK: patient_id → patients.patient_id
• lab_results (modeled for future analysis) — PK (composite): (patient_id, lab_date, test_type); FK: patient_id → patients.patient_id

Modeling assumptions

• attended_flag: 'Y' = attended, 'N' = no-show.
• gender: 'M', 'F', allow 'U'/null for unknown.
• action_count: number of times a patient performed an action_type on a given date.
• A patient can exist without records in engagement/appointments/lab_results, but not vice-versa. Part 1 – Data Modeling

Data quality & constraints

• All PKs NOT NULL.
• Controlled vocabularies for provider_type/action_type (no “GP” vs “General Practitioner” drift).
• Composite keys on engagement and lab_results prevent duplicate event rows.

Data Prep & Quality

Cleaning steps

1. Parsed dates (signup_date, date, scheduled_date); set correct data types.
2. Checked duplicate patient_id in patients (none affecting totals).
3. Ensured attended_flag normalized to Y/N.