evitaDB - Fast e-commerce database
logo
page-background

Price for sale computation algorithm

This chapter details the algorithm behind sale price calculation, exploring how it incorporates factors such as currency selection, applicable discounts, and price list selection based on user context. We'll walk through the logic with code snippets and real-world scenarios, providing a clear understanding of how the algorithm functions to accurately compute sale prices in a dynamic e-commerce environment.

Terms used in this document

ERP

Enterprise Resource Planning (ERP) is a type of software system that helps organizations automate and manage core business processes for optimal performance. ERP software coordinates the flow of data between an organization's business processes, providing a single source of truth and streamlining operations across the enterprise. It's capable of linking a company's financial, supply chain, operations, trading, reporting, manufacturing, and human resources activities on one platform.

product
A product is an entity that represents the item sold at an e-commerce store. The products represent the very core of each e-commerce application.
product with variants
A product with a variant is a "virtual product" that cannot be bought directly. A customer must choose one of its variants instead. Products with variants are very often seen in e-commerce fashion stores where clothes come in various sizes and colors. A single product can have dozens combinations of size and color. If each combination represented standard product, a product listing in a category and other places would become unusable. In this situation, products with variants become very handy. This "virtual product" can be listed instead of variants and a variant selection is performed at the time of placing the goods into the cart. Let's have an example: We have a T-Shirt with a unicorn picture on it. The T-Shirt is produced in different sizes and colors - namely:

– size: S, M, L, XL, XXL
– color: blue, pink, violet

That represents 15 possible combinations (variants). Because we only want a single unicorn T-Shirt in our listings, we create a product with variants and enclose all variant combinations to this virtual product.
product set
A product set is a product that consists of several sub products, but is purchased as a whole. A real life example of such product set is a drawer - it consists of the body, the door and handles. A customer could even choose which type of doors or handles they want in the set - but there always be some defaults.
When displaying and filtering by a product set in the listings on the e-commerce site, we need some price assigned for it but there may be a none exact price assigned to the set and the e-commerce owner expects that price would be computed as an aggregation of the prices of sub-products. This behaviour is supported by setting proper PriceInnerEntityReferenceHandling.
The primary source of pricing information is typically a company's ERPToggle Term Reference system. There is a wide variety of such ERPToggle Term Reference systems, often specific to the country in which the e-commerce business operates. These systems have their own ways of modeling and calculating prices, and B2B pricing strategies are sometimes very "creative".

The price calculation logic in evitaDB is designed in a very simple way that supports common pricing mechanisms and allows adaptation to even uncommon ones.

The structure of the individual price is defined by the interface.
The query allows you to search for prices:
Processing such a query will result in a list of prices, where multiple prices are assigned to a single product. Prices belonging to the same product will be sorted according to the order of the price lists in the (priceInPriceLists) constraint used in the query. A sorted price list is iterated on, taking only the first price for each product and skipping the others until a price is found for the next product. This means that at any given moment, there could be exactly one price valid for the combination of price list and currency. If this constraint were not enforced, the engine would not be able to select an appropriate selling price for the product.

To avoid price ambiguity, evitaDB allows an entity only one valid price at any moment per price list, currency and inner record. The entity builders reject a price whose validity overlaps the validity of another price of the same price list, currency and inner record - a price without validity overlaps every other one - and the server rejects it too when the entity is stored, whatever way the change arrives. For an entity without inner record handling, the builders and the server also reject overlapping prices of one price list and currency that differ only in their inner record ids, because the inner record id has no meaning for such an entity. The builders check the prices when they produce the entity mutation, not when a price is set, so a price may still be moved or removed and the inner record handling may still be set after the prices. Like the server, they check only the prices the change adds or moves - a change of the inner record handling moves all of them - so an entity that already holds two such prices stays editable as long as neither of them is moved.

An entity may still have several prices in one price list with non-overlapping validity spans. When the evitaDB query is missing the priceValidIn constraint that defines the exact time for the price evaluation, all of them are candidates, and the one with the lowest price without tax is selected (on a tie, the one with the lower price id), whatever price type the query uses. That's why you should always specify the correct date and time for a correct price for the sales resolution (unless you know that there are no time-dependent prices in the database).
A price list named more than once in the priceInPriceLists constraint keeps the priority of its first position.
The amounts are compared in the price type of the query (with tax by default) wherever the price for sale is chosen by amount - the lowest price of product variants and the bounds of the price range. The price for sale and the price range that a fetched entity reports are computed in the same price type as the one the query filters and sorts by.
You need to think carefully about how to model price lists and priorities in evitaDB. One of the more intuitive approaches is to convert price lists from ERPToggle Term Reference (in a 1:1 ratio) to evitaDB. That's usually fine - but ERPToggle Term Reference systems often use prices that are calculated on the fly according to some defined rules. This isn't possible in evitaDB and all prices have to be "pre-calculated" in a static form. This is necessary to be able to search prices quickly. You can create some so-called "virtual price lists", which mimic the ERPToggle Term Reference rule and keep all calculated prices in them.
You also need to pay attention to combination explosion (sometimes called Cartesian product). Some business rules can result in such a large number of possible price combinations that it is impossible to precompute and store them in memory. Let's look at an example:
Company XYZ has 1 million customers, 1 million products, and each customer may have a unique discount for the products. A naive approach to this problem is to compute 1 billion prices (i.e. all possible combinations). A smarter approach is to look at the discount layout. We may find that there are only a few types of discounts: 1%, 2.5%, 5%, 10%. We don't model our price lists by user, but by discount value - so we'd need 4 million pre-calculated prices.

