How to Store "Customers Who Purchased This Also Purchased"

I'开发者_如何学运维m building a functionality like Amazon's "Customers Who Purchased This Also Purchased". I have about 6 years of orders to mine for this data, and obviously continue to update with data from new orders.

A few questions come to mind:

1. How do I store these relationships? I'm thinking a simple table with productA, productB, and count (or rank). Is this sufficient?
2. I dont think older data is as relevant as newer data. How do I prioritize newer data?

Edit: This site sells only one type of product, so pretty much everything is relevant, no need for filtering. I'd also like to keep this as simple as possible - the data is already in my database, i'm looking for the simplest way to calculate and store it.

You could use easyrec for your task. It wills store the relations in the following format:

CREATE TABLE `itemassoc` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tenantId` int(11) NOT NULL DEFAULT '0',
  `itemFromId` int(11) NOT NULL DEFAULT '0',
  `itemFromTypeId` int(11) unsigned NOT NULL DEFAULT '0',
  `assocTypeId` int(11) unsigned NOT NULL DEFAULT '0',
  `assocValue` double NOT NULL DEFAULT '0',
  `itemToId` int(11) NOT NULL DEFAULT '0',
  `itemToTypeId` int(11) unsigned NOT NULL DEFAULT '0',
  `sourceTypeId` int(11) NOT NULL DEFAULT '0',
  `sourceInfo` varchar(250) DEFAULT '0',
  `viewTypeId` int(11) unsigned NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `changeDate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_itemassoc` (`tenantId`,`itemFromId`,`itemFromTypeId`,`itemToId`,`itemToTypeId`,`assocTypeId`,`sourceTypeId`),
  KEY `idFrom_assoc` (`itemFromId`,`itemFromTypeId`,`assocTypeId`,`tenantId`),
  KEY `recommender` (`itemFromId`,`itemFromTypeId`,`itemToTypeId`,`assocTypeId`,`tenantId`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=38480 DEFAULT CHARSET=latin1 COMMENT='Table containing item associations'

Which is basically

1. ITEMA
2. ASSOCTYPE (for example: BOUGHT_TOGETHER)
3. ITEMB
4. ASSOC VALUE (strength of the recommendation)

easyrec could import your "BUY ACTIONS" and calculate the rules out of it.

"How do I store these relationships? I'm thinking a simple table with productA, productB, and count (or rank). Is this sufficient?"

This is not sufficient, really. The best is to use the semantic of the object

So get the data associate to the object (like for a book: the fact it's a book, written by xxx, writing style, kind of book...), and see the relation to other data which bring you to another object (like this kind of book is associate to this kind, or this artist to this artist, or the both, ect...). That is a really really hard work to do.

After you can choose to do your own, but it may not be as much relevant as you want.

I really think you should see what already exist (on sourceforge or github for example).

look at http://taste.sourceforge.net/

Taste is a flexible, fast collaborative filtering engine for Java. The engine takes users' preferences for items ("tastes") and returns estimated preferences for other items. For example, a site that sells books or CDs could easily use Taste to figure out, from past purchase data, which CDs a customer might be interested in listening to.

Google also has a prediction api that can be tailored for your usecase. Check their scenarios here

Personally I wouldn't store this data. I would create a view that dynamically selects products to be suggested.

One simple implementation might be:

1. Select a representative number of people who also bought the same product (E.G. 1000)
2. Based on those users what are the top N products that they all bought.
3. Suggest those products to the user.

You could simplify it by cutting out step 2 so that you just show other products that have been purchased regardless of popularity.

As suggested by Simon Marc you can make this more sophisticated by filtering your products by their criteria.

As for older data, perhaps items can have a use by date or redundancy flag which means they are filtered from the selection.