Working for an eCommerce business, I am often tasked with comparing prices. My day to day involves a lot of spreadsheets. These spreadsheets are used to ensure that prices are as correct as can be.
Anyone who has created a spreadsheet with around 50 tabs and many thousands of calculations over tens of thousands of rows and hundreds of columns knows that this can become a cumbersome endeavour. Not to mention the task of trying to convey this mess of numbers and grids to others.
There must be a better way…
StipulationsPrices we have on the website need to;
- Be profitable when compared with our cost prices.
- Be in-line with competitor prices.
Those points are just the basics, there are many variables involved in pricing a product. There are also a lot of products (tens of thousands) spanning across multiple websites, categories, product types, suppliers… the list goes on.
ProcessAdded to all of this is the fact that prices need to be checked often and quickly. Here’s a scenario that often appears;
Solution 1: Let’s start at the top and go through these products one by one in the admin back-end of the website.
Solution 2: Let’s export the appropriate products, match them up with the products in the supplier’s price list and enter each price one by one.
Solution 3: Let’s export the products, as above. Then use a
INDEX(list-of-prices, MATCH(sku, list-of-skus, exact-match))formula to match a product’s SKU with the price list and pull in appropriate prices, then use simple formulas to add appropriate margins and other factored costs (delivery etc).
Hopefully anyone can see that of these 3 solutions, the third is the most efficient, but it still has problems.
What if we are using a different SKU to the suppliers?This unfortunately happens. In an ideal world it would not but if we get a SKU which has a ‘/’ character in it, we need to replace it with an ‘_’, ‘-‘ or nothing. This is totally avoidable with proper preparation and coding but that’s another story.
It also may be as simple as the supplier’s updating/changing their SKU in this new price list. The thing to do here is to use the formula here to match all we can, and then manually go through the ones left over.
So there’s the solution, what’s your point?Solution 3, above is what we currently do when updating prices from our suppliers. This works but still involves a lot of manual intervention and time.
Not mentioned above is also the situation of comparing prices with our online competitors. Aside from collecting the data, which is another matter, we need to match SKUs, take in to account other charges and consolidate the important parts of this information in to an easy to understand report at the end.
The computer can do the work, we just lack the instructions to give to the computer! This is where my light bulb moment occurred.
“Pieces of Eight”An idea I have mulled over for a long time is a project I have initially dubbed “Pieces of Eight” (because I like pirates and gold!).
At the moment this program presents the user with a form to;
Not terribly useful currently, my functionality ideas are;
-
- Be able to also read in competitor prices, to a list of competitors, and then;
- Compare the company prices with each competitor’s prices
- Report this information on screen
- Offer the option to save data to a file
- Be able to read in a supplier price list and given the below parameters, save a file to action this data.
- discount
- additional charges
- percentage markup
- Plus many others…
- Be able to also read in competitor prices, to a list of competitors, and then;
My biggest hurdle with these ideas, as I foresee, is going to be matching SKUs with what the other company’s are using. Something I need to do research on when I am there.
Currently I am working on this outside of work, so weekends and an hour or two most nights but if I can get it right, it could save myself and my colleagues hours and hours of time at work.
Share this:
