Product Sorting Development for E-Commerce

Product Sorting Development for E-Commerce

Sorting determines what users see first. Default sorting by added date — not a strategy. Correct default sorting increases conversion: high-rating products, good stock, suitable price appear first. Each sorting option — different ORDER BY query, each requires proper index.

Standard Options

Default sort — usually "Popularity" or custom "Store Rating" (merchandiser manual sort).

Weighted Rating

Naive sort by average rating wrong: product with 1 review at 5 stars ranks above product with 200 at 4.8. Use Bayesian average or Wilson score:

-- Bayesian: (C * m + sum_ratings) / (C + reviews_count)
-- C = prior weight (typically average review count by catalog)
-- m = prior mean (expected rating without data, usually 3.0–3.5)

UPDATE products SET
  bayesian_rating = (50 * 3.5 + rating_sum) / (50 + reviews_count)
WHERE id = :id;

Computed field updated on each review. Index bayesian_rating for fast sort.

Sales and Popularity Counter

sales_count — cumulative all sales. Problem: old popular product always ranks above new actively selling.

Solution — time-decayed popularity score:

-- Update daily via cron
UPDATE products SET
  popularity_score = (
    SELECT SUM(quantity * EXP(-0.1 * EXTRACT(DAY FROM NOW() - o.created_at)))
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    WHERE oi.product_id = products.id
      AND o.created_at >= NOW() - INTERVAL '90 days'
  )

Coefficient 0.1 tunable: higher — more depreciate old sales, lower — longer memory. For fast-changing assortment (fashion, seasonal) — higher. Stable categories (tools, appliances) — lower.

Manual Sorting (Merchandising)

Manager wants control: promote new, sponsored, old stock. Add sort_order — manual numeric field.

Interface: drag-and-drop product list in admin. Technically — save array product_id ordered or sort_order: integer per product.

Hybrid: first N positions — manual, rest — algorithmic.

ORDER BY
  CASE WHEN sort_order IS NOT NULL THEN 0 ELSE 1 END,
  sort_order ASC NULLS LAST,
  popularity_score DESC

Elasticsearch Sorting

With Elasticsearch sort in sort param:

{
  "sort": [
    { "popularity_score": { "order": "desc" } },
    { "bayesian_rating": { "order": "desc" } },
    { "_score": { "order": "desc" } }
  ]
}

Manual sort: pinned_position field with null for unpinned. ES has pinned query — elevates specific IDs at beginning without breaking relevance of rest.

Personalized Sorting

Advanced — show each user different order based on history. User often buys Apple — Apple products rank higher. User looked at specific price range — consider this.

Via custom boost factors in Elasticsearch:

{
  "query": {
    "function_score": {
      "query": { "term": { "category_id": 14 } },
      "functions": [
        {
          "filter": { "term": { "brand": "apple" } },
          "weight": 2.0  // personal boost for this user
        }
      ]
    }
  }
}

Boost factors computed offline (batch from history) cached in Redis per user_id.

UI Sort Component

Standard select dropdown. On mobile — bottom sheet. Current sort in URL (?sort=price_asc) synced with component.

On sort change — API request without page reload, scroll up to first product. Skeleton placeholders while updating.

Indexes for Performance

-- Price sort
CREATE INDEX ON products (category_id, price ASC) WHERE status = 'active';
CREATE INDEX ON products (category_id, price DESC) WHERE status = 'active';

-- Date sort
CREATE INDEX ON products (category_id, created_at DESC) WHERE status = 'active';

-- Rating sort
CREATE INDEX ON products (category_id, bayesian_rating DESC) WHERE status = 'active';

-- Manual + popularity hybrid
CREATE INDEX ON products (category_id, sort_order ASC NULLS LAST, popularity_score DESC);

Each sort option — potential separate index. With 8–10 sort options significantly affects index size and INSERT/UPDATE speed. Right solution: use ES for complex sorts, leave simple ORDER BY in PostgreSQL.

Timeline

• Basic sorts (price, date, rating, select): 2–4 days
• Weighted rating and time-decayed popularity: 1 week
• Manual merchandising with drag-and-drop: +1 week
• Personalization from user history: 2–3 weeks (needs history and offline computation)