Sometime earlier I wrote an article about tracking competitors’ prices.
In this post, I’m going to address a problem you may run into if you decide to not only track the prices but to compare them as well.
Price comparison problem
To compare prices you need to match your products with products from your competitors’ website.
This is where you run into a problem – you can sell identical products under different names. By that, I mean that you and your competitor can and in 99.9% cases will use different formatting, which makes VLOOKUP useless.
Above is an example of the same product with different names.
Good luck matching these, especially if you have thousands of products to go through.
Solution to price comparison problem
You can match names with different formats by using a Fuzzy Lookup add-in for Excel.
Fuzzy Lookup performs a so-called fuzzy matching of textual data in Excel. Which means it can match 2 items if they are n% similar, and/or if item A is the closest match for item B.
For example, you have 2 items from each store in separate columns:
a1. Apple iPhone X 4G LTE Unlocked Cell Phone 5.8″ Silver 256GB 3GB RAM
a2. Apple iPhone X 4G LTE Unlocked Cell Phone 5.8″ Silver 128GB 3GB RAM
b1. Apple iPhone X, Fully Unlocked 5.8″, 256 GB – Silver (Certified Refurbished)
Fuzzy Lookup will match 256GB iPhones since the highest % match for cell B1 is in cell A1.
Apple iPhone X 4G LTE Unlocked Cell Phone 5.8″ Silver 256GB 3GB RAM
Apple iPhone X, Fully Unlocked 5.8″, 256 GB – Silver (Certified Refurbished)
Real life example from one of my projects (can’t share more and too lazy to create a separate project):
As you can see I tried matching the prices from 2 e-comm stores after scraping their product names (left column) and prices (right column). It’s far from perfect, but it’s the best and probably only option if you need to match prices on tens of thousands of products. Add some manual editing and you’ll have a perfect list of matching products, and you can track prices on these products and compare them at any time.
How to use fuzzy lookup
- Get Microsoft Excel
- Download and install fuzzy lookup add-in
- Open the data you want to be matched in Excel
- Launch Fuzzy Lookup
- Tweak settings
There are a lot of guides available and I didn’t plan to explain how to use it, I just wanted to let you know that it exists and leave you to your own devices, it’s really not that complicated.
If you have questions or need help – feel free to leave a comment or contact me.