Drafting, implementing, and testing (complex) pricing strategies

Flowchart or table

Start with outlining your pricing strategy in either flowchart or table form. What is important is that it shows the logic used to derive at all the pricing end points

Add numbering to the rules / end points this will help greatly later on.

Below you see a flowchart and table example of the same pricing strategy. Both have 

1. Clearly defined end points
2. Numbering

Visualization option 1: Flowchart

Visualization option 2: Table


Number Category Brand Action
1.1 Bikes Gazelle Follow lowest competitor
1.2 Bikes Others Set equal to average market price
2.1 Football Elstar Follow lowest competitor
2.2 Football Others Set equal to average market price


Entering the rules in Omnia

For this we need to go through several steps, in this specific order
1. Connecting your product feed to Omnia: so you can set rules based on your categories and brand (link)
2. Define your competition (link)
3. Create your actions (link)
4. Create your rules (link)

Adding the rules should be done with a carefully thought out at universally applied naming structure for the maximum readability. Our suggestion:

End point numberEnd point description -> Action description

Below is an example for end point 1.1

All together, the rules look like below

Test single products

Find at least one product for each of the end points. Then use the EAN to see if the right step(s) are applied. For this you can use the green Test button as shown in the screenshot above, or look up the product in the Product overview.

Create the price advice report

Here you can find a detailed description on how to create a price advice report: link.

Bulk testing price advices

Add strategy input connect fields to price advice report

To the price advice report, add all the connect fields used in your price strategy conditions. In this case, we need Top level category and Brand. We also recommend adding some fields to get insight into price changes. Here I added the a field containing the relative price change, absolute price change, and the current selling price. Making the report field mapping look like below.

Subsequently, go the Export tab, and make sure all fields are ticked.

Import report data into Excel with a direct connection

After running the report, we will import the data into Excel. It is best to do this by establishing a direct connection the Omnia report, allowing you to refresh the data easily, at any time (only possible on Windows at time of writing - april 2021). 

First, copy the URL to the CSV file, found in the preview tab.

Second, open Excel, go to the Data tab, click From Webpaste in the URL, and click OK.

Check if the data preview is correct, if so, click Load. Sometimes the numbers are misinterpreted due to country settings (Omnia uses a dot as a delimiter, most European countries use a comma). If this is the case, click Transform and make the required adjustments (Omnia customer service can help with this).

Add price end point columns

Add a column called Final end point (to be filled later).

Now add a column for each strategy endpoint and give them a different color (Home > Cell styles).

Then insert an IF statement formula that returns the header name of that endpoint column, if the product should fall under that endpoint.

In the case of 1.1 - Bikes & Gazelle, the formula becomes:

=IF(AND([@Category]="Biking",[@Brand]="Gazelle"),Omnia_feed[[#Headers],[1.1 - Bikes & Gazelle -> Follow lowest competitor]],"")

Omnia feed refers to the name of the dataset. This might be different in your case: by default the name contains a long code which is the feed id. You don't need to type this, out, just click the header when making the formula, as shown in the GIF below.


Do this for all end point columns, by copying the formula and modifying it for that end point.

In the end, it will say for each products, under which end point it falls. 

You can then filter on an endpointcheck if the price advice is roughly as it should be (also map the selling price in the report to get an idea of the price increase/decrease), and look up the product id in Omnia product overview and click Show me why, to see if indeed the correct steps are performed.

Next, we will populate the Final end point. Use a Concat formula over all the end point cells for that row, to let it reflect the end points for each product. See the GIF below how to do this.


In our file the final formula will be as below.

=CONCAT(Omnia_feed[@[1.1 - Bikes & Gazelle -> Follow lowest competitor]:[2.2 - Football & Others - > Set to average price]])

What this formula does, is it combines the text of all price end points cells. Normally, only one cell should have text. If more than one cell has text, they will be combined. But this is good, since we definitely want to be alerted if more than 1 end point applies for a product, which should not be possible in a properly designed pricing strategy.

Visualize your pricing strategy

Now that we have one column which contains, per product, which end point is (supposed to be) applied. We can get make some very neat visualizations, giving us insights into how the strategy is being applied.

Using only the data from the report in Excel, I was able to make the following pivot table.

The Rows are the Final end point column. For values, you can now see the following data for each pricing end point

  1. The number of products that fall under the end point
  2. The percentage of products that fall under the end point
  3. The average price advice (suggested new price)
  4. The average selling price (current price)
  5. The average relative difference between the price advice and selling price (1 is no change, 0.85 is on average 15% lower)
  6. The average absolute difference between the price advice and selling price (0.30 is on average advice price being 30 cents higher)

Your pricing strategy companion for now and in the future

There are two great advantages using this exact setup:

1. Get any insights into the pricing rules you want

As you saw above, you can calculate any aggregated measure per end point you want, by simply adding it to the report in Omnia.

2. Easily synchronize the Excel file to the last price advice report

Since we made a direct connection to the CSV URL, updating the underlying data is just a push of the button. See the image below.