Data Cleaning for Healthcare Patient Databases: EHR Exports, Practice Management System Records, Patient Portal Data, Insurance Eligibility Files and Multi-Location Practice Consolidation
On this page
The Database Nobody Maintains
Medical practice databases are among the messiest in any industry, which is remarkable given how much regulation surrounds healthcare data. The irony is that HIPAA and other regulations focus on data security and privacy -- who can access patient data, how it must be encrypted, where it can be stored -- but say nothing about data quality. A medical practice can be fully HIPAA-compliant while maintaining a patient database that is 40% duplicate records, 25% outdated contact information, and filled with patients who have not visited the practice in a decade.
The mess accumulates because medical practices are focused on patient care, not data management. When a patient calls to schedule an appointment and the front desk cannot find their record, the path of least resistance is to create a new record rather than spend five minutes searching under alternate spellings or maiden names. When a practice merges with another practice or adds a new location, patient records from both systems are imported without thorough deduplication. When a patient updates their address at the front desk, the update may go into the practice management system but not the patient portal, the billing system, or the recall system.
Over years, a medical practice with 5,000 active patients may have 12,000 records in the database: 5,000 for active patients (some with duplicate records), 4,000 for inactive patients who have not visited in years, and 3,000 that are duplicates, test records, or records with insufficient information to identify the patient. This bloated database costs the practice money through inflated software licensing (many EHR and practice management systems charge per patient record), wasted mailing and messaging costs (appointment reminders and recall postcards sent to outdated addresses), and inaccurate reporting (patient count, active patient percentage, and recall rates are all skewed by database bloat).
EHR and Practice Management Exports
The starting point for healthcare database cleaning is exporting patient demographic data from the EHR or practice management system. Each system exports differently, and the available fields vary by platform.
athenahealth exports patient demographics through the reporting module. The export includes patient name, date of birth, address, phone numbers, email address, insurance information, primary care provider, and last visit date. athenahealth also tracks patient portal enrollment status, which indicates whether the patient has an active email address on file for portal communications.
eClinicalWorks exports patient data through the Report Builder. Include name, DOB, address, phone, email, insurance, primary provider, and last appointment date. eClinicalWorks also tracks patient communication preferences (email, text, phone, mail), which is useful for segmentation after cleaning.
Open Dental (commonly used in dental practices) exports patient data through the Query tool or through the Lists menu. The export includes patient name, address, phone, email, billing type, primary insurance, recall due date, and last visit date. Open Dental also tracks the "patient status" field (Patient, NonPatient, Inactive, Archived, Deceased, Prospective), which provides a built-in activity segmentation.
Dentrix exports patient lists through the Office Manager module. Include name, address, phone, email, insurance, provider, and last visit. Dentrix also supports exporting the guarantor (the person responsible for the account, who may be different from the patient -- a parent for a pediatric patient, for example), which is important for email communication because appointment reminders and billing notices should go to the guarantor, not the minor patient.
Export patient demographics from your system as CSV or Excel. Before uploading to any external tool, ensure compliance with your practice's HIPAA policies regarding patient data handling. Email Extractor processes files client-side in the browser -- no data is uploaded to external servers -- which aligns with data minimization principles, but your practice's compliance officer or HIPAA privacy officer should review and approve the workflow.
Upload the export to Email Extractor to extract and deduplicate email addresses. Medical practice databases commonly have the same patient listed with multiple email addresses (a personal email entered at registration, a work email entered when updating insurance information, and a different email used for patient portal signup), and duplicate patient records where the same person was registered under slightly different name spellings.
Duplicate Patient Record Identification
Duplicate patient records are the most common and most consequential data quality issue in medical practices. When the same patient has two or more records, their medical history is split across records. A physician reviewing the patient's chart may not see relevant medication history, allergy information, or lab results that are stored in the duplicate record. This is a clinical safety issue, not just a data management issue.
After email-based deduplication through Email Extractor, which identifies records sharing the same email address, review the remaining records for potential duplicates using other matching criteria.
Name + date of birth matches: The strongest duplicate indicator (though not foolproof -- there are people with identical names and birth dates who are genuinely different patients). Look for exact matches and near-matches (Robert vs. Bob, Catherine vs. Kathryn, hyphenated vs. unhyphenated surnames, maiden name vs. married name).
Address + phone matches with different names: This pattern often indicates family members (not duplicates) but can also indicate a patient who changed their name (marriage, divorce, legal name change).
Insurance ID matches: If two records share the same insurance subscriber ID, they are very likely the same person or immediate family members on the same plan.
Active vs. Inactive Patient Segmentation
Defining "active" versus "inactive" patients is essential for database cleaning and for practice operations (recall campaigns, appointment reminders, and marketing communications should target active patients, not patients who have not visited in five years).
The standard definition varies by specialty. For primary care, a patient who has not visited in 24-36 months is typically considered inactive. For dental practices, a patient who has not visited in 18-24 months (three or more missed recall cycles) is inactive. For specialists who see patients episodically, the inactivity threshold may be longer.
Export the patient list with the last visit date for each patient. Segment into active (last visit within the active threshold), inactive (last visit outside the active threshold but within the past 5 years), and dormant (no visit in 5+ years). Active patients receive all practice communications. Inactive patients receive recall campaigns ("It has been a while since your last visit -- we would love to see you"). Dormant patients may be archived to reduce database bloat and mailing costs, though their records should be retained per your state's medical record retention requirements.
Multi-Location Practice Consolidation
Practices with multiple locations, or practices that have merged with other practices, face the challenge of consolidating patient databases from different systems. A three-location dental group may have the same patient registered at two locations (they went to the downtown office for a cleaning and the suburban office for an emergency visit), creating duplicate records across locations.
Export patient demographic data from each location's system. Upload all exports to Email Extractor to deduplicate across locations. Patients who appear at multiple locations should be consolidated into a single patient record in the primary system, with their visit history from all locations merged.
This consolidation is particularly important when practices transition from location-specific systems to a centralized, multi-location EHR. The data migration should include deduplication, not just data merging, to prevent carrying duplicate records from the old systems into the new system.
