I work in Point of Care advertising at Publicis on the Pfizer account. If you don't know what POCwhich to be fair is most people is, I'll explain. You know how when you go to the doctor, there's a TV in the waiting room that will cycle through messages? It'll say things like "Check out these 3 tips for heart healthy living!" or "Watch for these 5 signs of heart disease!". And then, right after, there will be an ad that says "By the way, if you're worried about your heart, talk to your doctor about this medication."
My job is to put that ad there.
As part of my job, we get Excel lists from the client with hundreds of thousands of doctors on it identified by their NPI numberan NPI number is basically a unique ID number for doctors, nurses, and other healthcare practitioners. The list will have dozens of columns with different categories for each NPI, which we use to choose which doctors we want to target. These columns look like:
- Are they likely to prescribe: High/Medium/Low
- Do they stock the productMostly for vaccines: Yes/No
- Specialty: There will be dozens of different specialties like Neurologists and Cardiologists, but depending on the brand we only want to reach specific specialties
- Dozens of other similar internal segmentation categories.
When we buy the media to run our ads, we don't buy it based on the number of doctors we are reaching. We buy it based on the number of offices we are reaching. So to decide which offices we want to reach, we need to convert this list of NPIs to a list of officesThis is called "rolling up" an NPI list into an office list. That would be simple enough, but most offices have multiple NPIs in them (some with hundreds or thousands). As long as one NPI has what we want, we count the whole office as having what we want. In plain English, it's easy to say "Alright, if the office has a doctor who is High segment Neurologist, we consider that a High segment Neurologist office".
Unfortunately, Excel doesn't understand English. Even though it's conceptually simple, actually categorizing the offices properly takes a ton of time and has a lot of room for error. The formulas we use to roll up the list have to be implemented in the exact right order to ensure that every permutation of columns we are interested in is rolled up properly, which can easily take 4–5 hours each time. And if we ever get a new direction from the client about which segments we want (which happens often), you have to do it all over again. After all that, even if we do it right, we still have to manually break out vendor matches when we receive them.
I used Claude Code to build a tool that does all of that for you in 5 minutes and runs locally so you never need to worry about uploading sensitive data anywhere. You upload the Excel file. The tool groups NPIs into offices and asks you what to do for the columns where NPIs at the same office disagree. It rolls it up for you. You're done. Unfortunately, I can't demo the tool here because we're using it internally at the agency, but I can explain what is going on under the hood:
The first thing the tool does is consolidate addresses. "123 Main St" and "123 MAIN STREET" should be one office, but spreadsheets treat them as separate. The tool runs every address through a normalizer that handles the usual variations (Suite/Ste/STE., trailing whitespace, ZIP codes that lost their leading zeros to Excel's number conversion) and creates a list of unique addresses.
The part that actually saves the time is conflict resolution. For every non-address column, you decide what the tool does when NPIs at the same office disagree. Priority columns get a rankingCardiology over Endocrinology, Decile 10 over 9 over 8, etc. and the office takes the highest-ranked NPI's value. After the rollup runs, the tool shows you if there are any columns you didn't rank which produced conflicts and asks how you'd like to handle them. You make the calls in a few clicks.
What comes out of the tool is the office list with the NPIs that filled each office, ready to send out. Vendors take it, mark which offices they can reach and with which tactics, and send the list back. We feed those responses into the tool and now we can ask things like "if I want offices reachable in Waiting Room AND in Decile 9–10 AND with my preferred specialties, how many are available?" Without it, every time we want to look at a different permutation, it's a separate request to the vendor asking them for a new match for which we have to wait a full week. With it, we do it ourselves in a few minutes.
The takeaway from this project isn't that I'm awesome for building italthough that is true. It's that AI is a massive lever in the hands of people who understand the work. I'm not an engineer. I built this because I know the workflow inside and out and I know Claude Code well enough to guide it when it gets on the wrong track. For my team, the tool will save almost 3 weeks worth of man-hours over the year. Across the agency, that's the equivalent of a full-time employee in time savings. Multiply that by the number of internal tools every company already wishes existed and you can see the benefit of AI-native thinking.