Procurement spend analysis with inconsistent vendor names

Vendor-name cleanup is not the same as vendor identity resolution. Two spellings may refer to the same supplier, while two very similar names may belong to different legal entities. A spend analysis should merge records only under an explicit identity policy and preserve ambiguous cases for review.

Normalize names to generate candidates, then use verified evidence to assign a canonical vendor. Do not let a fuzzy string match silently decide who received the money.

Inspect a small spend extract

The synthetic vendor-spend fixture contains six invoices totaling 70,000 paise in INR:

The vendor map assumes verified entity evidence for V01, V02 and V03. R4's similar spelling is insufficient evidence in this exercise. It remains unresolved even though a string-cleaning rule could make it look identical to R1.

Join through a unique source identity

SELECT m.canonical_vendor_id,
 CASE WHEN m.canonical_vendor_id IS NULL THEN 'unresolved' ELSE 'resolved' END AS identity_status,
 COUNT(*) AS invoices,SUM(s.amount_paise) AS spend_paise
FROM vendor_spend s LEFT JOIN vendor_map m USING(raw_vendor_id)
GROUP BY m.canonical_vendor_id
ORDER BY m.canonical_vendor_id;

The result is V01 at 30,000 paise, V02 at 15,000, V03 at 20,000 and unresolved spend of 5,000. Total spend remains 70,000. The unresolved group is a reporting bucket, not a claim that all unknown records belong to one supplier.

from collections import defaultdict
from build_and_verify import database

db = database()
rows = db.execute('''SELECT s.invoice_id,m.canonical_vendor_id,s.amount_paise,s.currency
 FROM vendor_spend s LEFT JOIN vendor_map m USING(raw_vendor_id)''').fetchall()
db.close()
assert len(rows)==6 and len({r[0] for r in rows})==6
assert {r[3] for r in rows}=={'INR'}
totals = defaultdict(int)
for invoice,vendor,amount,currency in rows:
    totals[vendor] += amount
assert dict(totals)=={'V01':30000,'V02':15000,None:5000,'V03':20000}
assert sum(totals.values())==70000
assert totals[None]==5000
print({'spend_paise':dict(totals),
       'resolved_spend_share':65000/70000,
       'V01_share_all_spend':30000/70000,
       'V01_share_resolved_spend':30000/65000})

V01 represents about 42.86% of all spend, or 46.15% of resolved spend. Both calculations are arithmetically valid, but their denominators differ. Show identity coverage beside concentration measures so unresolved spend does not disappear from the interpretation.

Use matching evidence appropriate to the decision

Useful evidence may include a verified vendor-master identifier, approved registration information or a documented parent-subsidiary relationship. Decide whether the analysis concerns legal entities, payment recipients or corporate groups; those can produce different consolidation results.

Name similarity can prioritize a review queue. It cannot establish that two entities should be merged for every purpose. Shared addresses, common words and trading names can produce false matches, while a supplier rename can produce low textual similarity despite continuity.

Store the mapping's evidence, reviewer and effective version. Historical reports should be reproducible when mappings change. A procurement team may need both the original source name and the canonical grouping.

Reconcile before recommending consolidation

A duplicate mapping row can multiply invoices during a join. Enforce one applicable mapping per source vendor for the report's time and scope, then verify invoice count and total value before and after resolution. The lab's unique map key catches that failure.

Standardize currency and treatment of tax, credits and intercompany transactions before comparing spend. A high spend share does not itself prove excessive dependency or a consolidation opportunity. Category substitutability, contract terms, service quality and operational risk require additional evidence.

Exercise: create a candidate match for R4 with a similarity score but no verified entity evidence. Keep it outside the approved map, report its unresolved spend and describe the evidence required to approve or reject the candidate.

NeuraPath's Data Analytics with Generative AI course connects data quality with procurement reporting. A useful spend analysis preserves totals while making identity decisions auditable.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Supplier delivery performance with promised-date changes.
• Continue with Accounts receivable ageing with partial payments.