Table of contents :

Making SKUs Searchable the Right Way

100-days-of-weaviate-029

Table of contents :

SKU search looks simple until a store contains values such as TSHIRT-BLK-M, TSHIRT BLK M, shirt_blk_m, and supplier codes with leading zeroes. For a WooCommerce agency, the right implementation separates exact identification from useful discovery instead of treating every SKU as ordinary product text.

Start with the search requirement

There are usually three different SKU use cases:

  • Exact lookup: an operations user enters a complete SKU and expects one product or variation.
  • Prefix or partial lookup: a user enters part of a code, such as TSHIRT-BLK, to find a family of variations.
  • Mixed product search: a shopper or support agent searches for a SKU alongside product names, brands, and descriptions.

These cases should not rely on one field or one ranking strategy. Exact lookup should use a normalized value and an equality filter. Partial and mixed search can use a dedicated text property with an appropriate tokenization strategy and hybrid or BM25 search.

Keep the source SKU and a search SKU

WooCommerce remains the source of truth for the SKU. Store the original value for display and synchronization, then derive a separate normalized value for matching. A practical normalization pipeline commonly:

  1. Converts the value to a consistent case, usually uppercase.
  2. Trims leading and trailing whitespace.
  3. Converts repeated internal whitespace to one space.
  4. Applies a documented policy for separators such as hyphens, underscores, and slashes.

Do not remove meaningful characters without a business rule. For example, AB-01 and AB01 may be different supplier identifiers. If punctuation is removed for a secondary comparison field, preserve the original SKU and the primary normalized value as separate properties.

function normalizeSku(value) {
  return value
    .trim()
    .toUpperCase()
    .replace(/\s+/g, ' ');
}

function compactSku(value) {
  return normalizeSku(value).replace(/[-_\s/]+/g, '');
}

Use the same code, or an equivalent implementation with identical rules, during indexing and querying. A normalization mismatch is more damaging than a poor ranking configuration.

Model product and variation identifiers explicitly

In WooCommerce, the SKU may belong to a parent product or to a variation. Index the entity that should be returned by the workflow. For inventory and order support, that is usually the variation. Useful properties include:

  • sku: the original WooCommerce value.
  • skuNormalized: the canonical value used for exact matching.
  • skuCompact: an optional punctuation-insensitive comparison value.
  • productId and variationId: stable WooCommerce identifiers.
  • productName, attributes, and permalink: contextual fields for results.
  • storeId or another tenant identifier: required when several stores share a collection.

Keep identifiers as strings. Do not convert SKUs to numbers: leading zeroes, letters, and separators are significant.

Use field tokenization for identifier-oriented text

For a dedicated SKU text property, field-level tokenization is often a better default than word tokenization. It keeps the complete SKU as one searchable value, which avoids treating every hyphen-separated component as an independent word. The exact property configuration depends on the Weaviate version and client, but the intent is to define a text field with field tokenization and a separate normalized field for filters.

sku: {
  dataType: ["text"],
  tokenization: "field"
}
skuNormalized: {
  dataType: ["text"],
  tokenization: "field"
}

Tokenization is not an exact-match guarantee. It determines how text is indexed for lexical search. Exact behavior should come from a filter on skuNormalized or skuCompact, with tenant and publication filters applied at the same time.

Make exact lookup a filter

When a user submits a complete SKU, normalize it and query the normalized property with equality. In a Weaviate client, the shape varies by client version, but the logic is consistent:

const normalized = normalizeSku(inputSku);

const result = await products.query.fetchObjects({
  filters: Filter
    .byProperty("storeId").equalTo(storeId)
    .and(Filter.byProperty("skuNormalized").equalTo(normalized))
    .and(Filter.byProperty("status").equalTo("publish")),
  limit: 10
});

Return a clear duplicate warning if more than one active variation has the same normalized SKU. Silently selecting the first result can send an order, stock adjustment, or support agent to the wrong item.

Support partial SKU searches deliberately

Partial matching needs a defined UX. A query for BLK-M might mean “show every SKU containing these components,” “show SKUs beginning with this prefix,” or “find a variation whose code has these characters in sequence.” Standard lexical search does not automatically provide arbitrary substring matching.

For prefix workflows, store a prefix-friendly field or use a search service pattern designed for prefix queries. For arbitrary fragments, create a controlled n-gram or trigram field during ingestion, but consider the storage and index-size cost. Do not enable broad n-gram behavior across the entire product document just to solve SKU lookup.

A useful compromise is to index a small set of SKU terms or n-grams in a dedicated property, then restrict the query to that property. Apply a maximum input length and rate limits if the field is exposed to a public storefront.

Combine SKU search with product search

For a support dashboard or catalog search, hybrid search is useful because it combines lexical matching with vector-based semantic matching. SKU terms should receive strong lexical weight; embeddings are not a reliable replacement for identifier matching.

const response = await products.query.hybrid(input, {
  queryProperties: ["sku", "skuNormalized", "productName", "attributes"],
  alpha: 0.8,
  limit: 20,
  filters: Filter.byProperty("storeId").equalTo(storeId)
});

The exact API differs between Weaviate client generations. The important design choices are the property list, a lexical bias for identifier-heavy queries, and filters that enforce tenant and catalog visibility. For a complete SKU, route to the exact-filter path before running hybrid search.

Rank exact SKU matches above descriptive matches

A product name can match an SKU fragment incidentally, while the SKU field can contain the requested identifier exactly. Use a two-stage strategy:

  1. Normalize the input and perform an exact filter against skuNormalized.
  2. If there is no exact result, run a constrained lexical or hybrid search across SKU and product properties.

This is easier to explain to agency clients, easier to test, and safer for operational tools than attempting to tune one global score for every query type.

Synchronize variations and deletions

Use WooCommerce webhooks or a scheduled reconciliation job to update SKU properties whenever a product or variation changes. A synchronization process should handle SKU edits, parent-product changes, status changes, store moves, and deletion. If a variation is deleted or its SKU is cleared, remove or deactivate the corresponding Weaviate object rather than leaving a stale searchable identifier.

For bulk imports, use deterministic object identifiers based on the store and WooCommerce entity ID. This makes retries idempotent and prevents duplicate objects when an import is interrupted.

Test the cases that cause production failures

  • Leading zeroes, such as 00127.
  • Case differences, such as abc-123 and ABC-123.
  • Whitespace around or inside the value.
  • Hyphens, underscores, slashes, and supplier-specific punctuation.
  • Parent products and variations with overlapping identifiers.
  • Duplicate SKUs within one store and across tenants.
  • Archived, private, or out-of-stock products that should not be returned.
  • Short fragments that would produce excessive or ambiguous matches.

Measure exact-match accuracy separately from partial-search relevance. A SKU search implementation is successful when an exact identifier resolves deterministically, partial searches behave according to a documented rule, and ordinary product searches are not polluted by identifier-only fields.

Trending posts
You might also like