Treat product assortment as a data problem, not a catalog spreadsheet

A catalog usually gets messy slowly. Someone adds five near-identical variants because a supplier sent them. Another team keeps a slow seller because it had one good month last year. Search results fill up with products nobody buys, while customers still complain that you do not carry the thing they came for.

That is a product assortment problem, but for developers it often shows up as a data problem: unclear SKU relationships, missing category mappings, stale inventory, and dashboards that count products without explaining whether the catalog is useful.

Start with a catalog model you can query

Before you can reason about assortment, you need a few basic relationships to be explicit. At minimum, separate the sellable SKU from the product family and category taxonomy.

create table categories (
  category_id bigint primary key,
  parent_category_id bigint,
  name text not null
);

create table products (
  product_id bigint primary key,
  parent_sku text not null,
  category_id bigint not null references categories(category_id),
  brand text,
  title text not null,
  status text not null check (status in ('active', 'inactive'))
);

create table skus (
  sku_id bigint primary key,
  product_id bigint not null references products(product_id),
  color text,
  size text,
  price numeric(10,2) not null,
  inventory_count int not null,
  created_at timestamp not null
);

create table order_items (
  order_id bigint,
  sku_id bigint not null references skus(sku_id),
  quantity int not null,
  unit_price numeric(10,2) not null,
  created_at timestamp not null
);

If your catalog cannot answer “which SKUs are variants of the same product?” then depth metrics will lie. If category_id is nullable or overloaded with values like misc, breadth metrics will tell you that you sell in more categories than you really do.

The failure mode is familiar: the dashboard says you have 12,000 active SKUs, but customers see 40 copies of the same black phone case and still cannot find a charger for their laptop.

Measure breadth, depth, and length separately

Product assortment has a few useful dimensions:

• Breadth: how many categories you cover
• Depth: how many variants you carry inside a product family or category
• Length: total active SKUs
• Quality mix: distribution across price bands
• Consistency: how related the categories are to each other

You do not need a complicated model to start. These queries give you a baseline.

-- Catalog length
select count(*) as active_skus
from skus s
join products p on p.product_id = s.product_id
where p.status = 'active';

-- Breadth by active category
select count(distinct p.category_id) as active_categories
from products p
join skus s on s.product_id = p.product_id
where p.status = 'active'
  and s.inventory_count > 0;

-- Depth by category
select
  c.name as category,
  count(distinct p.parent_sku) as product_families,
  count(s.sku_id) as sku_count,
  round(count(s.sku_id)::numeric / nullif(count(distinct p.parent_sku), 0), 2) as avg_skus_per_family
from categories c
join products p on p.category_id = c.category_id
join skus s on s.product_id = p.product_id
where p.status = 'active'
group by c.name
order by avg_skus_per_family desc;

The last query is where problems often appear. A high avg_skus_per_family can be good for apparel, where size and color matter. It can be noise for commodity accessories, where 18 tiny variations split demand and make search results worse.

Find over-assortment with sales and inventory together

Counting SKUs is not enough. You need to know whether variants sell, sit, or cannibalize each other.

This query flags product families where many variants exist, but most have low sales in the last 90 days.

with sku_sales as (
  select
    s.sku_id,
    p.parent_sku,
    p.category_id,
    s.inventory_count,
    coalesce(sum(oi.quantity), 0) as units_90d
  from skus s
  join products p on p.product_id = s.product_id
  left join order_items oi
    on oi.sku_id = s.sku_id
   and oi.created_at >= now() - interval '90 days'
  where p.status = 'active'
  group by s.sku_id, p.parent_sku, p.category_id, s.inventory_count
), family_stats as (
  select
    parent_sku,
    count(*) as variant_count,
    count(*) filter (where units_90d = 0 and inventory_count > 0) as stocked_dead_variants,
    sum(inventory_count) as total_stock
  from sku_sales
  group by parent_sku
)
select *
from family_stats
where variant_count >= 8
  and stocked_dead_variants::numeric / variant_count >= 0.5
order by stocked_dead_variants desc;

Do not automatically delete these products. The query creates a review list. A variant might have zero sales because it launched yesterday, because it is seasonal, or because the product page is broken. The data points to a question, not the answer.

Detect under-assortment from demand signals

Under-assortment is harder because the missing product is not in your catalog. You need demand signals from searches, product listing views, support tickets, or competitor catalogs.

Suppose you track search events like this:

create table search_events (
  search_id bigint primary key,
  user_id bigint,
  query text not null,
  result_count int not null,
  created_at timestamp not null
);

A simple under-assortment signal is repeated searches with poor results.

select
  lower(query) as normalized_query,
  count(*) as searches,
  avg(result_count) as avg_results
from search_events
where created_at >= now() - interval '30 days'
group by lower(query)
having count(*) >= 50
   and avg(result_count) < 3
order by searches desc;

This catches cases like customers searching for “usb c gan charger” while your taxonomy only has a generic “chargers” category. The symptom is not just low conversion. It is demand with no useful landing place.

External catalog and price data can help here, but only if you store it as structured observations with timestamps and source URLs. Wire fits this part of the workflow when you need competitor assortment and pricing data as rows you can join against your own SKU and category tables.

Normalize competitor data before comparing it

Competitor matching fails in boring ways. Titles differ. Sizes use different units. One site says “midnight,” another says “black.” Bundles look cheaper until you compare unit counts.

A practical table for external observations might look like this:

create table competitor_offers (
  observed_at timestamp not null,
  source text not null,
  source_url text not null,
  external_title text not null,
  normalized_brand text,
  normalized_category text,
  normalized_size text,
  price numeric(10,2),
  in_stock boolean,
  match_confidence numeric(4,3),
  matched_sku_id bigint references skus(sku_id)
);

Keep external_title even after normalization. When a match looks wrong, the raw title is usually the fastest way to debug it.

Also store match_confidence. A low-confidence match should not drive repricing or assortment decisions. It should go into a manual review queue.

Turn metrics into an assortment review queue

The useful output is not a dashboard with five charts. It is a list of actions someone can inspect.

create table assortment_review as
select
  'possible_over_assortment' as reason,
  parent_sku as entity_id,
  json_build_object(
    'variant_count', variant_count,
    'stocked_dead_variants', stocked_dead_variants,
    'total_stock', total_stock
  ) as evidence,
  now() as created_at
from family_stats
where variant_count >= 8
  and stocked_dead_variants::numeric / variant_count >= 0.5;

You can add other reasons over time: missing category demand, price gaps, stale inventory, poor variant coverage, or competitor-only products with repeated search demand.

The important part is to keep the evidence with the recommendation. “Remove SKU 123” is not useful. “Review this product family because 9 of 12 stocked variants sold zero units in 90 days” is useful.

A good next step is to run the breadth, depth, and dead-variant queries against one category you know well. If the output surprises the category owner, fix the data model before you build more assortment logic on top of it.