How I Mapped Messy CRM Product Names to Clean Categories in Excel

Five minutes ago you handed your CFO the answer to a question you had scrambled to find in fifteen minutes. Now he is sitting in a board meeting about to share the intel with the powers that be.

The question was pipeline versus plan, whether we could tell if we were going to hit the number for the year. You told him it was going to be close, you called out the gap, and he seemed satisfied enough as you watched his Slack bubble go red.

Now your phone is buzzing, and it is him, from inside the meeting.

Hey, I'll probably be presenting in about 15. Most of our growth number is software. Can you help me understand how much of this is software and how much comes from other products? They're going to want to go deeper, and I'd love to have a little more color to give if possible.

There it is. The payoff for handing over good information is that it buys you harder questions, and the harder questions don't necessarily come with more time on the clock.

What this series is

I have spent the last twenty years working my way up the finance and analytics ranks, from junior analyst to CFO, and I started this series because there is a severe lack of realistic business scenarios being covered in training. All of it in the most accessible analytics tool on the market, Microsoft Excel. No Python, no SQL, not even AI, because the reps are yours to keep and cut your teeth on. And if you came out of a certificate program hunting for a capstone that isn't the bike share data set everybody else has been using, this is built exactly for that.

Just so we're straight on where I stand: I wrote the requirements to build this file, and I spent fifteen minutes in it on camera last week. That question was whether the year lands at all. This one is where the number actually comes from. Last week's fifteen minutes are down there too if you want the story from the top. You don't need it. This should stand on its own.

The situation

You're an analyst at a private equity rollup. There's been a handful of acquisitions, and you've got three Salesforce instances smashed all together into one.

The board's got a bookings plan written in four clean categories, and software represents about 80% of it. The CRM, meanwhile, thinks that we sell somewhere north of 500 different products. Free text, no legend, a decade of salespeople typing in whatever the customer was buying.

Last week the clock beat me to the mapping, so I answered the aggregate question and pointed at this exact problem on my way out. Well, here we are. File still open, and this time the mapping needs to be solved in order to answer the new question at hand.

Fifteen minutes on the clock. Let's get it.

Where last week left it

The bookings plan was given to us by the board. Full year target is $63.8 million, broken into four product categories, and $51 million of that 63 eight is software. That's the one he's asking about, because that's going to be the most valuable revenue we have.

The invoice history tab is 2025 data only, so it really just serves the purpose of providing us a map from product names to categories. The opportunity data out of the CRM is the issue. Here there's no real product names, or at least they're not clean at all, and they've all got this entity one, two, three thing in front of them from the merge.

Last week's answer, in three numbers. $48 million of open opportunity out there. Closing at the historic rate got us to about $56 million against a target of 63 eight, which meant a shortfall of $7.8 million, which meant we'd need a pipe of $20 million at that close rate to hit the target.

That was the aggregate. Now we need to blow it out at the product group level.

Strip the prefix

First thing is to strip out that preface, the entity tag sitting on the front.

New column. Product strip, something like that. =TEXTAFTER(), grab the product field, split on the dash, and we want the first instance just to not mess anything up further down the string.

Then the moment of truth. Are we going to get lucky?

The lookup that mostly worked

XLOOKUP the stripped product against the product names on the invoice history, and return the group category.

Oh. It actually doesn't look that bad.

Then the count on what fell through. 328 did not hit.

Sort, unique, and assign by hand

SORT(UNIQUE()) on the misses, so I'm looking at distinct product names instead of every single row. Analytics module US 2025, with annual spelled wrong, and it goes on like that.

"This is terrible data quality. As you'd expect out of a CRM. People are just keying in whatever the heck they want for no good reason."

Then the same sort and unique on the clean category list, the two dropped side by side, and I start labeling. We don't have time to luxuriate here.

Analytics module to software subscriptions. API access tier and benchmark to data and add ons. Compliance module to software. Custom integration and data migration to implementation services. We're just flying here, we got to go. Dedicated CSM to support and maintenance. Extra storage block to data and add ons. Mobile access monthly back to subscriptions.

Then onboarding support, which is both things at once. Onboarding is implementation, support is support. Good God. I hate data quality problems, and on a clock there's not a lot that we can do here. We just got to go fast.

Which is the honest read on the whole stretch:

"AI, regex, that kind of thing would probably be good here. It's going to be good to have, like, a master map of some sort."

But here we are, and we're just going to slap this together right now. Where the first lookup cannot find it, a second XLOOKUP goes against the assignments I just built by hand. Enter, and poof, they're gone.

Cut the pipeline by category

Five minutes on the clock. It's a little janky, but I think we can do it.

Update the pivot source so it picks up the cleaned column and gets rid of the ENT tags, refresh, and bring product group in.

Then the move that makes the rest of this fit. I build the category block once and set it up so it's repeatable. Data validation on a cell, allow a list, sourced from the category list, and that cell is now the switch. Then I update the existing formulas so they include the group column as a criteria range and that dropdown cell as the criterion, locked down with F4. The target lookup was still pulling the full year number, so it gets repointed at the bookings plan to pull the selected category's target.

Build it once and the other three categories cost you a dropdown change and about three minutes. That is the only reason four answers fit inside the last five.

Software

The one he asked about.

Pipeline needed, $17 million. Pencils down, no new leads, and we're looking at a $6.5 million gap through the end of the year.

Then the sanity check against the run rate in the pivot. Software last year, May through July, averaged $4.8 million. May through July now, $5.8 million, so we're up about 20% year over year. August, September, October last year averaged $6.7 million, so let's call it $7.5 million with the growth on it. Times the two and a half months of runway a deal has left because of our time to close, and you go back from the end of the year at $18.7 million.

We are in the clear.

Implementation, add ons, and support

Three more dropdown changes, three quick reads.

Implementation services. About $2 million in pipe, and the growth is so steep the comparison stops working. Dear goodness gracious, you're over. This must be a new push, because everything was software and now everything's going, so maybe part of this rollup they realized they could bolt this on top of the software subscription. I'm not even going to use the year over year multiple because it's going to be astronomical. These numbers are too big. This is meaningless. Easy slam dunk.

Data and add ons. About $4 million, and this is the tighter one. The close rate here is a lot lower. Add ons is harder to sell.

Support and maintenance. A positive value where the others have gaps. We don't have a gap. We have a negative gap. What's banked plus what's open has us already forecast to beat target.

I think we have our story, and we have ten seconds to go.

The message back to the CFO

Software, we're on target, and we believe there's a little bit of a buffer right now. Pencils down, no new leads, we'd have a $6.5 million gap through the end of the year, which means about $17 million in new pipe just on software at our current close rates. Given the rate we're adding new pipe coming into busy season, we should be able to hit that, and probably do better before the year's up. There is a gap, but it's manageable.

Implementations are in a good spot as well. Really solid attach rate, our close rate is really good, and they're closing very nicely. We think we hit that number no problem.

Add ons is a little bit tighter. We're not doing it as well, and we might want to think about how well we're actually winning those deals, because right now the win rate is low. And so it says that we need more pipe.

Then I ran the math on that last one. Take the add ons gap, divide it by 0.38, and see what a better close rate would actually buy us. About half a million dollars.

"So close rates, a big part of it, but it's not the entire part of it."

We're just behind.

What the total was hiding

And then support, which is actually the one that's offsetting.

Support is running a positive. Inside the aggregate, that positive nets against the shortfalls in software and in add ons. Last week the total said we have 20 million that we need. Priced category by category, it adds up to more than 20, because the support is so positive that it's causing an offset in the total.

The total made the number look smaller than it is. Anybody managing to that total would under-resource the one category that's genuinely behind.

So all in, this is actually a good story. Add ons is looking a little tough and we might want to push that. Implementations, we're solid. Software, we're solid. Support is a slam freaking dunk, and it's a bright spot. We just got to tighten up add ons, but overall we're still feeling good.

And none of that is visible until the products are mapped, which is what the fifteen minutes bought.

Leave the weeds on the forest floor

What you just read was directional. It's data backed in as much as we could do with the time that we had, and it comes with all of its own caveats and footnotes where needed.

One thing I do want to say about this process, though, and this is important to remember. Regardless if the work took you fifteen minutes or fifteen hours, nobody cares how hard you worked on something, because that's the job.

We care, of course, because we know how hard we worked, and because we often feel compelled to take others on our own learning journey just to show how much work we did.

Don't, though. Resist the temptation and use the BLUF framework. Bottom line up front. Leave the weeds on the forest floor. The only reason you should ever really be talking with others about the weeds is if you're seeking help during a process, or somebody asks explicitly about them and gives you permission to go there. Otherwise, just keep the weeds clear and focus on the outcome.

This took me a long time to learn, but it really changed how people receive my communication. As I'm suddenly far more succinct when I talk. Imagine that.