graystack

Projects / Vendor Master

Vendor Master · supplier data QA

Three systems, one supplier, and the evidence before you merge.

The same company sits in the ERP, the procurement tool and an old spreadsheet, spelled three ways. Vendor Master profiles the mess, scores every possible duplicate point by point, and lets a person decide before anything becomes one golden record.

Role
Master data, data quality, product
Platform
Web app, plus a Python command-line tool
Built with
JavaScript, Python (standard library)
Status
Concept study · working app

V-1001P-2001L-3001→VM-0002 · Acme Supply Company

01 / The problem

The same supplier, typed three different ways.

Every system that pays or buys from a supplier keeps its own copy. Over time the copies drift: capitals, suffixes, missing tax IDs, two phone formats. Reports split one supplier into three, and nobody is sure which record is right.

ERPV-1001ACME SUPPLY CO.
Tax ID
41-2093847
Phone
217-555-0142
Street
12 Market St
Category
OFF-SUP
ProcurementP-2001Acme Supply Company
Tax ID
412093847
Phone
(217) 555 0142
Street
12 Market Street
Category
blank
Legacy sheetL-3001acme supply
Tax ID
blank
Phone
+1 217 555 0142
Street
12 Market St.
Category
blank
Split spend

Spend reports count one supplier as three, so the biggest relationships look smaller than they are.

Look-alikes that aren't

Northwind Logistics and Northwind Legal share a word and a town. A name-only match would merge them.

Black-box merges

If a tool merges records without showing why, nobody can check it, and one bad merge can pay the wrong company.

A concept study, honestly framed. The suppliers, IDs and contact details are invented, and there was no formal research. The sample file was written to include real-world problems on purpose: true duplicates, near-misses, a tax ID conflict and a supplier using free webmail. The matching method is a common record-linkage approach, kept simple enough to explain.

02 / The scorer

Change a field and watch the score explain itself.

This is the app's own scoring code. Each input is cleaned the way the app cleans it (shown under the field), then every clue adds or takes away points. Nothing is hidden: the score is the sum of the lines below it.

Try a pair from the sample data

Record A

Record B

03 / The method

Profile, match, decide, then publish one record.

Four steps, in the order a data steward would take them. The app counts what's left on each one.

  1. Profile: field health and things worth fixing
    STEP 1

    Profile

    How full and how valid each field is, which values can't be right, and how many look-alike pairs there are.

  2. Reviewing a duplicate pair
    STEP 2

    Decide on duplicates

    Side by side, with the evidence and a preview of the merged record. M merges, N keeps apart, U undoes.

  3. Filling category codes
    STEP 3

    Fill categories

    A merged sibling's code first; otherwise a suggestion from the name, with the reason shown.

  4. Golden records with provenance
    STEP 4

    Golden records

    One row per supplier, the source of every value, and a crosswalk from every old ID to the new one.

A person decidesLikely pairs can be merged in one click, but never ones with conflicting evidence. Those always wait for a human.

04 / Survivorship

Which value wins, and why.

When records merge, each field follows a written rule. This table is built live from the sample data by the same code as the app; the highlighted cells are the values that survive.

Name: best-formedMixed case beats ALL CAPS, a legal suffix beats none, then the longer name.
Tax ID: agreed or newestIf the records disagree, the newest valid one is kept and the conflict is reported.
Contact: newest that has itPhone, address and a business email come from the most recently updated record.

05 / Same rules in Python

A command-line tool that gives the same answer.

Finance teams run jobs, not web pages. vendormaster.py mirrors the browser code line for line, with only the standard library, and replays the decisions file the app exports. A test checks that both produce the same pairs, scores, golden records and category suggestions.

vendormaster.py · score_pairView source
def score_pair(a, b):
    ka, kb = a["key"], b["key"]
    ev = []
    sim = name_similarity(ka["name"], kb["name"])
    ev.append({"field": "name", "points": round1(sim * WEIGHTS["name"]), "text": "Names are %d%% alike" % math.floor(sim * 100 + 0.5)})
    if ka["tax"] and kb["tax"]:
        if ka["tax"] == kb["tax"]:
            ev.append({"field": "tax", "points": WEIGHTS["taxMatch"], "text": "Same tax ID"})
        else:
            ev.append({"field": "tax", "points": WEIGHTS["taxConflict"], "text": "Different tax IDs"})
    if ka["phone"] and ka["phone"] == kb["phone"]:
        ev.append({"field": "phone", "points": WEIGHTS["phone"], "text": "Same phone"})
    if ka["domain"] and ka["domain"] == kb["domain"]:
        if ka["domain"] in FREE_MAIL:
            ev.append({"field": "domain", "points": 0, "text": "Same email domain, but it's a free one"})
        else:
            ev.append({"field": "domain", "points": WEIGHTS["domain"], "text": "Same email domain"})
    if ka["postcode"] and ka["postcode"] == kb["postcode"]:
        ev.append({"field": "postcode", "points": WEIGHTS["postcode"], "text": "Same postcode"})
    if ka["street"] and ka["street"] == kb["street"]:
        ev.append({"field": "street", "points": WEIGHTS["street"], "text": "Same street address"})
    raw = sum(e["points"] for e in ev)
    return {"score": round1(max(0, min(100, raw))), "evidence": ev}
