You are here:  » Mapping by both EAN and product name?


Mapping by both EAN and product name?

Submitted by Ch3W on Mon, 2015-07-13 23:45 in

Hi there,

I'm working with a number of feeds that have multiple product names per merchant, which makes mapping by name a bit of a nightmare. I looked at the feeds for EAN numbers, but only some of them are mapping via EAN. You'd think that the merchants would include EAN data so that affiliates could push more sales!

Anyway, is it possible to group by both EAN (http://www.pricetapestry.com/node/5034) and Product names? So, first group by EAN, and if EAN doesn't exist, attempt to group by name?

The other option I was considering, was to create a script that inserts the EANs into the feed before it imports... but this would be a nightmare to maintain. Perhaps a DB that maps popular product names with EAN numbers? Sorry, thinking out loud there.

Submitted by support on Tue, 2015-07-14 09:14

Hello Ch3W,

Let me experiment with this on my test server, I can see where you're coming from. The uidmap.php process as it stands picks out DISTINCT uid fields (ean in your case) and a single associated name. It then updates all products with the same uid with that single associated name.

An additional step would actually be to take the list of different names with the same uid field, and then once the initial mapping has been applied then also map any product with any of the list of names but no uid field to same single associated name.

In the mean time, as I notice from your comment in the UID mapping thread that you are running PHP 4 you would require a modification of the original version of uidmap.php as the current version uses mysqli_ functions which requires PHP 5, so if you would like to email me the version you are running I'll follow up to that with the above modification...

Cheers,
David.
--
PriceTapestry.com

Submitted by IG on Tue, 2015-07-14 12:54

I was just about to implement UID mapping on my site. Therefore, I am also very interested in such a solution.

Submitted by Ch3W on Fri, 2015-07-17 00:03

David thanks for explaining how the uid mapping process works, that was very useful.

Here's my current issue:
I currently have regexp that searches product names for certain strings. If the strings exist, product mapping successfully groups the products together. There is one particular merchant that does not have the necessary strings within the product name to be mapped via regexp (John Lewis), but thankfully, this merchant does have an EAN number unlike some of the other merchants that are already grouped together via regexp product mapping.

The problem I have, is that once I run the UID mapping script - it changes the product name of every product that has a matching EAN to that of the John Lewis's product name, thus removing the product mapping via regexp.

Sorry, that's a bit of a mouthful - do you understand my explanation?

To clarify further:

I have the following product names via different merchants that need grouping together via product mapping:
Green Cotton Jumper
Green Jumper Cotton
Green Jumper that is Cotton

So I use regexp product mapping to group all of the above. After an import, they're fine - nicely grouped together. There is one merchant however (John Lewis), that is not descriptive enough in their product name, and only lists the product as 'Green Jumper' - so it is difficult to group it with the other Green Cotton Jumpers from other merchants. However, John Lewis thankfully has an EAN number, as do some of the other merchants that are already successfully grouped together.
So, I run the UID mapping service.... but then the product names of the products that have a matching EAN number are changed to the non-descriptive 'Green Jumper' title, thus not matching the product mapping regexp, and thus removing them from the product group/map.

I think I've explained that ok - or I've not had enough coffee? ;)

Submitted by support on Fri, 2015-07-17 08:30

Hi,

Yes - that makes sense...

Automatic Product Mapping by UID takes place after import. You can detect that a product name has been modified by Product Mapping (either normal or RegExp) by comparing `name` and `original_name`. If they are different, a Product Mapping has been applied.

So uidmap.php can be modified to prioritise the use of an already mapped name as the master name chosen to apply to all products with the same EAN.

To do this, edit uidmap.php and look for the following code at line 44:

$sql2 = "SELECT SQL_CALC_FOUND_ROWS name FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' ORDER BY name LIMIT 1";

...and REPLACE with:

$sql2 = "SELECT SQL_CALC_FOUND_ROWS name FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' ORDER BY ABS(STRCMP(name,original_name)) DESC LIMIT 1";

The trick here is:

ORDER BY ABS(STRCMP(name,original_name)) DESC

STRCMP() returns -1 (less than), 0 (equal) or 1 (greater than) based on a string comparison of the two parameters; and combining this with ABS() modifies this to just 0 (same) or 1 (different) so sorting by the result of this DESCending will prioritise mapped names over non-mapped names as the master product name chosen.

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by Ch3W on Fri, 2015-07-17 11:17

Excellent - thanks David will check it out.

Matt

Submitted by KennethJ on Wed, 2015-07-29 19:11

This sounds very interesting. Would love to see a smooth solution to the situation described here.

Regards,
Ken

Submitted by sirmanu on Wed, 2017-03-01 20:46

Hi David.
I am having some problems after running EAN mod. The thing is that it hides some manual product mapping.
Can the above code solve my problem? Maybe run only of rows where original_name = name?

Submitted by support on Thu, 2017-03-02 08:55

Hi,

Yes, that's exactly the trick. So line 77 of uidmap.php where you will have the following code;

      $sql2 = "UPDATE `".$config_databaseTablePrefix."products` SET name='".database_safe($product["name"])."',normalised_name='".database_safe($normalisedName)."',search_name='".database_safe($searchName)."' WHERE ".$config_uidField."='".database_safe($product["uid"])."'";

...REPLACE with:

      $sql2 = "UPDATE `".$config_databaseTablePrefix."products` SET name='".database_safe($product["name"])."',normalised_name='".database_safe($normalisedName)."',search_name='".database_safe($searchName)."' WHERE ".$config_uidField."='".database_safe($product["uid"])."' AND `name`=`original_name`";

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Thu, 2017-03-02 23:59

In fact, I think is the opposite xD, only apply when mapping it wasn't done at import time.
AND `name`=`original_name`";

Submitted by support on Fri, 2017-03-03 08:35

Ah yes quite right - corrected above!

Cheers,
David.
--
PriceTapestry.com

Submitted by bodybuildingcom... on Sun, 2017-09-24 15:18

Hi David,

I am wondering if it is possible to map by both UPC (Universal Product Code) and MPN (Manufacturer Part Number) rather than by name?

I have some feeds which have UPC and some with MPN only, but also some feeds which has both. If it is possible to map with both UPC and MPN, I am wondering if the feeds which have both, can those fields in the feeds be imported/indexed together? For example, retailer A has UPC and MPN in their feed, can I import retailer A's feed with both UPC and MPN numbers? I am wondering if that causes some kind of a problem in mapping? Or would I have to select only one of those fields to avoid conflict?

By having both UPC and MPN, it would allow me to map out my products better automatically.

Thanks.

Norbert

Submitted by support on Mon, 2017-09-25 08:51

Hello Norbert,

I have a version of uidmap.php that operates on multiple UID fields which would be the first thing to try - please could you email me the current uidmap.php that you are using (as there have been a few versions!) and I'll modify for use with multiple fields...

Cheers,
David.
--
PriceTapestry.com

Submitted by bodybuildingcom... on Mon, 2017-09-25 20:11

Hi David,

I have sent across my uidmap.php via email.

Thanks.

Norbert

Submitted by bodybuildingcom... on Sun, 2017-10-08 21:30

Hi David,

Thank you for your help so far with implementing the mpn and upc fields for me. I have imported two feeds, both feeds have overlapping products with identical upc codes, but it doesn't seem to map out the products automatically. For example, these two products are identical {link saved} but it doesn't seem to map them out automatically when I import the feeds. I have checked to make sure that the upc codes are identical in both feeds, as well as to make sure they are correct when it is imported.

Not sure if you have any ideas?

Thanks.

Norbert

Submitted by support on Mon, 2017-10-09 08:42

Hello Norbert,

Please could you email me /pt/html/prices.php from this installation and also copy into your email the custom field entries added to $config_fieldSet in config.advanced.php for your multiple custom UID fields and I'll check it out further with you..

Thanks,
David.
--
PriceTapestry.com