Home » Forums » Ubercart » Ideas and Suggestions

Customer who bought this also bought...

Submitted by deanloh on Wed, 09/26/2007 - 10:27
deanloh
Posts: 8
 Joined: 08/20/2007

I'm really curious and boggled to see no one has asked about this feature before (I assumed so as I couldn't find it in the forum). It is a common feature that while users look at a product page, there will be some suggestions along the side or at bottom that cross sells other similar products, usually with headers like "May we also suggest...", "Customers who bought this also bought..." does Ubercart have this already or there are other ways to achieve this?

I saw this feature being implemented at sundaysenergy.org ("Similar" block"), but it would be a lot nicer if it can display the thumbnail of the products also.

I'm in the midst of planning an implementation of online store, this is important to have and I need to decide if I have to use another shopping cart or I can use Ubercart. Someone please help enlighten me Sad
permalink #1 Wed, 09/26/2007 - 10:33
Lyle

Posts: 2271
 Joined: 08/07/2007
AdministratoreLiTe!

Others have asked about it before, but all that got lost in the server crash. No one's announced that they're working on anything like that. I started a marketing module to hold this kind of functionality, but I really only got as far as this query:

<?php
  $result = db_query("SELECT that.nid, COUNT(that.nid) AS frequency FROM {uc_order_products} AS this LEFT JOIN uc_order_products AS that ON this.order_id = that.order_id LEFT JOIN uc_orders AS orders ON that.order_id = orders.order_id WHERE this.nid = %d AND that.nid != %d GROUP BY that.nid ORDER BY frequency DESC, orders.created DESC LIMIT %d", $nid, $nid, variable_get('uc_marketing_display_limit', 6));
?>

I don't see myself going back to it unless I get really burnt out on other problems. Anybody is welcome to take this and run with it.

permalink #2 Wed, 09/26/2007 - 10:36
Ryan

Posts: 5378
 Joined: 08/07/2007
AdministratorHead Code Monkey - I eat bugs.

Folks have suggested this in the past, and I'd love to see it happen. We haven't had a chance to code this feature ourselves, but I also know that everyone will have a different specification for how this should work. All the data is in the database, it's just a matter of someone having the time or doing it for contract. Perhaps you could give a solid spec. of what you're looking for and post it in the bounty forum or see if you can collaborate w/ someone else?

permalink #3 Sun, 10/07/2007 - 08:34
72dpi

Posts: 57
 Joined: 10/07/2007

I can see this as being a major bonus.

I'll ask my mate if he can look into it. it would certainly help with promotions....

--

www.archetoy.com

permalink #4 Sun, 10/07/2007 - 15:37
psynaptic

Posts: 329
 Joined: 08/28/2007
Early adopter... addicted to alphas.Not KulvikTheminator

+1 for related items. I wonder if it could be done using views.

--

Freestyle Systems
Contributions API

permalink #5 Mon, 01/21/2008 - 01:26
KillerJerseys
Posts: 27
 Joined: 01/17/2008

Bump, I could really use a feature like this!

permalink #6 Mon, 01/21/2008 - 03:40
zmove
Posts: 541
 Joined: 08/13/2007
Bug FinderEarly adopter... addicted to alphas.Getting busy with the Ubercode.Internationalizationizer

+1 for view filter that allow to list products that customers bought with the product you see.

permalink #7 Mon, 01/21/2008 - 20:19
KillerJerseys
Posts: 27
 Joined: 01/17/2008

Something that searches for similar keywords that is related to the content?

I wouldn't even know where to begin.

permalink #8 Mon, 01/21/2008 - 21:38
torgosPizza

Posts: 1314
 Joined: 08/14/2007
Bug FinderEarly adopter... addicted to alphas.Getting busy with the Ubercode.

Our old system did it by having a select list with other products you could choose to upsell at Checkout. Unfortunately that's not a good option for a site with a lot of products.

There are two things mentioned here: Related Products and Recommendations.

The main differences, IMO, are that Related products can be things in a similar vocabulary or category, while Recommended (or "Other users bought these ...") might not be related at all, although they could be for instance an accessory to the item you're buying. In that way they might still technically be related.

I'm not 100% certain you can do it with views, but I would propose a module that looks at the items in a user's cart, does a fuzzy search based on those items and the text, title, and other keywords within the product's node. Then, I would also look to see what items are in that taxonomy vocab and see how they relate to the search. This could be similar to the way the views_fastsearch works, with the exposed taxonomy etc., and then based on relevance, show the related products.

Something like Amazon's "65% of customers bought this item, others bought this item.." might be a bit more complex to do...

This is something I wouldn't mind having on the site either, and anything that can dynamically generate relevance (or even based on actual sales, although that might be a large, complicated query) would get my help. Let me know.

--

"Pain don't hurt." - Dalton

Mike Nelson's RiffTrax! www.rifftrax.com

permalink #9 Thu, 01/31/2008 - 21:41
torgosPizza

Posts: 1314
 Joined: 08/14/2007
Bug FinderEarly adopter... addicted to alphas.Getting busy with the Ubercode.

Well I got something that works, but only partially. It seems to work great as long as you don't have very many orders - the main issue is that the query takes an incredibly long time to run sometimes. I haven't isolated the issue yet, and I've tried optimizing it, but it still seems to get hung up while copying the query to a temp table.

Anyways, you need to put this into a theme_function override. In my case, the theme name was "RiffTrax" - so the function is called RiffTrax_uc_cart_view_form.

<?php
function RiffTrax_uc_cart_view_form($form) {
  drupal_add_css(drupal_get_path('module', 'uc_cart') .'/uc_cart.css');

  $output = '<div id="cart-form-products">'
          . tapir_get_table('uc_cart_view_table', $form) .'</div>';

  if (($page = variable_get('uc_continue_shopping_url', '')) != '<none>') {
    if (variable_get('uc_continue_shopping_type', 'link') == 'link') {
      $continue_shopping_link = l(variable_get('uc_continue_shopping_text', t('Continue shopping')), $page);
    }
    else {
      $continue_shopping_link = drupal_render($form['continue_shopping']);
    }
  }

  $output .= '<div id="cart-form-buttons"><div id="cart-button-shopping">'
           . $continue_shopping_link .'</div>'
           . drupal_render($form) .'</div>';
         
           $output .= '<div style="padding:8px;" id="cart-related-items">Customers who purchased the items in your shopping cart also bought:<br /> ';
                   
           $cartitems = uc_cart_get_contents();
           $random = mt_rand(0, count($cartitems));
           $random_item = $cartitems[$random];          

           
           $result = db_query("SELECT that.nid, that.title, COUNT(that.nid) AS frequency
                        FROM {uc_order_products} AS that
                        LEFT JOIN {uc_order_products} AS this
                        USING (order_id)
                        WHERE this.nid = %d AND that.nid != %d
                        GROUP BY that.nid
                        ORDER BY frequency DESC
                        LIMIT 5", $random_item->nid, $random_item->nid);
           
               while ($upsell = db_fetch_object($result)) {           
       
                   $image = db_result(db_query("SELECT field_image_cache_title FROM {content_field_image_cache} WHERE nid = %d", $upsell->nid));
                  
                   $output .= '<div id="cart-related-item" style="padding:5px; width:18%; float:left; position: relative; text-align:center;"><a href="/'.drupal_get_path_alias("node/".$upsell->nid) .'">
                           <img src="/files/imagecache/thumbnail/files/'.$image.'"><br />
                           '.$upsell->title.'</a><br />';
                        //<a href="cart/add/i'.$upsell->nid.'Up-p'.$upsell->nid.'_q1?destination=cart"><strong>(Add to cart)</strong></a></div>';
               } 
         
           $output .= '</div>';
          
return $output;
}
?>

Please test this on your sites if you can, and if you know a way to optimize this query so it doesn't hang half the time, I'd be much obliged Smiling

(I've taken it down from the site until I can do more testing on the query. Attached a screenshot as proof that in works, since you can't see it live right now.)

AttachmentSize
upsell-screencap.jpg159.75 KB
--

"Pain don't hurt." - Dalton

Mike Nelson's RiffTrax! www.rifftrax.com

permalink #10 Fri, 02/01/2008 - 10:12
Ryan

Posts: 5378
 Joined: 08/07/2007
AdministratorHead Code Monkey - I eat bugs.

Honestly, I think the way for a feature like this to be scalable is for it to just go into a contrib module and keep a mapping table. This module can use hook_order() on the 'submit' $op and insert a row in the table for every combination of products. The table should have a product node ID and a related node ID, and you should enter a single row for every possible combination of product nodes. Then to display products, you simply query this table based on the currently viewed node, order it randomly, and limit the results.

permalink #11 Fri, 02/01/2008 - 11:36
torgosPizza

Posts: 1314
 Joined: 08/14/2007
Bug FinderEarly adopter... addicted to alphas.Getting busy with the Ubercode.

While I was thinking about the issue last night, that's almost the exact solution I came up with. I'll do one per nid, just to seed the database table. Serialized arrays and all that.

I'll be finishing it up today and posting as a contrib, with any luck.

Ryan, while I have your attention: is a theme_override function the way to go for this? Or is there a better way we can hook into that page? Not that copying and pasting the view_cart function whenever there is an update is THAT hard - but I feel like there has to be a way to do this right from the Contrib in a universal way, so that it doesn't have to be done on a per-theme basis. Maybe I'm missing something basic.

Perhaps a hook_uc_view_cart_form that will get called at the end of viewing the form contents? I don't really see any other use for such a thing, though... maybe my Contrib will get considered for Core inclusion one of these days (once it's done) Eye-wink

--

"Pain don't hurt." - Dalton

Mike Nelson's RiffTrax! www.rifftrax.com

permalink #12 Fri, 02/01/2008 - 11:41
Ryan

Posts: 5378
 Joined: 08/07/2007
AdministratorHead Code Monkey - I eat bugs.

Sorry, I should've checked out your screenshot first time around. Check out hook_cart_pane(). It's a little rough around the edges, but it should get the job done.

permalink #13 Mon, 02/04/2008 - 00:41
torgosPizza

Posts: 1314
 Joined: 08/14/2007
Bug FinderEarly adopter... addicted to alphas.Getting busy with the Ubercode.

Upsell ontrib is online! More enhancements to come later.

http://www.ubercart.org/contrib/2972

--

"Pain don't hurt." - Dalton

Mike Nelson's RiffTrax! www.rifftrax.com