Before linking the orders to the dispense history, you must consider the granularity of the data that you are starting with alongside the goal. For this example, the unit is the medication order, not the patient. This allows for the tracking of individual prescription lifecycles from order to fulfillment. Depending on your individual use case, you may want the data to be pulled and aggregated at the patient, department, facility, or another level.

For our use case, we did not assess medication dispense at the patient level across multiple encounters, though this is certainly an option for future studies.

We included outpatient and emergency department encounters for pediatric patients (aged 6 mo to <18 y) diagnosed with AOM using International Classification of Diseases, Tenth Revision, Clinical Modification (ICD-10) codes H65, H66, and H67 [14,15], between January 1, 2021, and January 1, 2024. The final cohort consisted of 3404 antibiotic orders for amoxicillin, amoxicillin-clavulanate, or cefdinir. An attrition diagram describing the encounter selection is in Multimedia Appendix 1.

The included 3404 encounters were distributed among 2930 unique patients. Of these patients, 368 had multiple encounters, contributing a total of 842 encounters, with 2 to 6 encounters per patient (Multimedia Appendix 2). A description of the demographics for these 3404 encounters is provided in Table 1.

Because this study was restricted to children in California, where pediatric health coverage is available through Medi-Cal regardless of their parents’ employment or immigration status, nearly all patients in the cohort were insured through either public or private insurance.

For the second step, there are a few baseline definitions that will be used to describe the process of pulling this data:

In this step, it is important to define these elements and to be aware of what the triggering event settings are within your EHR. A triggering event activates the pulling of data from the external dispense data vendor for each event, and the data pull is often marked with the date when the data were retrieved. It is also important to be aware of the look-back period for the data pull.

At our institution, the University of California, San Francisco (UCSF), the system retrieves up to 365 days of prior dispense data at the time of a triggering event. Triggering events in our EHR include refill encounters, order encounters, hospital encounters, appointments, patient messages, or telephone encounters.

Within our system, there is no distinct foreign key that can be used to match the data to the external dispense data. The order ID for a specific medication in our system is not reflected in the commercial database. Thus, we cannot easily match an order from an encounter to a specific dispense from the pharmacy database. As a result, we had to develop surrogate key logic with multiple steps, which is outlined in Step 4.

Data should be extracted from the EHR reporting database. This typically involves developing and testing a SQL query. The key step here is to identify the order data, patient information, and pharmacy and medication information. Researchers may consider including the following to augment their analysis:

Next, it is important to locate the external dispense data and, if necessary, join it to the same patient, pharmacy, and medication information tables.

Data were extracted from Epic Clarity. The SQL queries used for data extraction were developed and internally reviewed by the study authors. The original code was written by JP and subsequently updated and revised by BM. No separate IT or external technical review of the SQL code was performed. Both authors are Epic Clarity–certified.

The research team performed random sampling of records to verify data integrity and ensure inclusion criteria were met. We were able to identify the order, patient, medication, and pharmacy tables, along with the external dispense data tables. Due to copyright restrictions and privacy agreements governing the proprietary EHR data tables, we are unable to share the exact code used in this project. Instead, we provide a simplified, illustrative outline of the coding approach on GitHub [16], and a Jupyter notebook containing deidentified practice data is also provided on GitHub [17]. An archived version corresponding to this publication is available through Zenodo [18]. The repository is intended to demonstrate the general analytic workflow and is not a runnable or complete implementation of the analysis; therefore, it should not be used to reproduce the analysis directly.

Our institution has a proprietary agreement with DrFirst for the majority of our database dispenses. We also maintain a relationship with Surescripts as a backup and for certain insurance types.

Your EHR may not contain a foreign key that can be used to link the external dispense information to the internal order (such as an order ID). To match at the order level, this can be accomplished by leveraging surrogate secondary keys in a multilookup configuration to ensure you have the correct order. The patient, medication, and pharmacy identifiers are often present in both the ordering and the outpatient dispense tables within the database. All three should be used at a minimum. Additionally, if needed for the use case, ordering provider identifiers, such as a Drug Enforcement Administration number or a National Provider Identifier (NPI), can be leveraged. The reader should note that clinics with multiple ordering providers—trainees, such as residents and fellows, or advanced practice providers—may have discrepancies between the ordering provider and the attending physician who signed the visit.

Linking the EHR encounter to the correct outpatient dispense record was the central challenge of our project. The foreign key that our EHR vendor suggested would be associated with the order data was unusable because the order number sent by our EHR does not match the prescription number used by the pharmacy processing software. Additionally, the dispense data received did not contain the original EHR order number that we could locate.

Identification of a common key is a challenge that most EHRs face with outpatient, external prescription dispense data, as internal data primary keys rarely match those used by external pharmacies or databases. Thus, multiple matches using secondary keys must be used with conditional join logic to establish the correct link. This is in contrast to internal inpatient hospital pharmacies, which will likely share order IDs, thereby simplifying the linkage between encounters and dispenses.

