Why Your Spend Data Is Wrong Before You Analyze It

Here’s a quick challenge for you today: Think of your largest supplier. Now pull and open your spend by supplier by category report and take a look at the top of the list. You might be surprised to find the supplier you assumed was your largest isn’t at the top of the list.

But it may still be your largest supplier, just broken up across three different spellings and multiple categories.

Items can have the same problem, with one plant buying material by the roll while another buys by the “piece”. And if nobody compares the two, you may end up tracking the same items under two different types.

The fix isn’t always a quick chart update, a manual data merger, or a dashboard fix. The records underneath the reports were wrong before any of your visualizations were created, so it may not make much difference how you show the data: it’s going to be wrong until it’s cleaned up for good.

What is spend data cleansing?

Spend data cleansing is the process of making your purchasing records consistent before anyone analyzes them. 

It involves steps like merging duplicate supplier records and standardizing item descriptions or units, and assigning every line to a single, consistent category scheme.

Cleansing also goes beyond what sits in your ERP. You will often need outside, non-ERP data, enrichment data to progress with validated data cleaning including gated addresses, a roster of alternative company names for each supplier, and the correct VAT numbers, country validation etc.

The data in the system will usually let you clean only a fraction of your records, so plan to bring in enrichment data early and make sure the enrichments are live for all datapoints being added to your system going forward - this is not a one-off task.

This is an essential step before utilizing the spend data, because a report can only total what all the records currently say. Anything built on unclean data risks coming off incorrect or misleading. 

Why is spend data wrong before you analyze it?

Spend data doesn’t end up wrong in analysis because of malicious intent. It’s usually the result of many people creating many records across multiple years of business. Most people do what makes sense to their local context.

For instance, Microsoft’s Business Central documentation notes that a single vendor can end up with more than one record when different users create vendor cards over time (or during a migration). Detail can also get lost as data moves into the ERP.

Drift typically shows up in three places:

1. 1. Suppliers (one company, several records with incomplete data)

   2. Items and categories (one material, three descriptions, mixed units and not all linked to the catalogue items in the ERP system)

   3. Trace (A data point that nobody can follow back to its original invoice lines)

You can test this and see where your drift is at in about five minutes: sort last year’s accounts payable export by supplier names and invoice lines and look for near-duplicates in the data. Then you can see how two plants might describe the same material.

How do you clean spend data?

Before you start making any changes, remember to always work on a copy of your data. You don’t want to start working directly with the records in your ERP.

You’ll be making updates in this copy of the data, and you’ll want to keep the original ID in there next to each new “cleaned” label. That way, you can always trace a number back to its original source lines.

To make it a bit clearer, we’ll trace each step through an imaginary supplier to show how numbers and names can impact how clean (or not) your data spend is and becomes.

Step 1: Set the scope and write down the total

Start the process by exporting 12 to 24 months of your accounts payable lines from your ERP into a spreadsheet or database. NIGP and CIPS list accounts payable transactions as the minimum data required for a spend analysis.

In your export, pull the:

1. 1. Supplier ID

   2. Supplier name

   3. Invoice number

   4. Line amount

   5. Item description

   6. Unit of measure

   7. Date

Add up the amounts and “write” the total down. 

Our example:
For our example company, the export totals $31,500,000

• A company called Brightline Widget Components is first on the supplier report at $2,400,000

• A supplier called Alder Ridge Widget Metals appears three times lower down on the report, at $1,300,000, $900,000, and $700,000.

Step 2: Resolve your suppliers first

This step matters because everything after inherits the supplier’s identity. Sort your suppliers by name and look for any variants. 

These could be abbreviations, legal suffixes, a store name at the end of a title, VAT numbers, full addresses, the correct country (yes, people also mess up the country codes!), etc.

Names can mislead, which is why they are a common source of spend data drift. Check the fields that shouldn’t change between spellings, starting with tax or registration number or bank account. Then look for the address and the name. 

Be aware that you represent a legal entity dealing with another legal entity (which might actually have changed name), so it has to be correct. Your ERP often holds only some of these fields, so this is where the outside enrichment data described earlier does the most work.

A shared registration number likely indicates the data points are the same company. Shared bank accounts can also point to a single entity, but payment processors and shared-service centers can serve several companies, so they may be less reliable. 

Add a column in your Step 1 export pull that maps every original supplier ID to a single clean name. You’ll want to keep the original ID beside it in the copy, because you’ll need it for the list when it’s time to fix the ERP later.

Our example:

• All three Alder Ridge Widget Metals records carry the same registration number

  • Grouped, they total $2,900,000 ($1,300,000 + $900,000 + $700,000)

  • Alder Ridge Widget Metals moves to first place, ahead of Brightline Components at $2,400,000

  • Anyone negotiating with Alder Ridge from the original report would believe they buy less than they do

Step 3: Standardize items and units

Now you’ll want to look for the same item described in different ways and convert everything to a consistent unit. Do this before you compare any prices, and make sure your conversion factors come from a legitimate spec sheet or a buyer who knows the material, not a guess.

Going back to our example, consider a packaging film that’s bought at three different plants:

• • Plant A pays 120.00 per “roll”

  • Plant B pays 115.00 per “RL”

  • Plant C pays 0.33 per “piece”

When you look at the items themselves, a roll covers 400 square meters while a piece covers one. Once converted, the prices come out to 0.30, 0.29, and 0.33 per square meter. 

