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
I propose to design two tables to collect those data:
It stores the ordered related products for a product.
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.)
One2Many is added on
product.product to list the related products ordered by weight descendant.
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
MultiValue 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: