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 number - End 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 Web, paste 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 endpoint, check 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
- The number of products that fall under the end point
- The percentage of products that fall under the end point
- The average price advice (suggested new price)
- The average selling price (current price)
- The average relative difference between the price advice and selling price (1 is no change, 0.85 is on average 15% lower)
- 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.