For our outpatient use case, we used Generic Control Number (GCN; First DataBank [19])–based matching to group across brand/generic variations and different strengths. Pharmacy phone numbers were used as the primary link because pharmacy IDs were unstable during ownership changes, while phone numbers remained constant. Patient matching was performed upstream by the EHR and commercial pharmacy data networks using vendor-specific proprietary matching algorithms and patient identity reconciliation processes. These matching procedures, which may incorporate multiple patient identifiers and vendor-defined heuristics, were not part of the custom code developed for this study and were not accessible to the study team. The study’s code used the EHR-assigned patient primary key to identify and filter orders associated with each patient.

Our approach was as follows: First, all antibiotic orders for AOM associated with relevant office visits and emergency department encounters were extracted, as described in step 3. Second, patient, medication, pharmacy, and provider information were joined to the order data. Third, using the outside pharmacy dispense data, the orders were joined using the internal EHR patient identifier, pharmacy phone number, or pharmacy ID. Fourth, the order medication GCN was matched to the prescription National Drug Code (NDC), and then to the GCN corresponding to that NDC. Finally, the prescription dispense date was matched to a date up to 14 days after the order date. Fourteen days was selected for our particular use case because AOM is expected to resolve within 2 weeks [20], and any symptoms persisting beyond this period are typically indicative of a new infection [21]. Readers may choose to change this dispense period depending on the type of behavior they expect from patients for the particular medication under study.

These joins are representative of the types of foreign keys needed to accurately join EHR order information to external prescription dispense data. We chose to use GCN, as it allowed for matches between brand and generic products. The pharmacy phone number was necessary because some pharmacies had changed ownership and the pharmacy ID in our EHR did not match the pharmacy ID in the dispense data, while the phone number remained the same.

In our logic, we did not use the prescriber name or NPI due to the heavy presence of trainees in our clinics, which means that the prescriber might not match the attending provider assigned to the encounter. Using the NPI could have been used to further restrict matching to the ordering provider in clinics that do not have such a large trainee presence. Potential joins are outlined in Figure 1, with details for consideration provided in Table 2.

To ensure that we had dispense data for each encounter, we limited the analysis to patients who had a dispense data triggering event at UCSF within 365 days of the initial encounter. Triggering events in our EHR were defined as follows: refill encounters, order encounters, hospital encounters, appointments, patient messages, or telephone encounters. This restriction was necessary as dispense data are retrieved from the pharmacy server only when the EHR identifies a triggering event. For scheduled appointments, this retrieval occurs via a batch job run during off-hours. This allows the data to be viewable to the outpatient clinician at the time of the appointment.

What constitutes a triggering event and the lookback period for retrieving dispense data will vary depending on an institution’s EHR configuration. At UCSF, our configuration’s look-back period pulled 365 days of dispense data for a triggering event. Once retrieval is initiated by a triggering event, the dispense history is extracted to the reporting server for future analysis during the standard extract, transfer, and load process for the EHR.

When dispense data are aggregated from multiple vendors, duplicate records must be identified and merged to avoid overcounting fulfillment. Multiple triggering events may pull the same medication dispense record. For example, consider a medication order for amoxicillin that is sent from an emergency department on a Monday and dispensed at an outpatient pharmacy later that same day. If you were to check the EHR that evening, you would not see the dispense because the patient would not yet have had a triggering event to bring that dispense from the pharmacy database into the institutional EHR. Imagine that later that same week, on Tuesday, the patient visits their physician for a different medication refill. At that Tuesday visit, the dispense from Monday would be evident because, in anticipation of the Tuesday appointment, the extract, transfer, and load would have run the night prior, retrieved the pharmacy database information, and incorporated it into the EHR.

However, most studies do not focus on such recent prescriptions but instead analyze long retrospective time periods. Going back to the example, imagine the patient then sees another provider on Thursday. If each of those encounters is a triggering event, the same dispense information would be retrieved on Tuesday and again on Thursday, leading to duplicate rows in the EHR.

This deduplication challenge arises because each triggering event returns the same underlying dispense record. In this example, the dispense that occurred on Monday, with triggering events on Tuesday and Thursday, would produce rows with identical dispense date, medication identifier, pharmacy identifier, and patient identifier. Without deduplication, these would appear as multiple dispenses. The researcher conducting a retrospective study must, therefore, distinguish data retrieved across multiple triggering events that may represent the same underlying dispense. Failing to do so could lead to grossly overestimating the number of dispenses, misrepresenting the true time of the dispense, or associating the wrong dispense with the encounter. In this example, it could appear that the patient received the prescription multiple times rather than once.

To address this, medication dispense data should be deduplicated by grouping key fields such as patient, dispense date, pharmacy, provider, and medication. This grouping produces a single record per dispense, regardless of whether the source data originated from a prescription-processing system or a payer claim.

