Thursday, 24 October 2013

Product recommedation engine built in sql server

Hi there, yes the topic of today's discussion is a product recommendation engine built in sql server.

It seems a strange concept to most I'd imagine. Most people would only talk about such a thing in programs such as R, python or hadoop because they imagine that only these programs hold the processing power to do such things. However, the reality is that unless you hold the data of google and you are looking to build a search engine from scratch, you probably don't need hadoop. R and Python are brilliantly powerful tools and I don't discount them for a minute, however, if you come from the humble background of a sql developer/analyst, you can achieve some great results without needing to learn a whole new language even though they may be quite attainable.

Warning
So I'll warn that matrix factorization in sql requires a lot of repetitive coding. The reality is that if you also have a good grasp of excel, you can probably do all the leg work there. The key is to have your list of products that you wish to build your recommendations off as well as your customer table and your transaction table handy. With those at hand it is simply a coding issue and if you're handy with sql this shouldn't be an issue.

Use case
I've built a product recommendation engine based on 3.3 million customers and 20 products. The end result is a product recommendation on what a customer would like to purchase based on what the customer would like to purchase next. This is generated from the cohort effect (ie what other customers similar to them have purchased). It also removes the purchases they have already made so as to remain relevant.

Benefits
The benefit of the way this was developed was that it is easy to run and re-run on a as desired basis so that campaigns can be generated off the back of them. A lot of recommendations within CRM's are generated from data outside of an organisation such as census data, credit bureau data, as well as other intel collated via list brokers. This method however sources the richest form of data available to an organisation which is its own transaction data linked to its customers. Many will discount this as hard to decifer but the reality is that if you compare the r-squared values of transactional data to any other form of purchased data to the likelihood of sales, you'll find that transactional data wins out hands down.

Relevancy is key
The fact is a lot of communications coming out of organisations are promotion or product based. This means that either an offer or sale is being promoted or a new product is. So the centre of the communication is either the promotion or the product. Unfortunately, in these examples the individual consumer is not the first though, and in many examples, the individual consumer is not the thought at all.

Consumer centric marketing
Imagine a world where Google only served you the search results they wanted to show you based on what their advertisers wanted to pay for. Or imagine a world where Amazon would show you only what the highest bidder would pay to promote their latest book. This is what product centric marketing looks like and is a polar opposite to what either company does.
The reality is consumer centric marketing is key. This can be achieved in a variety of ways but I guess the most successful companies in this space have achieved this via product recommendations tailored to each and every individual customer. The reason is that this can be achieved in a fairly automated fashion.

How to make this work in your environment
The simple answer is run matrix factorisation over the products you wish to. Then summate this across the products your customers own. Next remove the products your customers own from the recommendations. Then viola, you have built your own product recommendation engine.

Get in touch for more information on how to make this work in your organisation.