BIQ - Power Tools for the Data Analyst
Product Working With Us Articles Partners Company Downloads

White Papers


Mapping Spend: Three Easy Steps

We'll tell you what no other spend analysis vendor will tell you: mapping spending into a commodity hierarchy is easy. Vendors don't want you to know this, because they'd prefer to charge you a high price to do it for you. You'll hear lots of stories about how tough the process is, how they've created custom databases of vendors and SIC/UNSPSC codes,
It's a mistake to think of data mapping as an offline process. Data mapping should be part of the everyday use of a spend analysis tool.
databases of who owns whom, artificial intelligence algorithms that can deduce the manufacturer of a screw by its partial description on an invoice, and so on. There are many such claims.

And they're largely irrelevant. For most companies, basic spend mapping can be done in a simple and straightforward way, by clerical resources with no special knowledge or skill. That is exactly how spend is mapped by most vendors behind the scenes, despite all their marketing claims. With BIQ, that basic spend map can be altered easily by commodity experts, at any time, as required.

The main requirement is a rules system (like BIQ's) that supports a "rules overlay" concept. Here's how to think about rules overlays. Look at a light bulb, and let that light represent your total spending. If you put a piece of Swiss cheese in front of the bulb, the cheese will block some of the light. That piece of cheese represents a mapping rules overlay, and any light that's blocked represents spend that has been mapped by the rules in that overlay. The "spend" that passes through the holes in the cheese remains unmapped.

If you put a new piece of Swiss cheese between the light bulb and the first piece of cheese, the new piece will intercept some of the light before it reaches the first piece. Its rules are therefore "higher priority." Nevertheless, the "spend" that passes through the holes in the new piece is unaffected by its rules. That "spend" may either continue on to hit the first piece of cheese, and be mapped there, or it will pass through both pieces of cheese and remain unmapped.

If you put a third piece of Swiss cheese in front of the second piece, it may intercept the spend before it reaches either the second piece or the first piece, and so on.

Most spending can be mapped efficiently with four mapping overlays and three easy steps.


We'll need four rules overlays:

  • Base — contains one rule which maps all spending to a commodity called "Other." Its only purpose is to ensure that no spending remains totally unmapped (BIQ will create this rule overlay, and the base rule as well, when you initially create a mapped dimension).
  • GL — maps general ledger code to commodity
  • Vendor — maps vendor to commodity
  • GL+Vendor — maps a combination of GL and Vendor together, to commodity.

In this case the "light" is shining "up" through this overlay list. Thus, the highest priority overlay is "GL+Vendor". The next is "Vendor." The next is "GL." And, any spending that gets through all of these overlays and is still unmapped hits the "Base" overlay and is dumped into the "Other" category.

This priority ordering is deliberate. Vendor rules are, in general, stronger than GL rules. The combination of Vendor and GL together is stronger than Vendor, since Vendors can supply more than one commodity.

The Three Steps

  1. Use BIQ to find the GL codes that represent the top 90% of spend. For each of those GL codes, map it to a Commodity (putting the rule in the GL overlay).
  2. Use BIQ to find the Vendors that represent the top 80% of spend. For each of those Vendors, map it to a Commodity (putting the rule in the Vendor overlay). If the Vendor sells more than one thing, guess at the largest Commodity, and put it there — but make a note of that Vendor.
  3. For each of the Vendors that you noted in (2), create rules that are a combination of Vendor and GL code together, and that correctly apportion that Vendor's spend to the proper Commodity (putting the rules in the "Vendor and GL" overlay).

Why this technique works

Mapping the top GL codes first is an ideal way to map the "tail" of the spend distribution, efficiently and cheaply. You can't map all your Vendors (there are tens of thousands of them, typically), nor do you need to; so the GL rules will pick up the tail of the distribution that you don't care about, and will do a decent job of mapping that spend.

Mapping the top Vendors after the top GLs cleans up GL mappings that are inaccurate. Doing so manually, while paying attention to Vendors who provide multiple Commodities, ensures accuracy. When in doubt, a Google search on the vendor will resolve what they do almost immediately; and, you can use the BIQ Viewer to drill down to raw transactions or to spot GL or Cost Center apportionment by Vendor for clues.

Why not run all your Vendors through an automatic mapping process? Because it's a poor approach. Even the most extensive Vendor databases only contain 50-60% of the vendors (40% in Europe, worse elsewhere), and automatic matching technology (regardless of claims to the contrary) is quite poor at resolving spelling errors and abbreviations for the Vendors that could theoretically be matched. What's worse, automatic matching technology can't build an accurate mapping rule — instead, it tries to pick a "most probable" commodity. Far better to map the top Vendors by hand, and do this accurately.

The BIQ difference

BIQ incorporates rule-building directly into its online Viewer. There is no offline process, no special "tool" that has to be run, and no "publish" process to insert the results of the offline processing into the online dataset. Rules processing is done online, in real time. Rules can be "undone" at any time, too. So, unlike other rules systems, you can see the impact of a rule and then undo that rule, if you want to, all within the online Viewer context.

Why is this important? It's important because there is no better tool than BIQ's online Viewer for figuring out how to map spend. You can find out when the spending occurred, what cost center it was booked to, what GL it was booked to, even which office ordered it (if your dataset has a business unit dimension). And, you can drill down to individual transaction level detail to find out more — all within the context of a tool that you already know!

If a spend analysis vendor gives you a different, offline tool to map spend, it needs to be every bit as competent as the main Viewer in allowing you to look at that spend from every conceivable angle. So, why doesn't the Vendor's mapping tool leverage the same Viewer that they're trying to sell you?

The answer is, spend analysis vendors can't use their main Viewer for mapping, because their architecture is based on an antiquated work -> cook -> republish model. In such a model, change is difficult and slow, because it requires separate tools, tedious re-publishing processes, and special training to use those separate tools.

What happens if a mapping error is found? Who fixes it? When is the fix "published"? With BIQ, the change can be applied instantly. It's a mistake to think of the mapping process as a discontinuous process. In fact, mapping is part of the normal every day use of a spend analysis tool. It is not a "special exercise" that's done only rarely, or as part of a monthly or other periodic refresh. Mapping needs to occur continually, just as hierarchy adjustments need to occur continually.