CODEWITHSUNDEEP
mysqlrecommendation-engine
shin
shin

Reputation: 32721

What do I need in a database for "Customers Who Bought This Item Also Bought"?

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?

Upvotes: 10

Views: 9871

Answers (7)

Hetti
Hetti

Reputation: 55

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

Upvotes: 0

lmsasu
lmsasu

Reputation: 7583

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

Upvotes: 0

duffymo
duffymo

Reputation: 308763

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.

Upvotes: 0

Drew Hall
Drew Hall

Reputation: 29045

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! :)

Upvotes: 4

Alix Axel
Alix Axel

Reputation: 154543

Here are some similar questions:

Upvotes: 13

nickf
nickf

Reputation: 546035

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.

Upvotes: 6

Eimantas
Eimantas

Reputation: 49354

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

Upvotes: 2

Related Questions