Pharmacy Claims Data Analysis

Why Your Pharmacy Claims Numbers Look Wrong

Most people pulling pharmacy claims data end up with inflated dispensing counts or duplicate fills because they don't understand how the data is structured before they start analyzing it. I've sat through more meetings than I can count where someone presents a clean-looking report on prescription adherence and every single number is wrong by a factor of two or three. The problem isn't the tool. The problem is the source.

Pharmacy claims data sits somewhere between billing records and actual clinical truth. A claim gets submitted when a pharmacy bills a payer — usually a PB, a health plan, or Medicare — for a dispensed drug. That's the basic transaction. But what you get back is a messy aggregation of these transactions that needs serious cleaning before it means anything. At its core, Pharmacy Claims Data Analysis involves extracting, cleaning, and interpreting prescription transaction records to answer questions about utilization, cost, adherence, and outcomes. The raw data typically comes from sources like Medicare Part D claims, commercial payer feeds, or PB data warehouses. Each source has slightly different fields and quirks. A standard claim record contains roughly these fields: member ID, date of service, National Drug Code (NDC), quantity dispensed, days' supply, pharmacy ID, professional claim type code, payer info, and cost breakdowns including what the member paid out of pocket versus what the plan covered. That last part — the cost structure — is where most people get tripped up early on.

Here's something most beginners miss: the days' supply field is almost never filled in accurately across the board. Some pharmacies use the package size. Others estimate based on the prescriber's intent. For insulin products and chronic maintenance medications, the discrepancy between reported days' supply and actual fill intervals can be massive — sometimes off by 40 to 60 days. If you're calculating medication possession ratio (MPR) or proportion of days covered (PDC) without validating this field against the actual fill dates, your adherence numbers are going to look better than they actually are. I spent three weeks last year trying to figure out why our MPR calculations for a diabetes cohort looked impossibly high — over 90 percent across the board. The fix turned out to be that a major retail chain was reporting days' supply as 365 for all their GLP-1 agonist fills regardless of the actual prescription length. The data had a specific NDC range that flagged it. We ended up dropping the days' supply field entirely for those products and calculating adherence purely from fill intervals. The MPR dropped to about 72 percent, which was the real number all along.

What You Actually Need Before You Start

Don't pull data until you have a specific question. I know that sounds obvious but I've seen analysts request six months of claims for 50,000 members and then not know what to do with it when it arrives. Define the outcome you're trying to measure first. Then figure out the member population, then the time window, then the data elements you actually need. Everything else is noise. You'll also need a reliable way to de-duplicate and map drug names. NDC is the standard identifier but here's the thing — NDCs change frequently. Manufacturers repackage, reformulate, or rebrand. The same drug product might have five different NDCs across a two-year period. If you're doing longitudinal analysis and you treat each NDC as a separate drug, you'll artificially inflate your medication counts. Mapping to the RxNorm concept unique ingredient (CUI) or at minimum to the brand/generic name level is non-negotiable for anything beyond a simple snapshot. Claim type codes are another landmine. In commercial data you'll see codes like 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, and so on. Each one represents a different kind of claim — professional, institutional, prescription drug, mail order, etc. If you don't filter to the right claim type before you start summing quantities or costs, your totals will include physician-administered drugs, hospital outpatient fills, and everything else mixed together with retail prescriptions. That's not a small error. It's a structural one.

The Pipeline, Step by Step

Start by pulling raw claims from your source. This could be a direct feed from a PB, a Medicare data repository, or your internal data warehouse. Export it as flat files or query it directly. Don't try to work in the source system unless you have to — it's slower and more brittle. Once you have the data, run a basic sanity check. Count total claims. Check the date range. Make sure you have zero dates. Verify that days' supply values are positive and within a reasonable range — anything over 365 days for an oral medication is almost certainly wrong unless it's a specific long-term supply exception. Next step is mapping. Take your NDCs and map them to a standard vocabulary. RxNorm is the gold standard for this in the US healthcare space. The NIH maintains free mapping tools and batch files you can download. If you're working with Medicare data specifically, you can also map through the CMS standard product code (SPC) crosswalks, which are sometimes more stable across years than NDCs.

After mapping, de-duplicate. Same member, same drug, overlapping fill dates — that's a duplicate or an early refills situation, not two separate courses of therapy. The standard approach is to collapse overlapping fills by taking the earliest start date and the latest end date, then flag the original claim. There are more sophisticated algorithms like the RxFill approach used in the IMS Health methodology but for most practical purposes, the date-overlap method gets you close enough. Then calculate your metrics. For adherence, PDC is generally preferred over MPR because it accounts for overlaps and early refills in a more clinically meaningful way. For cost analysis, separate the member cost share from the total allowed amount — mixing those two gives you nonsense results. For utilization trends, always normalize by per-100-member or per-1000-member rates so your denominators stay comparable across time periods.

Where This Method Actually Breaks Down

Pharmacy claims data only captures filled prescriptions with a claim submission. It does not capture medications that were prescribed but never filled. It does not capture over-the-counter drugs. It does not capture medication taken but not billed — which happens more often than you'd think with samples, manufacturer assistance programs, and copay accumulator adjustments. If your analysis requires knowing what patients actually took, claims data alone will undercount by a meaningful margin. There's also the issue of data lag. Most payer feeds are not real-time. Medicare Part D data, for example, is typically available with a 90-day lag. Commercial data varies by contract but is usually 30 to 60 days behind. If you need current snapshot information for operational decisions, claims data is the wrong tool. You'd be better off pulling from a pharmacy management system or a real-time eligibility and benefits platform. Another blunt limitation: pharmacy claims don't tell you why. You can see that a patient stopped filling their blood pressure medication in March. You cannot tell from the claims data whether they ran out of money, had a bad side effect, switched to a different drug, died, or simply forgot. Attribution in claims data is correlation at best. If you need causation, you need clinical data to go with it — labs, notes, encounter history. Claims alone won't get you there.

For the overlap de-duplication step specifically, I've found that the standard collapsing algorithm over-corrects in cases where a patient legitimately has concurrent therapies for the same drug class. A patient on both metformin and a sulfonylurea might have overlapping fill dates that the algorithm would incorrectly merge. Always validate your de-duplication logic against a manual sample of at least 50 records before applying it to your full dataset. Takes about twenty minutes and saves you from publishing garbage results.

Tools I Actually Use

For most Pharmacy Claims Data Analysis work I pull raw data into SQL, do the heavy lifting there, then move into Python for the mapping and metric calculations. Pandas handles the date overlap logic cleanly. For RxNorm mapping I use the UMLS REST API or the downloadable RRF files from the NIH — the API is slower but easier to automate in a pipeline. The batch files are faster for one-off work. If you're doing this at scale across millions of members, consider loading the claims into a columnar database like ClickHouse or Snowflake. Standard relational databases choke on the volume and the date-range joins are brutally slow. I had a job where we were running a simple MPR calculation on 2 million members with three years of claims and it took six hours in PostgreSQL. Moved to Snowflake and it took twelve minutes. Same logic. Same output. For visualization and reporting, I stick to what works rather than chasing the newest dashboard tool. Tableau for ad hoc exploration, then Python scripts that output clean CSVs for stakeholder distribution. The people who read these reports usually want a table and a chart, not an interactive dashboard they'll never open again.

The biggest productivity win I've found is building a reusable template for the data cleaning and mapping steps. Once you've written the NDC-to-RxNorm mapping pipeline and the overlap de-duplication logic, wrapping it in a function or a stored procedure cuts the setup time for each new analysis from about two days down to maybe an hour. The first build is painful. Everything after that is trivial.