Order by Biggest Discount in evitaDB
We're excited to introduce a powerful new feature in evitaDB — the priceDiscount ordering constraint. This feature
allows you to sort your product listings based on the discount amount, helping you highlight the best deals to your
customers. In this post, we'll explore how to use this feature effectively, with real-world examples, practical query
snippets, and insights into different pricing strategies, including handling products with variants and product sets.
How the Discount is Calculated
- Selling Price: The first valid price found in the priceInPriceLists constraint, matching the priceValidIn date and currency specified in priceInCurrency. Prices are considered in the order of the price lists provided.
- Reference Price: The first valid price found in the price lists specified in the priceDiscount constraint, matching the same date and currency.
- Discount: Calculated as Reference Price - Selling Price.
If a price in a given price list is not available or not valid at the specified time, it is skipped. The algorithm automatically continues to the next price list in the priority order until it finds a valid price.
- LOWEST_PRICE Strategy: For products with variants, the discount is calculated based on the variant selected for sale. It's usually the one with the lowest price, or if the priceBetween filter is used, the one with the lowest price that still meets the selected price range. The reference price must be from the same variant in different price lists.
- SUM Strategy: For product sets, the selling price is the sum of the selling prices of all components. The reference price is calculated by summing the reference prices of the same components, excluding any components that didn't have a selling price to maintain consistency.
Implementing priceDiscount in Your E-Commerce Solution
Scenario: Highlighting Top Discounts During a Flash Sale
Suppose you're running a flash sale and want to display products with the highest discounts. You have multiple price lists:
- "flash-sale": Contains special prices for the flash sale.
- "standard": Contains regular prices.
- "msrp": Contains manufacturer suggested retail prices.
You want to show products sorted by the highest discount, calculated between the selling price from the "flash-sale" price list and the reference price from the "msrp" price list.
Crafting the Query
This query filters products that have valid prices in the "flash-sale" price list, priced in USD, and valid at the specified time. It orders them by the discount amount compared to the "msrp" price list. If products lack price in the "msrp" or "flash-sale" price lists, their price from "basic" price list is used instead.
How the Algorithm Selects Prices
- Selling Price: The algorithm searches the "flash-sale" price list for a valid price at 2023-11-07T12:00:00-05:00. If not found, it tries to find price in "basic" price list and if that is not found, it skips the product entirely.
- Reference Price: It looks for a valid price in the "msrp" price list with fallback to "basic" price at the same time.
Detailed Example: Electronics Store Flash Sale
Imagine you're managing an online electronics store preparing for a 24-hour flash sale. Your products have prices in different price lists, some with time-limited validity.
Product Data valid on 7 Nov 2023
| Product | MSRP Price | Basic Price | Flash Sale Price | Flash Sale Price Validity |
|---|---|---|---|---|
| 4K Smart TV | $1,000 | $950 | $800 | All day |
| Gaming Laptop | $2,000 | $1,950 | $1,600 | All day |
| Bluetooth Speaker | $100 | $95 | (Not available) |
| Product | Variant | MSRP Price | Basic Price | Flash Sale Price | Flash Sale Price Validity |
|---|---|---|---|---|---|
| Noise-Canceling Headphones | Black | $200 | $190 | $150 | Until 13:00 |
| Silver | $200 | $180 | (Not available) | ||
| Gold | $200 | $170 | (Not available) |
| Product | Component | MSRP Price | Basic Price | Flash Sale Price | Flash Sale Price Validity |
|---|---|---|---|---|---|
| Home Theater Bundle | Soundbar | $500 | $450 | $400 | Until 13:00 |
| Subwoofer | $300 | $280 | (Not available) | ||
| Rear Speakers | $200 | $190 | $150 | All day |
Query at 12:00 AM
| Product | Selling Price | Reference Price | Discount |
|---|---|---|---|
| 4K Smart TV | $800 (flash-sale) | $1,000 (msrp) | $200 |
| Gaming Laptop | $1,600 (flash-sale) | $2,000 (msrp) | $400 |
| Bluetooth Speaker | $95 (basic) | $100 (msrp) | $5 |
| Noise-Canceling Headphones | $150 (flash-sale, Black variant) | $200 (msrp) | $50 |
| Home Theater Bundle | $830 (components) | $1,000 (components) | $170 |
Discount can never be negative, so if it happens that the selling price is higher than the reference price, the discount is perceived as $0.
- Gaming Laptop ($400 discount)
- 4K Smart TV ($200 discount)
- Home Theater Bundle ($170 discount)
- Noise-Canceling Headphones ($10 discount)
- Bluetooth Speaker ($5 discount)
The validity of prices affects both inclusion in results and discount amounts.
Query at 2:00 PM
- Noise-Canceling Headphones:
- Selling Price: $170 (basic) - now the Gold variant is the one with the lowest price
- Reference Price: $200 (msrp)
- Discount: $200 - $170 = $30
- Home Theater Bundle:
- Selling Price: $450 (Soundbar, basic - flash sale price is no longer valid) + $280 (Subwoofer, basic) + $150 (Rear Speakers, flash-sale) = $880
- Reference Price: Remains $1,000 (msrp)
- Discount: $1,000 - $880 = $120
What if there is no MSRP and Basic Price for a component part?
When calculating the reference price for a product set, the algorithm excludes components that don't have a selling price. But what if a reference price for a component is missing? In such cases, the algorithm uses the selling price as the component reference price to maintain consistency.
Conclusion
Implementing this feature is straightforward with evitaDB's flexible query language. Understanding how selling and reference prices are determined, considering time validity, and utilizing appropriate pricing strategies—including handling products with variants and product sets—allows you to tailor this feature to your business needs.