Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    SAS ProductID - not unique? How to identify unique record?
    As I've been importing datafeeds, I've discovered that some datafeeds do NOT have unique ShareASale ProductID numbers (first column of datafeed). Specifically, the datafeeds from at least four merchants (merchant IDs 11526, 12109, 8780, 5733 ) has the number "1" for the ProductID in all rows. (I count a total of 831,164 such records out of 2.4 million in my database.)

    This has thrown me for a loop, because I had assumed that every row in every datafeed would have a unique ShareASale ProductID that would never be duplicated. Now that I see that this isn't true, I'm trying to find some other slice of data that will be unique, so I can recognize during import if an existing record has been changed (e.g. price change only).

    (I also submitted this as a 'trouble ticket.')

  2. #2
    Affiliate Manager adambha's Avatar
    Join Date
    October 20th, 2006
    Posts
    301
    This has to be an error.

    What does the 'Link' field look like? The URL includes the variable 'ProductID' so what does that show? If every link has ProductID=1 then there's definitely a problem. If not, you might be able to extract the ProductID from the link.

    Even better, test one of the links and see where it goes, if anywhere.

  3. #3
    http and a telephoto
    Join Date
    January 18th, 2005
    Location
    NYC
    Posts
    17,708
    If multiple merchants are using the same SKU's as product ID's you will have duplicates also.
    Deborah Carney
    TeamLoxly.com BookGoodies.com ABCsPlus.com

  4. #4
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    adambha wrote: > "What does the 'Link' field look like? The URL includes the variable 'ProductID' so what does that show?" <

    A good question. The link URLs appear to use "URLLink=" parameters, not "ProductID=" parameters.

    loxly wrote: > "If multiple merchants are using the same SKU's as product ID's you will have duplicates also." <

    I don't think so -- I believe the ProductID column is a number that ShareASale generates, not the merchant. I suppose SAS could elect to make ProductID unique "per merchant" and not unique "across SAS" (if so, I could use a combination of merchant ID and Product ID as the unique identifier). I certainly realize that merchant's own SKU identifiers, especially iSBN or UPC, are going to replicate across multiple merchants -- in any event, currently SAS does not pass the merchant SKU as a parameter.

  5. #5
    Affiliate Manager adambha's Avatar
    Join Date
    October 20th, 2006
    Posts
    301
    Quote Originally Posted by markwelch
    The link URLs are appear to use "URLLink=" parameters, not "ProductID=" parameters.
    Ah, I was hoping it'd be simple.

    In that case, I'll be interested to hear was SAS has to say, that's a weird one.

  6. #6
    Full Member jollygoodpirate's Avatar
    Join Date
    January 18th, 2005
    Location
    NC
    Posts
    227
    Use the merchant ID as well...
    You can make the unique ID the product id, a dash, and the merchant id.

    I am not tracking price drops, but there are several people here that can give you more definitive answers, since several are doing exactly that.

  7. #7
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    Jollgoodpirate wrote: > "You can make the unique ID the product id, a dash, and the merchant id." <

    No, I can't. With the datafeeds that use "1" as the product ID for every single product, there would be 831,164 records which together contain only four unique values ("1-11526" "1-12109" "1-8780" or "1-5733").

  8. #8
    ABW Ambassador Nature Boy's Avatar
    Join Date
    January 18th, 2005
    Location
    Tennessee
    Posts
    1,423
    I had/have this problem with some of my merchants too and passed it along to them. They said that they would ask SAS about it, but so far, I haven't seen the problem fixed via the FTP site. Perhaps SAS is changing the way the feeds are setup.

    I, for one, do not like that idea.
    Scott
    If you can't dazzle them with brilliance, then baffle them with bulls#!t
    Don't tell me that you'll do it... SHOW ME.
    Just because everyone else is drinking it is no reason for me to drink the KOOL-AID.

  9. #9
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    SAS did reply to my 'trouble ticket' via email. They did confirm that there are "some" merchants whose datafeeds don't have a unique ProductID, nor are these datafeeds are not processed or validated in the usual way.

    This is definitely a distraction; without a unique/distinct ID which persists through updates, I can't automate the propegation of changes from the datafeed out to my web content.

    I did look closely at one of these feeds, and determined that the filename part of the URL appears to be the sku, which may be unique; I'd need to spend some "quality time" with each of these feeds four feeds to confirm whether that's a workable solution.

    For the moment, I am simply excluding these four merchants from my current site-development projects. Since these four merchants do represent one-third of all product rows in my 383-merchant database, I definitely can't ignore them forever, but I am definitely not investing my time on these datafeeds before March 23.

  10. #10
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    Could anyone share a current list of ShareASale merchants whose datafeeds use non-unique ProductIDs? Certainly, ReStockIt is still in this group; BatteryFuel has switched to unqiue ProductIDs.

  11. #11
    Beachy Bill's Avatar
    Join Date
    November 20th, 2005
    Posts
    8,266
    Quote Originally Posted by markwelch
    ......
    This is definitely a distraction; without a unique/distinct ID which persists through updates, I can't automate the propegation of changes from the datafeed out to my web content.
    ......
    I feel your pain. In developing our project we had to program a unique work-around to automate the datafeed import process to circumvent all of those Identical IDs. That may be the main reason these types of programs or scripts are not cheap. There are hundreds/thousands of hours of programming that go into these things - even by people who are very experienced in LAMP and/or (ugh) that Micro$oft stuff. Those coding "magicians" don't work for 20 bucks an hour.
    Bill / Marketing Blog @ 12PM - Current project: Resurrecting my "baby" at South Baltimore..
    Cute Personal Checks and Business Checks
    If you are too busy to laugh you are too busy.

  12. #12
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Hey, Mark. I had the exact same problem with all the "1" in ProductID with one of the large SAS datafeeds I wanted to use. The explanation someone here gave me is that large datafeeds beyond, what, maybe 30,000 or 40,000 products are automatically assigned "1" in all the ProductID fields. Don't know why.

    Here's what I did -- and it may not work for all such datafeeds -- I used the UPC column for the Primary Key in my MySQL table, instead of the ProductID column.

    But, as to whether that'll work with the datafeeds you're using, I don't know.
    Generate more fake news.

  13. #13
    http and a telephoto
    Join Date
    January 18th, 2005
    Location
    NYC
    Posts
    17,708
    The reason for that is the large feeds that do not fit into MakeAPage. When they are submitted to Shareasale they *do* have a unique identifier, that is changed in the process to convert the feed to their format.

    It is not a merchant issue, it is a Shareasale issue.
    Deborah Carney
    TeamLoxly.com BookGoodies.com ABCsPlus.com

  14. #14
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    > The explanation someone here gave me is that large datafeeds beyond, what, maybe 30,000 or 40,000 products are automatically assigned "1" in all the ProductID fields. <

    FYI, a number of merchants with large datafeeds have unique ProductIDs, so it is not an absolute technical issue due to datafeed size. For example, TheNerds.net has 133,011 products in its ShareASale feed.
    Last edited by markwelch; June 25th, 2008 at 03:58 PM.

  15. #15
    http and a telephoto
    Join Date
    January 18th, 2005
    Location
    NYC
    Posts
    17,708
    As a merchant I was told it was because of the size. TheNerds must be doing something differently that I was not made aware of.
    Deborah Carney
    TeamLoxly.com BookGoodies.com ABCsPlus.com

  16. #16
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    Out of two dozen of my "largest" merchants (measured by number of products n their datafeeds), I found ten merchants who have non-unique ProductIDs in their ShareASale datafeeds:

    #8780 Betamonline.com (176,249 products)
    12861 Tradebit AG (150,065 products)
    10059 ReStockIt.com (109,026 products)
    13681 Online Sports (99,089 products)
    14618 Hotels By City Inc. (47,856 products)
    15010 PC Universe, Inc. (37,038 products)
    14237 Audio Blowouts Inc. (33,983 products)
    17445 iBuyOfficeSupply.com (31,084 products)
    11190 eSticker (PeerImage Inc.) (29,088 products)
    13820 DivaDiamonds (24,187 products)
    10851 RugsUSA (23,770 products)

    Total for these 10 merchants: 762,135 products

  17. #17
    Comfortably Numb John Powell's Avatar
    Join Date
    October 17th, 2005
    Location
    Bayou Country, LA
    Posts
    3,432
    Mark, Did you ever see this? One of my feed merchants with SAS changed all it's product IDs when updating their feed. I had the Product ID in the url and so there went my pages in the search index. I tried to work with the merchant and SAS and could never get it resolved satisfactory.

    It's that kind of stuff that makes you want to never see another feed. Merchants and AMs brag about having feeds, but they don't seem to care if they are useful.


  18. #18
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Quote Originally Posted by bumpaw
    ... Merchants and AMs brag about having feeds, but they don't seem to care if they are useful. ...
    Wow, bumpaw. You've just about said it all there -- single biggest difficulty in using datafeeds in a nutshell.
    Generate more fake news.

  19. #19
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    Did you ever see this? One of my feed merchants with SAS changed all it's product IDs when updating their feed.
    http://forum.abestweb.com/showpost.p...90&postcount=3

  20. #20
    Member buy-tees's Avatar
    Join Date
    December 1st, 2005
    Location
    UK
    Posts
    175
    On one of my sites I actually encrypt the image url and that becomes my unique identifier. I found that 98% of the time the image url always stayed the same. But I was working with only a select few merchants at the time, the logic may not apply to 5 million merchants

    But if you're looking for a quick solution, a quick fix, that's what worked for me. I ran a similar setup to what you are after Mark.

  21. #21
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    Quote Originally Posted by buy-tees
    On one of my sites I actually encrypt the image url and that becomes my unique identifier.
    That won't work for Betamonline.com as all thier images appear as " /images/imagenot.gif "

    I have written a system that can select any specific field on a per merchant basis. The standard takes field 1 but for Betamonline I use field 25 and ReStockIt I use field 20 etc.

    But I have to agree that it would be better if the standard was indeed a standard.

  22. #22
    Affiliate Manager PetsWarehouse.com's Avatar
    Join Date
    January 17th, 2005
    Location
    Long Island, New York
    Posts
    1,616
    Mark, we had a number of affiliates mad at us in fact a few dropped our program because of this "1" in the first field.

    Brian told me it was the size of the table that makes this happen.

    We've tried adding another unique ID in an optional field but many affiliates want to import the feed in a set way.

    I can say that PFX has no such problem.
    Bob Pets Warehouse
    Worlds Largest Pet Supply DataBase
    Join our Share-A-Sale Program [since 2003] Twitter



  23. #23
    Member buy-tees's Avatar
    Join Date
    December 1st, 2005
    Location
    UK
    Posts
    175
    Putting my programming/dba hat on.. that just sounds bizarre. Sharesale need to fix that.

  24. #24
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    Update: I've now identified 14 merchants as having "non-unique" productIDs in their ShareASale datafeeds (out of 300+ merchants who have datafeeds with more than 2,000 products):

    #8780 Betamonline.com 176,249 non-unique ProductIDs
    12861 Tradebit AG 150,065 non-unique ProductIDs
    10059 ReStockIt.com 109,026 non-unique ProductIDs
    13681 Online Sports 99,089 non-unique ProductIDs
    14618 Hotels By City Inc. 47,856 non-unique ProductIDs
    15010 PC Universe, Inc. 37,038 non-unique ProductIDs
    14237 Audio Blowouts Inc. 33,983 non-unique ProductIDs
    17445 iBuyOfficeSupply.com 31,084 non-unique ProductIDs
    11190 eSticker (PeerImage Inc.) 29,088 non-unique ProductIDs
    13820 DivaDiamonds 24,187 non-unique ProductIDs
    10851 RugsUSA 23,770 non-unique ProductIDs
    15979 Farm & Home Supply Center, Inc 14,458 non-unique ProductIDs
    11590 EliteFixtures.com 9,731 non-unique ProductIDs
    #5733 DirectForSale.Com 2,122 non-unique ProductIDs

    12869 Brandsplace.com Inc. 39,155 raw HTML in feed

  25. #25
    Full Member iolaire's Avatar
    Join Date
    October 3rd, 2006
    Location
    Arlington, VA
    Posts
    229
    I don't prevent duplicates 100% but probably could if I used the buy url rather than the product name in the following sql code for my master_index column:
    Code:
    CONCAT(if(`ProductID`=1,right( MD5(`Name`),20),`ProductID`), RIGHT(`Name`,20), RIGHT(`Category`,20), RIGHT(`Name`,20))
    Sp for my master index I string the following togeater:
    1. Product ID or else a hash of the first 20 letters of the product name
    2. 20 characters of the name
    3. 20 characters of the category
    4. 20 characters of the name (it would work better if this was a hash of the url)

+ Reply to Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 4
    Last Post: June 19th, 2013, 01:50 PM
  2. Help with Unique column in SAS datafeed
    By likemynick in forum ShareASale - SAS
    Replies: 3
    Last Post: August 16th, 2006, 06:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •