Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    170
    Plugin Contributions
    1

    Default "Also Purchased" returning bogus results

    My also purchased links are not returning appropriate recommendations for products, most of the recommendations are for combos that were never purchased together.

    I suspect that this might be due to the fact that my catalog has evolved somewhat, and that products associated with old orders have been deleted, and in the process of creating new products they have been assigned to the old product_id.

    There are about 300 orders in my DB from the "old days" using the original catalog. I would like to delete them based on a date range. Doing it one order at a time through the admin is too time consuming. What DB tables/entries should I look a deleting via phpMyAdmin in order to get rid of the old orders (before a certain date)?

    Thanks!

  2. #2
    Join Date
    May 2006
    Posts
    170
    Plugin Contributions
    1

    Default Re: "Also Purchased" returning bogus results

    update: I have gone through and manually deleted all of the orders for products that are no longer in the catalog.

    however I am still getting chaotic suggestions for the "also purchased" section. popular combinations that I know are sold together frequently never show up in that sections, whereas pairings that I know never happen during orders show up in "also purchased"

    does anyone have any additional suggestions for how to reset this? ZC 1.3.7

  3. #3
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: "Also Purchased" returning bogus results

    Quote Originally Posted by ts232 View Post
    .. in the process of creating new products they have been assigned to the old product_id.
    That's exactly the problem. Re-using product numbers for completely different items will cause your "also purchased" data to return unexpected results.

    You have 3 options:

    1. Turn off the Also Purchased feature.

    2. Delete all your products and re-add them, using new product numbers. BEWARE: This can have an adverse effect on your search engine indexing, since all the existing indexed content will be obsolete.

    3. Delete all existing orders altogether, meaning your "also purchased" data will start from scratch. BEWARE: deleting order history affects your customers, and maybe your own order mgmt and bookkeeping.

    And, in the future, don't re-use product numbers for unrelated items.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  4. #4
    Join Date
    May 2006
    Posts
    170
    Plugin Contributions
    1

    Default Re: "Also Purchased" returning bogus results

    Thanks for the tips. I cleaned up all order items in zen_order_products to remove any references to older products. It turned out that there was actually no overlap in product IDs however. Digging in deeply, I did find that most of the also purchased items were actually "also purchased", but that it was the rare cases that were being triggered instead of the most common ones. Digging around in define_queries.php, it appears that the SQL is returning the most recent matches rather than the most popular ones:

    Code:
    DEFINE('SQL_ALSO_PURCHASED', "select p.products_id, p.products_image
                         from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
                                . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
                         where opa.products_id = '%s'
                         and opa.orders_id = opb.orders_id
                         and opb.products_id != '%s'
                         and opb.products_id = p.products_id
                         and opb.orders_id = o.orders_id
                         and p.products_status = 1
                         group by p.products_id
                         order by o.date_purchased desc
                         limit " . MAX_DISPLAY_ALSO_PURCHASED);
    I modified this to do a match count and return the most popular matches first, which seems to work much better:

    Code:
    DEFINE('SQL_ALSO_PURCHASED', "select p.products_id, p.products_image, COUNT(p.products_id) num_matches
                         from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
                                . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
                         where opa.products_id = '%s'
                         and opa.orders_id = opb.orders_id
                         and opb.products_id != '%s'
                         and opb.products_id = p.products_id
                         and opb.orders_id = o.orders_id
                         and p.products_status = 1
                         group by p.products_id
                         order by num_matches desc
                         limit " . MAX_DISPLAY_ALSO_PURCHASED);
    This works much better for me. Please consider it as a possible contribution/fix for future versions of ZC.

  5. #5
    Join Date
    Mar 2008
    Posts
    268
    Plugin Contributions
    0

    Default Re: "Also Purchased" returning bogus results

    I'm trying to find out how to turn off the 'Also Purchased' feature but can't seem to find it anywhere, can some one tell me where abouts to look please?

  6. #6
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    69,402
    Plugin Contributions
    6

    Default Re: "Also Purchased" returning bogus results

    Look in the Configuration ... Maximum Values ...
    Also Purchased Products
    Number of products to display in the 'This Customer Also Purchased' box
    setting to 0 will turn the Also Purchased products off ...
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.5]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...
    Signup for our Announcements Forums to stay up to date on important changes and updates!

 

 

Similar Threads

  1. Turning off my "also purchased" does not work
    By MK1990 in forum General Questions
    Replies: 3
    Last Post: 1 Dec 2011, 09:16 PM
  2. "Also Purchased" Not Displaying on Product Info Page
    By nlcabrera in forum Templates, Stylesheets, Page Layout
    Replies: 2
    Last Post: 30 Sep 2010, 11:31 PM
  3. Replies: 1
    Last Post: 1 Apr 2010, 10:22 AM
  4. "Also Purchased" area above checkout box...
    By NovaBandit in forum Templates, Stylesheets, Page Layout
    Replies: 2
    Last Post: 8 Jan 2008, 09:47 PM
  5. Reset the "Also purchased" module in "product info"
    By Char3 in forum General Questions
    Replies: 6
    Last Post: 8 Jul 2007, 04:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR