Customer: Customer is a leading European beauty products and cosmetics manufacturer having sales branches in 24 countries of the world.

Industry: Retail

Challenge: The customer is an active player on the market since 1997. And during those years the company has gathered a large volume of statistical data on its products sales in different regions.

In order to increase volume of sales the customer needed a tool that allows processing and analysis of this data. Having historical data analyzed would allow the customer to duly plan its marketing/sales activities and provide the ability to forecast how to increase the sales in various regions on various product groups.

Solution: Our team has developed a software solution that measures the effectiveness of previous sales efforts taking into account organized sales campaigns, direct sales, other factors. The forecast for future sales is done based on the provided historical data.

In database the information for the past 15 years on the performed sales/marketing activities was stored, as well as the received output (in terms of increased volume of sales, profit, etc). Moreover, the system allows to add information on the planned sales campaigns, on the growth of the sales team (sales agents), pricing tendencies on the market and other. The system algorithms analyze this information as well as previous data, and provide future forecasts. The forecasted figures are: sales volume, profit, recommendations for the number of active agents, etc.

Some technical details on the developed solution are below:

The solution is a distributed application that consists of a web role (website) and a worker role (virtual machine(s) with installed RScript software and a special windows service). Both parts of the solution – website (visual to the user) and back-end (where calculation based on the mathematical algorithms is performed) – can be scaled independently.
OLAP data is stored in Microsoft Server Analysis Services in Azure.
Mathematical forecast algorithm is developed with RScript programming language.
In order to avoid loading peaks we use Azure Service Bus to smooth load and provide scalability.
User can define the influence parameters through user interface or import them from Excel file.

Solution stores forecast requests in Message bus queue.

Windows service gets forecast requests from Message bus queue, executes math scripts with defined parameters (with error handling and timeout support), stores forecast results in MSSQL database and sends notifications “forecast complete” to web server.
Web server uses SignalR library to provide real-time information about forecast progress to client browser. Also web server provides table and chart visualization of forecasts and exports data to Excel files. (And, of course, a lot of linked functionality – forecasts history, viewing status of currently calculated forecast, and etc.)
Since forecast creation is a long running operation we store forecast results in a database and, if similar forecasts requests are detected, the system proposes a choice to the user: either to use the previously saved forecast or recalculate the forecast.

Technologies:

Web site: ASP.NET MVC 5, Entity framework 6, Unity IoC, epplus (Excel integration), NLog, SignalR, Jquery, JQuery UI, Knockout js, underscore js, hightchart js, Azure SDK.

Windows service: Microsoft Task Parallel library, Azure SDK, Entity Framework 6, Nlog.

Duration: 6 months