Finally, your EHR may integrate data from multiple third-party dispense vendors. Depending on system configuration, one or more vendors may be queried per triggering event. The same grouping logic ensures a single-row representation even when multiple vendor sources contribute overlapping data.

Of the 2 commercial pharmacy databases integrated into the EHR system, DrFirst provided the majority of the data, with 2345 (68.9%) exclusive entries, while SureScripts contributed 57 (1.7%) entries. Only 214 (6.3%) orders had dispense data available from both databases (Table 3).

This distribution is consistent with DrFirst serving as the preferred vendor within our system. Instances of overlap likely arise from multiple triggering events for a given prescription or from differences in data availability over time.

For example, dispense data may have been unavailable from DrFirst at an initial triggering event and was therefore pulled from Surescripts, but subsequently became available at a later event, resulting in retrieval from both sources across consecutive time points. In these instances, records were merged using the above deduplication grouping into a single “Dispensed” status to ensure that the match proportion was not artificially inflated.

Pharmacy identifiers must be harmonized to account for discrepancies between the EHR’s “intended” sent to pharmacy and the “actual” dispensing pharmacy. Pharmacy addresses, phone numbers, National Association of Boards of Pharmacy (NABP) numbers, or NPI numbers can be used to match the dispense data to the order data. The pharmacy phone number and address often remain unchanged when a pharmacy changes ownership, whereas the NPI or NABP numbers are sometimes updated.

Additionally, it is helpful to categorize your pharmacy data into large groups based on the company. This becomes important during the validation step to determine whether pharmacies are sending dispense data to the database.

We categorized 307 pharmacies into the following groups: CVS, Walgreens, Safeway, Rite Aid, Walmart, Costco, On-Site Hospital Pharmacy, Other, and Kaiser. Normalization revealed significant variation in data capture: large chains such as Walgreens and CVS showed an 82.4% and 81.9% match proportion, respectively, while “Other” pharmacies dropped to 49.3%, and Kaiser/Costco showed the lowest retrieval proportion (Table 4).

Validation of the joins and pharmacy inclusion in the dispense data source is essential. First, evaluate the matched order-to-prescription data for a subset of your total data to validate that it is correctly using the logic to match the order to the dispense or claim. Second, gather the distinct pharmacies included in your order dataset and evaluate whether dispense data have been received for any patients. We also recommend comparing characteristics of linked vs unlinked orders (eg, patient demographics, encounter type, or pharmacy type) to assess potential bias in linkage.

To further ensure completeness of pharmacy reporting, we extracted the full list of pharmacies present in our order dataset and queried our EHR reporting database (Clarity) to determine whether each pharmacy had returned any dispense records for any medication and any patient during the study period. This step was used to confirm that pharmacies included in the analysis were actively transmitting data through the commercial pharmacy aggregation system into the EHR, rather than appearing not to have dispenses that were attributable to linkage or data availability issues. Specifically, we recommend pulling the first dispense date, last dispense date, and total number of dispenses for each pharmacy. This step confirms whether a pharmacy is a “reporting” pharmacy.

It is important to keep in mind that patients without a follow-up triggering event will not have any dispense data—which is why it is important to ensure that all included pharmacies are reporting. Generally, patients without a follow-up triggering event should be excluded from the analysis, as they will never have corresponding dispense data.

As an additional validation step, a subset of linkages could be reviewed to confirm concordance between ordered medications and dispensed products (eg, agreement with NDC). More comprehensive validation of linkage accuracy and data completeness—such as quantifying potential data transmission loss between the EHR and PDS—would require access to both systems. Such analyses were beyond the scope of this study but represent an important area for future work.

Overall, 307 pharmacies were included in our cohort. Of these, 302 (98.3%) were confirmed to have returned at least 1 dispense record for any medication and for any patient in the EHR during the study period, indicating active data transmission. Within our study cohort, most patients elected to send their medications to large pharmacy chains such as CVS and Walgreens, whereas 994 (29.2%) prescriptions were sent to the local hospital pharmacy (Table 4). Only 71 (2.1%) encounters involved a pharmacy that was neither a major chain nor the local hospital pharmacy.

Of the 3404 antibiotic orders, 2616 (76.9%) were successfully linked to dispense data using the multikey strategy. Depending on which keys are used, this number will likely fluctuate. Linkage performance should also be evaluated under different combinations of available data elements, as missing or inconsistently captured fields may reduce the match proportion. To assess linkage validity, a subset of matched records was manually reviewed to confirm concordance between the physician notes, ordered medications, and dispensed products (eg, agreement between medication identity and the NDC).

In our dataset, on-site pharmacies demonstrated a lower observed dispense percentage. This likely reflects temporal operational factors during the study period, including a transition from a third-party vendor (Walgreens) to an internally managed UCSF pharmacy system, as well as concurrent contractual and data integration changes. These disruptions may have affected the capture and availability of dispense data in the EHR, leading to under-ascertainment of dispenses rather than true differences in patient medication pickup behavior.