Product recommendation is a powerful selling tool. The goal is to upsell addition product.
There are many ways to design the recommendation feature (e.g. based: on product in cart/sale, on current product, on browsing history, search result etc.). But we can see that most of the case are based on two main data:
products related to a product
product popularity
Proposal
I propose to design two tables to collect those data:
product.product.related
It stores the ordered related products for a product.
origin: Many2One to product.product
target: Many2One to product.product
weight
manual: Boolean
It is filled by a cron job using the result of a customizable SQL query.
The job delete all non manual record before inserting new one.
By default, the SQL query count the number of times a couple of products are present a confirmed sale order. The count result is used to fill the weight.
(The query must be customizable to allow other couple input like browse history etc.)
A One2Many is added on product.product to list the related products ordered by weight descendant.
product.product.popularity
product
popularity
It will also be filled by a cron job using a customizable SQL query which returns the count per product on the sale line (at least confirmed).
It will be the base of a popularityMultiValue field (which could be extended to support company or web-shop criteria).
The period on which the cron job operate will be a configuration parameter.
In order to be fast, both jobs will perform at SQL level (skipping) the ORM.
I propose also to have the same tables but for product category: product.category.related and product.category.popularity.
I would add the possibility of having more than one relationship. This would look like:
origin : Many2One to product.product
target : Many2One to product.product
weight
manual : Boolean
type: Selection
The reason is that in many cases you want to show customers not only products with one kind of relationship but several of them. For example, Amazon shows:
Sponsored related products
Products browsed by customers that browsed this product
Products purchased by customers that purchased this product
Or for one of our customers we implemented (in the same website):
I do not see the point. Why does the customer need to know based on what you are recommending. It does not chance the behavior to select or not. More over, it does not have to be true neither.
They will not be filled the same way. Indeed such link is more about category.
For me it is pretty clear that it is not the same that some recomendations are based on sponsorization or not.
The products recommended because they were purchased by people that purchased the product will probably link to products that complement this one. For example, some headphones for an iPod.
Whereas “browsed by those who browsed” will probably bring you to competing products. Not necessarily in the same category. Or with finer granularity than category-level.
Of course, but I don’t see why that is important here.
Building a successful online shop is a lot about trial and error and seeing what works best. The fact that top salers like Amazon, Zalando and many others use not one but several recommendation lists most probably means that it is something that helps improving sales.