Customer who bought this also bought...

13 replies [Last post]

Wed, 09/26/2007 - 10:27

deanloh

Offline

Joined: 08/20/2007

Juice: 27

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

Wed, 09/26/2007 - 10:33

#1 View page

Lyle

Offline

Joined: 08/07/2007

Juice: 6841

Re: Customer who bought this also bought...

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.

Sun, 10/07/2007 - 08:34

#2 View page

72dpi

Offline

Joined: 10/07/2007

Juice: 134

Re: Re: Customer who bought this also bought...

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

Sun, 10/07/2007 - 15:37

#3 View page

psynaptic

Offline

Joined: 08/28/2007

Juice: 731

Re: Re: Re: Customer who bought this also bought...

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

Drupal Contrib API
Freestyle Systems

Wed, 09/26/2007 - 10:36

#4 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15422

Re: Customer who bought this also bought...

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?

Mon, 01/21/2008 - 01:26

#5 View page

KillerJerseys

Offline

Joined: 01/17/2008

Juice: 58

Re: Customer who bought this also bought...

Bump, I could really use a feature like this!

Mon, 01/21/2008 - 03:40

#6 View page

zmove

Offline

Joined: 08/13/2007

Juice: 1192

Re: Re: Customer who bought this also bought...

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

Mon, 01/21/2008 - 20:19

#7 View page

KillerJerseys

Offline

Joined: 01/17/2008

Juice: 58

Re: Re: Re: Customer who bought this also bought...

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

I wouldn't even know where to begin.

Mon, 01/21/2008 - 21:38

#8 View page

torgosPizza

Offline

Joined: 08/14/2007

Juice: 4110

Re: Re: Re: Re: Customer who bought this also bought...

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.

--
Help directly fund development: Donate via PayPal!

Thu, 01/31/2008 - 21:41

#9 View page

torgosPizza

Offline

Joined: 08/14/2007

Juice: 4110

Re: Re: Re: Re: Re: Customer who bought this also bought...

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

(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.)

Attachment	Size
upsell-screencap.jpg	159.75 KB

--
Help directly fund development: Donate via PayPal!

Fri, 02/01/2008 - 10:12

#10 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15422

Re: Re: Re: Re: Re: Re: Customer who bought this also bought...

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.

Fri, 02/01/2008 - 11:36

#11 View page

torgosPizza

Offline

Joined: 08/14/2007

Juice: 4110

Re: Re: Re: Re: Re: Re: Re: Customer who bought this also bought

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)

--
Help directly fund development: Donate via PayPal!

Fri, 02/01/2008 - 11:41

#12 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15422

Re: Re: Re: Re: Re: Re: Re: Re: Customer who bought this also bo

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.

Mon, 02/04/2008 - 00:41

#13 View page

torgosPizza

Offline

Joined: 08/14/2007

Juice: 4110

Re: Re: Re: Re: Re: Re: Re: Re: Re: Customer who bought this als

Upsell ontrib is online! More enhancements to come later.

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

--
Help directly fund development: Donate via PayPal!

	Shopping cart
	View your shopping cart.

	User login
	Log in using OpenID: What is OpenID? Username or e-mail: * Password: * Remember me Log in using OpenID Cancel OpenID login Create new account Request new password

Ubercart

Navigation

Shopping cart

User login