How a product becomes a code, and a code becomes a match
Two mappings carry the whole product. A spreadsheet row becomes a brand family and an HS6 code; a customs declaration becomes an HS6 code; the two meet on that six-digit string. Every claim of the form this account buys what you sell is that string matching — nothing more. Both mappings lose information on the way, and this page is where that loss is stated.
Two mappings, one join key
What has to be true for 'this account buys what you sell' to mean anything?
The brand family labels a row but never joins on one — it plays no part in matching. Everything the product claims rests on six digits derived twice, from two unrelated sources, by one rule.
Product → brand family
An exact string match. The Taxonomy sheet lists each family's products in a newline-separated cell; every Product Name* in the Client sheet is looked up in that list. All 2,512 match, none are duplicated, none are left over.
A perfect exact-match rate means Lanxess supplied the two sheets as a matched pair. There is no fuzzy fallback and no error path: change one character in either sheet and that product keeps its HS6, silently gets no family, and disappears from every family page while still counting toward code-level totals. Nothing would report it. If a future catalog arrives from a different process, check the unmatched count before trusting a family number.
2 pairs of families differ only by casing or trademark symbol — KALAMA® / Kalama®, KLARIX™ / Klarix®. They are kept separate rather than merged, because Lanxess listed them separately and collapsing a customer's own taxonomy is not our call — each page flags its twin. 25 of 234 families vanish entirely: every product they contain lacks a usable HS code, so nothing is left to match on.
Product → HS6
The HS Code (MARC) column is not uniform: 8 to 12 characters, some with a trailing letter (39092029000T), some dotted (3808.94.20.29). 128 of 2,512 products have no code at all and are excluded everywhere — they cannot be checked against any account.
Declaration → HS6
The customs field carries the code and its description: "90200090 - OTHER BREATHING APPLIANCES…". Both sides now use one rule (pipeline/hs_codes.py): take the leading code token, strip separators inside it, keep the first six digits. It cannot be “strip every non-digit” — that would absorb every digit in the description.
The pipeline took the leading digit run; the Lanxess catalog stripped all non-digits. On 3808.94.20.29 that is 3808 against 380894 — two values that never match, with no error raised. They agreed on all 10,889 observed customs codes only because none contained an internal separator; the spreadsheet did. Unified, verified as producing identical output on every code on both sides.
| Source code length | Distinct codes | Rows | Reading |
|---|---|---|---|
| 2 digits | 39 | 1,660 | Chapter or heading only — truncates to an hs6 that matches nothing |
| 4 digits | 238 | 9,610 | Chapter or heading only — truncates to an hs6 that matches nothing |
| 6 digits | 1,036 | 45,462 | HS6 exactly, internationally comparable |
| 8 digits | 3,082 | 239,606 | National extension; digits beyond six are country-specific |
| 10 digits | 4,519 | 299,715 | National extension; digits beyond six are country-specific |
| 11 digits | 1,760 | 49,179 | National extension; digits beyond six are country-specific |
| 12 digits | 215 | 2,689 | National extension; digits beyond six are country-specific |
Digits seven onward are set by the importing country, so two countries' 8-digit codes are not comparable even when they describe the same goods. Six is the deepest level that means the same thing everywhere, which is why it is the join — not a convenience.
277 codes across 11,270 rows yield fewer than six digits. They currently reach the payloads as two- and four-character “hs6” values that can never match a portfolio code. Dropping them is the right end state, but it moves figures app-wide, so it is a change to make deliberately rather than as a side effect — for now nonstandard_hs marks them.
6 codes (970 rows) claim chapter 00, which does not exist — zero-padded values like 0000320890, which almost certainly mean 320890. The padding is not stripped: “almost certainly” is a guess, and this pipeline flags rather than guesses. Chapters 98 and 99 are not flagged — both are legitimate national-use chapters and 36 codes (1,242 rows) use them here.
Some declaration lines name several codes at once — 320810 - PAINTS…, 320890 - PAINTS…, 320820 - PAINTS…. The first code takes the row's entire value, because the declaration states no split and inventing one would be a fabrication. multi_hs_code now marks these so the choice is countable rather than invisible.
What six digits cannot tell you
And from the other direction: the largest Lanxess fan-out is 391400 (172), 380899 (164), 390810 (156), 282110 (133), 390799 (110).
81.6% of Lanxess products share a code with ten or more siblings, and up to 92 national codes sit behind a single HS6 on the customs side. A match says the account imports something classified under this code. It cannot say which product, and no amount of presentation makes it able to. Every product page therefore labels its figure inherited from the code rather than attributed to the SKU.
The second mapping: CAS numbers in declaration text
HS6 is the join that carries the money. A CAS number is a second, far narrower join that carries identity — and it is the only route in this product from a declaration to a named chemical. It reaches a minority of rows and is worth having anyway, because on those rows the answer stops being a tariff bucket.
| Coverage in the catalog | 279 valid CAS numbers over 760 HS-coded products — about a third. The other two thirds can never be reached this way, which is why CAS supplements HS6 rather than replacing it. |
| Coverage in customs text | 5.43% of declarations contain a CAS-shaped token; 2,118 distinct values survive check-digit validation, 213 do not. |
| The check digit earns its place | 9003-55-9 appears 19 times and does not exist — it is a typo for 9003-55-8, styrene-butadiene copolymer. Shape alone would have accepted it. |
| Confirmed | 467 declarations, $6.75M. The CAS matches and the code agrees with one Lanxess assigns it, so the shipment is the chemical. |
| Ingredient | 5,503 declarations, $60.31M. The code disagrees — Lanxess chemistry inside someone else's product. Never counted as displaceable spend. |
Per-CAS values overlap: a declaration naming two Lanxess CAS numbers appears under both, so this column must never be summed — the same rule as brand-family reach. Buyers are resolved through the same clustering the opportunity engine uses; matching on raw names finds 62 rows at a covered account where the resolver finds 197.
The third mapping: a company name becomes an account
HS6 says what was bought and CAS says which chemical. Neither says who. That is a separate join, over free-text company names typed by customs brokers in a dozen countries, and it is the one that has gone wrong most expensively.
Three levels, each narrower than the last, all in pipeline/entity_resolution.py:
| Level | What it does | Used for |
|---|---|---|
entity_key | One normalized spelling of one name | Deduplicating a single company’s variants |
family_key | Unions names that fuzzy-match above threshold, via union-find over rapidfuzz token-sort ratio | The account you see on screen |
group_key | Reduces a family to its distinctive root token | Intra-group detection only — never competitor identity |
The asymmetry between the last two is deliberate and worth understanding. Over-merging at group_key excludes trade and understates opportunity, which is safe. Over-merging competitor identity would overstate an incumbent’s share, which is not. So the two are allowed to disagree, and a company can be two accounts while still being one group.
Both levels reduce a name by stripping legal forms — LTD, GMBH, SDN, BHD and forty others. The list did not contain CIA, the Spanish and Portuguese abbreviation for Compañía. So CIA SHERWIN WILLIAMS SA DE CV reduced to the root CIA rather than SHERWIN, and two things followed: Sherwin-Williams appeared as two separate accounts, and the intra-group check could not see that Sherwin Mexico buying from Sherwin US is not displaceable business. $59.6M across 79 rows — the largest single line $12.0M on one HS6 — was presented as an opportunity to sell against a company’s own parent.
The failure mode was not that nobody understood the risk. The docstring on group_key predicts it exactly, in the abstract, about Yokohama. A comment cannot fail a build, which is why scripts/check_payloads.py now asserts it and scripts/check_selftest.py proves the assert still fires.
Name similarity cannot infer ownership. NIPSEA is Nippon Paint’s South-East Asia arm and Nuplex was acquired by Allnex; no token rule reaches either. Those need a human decision, recorded in knowledge_base/entity_aliases.csv, where every row sharing a corporate_family_id collapses onto one canonical name.
That approval rate is the honest state of this join: almost everything is resolved by fuzzy name matching, and only a handful of relationships have been confirmed by a person. It works because the names in this dataset are mostly clean. It is not a general guarantee, and the CIA defect is what the gap looks like when it bites.
What else is normalized before any of this runs
Every join above assumes the columns hold what their headers claim. Four normalizations earn that assumption, and one of them is doing far more work than its name suggests.
| Step | Coverage | What it reconciles |
|---|---|---|
| Country → ISO3 | 99.9% | country_aliases.csv, plus a master catalog for geocoding |
| Unit → canonical | 97.8% | unit_aliases.csv; carries the conversion factor to a base unit |
| Date → ISO | ~100% | Several formats tried in order — day-first and ISO both appear, sometimes in the same file |
| Schema shift repair | 19.1% | Unit and Value arrive transposed and are swapped back |
The last one deserves its emphasis. Four inputs — 3M, Yokohama, Arkema and Owens_Corning, all named *_2025_2024 — are two exports concatenated, and the second block uses a layout with Unit and Value the other way round. It is a clean split at a row boundary, 123,212 rows, and only those two columns move: date, buyer, supplier, HS code, quantity and direction stay correctly aligned in both blocks. Verified by reading across the boundary in 3M_2025_2024.csv at rows 60,000 and 60,002. Three Arkema rows escaped the repair because their Value was blank rather than a unit name, stranding $5,299 in the wrong column; the rule was widened and a check now asserts that no transposed row survives.
What an HS description actually is
No official tariff nomenclature ships with this repository, so all 10,889 observed codes carry classification_source: observed_source_code and status: source_only. A description shown anywhere in the product is what the declaring party wrote on the paperwork — not what the tariff says the code means. Adding an approved official code table to the knowledge base promotes them to real matches; until then, treat descriptions as evidence of intent rather than classification.
Worth stating plainly, because “description matching” sounds like something that runs and sometimes fails: it has never matched a single row. hs_official_match is true for 0 of 644,728 declarations, and description_match_status reads no_official_source on 99.9% of them — the remainder are rows with no code at all. There is no tariff schedule in the repository to match against, so the comparison is defined, wired up, and inert.
One consequence is worth fixing before anyone relies on it. The HS review queue is written by filtering on NOT hs_official_match, which is every row — so hs_review_queue.csv holds 672,439 rows, more than the corpus itself. A review queue containing everything flags nothing, and it has been that way since it was written.
Portfolio matching
A pasted portfolio goes through parseCodeList (lib/portfolio.ts), which truncates 8- and 10-digit national codes to six — the correct reading of a tariff line, not a guess — and reports anything unrecognised back instead of dropping it silently.
Of the 206 Lanxess codes, 178 appear somewhere in observed customs data. That is a larger number than the count of codes with demand shown on /lanxess, and both are correct: this one asks whether the code was ever seen at all, that one asks whether a covered account with usable coverage imported it. Neither is a market size.