10

Amazon has "Customers Who Bought This Item Also Bought".

I am wondering and want to add this to my shopping cart which I made.

What fields do I need in a database? Any website, blog or resources for this?

Can you suggest the mechanism how to I should code it please?

Charles
  • 50,943
  • 13
  • 104
  • 142
shin
  • 31,901
  • 69
  • 184
  • 271

7 Answers7

13

Here are some similar questions:

Community
  • 1
  • 1
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
6

You probably don't need any new fields in your database - just keep a history of your orders. Then when you want to find your list of what other people bought:

  1. Select all users who have an order containing Item X
  2. For each of those users, total up everything else they have bought
  3. Get the top 3/5/whatever and there's your list.
nickf
  • 537,072
  • 198
  • 649
  • 721
  • 1
    Simple count-based systems generally make pretty weak recommendation engines. It's really a statistics / data mining problem. – RickNZ Jan 03 '10 at 11:38
  • @RickNZ Simple counts are robust estimators. Complicated ≢ better. @nickf @alix axel You could incorporate intersections and trim off the least relevant 25% and improve the count. – isomorphismes Mar 14 '11 at 04:21
  • 1
    In step 2, I would only count the items that were ordered around the same time as X or give them a higher weight. – Martin Jambon May 22 '16 at 08:27
4

It's not too tricky. Assume you have the following tables:

  • Customers, primary key CustomerID
  • Products, primary key ProductID
  • Orders, primary key OrderID, foreign key CustomerID
  • OrderItems, primary key OrderItemID, foreign keys OrderID, ProductID

To find the products you seek, you need to find the set of customers who have bought that particular product ID:

SELECT CustomerID
FROM (Customers INNER JOIN (Orders INNER JOIN OrderItems))
WHERE OrderItem.ProductID = <your product id here>

Then, you need to get the other products those customers have bought:

SELECT ProductID
FROM (Customers INNER JOIN (Orders INNER JOIN OrderItems))
WHERE (Customer = <given customer ID>) AND (ProductID <> <your product id>)

Then select the top few products and you're off to the races.

Note: I'm a numerical guy. The DB gurus will be able to do this in 1 query! :)

Drew Hall
  • 28,429
  • 12
  • 61
  • 81
2

You need history of orders so that you can check for other items that were bought together with the item user is currently viewing.

Eimantas
  • 48,927
  • 17
  • 132
  • 168
0

You need "Programming Collective Intelligence". They have some nice chapters about recommendations and such. You'll want to read about Pearson differences and other measures.

duffymo
  • 305,152
  • 44
  • 369
  • 561
0

Have a look at Algorithms of the intelligent web, chapter 3 "Creating suggestions and recommendations". For your question: optionally, you might need a table with user ratings for different items. Based on these ratings, you will be able to measure similarity between two clients and then perform an estimation based on these values on the items one of client is about to achieve. These estimations are used to rank the items.

Also, have a look at the Apriori algorithm chapter 4 or a general description of it here; this works for items bought together and extract some associations rules. Based on these rules, you will detect which of the items you sell might be added to the client's basket. For your question: no additional field should be added to your database; you have to maintain only a table to group items purchased together (market baskets contents).

lmsasu
  • 7,459
  • 18
  • 79
  • 113
0
select `A`.`ORDER_NO`, `A`.`SKU`, `B`.`SKU` `REL_SKU`
from `order_detail` `A`
inner join 
    (select DISTINCT `ORDER_NO`, `SKU` 
        from `order_detail`) `B`
    on `B`.`ORDER_NO` = `A`.`ORDER_NO`  
WHERE `B`.`SKU` = 'XXXXXXXX'
  AND `A`.`SKU` <> 'XXXXXXXX'

This works for me. of course add in any filters in the where clause as appropriate such as order status etc...

Hetti
  • 45
  • 1
  • 4