python vendormaster.py match vendors.csvreal output, trimmed
 100.0  likely  V-1001   P-2001   name+60  tax+30  phone+10  domain+10  postcode+5  street+10
  98.8  likely  V-1007   P-2007   name+33.8  tax+30  phone+10  domain+10  postcode+5  street+10
  93.8  likely  V-1011   L-3011   name+38.8  tax+30  phone+10  postcode+5  street+10
  85.0  likely  V-1001   L-3001   name+60  phone+10  postcode+5  street+10
  75.5  review  V-1002   L-3002   name+50.5  phone+10  postcode+5  street+10
  74.1  review  V-1019   L-3019   name+49.1  phone+10  domain+0  postcode+5  street+10
  …
  70.0  review  V-1013   P-2013   CONFLICT  name+60  tax-25  phone+10  domain+10  postcode+5  street+10
build, then the testsreal output
$ python vendormaster.py build vendors.csv --auto \
    --out master.csv --crosswalk crosswalk.csv
48 source records -> 38 golden records (master.csv, crosswalk.csv)

$ python -m unittest discover -s tests
.............
----------------------------------------------------------------------
Ran 13 tests in 0.008s

OK

06 / Screens

The workbench, captured from the app.

Select any screen to see it larger.

DuplicatesNames only 56% alike, yet tax ID, phone, domain and address all agree: 98.8, likely the same.
A conflictAtlas Builders: same name and address, different tax IDs. The −25 keeps it out of bulk merges.
ProfileFilled versus valid for every field, with each problem linked to the step that fixes it.
Golden recordsOpen a record to see which source each kept value came from, and which rule chose it.
CategoriesSuggestions name their reason. Accept one, all, or pick from the house list.
Matching rulesEvery compared pair on one histogram, both thresholds draggable, and a scorer for any two records.
Your dataLoad your own export. Columns like “Vendor Name” or “Zip Code” are matched automatically and can be corrected.
On a phoneThe comparison stacks label over values, so a pair still reads at a glance.

07 / Try it

The real workbench, with the sample suppliers.

48 records from three systems. This demo starts fresh each time; the full app keeps your decisions in your browser.

  1. 1

    Merge the easy ones. On Duplicates, merge the 11 likely pairs in one click, then see what's left for you.

  2. 2

    Judge the rest. Work through the review pairs with M and N. Is Delta Courier on Gmail the same company?

  3. 3

    Check the result. Fill the categories, open a golden record's provenance, and download master.csv.

Everything stays in your browser. Load your own CSV from Your data.

Open the full app

08 / How it's built

One small core, written twice and tested against itself.

core.js and vendormaster.py hold all of the matching and survivorship rules. The interface only reads their results and records your decisions.

  • ~1,880lines across JavaScript, CSS and Python, tests included
  • 30comparisons for 48 rows, instead of 1,128, thanks to blocking
  • 13tests, including exact parity between the two languages
  • 0bytes different between the app's master.csv and Python's, on the same decisions
  1. Explainable scoringSix weighted clues, each producing a line of evidence. The score is their sum, capped at 100, so it can always be read back.
  2. Blocking before matchingRecords are only compared if they share a name prefix, tax ID, phone or business email domain, which keeps big files fast.
  3. Rounding that agreesPython rounds halves to even; JavaScript doesn't. Both use the same half-up helper so scores match to the decimal.
  4. Decisions, not editsThe app stores which pairs you merged or kept apart. Golden records are rebuilt from those, so undo is exact and the file replays in Python.
  5. Columns mapped on importCommon export headers are recognised and rewritten to the house names, so the same file works in the browser and on the command line.
  6. Crosswalk for the old systemsEvery source ID maps to its golden ID, so the ERP and procurement tool can be updated without losing their history.

09 / What I'd test next

What real supplier data would teach it.

The rules behave on a sample built to test them. Real exports would show where they need tuning.

Are the weights right?

Label a few hundred real pairs as same or different, then measure precision and recall at each threshold before trusting the defaults.

Test: a labelled pair set
Do reviewers trust the evidence?

Compare decision speed and accuracy with the evidence panel shown and hidden, using the same pairs.

Test: a timed review session
Does it scale past a few thousand rows?

The browser stops at 5,000 rows. The Python tool has no limit, but blocking on name prefixes may need a smarter key.

Test: a 100,000-row export
What about non-US suppliers?

Tax IDs and phones are parsed as US formats. VAT numbers and international phones would need their own rules.

Next: per-country normalisers