The access team wants one number: how many patients are on therapy. You have a dozen specialty pharmacy files, a hub feed, and a copay vendor. Every one of them can count patients. None of them agrees with the others.
Here’s the uncomfortable part. Nobody has the one number. You can bound it, and you can make the bounds tight. Pretending it’s exact is how the count ends up in a board deck and then has to come back out.
Why the same patient shows up three times
Each specialty pharmacy de-identifies patients its own way, so the same patient can appear three times under three tokens and you can’t tell. That’s the stance the 867 mismatch answer takes, and it holds here. The data sources reference lists de-identified patient tokens as an identifier whose trap is exactly this.
Four more effects inflate or scramble the count. Unlike the token problem, you can detect each one:
| Source of double-counting | What it looks like | What to do |
|---|---|---|
| Status snapshots | One patient, four records on one day | Keep the last status per patient per day |
| Two active states at once | A patient both “on therapy” and “pending” | Test that each patient sits in exactly one stage |
| Token change between deliveries | Adherence curve resets for a cohort | Compare token sets across deliveries; flag mass turnover |
| Pharmacy transfer | A stop at one pharmacy, a start at another | Look for paired stops and starts in the same week |
The snapshot row comes from a rare-disease biotech whose specialty pharmacy sends four files a day: patient status, dispense, inventory, and demographics. The status file is an end-of-day snapshot, so a patient who moves through every stage in one day arrives as four records. The two-states row is one of the relational failures in why testing commercial pharma data is harder. The token-change row is the characteristic failure of longitudinal patient data, which quietly resets adherence and persistence curves. The transfer row is our reasoning, not a documented case: because tokens are assigned per pharmacy, expect a patient who switches pharmacies to show up under a new token.
The token check needs last month’s delivery to compare against. A two-stage FITT architecture keeps raw data exactly as it arrived, so the prior token set is already there when the new file lands.
Count an event, not a patient
“Unique patients” is three or four different numbers. Referred, enrolled, shipped, and on therapy each need their own definition, and the pharmacies don’t agree on the dates. One counts a referral the day it arrives, the next the day benefits clear, the third when the first box goes out the door.
So write the definition down per event, map every feed to it, and only then count. The funnel logic has to put every record in exactly one stage. A gap drops patients out. An overlap counts them twice.
Report a floor and a ceiling
This is the method we recommend, not an industry standard. Once each source is clean, count distinct tokens within each source, by event and by week. Then report two numbers.
| Specialty pharmacy (illustrative) | Distinct shipped patients, one week |
|---|---|
| Pharmacy A | 140 |
| Pharmacy B | 95 |
| Pharmacy C | 30 |
| Ceiling: sum across pharmacies | 265 |
| Floor: largest single source | 140 |
Made-up numbers to show the method, not a customer result.
The true count sits between 140 and 265. The floor holds only while Pharmacy A’s tokens stay stable between deliveries, which is why the token-turnover check matters. How wide the gap runs in practice depends on how often your patients fill at more than one pharmacy, so measure that before you quote the midpoint.
Then use the hub as a cross-check. The hub feed carries enrollment, benefits verification, prior authorization, and copay activity. Its weekly enrollment count against the pharmacies’ shipped count is a second view of the same funnel. Copay redemptions that never reconcile to a dispense usually trace to a plan or bridge-file mapping change, not a missing patient.
Leave the tokens alone
The tempting fix is to link tokens across sources yourself, on date of birth, zip, and fill date. Don’t do that in the warehouse. The data arrived de-identified for a reason, and whether any linkage is allowed is a question for your privacy team and your tokenization vendor, not a SQL problem. Build the floor-and-ceiling report on what you received, and let the access team see the range.
Specialty pharmacy integration is almost always somebody’s afterthought. A range with its method written beside it is the first version of this number anyone can defend.