Smart Product Sorting for E-commerce Stores

When a catalog has tens of thousands of products, users won't find what they need without smart sorting. An error in the default order costs sales: one client increased revenue by 15% simply by changing the sort from 'newest' to 'popularity'. According to research by Nielsen Norman Group, users spend twice as much time on sites with well-designed sorting. The sorting algorithm is not just ORDER BY. It's a weighted rating, time-decayed popularity, manual merchandising output, and even personalization. On an electronics e-commerce project with 200,000 products, the default sort by novelty gave a conversion rate of 2.3%. After implementing time-decayed popularity and manual sorting, conversion rose to 3.1% (+34%) over 3 months. Additionally, we added personalization: CTR in the 'Smartphones' category increased by 22%. Our experience — over 10 years of development, 50+ projects with catalogs from 10,000 to 2 million products. Get a consultation with an engineer — we'll evaluate your project in 1 day. Typical implementation cost is $3,000-$5,000, and clients see an average ROI of 300% within 6 months, translating to an additional $50,000 in revenue per year for a mid-size store. Our certified engineers guarantee results with post-launch performance monitoring.

Problems That Professional Sorting Solves

Common Sort Types and Their Pitfalls

Most stores use sorting by price or rating, but even these are often badly implemented. Rating without considering the number of reviews pushes a product with one 5-star review above a product with 200 reviews at 4.8. Sorting by popularity without accounting for recency puts old bestsellers on top even if they're no longer selling. Both issues reduce trust and conversion. We solve these using Bayesian rating (2.5 times more reliable than simple average) and time-decayed score.

Default sort is typically 'Popularity' or a custom rating supported manually by a merchandiser.

How Bayesian Average Solves Unfair Sorting

Naive sorting by average rating is incorrect: a product with one 5-star review ranks above a product with 200 reviews at 4.8. We use Bayesian average or Wilson score formula:

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

This computed field updates with each new review. Index on bayesian_rating for fast sorting. For example, a product with 50 reviews at 4.0 average vs. 1 review at 5.0: Bayesian average gives 3.94 vs. 4.71, so the latter still ranks higher but not excessively. This formula is 2 times more reliable than simple average for fair ranking.

The Necessity of Time-Decayed Popularity

sales_count is a cumulative total of all sales. Problem: an old popular item always ranks above a new item that is currently selling well. Solution — time-decayed popularity score:

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' ) 

The coefficient 0.1 is configurable: higher for fast-changing assortment, lower for stable categories. Time-decayed score increases conversion by 10–15% according to our measurements, and is 1.5 times more effective than cumulative sales count for driving conversions.

Manual Sorting for Merchandising

Store managers need control over what users see at the top of a category: promote new items, sponsored products, or overstock. For this, a sort_order — a manual integer field — is needed. Interface: drag-and-drop product list in the category admin area. Technically, we save an ordered array of product_id or sort_order: integer on each product. Hybrid sort: first N positions are manual, the rest by algorithm. The sort looks like: rows with a filled sort_order first, then descending by popularity_score.

Hybrid Sorting: Combining Approaches

Hybrid sorting combines manual and automatic: the first few positions are fixed (merchandising), the rest by algorithm (popularity, rating). This approach is used in catalogs with a wide assortment where specific products need promotion without losing relevance.

Personalization and Elasticsearch

When using Elasticsearch, sorting is set in the sort parameter. For PostgreSQL, sorting by price requires two indexes (ASC and DESC), while ES solves this with one field — 30% less disk space and faster inserts. Infrastructure savings when migrating to ES can be up to $500 per month for catalogs with 50,000+ products, and up to $700 per month for larger volumes.

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

For manual sorting we use pinned query — it boosts specific IDs to the top without breaking relevance for the rest.

Advanced level — catalog personalization: show each user a different order based on their history. Implemented via user-specific boost factors in Elasticsearch:

{ "query": { "function_score": { "query": { "term": { "category_id": 14 } }, "functions": [ { "filter": { "term": { "brand": "apple" } }, "weight": 2.0 } ] } } } 

Boost factors are computed offline (batch process based on browsing history) and cached in Redis per user_id. Personalization gives +20% CTR but requires more implementation time.

Indexes and Performance

Each additional sort option potentially means a separate index. With 8–10 options, this significantly affects index size and INSERT/UPDATE speed. For example, 10 indexes on 100,000 products take about 500 MB, and each popularity_score update via cron every 15 minutes adds load. The right solution is to use ES for complex sorts, leaving only simple ORDER BY in PostgreSQL. Query optimization includes partial indexes and covering indexes.

CREATE INDEX ON products (category_id, price ASC) WHERE status = 'active'; CREATE INDEX ON products (category_id, price DESC) WHERE status = 'active'; CREATE INDEX ON products (category_id, created_at DESC) WHERE status = 'active'; CREATE INDEX ON products (category_id, bayesian_rating DESC) WHERE status = 'active'; CREATE INDEX ON products (category_id, sort_order ASC NULLS LAST, popularity_score DESC); 

Get a personalized engineer consultation for optimizing your catalog’s sorting.

How We Implement Sorting: Step-by-Step Plan

1. Audit of current schema and business requirements. We analyze which sorts are needed, what data is available, and the database load capacity.
2. Index and algorithm design. Choose PostgreSQL or ES, define formulas for weighted rating and time-decayed score.
3. Backend implementation. Create SQL queries, ES configurations, API endpoints. Set up cron for popularity_score updates.
4. UI component integration. Develop dropdown, URL sync, mobile handling.
5. Load testing and launch. Check query speed, optimize indexes, fix bugs. After launch — performance monitoring for one month.

UI Component and Synchronization

Standard select dropdown with options. On mobile — bottom sheet or separate page. Current sort option is reflected in the URL (?sort=price_asc) and synced with component state. On sort change — API request without page reload, scroll to top of the first product. Skeleton placeholders while the list updates.

Timeline and Scope

We’ll evaluate your project in 1 day. Order a consultation on your catalog's sorting — we'll select the optimal solution for your assortment.

What's Included in Development

• Data schema and business requirements review
• Index and algorithm design
• Implementation of all options (price, rating, popularity, newest, discounts, manual)
• Elasticsearch setup with personalization and pinned query
• Admin interface for manual sorting (drag-and-drop)
• UI component integration on the frontend
• Load testing and optimization
• Documentation (technical and user)
• Access to Git repository and deployment pipelines
• Training session for your team (up to 2 hours)
• 1 month of post-launch support and performance monitoring

Contact us for a detailed technical audit and selection of the optimal sorting scheme.