In this example, Plant C is paying roughly 10 to 15% more, which you may have never found in the original report. 

Note that you’ll want to do this same process for descriptions. Pick a single consistent naming pattern and put it in a new column in your export pull copy.

One of the biggest challenges in Item cleaning is that Items simply change names. The Item you bought last year is obsolete, so you are buying the replacement item. 

For you, the buyer, the items are the same, but the supplier might have changed, and the original item name has likely changed.

Step 4: Classify to a single scheme

With everything mapped out and sorted, it’s time to start categorizing. Pick one category scheme and map every line in the export copy to it.

Go three levels deep at most. Each extra level adds risk of misclassification, and clean item names will do more for you than getting levels 4 and 5 to work. Also, don’t make Indirect versus Direct materials your level 1 category. Handle that with a tag instead, which keeps the tree simpler for your colleagues to use

An easy way to do this is by starting with your own category tree or the one your customers already use. UNSPSC is an open standard that you can start with, but it’s large, has too many levels, and covers services rather thinly. 

Whichever scheme you choose, leave room for finance costs like bank fees, VAT, taxes, duties, and interest, as they can end up piling up under “other” and hide your spend again.

Give each supplier a default category. Then handle any items that don’t fit. Classify at the high level first, then drill down into any categories with the most spend.

A note on AI: AI is becoming increasingly helpful for quick classification. But if you use AI, always check it. You should always manually look at a small sample of AI-classified data by hand before running it on the full report. 

Confirm that every category it returns exists in your tree before you trust the full process, and then always check every single categorization afterward. 

One small AI mistake can cost you millions of dollars in unaddressed spend.

Our example:

• Under the single consistent scheme, Alder Ridge Widget Metals now defaults to metals and raw materials

  • Its “freight” line moves to logistics but can be seen in the overall cost picture for the supplier.

Step 5: Reconcile

Now it’s time to check your work. Add up the total spend again and make sure that it matches the same number you originally pulled in step 1 (to the cent). Spend data cleansing is only about changing labels, never amounts. 

If the numbers don’t match, you may have dropped a record or counted it twice during cleaning. Look for a gap that matches a single invoice or differences in totals by supplier before and after. 

As a rule, never ever remove spend unless it is not really spend (intercompany being the prime example). We have had clients who only showed spend in their pre-CostBits cube that was “cleaned and verified”. 

Because there were so many items to categorize, they missed a lot and ended up not seeing millions in their antiquated spend cube. 

Then, re-run the questions in the test from our article on spend data and AI:

• • Does the supplier count agree across entities?

  • Does a category total match however you ask for it?

  • Are you able to reach back to the invoice lines behind any single number in a report, using your original ID column?

Our example: 

• • The clean copy still totals $31,500,000, matching the number from step 1 Alder Ridge Widget Metals totals $2,900,000, the sum of its three original records

  • Brightline Widget Components still totals $2,400,000
    

    

What about your ERP?

We advocate for cleaning a live copy of your data before you touch your ERP records. We also strongly oppose blindly pushing cleaned records back into your ERP, which is why CostBits can’t write to your ERP systems. 

ERP records can be wrong for analysis but still functional for operations. Rewriting any of them to clean up a report could unintentionally break what depends on them. We have clients where they on purpose create the same freight supplier twice — one for inbound (budget one) and one for outbound (budget 2) — same supplier, slightly different name but one contract.

Fix your copy first, and then follow up with fixes at the source at its own pace. Hand a cleaned list to your master data team and have them update the ERP in their normal process to keep everything clean and safe.

Once the ERP is amended, it’s a good idea to re-run your mapping once more to make sure everything translated correctly. This will give you even more confidence in your analysis.

How do you keep spend data clean?

We’ve talked about how to clean the data, but how do you keep it clean in the long run? The best way is much like you’d approach cleaning your house: a strategy that keeps things clean consistently over time. 

With spend data, that strategy typically means updating how a record is created, or, if the record can't be cleaned for operational reasons, adding a clean data layer on top, like CostBits.

Give master data a single owner, and send every new supplier request through one check for an existing record. Then re-run step 5 above to reconcile everything. 

But cleaning by hand takes time most teams don’t have when they are trying to run a business. That’s why the CostBits Clean Data module uses extensive external enrichments before we activate our machine learning and AI engines to clean, normalize, and categorize your procurement data, so you don't have to manage the load. 

You can merge data from multiple source systems into a single format and keep your processes updated as the engine learns from new information and adapts. 

Plus, CostBits Clean Data cleans without writing back to your ERP and automatically adjusts when your master data team fixes records at the source. Best of all, it works with any ERP.

Once supplier records are clean, you can then check every transaction against your rules with Live Issue Detection. 

Start with one supplier and create a spend data cleaning strategy with CostBits

Want to try out the process above for yourself? Pick your largest supplier and pull your report. Find each record for that supplier, group them all under one name in a copy, and watch what the ranking does compared to what you expected. 

The result may surprise you and show you just how easy it is for spend data to drift unknowingly over time.

To see what your own data shows, learn more about CostBits online and run a free scan on your ERP and AP data today.

Want to see where your own data stands? Start right now at costbits.com. You can also run a free scan on your ERP and AP data to get instant insights with no strings attached. 

Don’t let missing data hold your business back. Explore our top 20 free tips for rapid supplier cost reduction or contact CostBits to learn how our platform can unlock the full potential of your invoice data.