Hi,
I've been thinking about this all weekend, and it's the largest problem I have to overcome design wise.
Would this be the best way to go about "Product Mapping" (ie. similar to Category mapping except for products)? This idea seems like the most elegant solution I can come up with.
I'm thinking the best way is to create a master product table where I manually insert products I would like to have on my site. I would then link this master product to the many real products via the product name or SKU.
I want to display other stuff about the product (like my own description, reviews from the internet etc.) so I don't want to have duplicate data for the same product. A master product table would solve this.
I would then have to modify the search algorithm to search this table instead of the feeds directly.
What do you think?
cheers,
Peter
Please correct me if I've missed something - but I agree this is pricetapestry's most significant - well, only - shortcoming. The assumption that with as little as 2-3 different merchants, perhaps on different networks, will all have exactly the same "product name" field (or even SKU for that matter) is just not realistic.
If we have four products from different merchants (that are actually all the same product):
Widget1-Blue
Widget 1, Blue
1 Widget Blue
Blue widget 1
(And believe me, yes feeds do vary that much!!)
... there needs to be a way of "mapping" all of them to be the same product - so they can have the same description, same reviews and information - but price comparison.
I've had such a system implemented, but it uses two tables of products at a time, resulting in no doubt apalling efficiency and products appearing twice in a search (both the corrected version and the previous one). It comes with an "/admin2" panel, where instead of all products being shown as text, they're shown as textfields.
If Peter or anyone else needs this feature and wants to go halves on some custom coding, please contact me.
David, if you're considering implementing this sort of feature and want some inspiration, let me know and I'll send you the above mentioned ineffective and inefficient scripts :)
Hi Harvey,
The reason I have never implemented this feature is because of the sheer number of products that most people run with, it is just totally impractical to try and match up products manually in this way. The big price comparison sites employ small armies of people to do this job, so for all but the smallest of niche sites I'm afraid I really don't see it being practical.
One point to note, is that whilst because of the variation in product name between merchants, you often don't get all the same products on one page, because of the way that MySQL's Full Text search operates they are almost certain to find all versions of a particular product, if not on the search results page then on the related products section on the product page...
Cheers,
David.
Hi Harvery,
That's an interesting offer. Could you explain the design of your "product mapping" feature a little more?
I have implemented product mapping this way:
As usual I import the data feeds
The following get categorized under "Jelly" via category-mapping.
Data product Feeds
1. Super Jelly Green (green)
2. Super SmellyJelly-Green
3. Green Stuff
A new table is created "prodmap".
I insert a new product "Super Green Jelly".
I set product mapping for this product. I insert all the above 3. lines into this area.
I import all feeds again.
Next, all three are renamed "Super Green Jelly" via product-mapping.
So now there are three products in the products table with the same name:
1. Super Green Jelly
2. Super Green Jelly
3. Super Green Jelly
These are products manually created by me. These can be linked to my master "Super Green Jelly" product in my "prodmap" table because they all have the same name.
I access my "Super Green Jelly" master page in admin and proceed to insert information specific to this product/category. ie. height, weight, color etc.
This seems like the only way to do it. All manually. One product at a time. I'm working in one niche (with perhaps three categories at most) only so it will be almost manageable I hope. With an increase in data feeds and products, the amount of manual work becomes unmanagable. ie. 1000 products and 10 different shops. That's 10,000 times I have to manually check product names.
Now when it comes to search I haven't decided the best way to do it. Nor have I come up with a good solution for the variation in data/specification of the products. (some products only need three fields, some require fifteen or so. fields may have different names also.)
cheers,
Peter
Hmmm, my solution seems more human brain power efficient than yours, but maybe less processing power efficient!
I must admit I'm not entirely sure how it works; I just used a freelance coder, didn't make them document it and they've since cleared off - so I learnt that lesson the hard way!
Basically though, there's a new table called product2, and renaming a product (via the admin2 panel) will result in that product's data being copied to a row in product2, but with the different "name" and a field called "changed" marked as "1".
When a search is performed, it comes up with a mixture of products from the two tables - apart from the horrible database structure, this is the only significant problem. Oh and for some reason it uses dupe_hashes instead of merchant mapped SKU's, so it's pretty useless if the merchant adjusts the table!
I think the only solution is, and once again if anybody knows of a good coder, I'd be much obliged :)
- When wanting to rename a product, an entry to a second table is made, with the merchant ID/name, the UNIQUE (for that merchant at least) product ID, and the name it is to be called.
- These changes are made in real time to the current database, too.
- When the feeds are updated, these changes are overwritten...
- But then, heroically and automatically, a script is run to find the changes made and adjust them accordingly.
David, interesting you should mention that about it taking too much time to maintain. I have a site of about 2.5k products, many of which the same. It took about half a week to spot duplicates, but then when I finished it was all worth it. Also, when using the "if product doesn't contain xxx so don't include it" filters you sent me, reasonably sized niche sites can be made with little effort.
Cheers
Hi,
On that basis, I think i'll add Product Mapping to the script - with a note in the documentation that it should only really be used on small, niche sites, especially considering that the only efficient way to handle the mapping is to load the entire mapping table into memory during import.
I'll post here once it's in the distribution...
David, glad you see that you have added product mapping.
Harvey, I think I'll stick with my own solution for the time being. I will probably post an update when I get further into this.
Hi Peter,
Product mapping working in exactly the same way as category mapping would work fine if you think it would be manageable for the number of products you intend to list.
If you mirror the category mapping implementation you would not need to change the search functionality at all because products will be in the database with their mapped name. This also means that you could map product by SKU by registering SKU as the product name field, and then listing it on your new product mapping page...
Cheers,
David.