Token sort matching: reconciling websites with a database

Every week, websites add listings, drop listings and quietly change the details on the ones they keep. Our database has to follow. Checking that by eye, row by row, was slow, and it was the kind of work where people miss things by Friday afternoon.

The problem: the same thing, written differently

Intake Watcher tracks a listing's name, link, and intake month and year, on the website and in the database. In theory you just join the two lists. In practice the website says "MSc Data Science (Full Time)" and the database says "Data Science MSc - Full Time". Same course. A plain string comparison calls them different, and you get a false "new" and a false "deleted" from one listing.

Matching names was the biggest challenge in the whole project. Everything else depended on getting it right.

Token sort, the way I built it

Most name differences in our data were about order and punctuation, not spelling. So the fix is to make the order and punctuation disappear before comparing:

1. Lower-case everything.
2. Strip special characters like brackets and dashes.
3. Split the name into words.
4. Sort the words.
5. Join them back together, without spaces, into a key.

"MSc Data Science (Full Time)" and "Data Science MSc - Full Time" both become datafullmscsciencetime. Same key, so they match. The animation at the top of this page walks through exactly that.

If you've used RapidFuzz or fuzzywuzzy, this is the idea behind token_sort_ratio. The difference is what happens next. Those libraries give you a similarity score, and you pick a cut-off. I compare the keys exactly. There's no threshold to tune and no "0.87, close enough". Two listings either produce the same key or they don't. I explain why that was the right call for this data, and when it wouldn't be, in token sort matching, explained.

Strictest key first, then fall back

A name alone isn't enough. The same course can run in September and January, at two campuses, full-time and part-time. So the match key is the sorted name plus the intake month and year. Then, when both sides have them, it adds campus, study mode and duration.

The matcher tries the most specific combination first and works down. A field only joins the key if both the website and the database have it. That way a listing with full details never gets matched on a vaguer key while a precise match is available. It also checks two alias name fields in the database, because some listings have been renamed over the years and the old name still shows up on the web.

Four outcomes

Every listing ends up in one of four buckets. New means it's on the website but not in the database. Deleted means it's in the database but gone from the website. Matched means it's in both. Modified means it's in both but a data point, like the fee or the campus, changed.

The output goes three ways: an Excel file for whoever does the update, a summary in MongoDB for the record, and a Slack message so the team knows a run finished and roughly what it found.

How it runs

Intake Watcher doesn't run on its own schedule. A small companion service, Intake Scheduler, keeps a next-run date and a repeat interval for every source. When a source is due, it triggers the scrape, checks its status every few seconds until it finishes, then hands the fresh data to Intake Watcher. Slack hears about each step, pass or fail.

What I'd tell you before you copy this

• Look at your mismatches before you pick a method. Ours were order and punctuation. If yours are typos, token sort alone won't save you.
• Exact keys are easier to defend. When someone asks why two listings matched, I can show them the key. With a threshold, the honest answer is "the score was above 0.85", and that's a harder conversation.
• Abbreviations still need help. "MSc" and "Master of Science" won't sort into the same key. That's what the alias fields are for.

Stack

Python, Pandas, Flask, MongoDB, Slack API. Excel output for the review step.

My role: I designed the matching logic and built it, including the token sort and key fallback. Sample listings on this page are made up.