Repricer: Formula Builder

We start with the explanation of elements that can be used in the formula builder, such as operators, advanced functions, standard price attributes, and custom attributes from the catalogue.

1. Operators
2. Advanced functions
3. Price attributes
4. Attributes
5. Entering formulas
   1. Example of entering a minimum price formula
   2. Example of entering a maximum price formula

Operators

Advanced functions

Price attributes

Available standard price attributes differ per marketplace in the repricer.

Attributes

In the repricer, it is possible to directly click an existing attribute in EffectConnect within the formula builder and use it in your formulas.

For example, the purchase price of your products can be found in this attribute list and selected for use in your formulas. You can also search for a specific attribute using the search function.

Entering formulas

We will use the formulas under the tab “default price limits” as an example. Entering formulas in other parts of the repricer works in exactly the same way.

Example of entering a minimum price formula

Click on ‘enter formula or amount’ to open the formula builder.

You will then see a screen with three elements: entering the formula or amount, enabling a live preview, and the components that can be used in formulas, as explained above.

Click on the input field under ‘current formula’.

In this field, you can, for example, select attributes or components by typing @ and then entering part of the attribute name or component. For example, @cost price.

Click on cost price, or use the arrow keys and press enter:

You have now added the purchase price to the formula. You can also click on the attribute purchase price in the formula bar, which will open a slide-out where you can indicate in which format the value in the attribute is stored:

In this example, the purchase price attribute contains a decimal amount. If you have attributes in eurocents in your catalogue, set this to ‘in cents’, and the repricer will automatically convert this in the calculations.
If you have an attribute for, for example, a VAT percentage where 1.21 or 1.09 is stored, use the ‘numerical values’ option.

You can of course further extend the formula. For example, if you want to add a 10% markup to the purchase price, you can add *1.1 to the formula:

It may be that you do not want to add a fixed 10% to every product, but have created a split via a custom attribute, where some items get a value of 1.1, others 1.2 or 1.15, etc.
To add such an attribute to the formula, scroll slightly down and find the attribute in the list of attributes:

By clicking on the example attribute Repricer - Variable Margin, we add it to the formula:

We set the attribute ‘Repricer - Variabele Marge’ as ‘numerical values’:

Next, for example, we add shipping costs to the formula. First, we enclose the already entered part of the formula in parentheses:

Then we type +, and in this example, we have an attribute where the correct shipping costs are stored per product. We click this attribute from the list.

Result:

We enclose the formula again in parentheses and then add VAT of 21%:

If you want to see how the formula looks with filled-in values, enable the live preview:

You can display any product or select a specific product for which you want to see the calculation.

In this example, we are creating a formula for the minimum price on Bol. For Bol, it is possible to include the correct commission costs (already including VAT) in the formula.
First, add Commission (fixed amount) (CA) to the formula by typing + and clicking on this price attribute:

Enclose the formula again at the beginning and end with parentheses:

Finally, add the variable commission, i.e., the commission percentage. Type /(1-( in the formula bar, and then click commission percentage (CP) in the price attributes list:

We then immediately add /100)) to complete the formula.

The result is:

The outcome of the first part of the formula, up to Commission (fixed amount) (CA), is divided by, for example, 0.85 if the commission percentage is 15%. This gives the correct amount including the commission. You pay commission on the final selling price. By adding the divide by 0.85, you get the correct final amount.

A common misconception is that the commission percentage should be added by multiplying by, for example, 1.15. 
If you did that, you would add 15% to the first part of the formula, but the 15% applies to the final amount. Example with €100:

• 100 x 1.15 = 115
• 100 / 0.85 = 117,65

You could thus immediately lose €2.65 margin with the wrong formula.

Click Apply to save the formula:

In the next screen, click Save to also save the adjustment in the repricer rule:

We have now entered a basic formula for the minimum selling price. In the background, for each product in the product selection linked to the repricer rule, the correct value for each product’s purchase price, margin, shipping costs, etc., is retrieved. This ensures the correct calculation is applied in bulk for every product using the individual data points.

Example of entering a maximum price formula

Go back to the ‘default price limits’ screen to set a maximum selling price (in this example, again for Bol).

Click on ‘enter formula or amount’:

A new screen opens with the formula builder for the maximum selling price. In this example, we choose an imported price attribute with the recommended retail price (RRP). We want to compare the RRP with Bol’s 2-star price, so the product remains displayed online if the 2-star price is lower than the RRP. If the RRP is lower than the 2-star price, we want to use that as the maximum price.

Click the input field in the formula builder:

Type @minnotzero and click the function MINNOTZERO:

A warning immediately appears that there is an invalid number of variables in the formula. We need to add (at least) 2 variables (e.g., attributes or formulas) and separate them with '  ; '

First, we take the Bol 2-star price. Click it from the price attributes list or type @bol 2-star and click the function. Immediately add a ; after the element:

Now add the RRP. In this example, we have an attribute for it. Click the attribute or use the @ function:

The attribute RRP is now added to the formula:

It is might still highlighted in red, asking whether the RRP attribute contains decimal amounts, eurocents, or a simple numeric value. Click the attribute in the formula bar to set this:

In this example, the attribute contains amounts in decimals.

Then click Apply in the formula builder:

We have now entered a minimum price formula and a maximum price formula. Click Save in the screen to also save these formulas in the rule.

 

Would you like help setting up the repricer after reading these articles? That’s absolutely possible, we offer a Reprice Consultation! During this session, we go through the repricer setup with you and configure it according to your preferences and specific situation.

You can also contact us any time without obligation:

📩 Email us at support@effectconnect.com
💬 Contact us via chat or give us a quick call