Model examples for standard cases

Let's have the following products:

ProductBaseline pricePrice list APrice list BPrice list C
Honor 10€10000€9000
(valid 1.1.2020 00:00:000 - 31.1.2020 23:59:59)
€7500
HUAWEI 20 Pro€12000€14000€8500
iPhone Xs Max€21000€23000€19000
(valid 1.1.2020 01:00:000 - 31.1.2020 22:59:59)
You can store your priceList validity information in a separate evitaDB entity. Then you could emulate the price list validity on the client side before the price list code array is passed to the priceInPriceLists constraint that is used to retrieve products.

Client logic could work as follows: list all user price lists whose validity overlaps an interval from now to +1 hour, ordered by priority/order attribute, cache for one hour (this could be handled by a single EvitaDB query).

When the priceInPriceLists constraint is about to be used for listing products, filter all locally cached price lists by the validity attribute using the isValidFor(OffsetDateTime) method for the current moment and use them as arguments in the same order as they were fetched to the cache.

These results are expected for the following queries:

First query

  • price lists: A, Baseline (argument order controls price list priority)
  • valid in: 1.11.2020 13:00:00
Expected result:
ProductPrice for sale
Honor 10€10000
HUAWEI 20 Pro€14000
iPhone Xs Max€23000
The A price list has higher priority, and as such, prices from this price list should be used when available. The Honor 10 doesn't have a price in this has no price in this price list, so the Baseline price will be used for it.

Second query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 1.11.2020 13:00:00
Expected result:
ProductPrice for sale
Honor 10€10000
HUAWEI 20 Pro€14000
iPhone Xs Max€23000
In this case, price list B is the most prioritized price list from the query, but the time validity of its prices doesn't match the constraints, so none of them will be part of the evaluation process. Price list C has the lowest priority, so if there is any price in price list Baseline or price list A, it would not be used. That's why the result is the same as in the first query.

Third query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 2.1.2020 13:00:00
Expected result:
ProductPrice for sale
Honor 10€9000
HUAWEI 20 Pro€14000
iPhone Xs Max€19000
Pricelist B is the highest priority pricelist from this request, and the validity of its prices matches the request, so it's part of the evaluation process. Price list C has the lowest priority, so if there is a price in price list Baseline or price list A, it would not be used.

Fourth query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 2.1.2020 13:00:00
  • price between: €8000 and €10000 (both inclusive)
Expected result:
ProductPrice for sale
Honor 10€9000
The query is the same as the third query with the addition of the Price Range filter. The result contains only the Honor 10 product, which is the only product with a matching price. The other products have prices that would match the price range in other price lists - but those prices were not selected as the price to sell, so they cannot be used in the price range predicate.

Product variants extension

Product with variantsToggle Term Reference must contain prices of all its variants. Variant prices must be distinguished by the inner entity id property (see interface ). The product with variants must have a price inner entity reference handling mode set to LOWEST_PRICE.

In this product setup, the selling price is selected as the lowest selling price of all variants, compared in the price type of the query. When two variants sell for the same amount, the price with the lower price id wins, then the variant with the lower inner entity id. This price is used to filter products by price.

The entity will also provide calculated prices for each of the product variants, selecting the first price ranked by priority for each inner entity identifier. This information can be used to display the price range for the product with variants (i.e. price from €5 to €6.5) or to calculate an average price for sale of all product variants. The lowest and the highest price of the range are chosen in the price type of the query as well.

Model example

Let's have the following products:

ProductMaster productBaseline pricePrice list APrice list BPrice list C
Variant: blueT-Shirt I Rock€10€9
(valid 1.1.2020 00:00:000 - 31.1.2020 23:59:59)
€7.5
Variant: redT-Shirt I Rock€12€14€8.5
Variant: greenT-Shirt I Rock€21€23€19
(valid 1.1.2020 01:00:000 - 31.1.2020 22:59:59)
Variant: blueJumper X-Mas Deer€26€19
(valid 1.1.2020 02:00:000 - 31.1.2020 21:59:59)
€9
Variant: redJumper X-Mas Deer€26€22€9
Variant: greenJumper X-Mas Deer€26€21€18
(valid 1.1.2020 03:00:000 - 31.1.2020 20:59:59)

These results are expected for the following queries:

First query

  • price lists: Baseline
  • valid in: 1.11.2020 13:00:00
Expected result:
ProductPrice for sale
T-Shirt I Rockfrom €10 to €21
Jumper X-Mas Deer€26
The Jumper X-Mas Deer has a single price for sale because all its variants in the Baseline price list have the same price. The T-Shirt I Rock must signal that its cheapest price is €10 and its most expensive price is €21.

Second query

  • price lists: B, Baseline, C (argument order controls price list priority)
  • valid in: 1.11.2020 13:00:00
Expected result:
ProductPrice for sale
T-Shirt I Rockfrom €10 to €21
Jumper X-Mas Deer€26
The result in this query is the same as in the first query. The prices in price list B cannot be used because their validity period is not valid and price list C has the lowest priority and the prices in Baseline will override it.

Third query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 2.1.2020 13:00:00
Expected result:
ProductPrice for sale
T-Shirt I Rockfrom €9 to €19
Jumper X-Mas Deerfrom €18 to €22
The result in this query will be determined by prices from price list B, all of which are now valid. Price list C has the lowest priority and won't be used at all. The price list Baseline wouldn't be used either, because all products have a price in one of the higher priority price lists - i.e. price lists A and B.

Fourth query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 2.1.2020 13:00:00
  • price range between: €8 and €11 (both inclusive)
Expected result:
ProductPrice for sale
T-Shirt I Rockfrom €9 to €19
The query is the same as the third query with the addition of the Price Range filter. The result contains only the product T-Shirt I Rock, which has at least one variant with a price that matches the price range. The Jumper X-Mas Deer has a price that would match the price range in other price lists - but those prices were not selected as the selling price and therefore cannot be considered in the price range filter.

Product sets extension

Product setToggle Term Reference must contain prices of all its components. Component prices must be distinguished by the inner entity id property (see interface . The product record must have the price inner entity reference handling mode set to SUM.

In this setup, the product sales price is calculated on the fly as the sum of the sales prices of all its components. This aggregated price is used for filtering products by price.

If the component does not have a sales price for the query passed, the product set sales price is calculated without that particular component.

Model example

Let's have the following products:

ProductProduct setBaseline pricePrice list APrice list BPrice list C
FrameDrawer€100€90
(valid 1.1.2020 00:00:000 - 31.1.2020 23:59:59)
€75
Set of knobsDrawer€120€140€85
HingesDrawer€210€230€190
(valid 1.1.2020 01:00:000 - 31.1.2020 22:59:59)
Head/footboard slatBed€260€190
(valid 1.1.2020 02:00:000 - 31.1.2020 21:59:59)
€90
TorsoBed€260€220€90
DrawersBed€260€210€180
(valid 1.1.2020 03:00:000 - 31.1.2020 20:59:59)

These results are expected for the following queries:

First query

  • price lists: Baseline
  • valid in: 1.11.2020 13:00:00
Expected result:
ProductPrice for sale
Drawer€430 (€100 + €120 + €210)
Bed€780 (€260 + €260 + €260)

Product sets have a selling price that is the sum of the selling prices of their parts.

Second query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 1.11.2020 13:00:00
Expected result:
ProductPrice for sale
Drawer€470 (€100 + €140 + €230)
Bed€690 (€260 + €220 + €210)
Price list B cannot be used in the calculation because none of its prices meet the validity condition. Price list C would be used if there are no prices in price list Baseline or in price list A, which is also not fulfilled. The price for the sale is calculated as the sum of the prices in the Baseline price list and the prices in the A price list.

Third query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 2.1.2020 13:00:00
Expected result:
ProductPrice for sale
Drawer€420 (€90 + €140 + €190)
Bed€590 (€190 + €220 + €180)
Price list B can now be used because its validity constraint is satisfied by the prices in this price list. The parts that don't have a price in price list B will use their price from a second price list with the highest priority - price list A.

Fourth query

  • price lists: B, A, Baseline, C (argument order controls price list priority)
  • valid in: 2.1.2020 13:00:00
  • price between: €0 and €500
Expected result:
ProductPrice for sale
Drawer€420 (€90 + €140 + €190)
The query is the same as the third query with the addition of a price range filter. The result will contain only the Drawer product whose sum of selling price of its parts is in the specified range.

Author: Ing. Jan Novotný

Date updated: 7.11.2023

Documentation Source