Re: [PHP] People who bought this also bought (amazon style) functionality...logic problem

On Tue, September 26, 2006 7:18 am, Ryan A wrote:
I want to write a "module" for xcart (a commercial
'shopping cart') which would be like what Amazon.com
offers when you go to shop on their site... when you
click on a product to see its details you get a little
box below that recommends more products based on what
others have purchased

eg:
If you click on a toy helicopter it would recommend
extra rotor blades,landing gear etc

eg 2:
If you click on Eminem's latest album it would show
you his other two albums that people bought with this
album

etc

I'm trying to work out the logic to do this... and
quite frankly have hit a brick wall.

Possibly because Amazon's algorithm does not down-scale well...

So far:
-------
I was thinking when someone (Joe) purchases X with
product id Y I would put that in a seperate table,
then when someone (Jane) else comes looking at item Y
I would display everything that Joe bought...
but then it gets a little complicated, if Jane buys Y
and other stuff that Joe didnt buy... how do I merge
what Joe and Jane bought to display to the next
visitor? (I can only dispaly 5 recommendations at
once)

You want something like this:
select other_stuff.product_id, count(order_id) as score
from orders as other_stuff, orders as this_product
where other_stuff.order_id = this_product.order_id
and other_stuff.product_id != $product_id
and this_product.product_id = $product_id
group by other_stuff.id
order by sold DESC
limit 5

The point being that you get the 5 things that are MOST purchased in
other orders with this same product.

The above query may well bring your DB to its knees, and you may need
to optimize the heck out of it.

YMMV

I would appreciate any tips/advise and code you can
give me, would be fantastic of course if you have
worked with xcart or have already done this.

The problem is that unless you have the volume of Sales that Amazon
has, there may not be enough people buying enough stuff for this to
make sense.

It also won't group accessories or anything -- It goes solely based on
who bought what.

This is possibly great for "e-commerce" but might not be the ideal.

I *much* prefer the CDBaby approach where somebody listens to the CD
and then recommends artists they think are complementary. :-)

--
Like Music?
http://l-i-e.com/artists.htm
.

Relevant Pages