Big Data Solution for a 360-Degree Customer View and Optimized Stock Management

About Our Client

The Client is a US-based corporation running omnichannel retail, hotel, restaurant, and other businesses.

Challenge

The Client wanted to earn customer loyalty with a personalized approach, as well as to optimize internal business processes. However, they weren’t able to achieve this with the data locked within multiple applications specific to their business directions.

Solution

Delivering a proof of concept

As the solution-to-be was to serve all the Client’s business directions, collect and aggregate data from 15 different data sources, such as CRM, Magento, Google Analytics, dedicated hotel, restaurant and wellness systems, ScienceSoft’s team first provided the Client with a proof of concept: based on the Client’s ERP data, we created a set of sample analytics reports.

Preparing conceptual solution design

ScienceSoft’s team defined high-level architecture components and outlined their main functions. The analytical solution was planned to be highly scalable. Initially, it was to analyze the historical data for 5 years, and in the future it was to deal with data growth.

As the Client was concerned about the security of their data, the solution was hybrid (hosted inside a private cloud in a data center).

Consulting on a data analytics solution

ScienceSoft recommended the technology stack that would satisfy the Client’s requirements to the solution, such as scalability, performance, availability for both mobile and desktop users. As the Client already had some of their legacy systems running on Microsoft SQL Server, we first checked whether this technology and the related Microsoft stack was suitable for the solution-to-be, as this would allow the Client to reduce implementation costs (less additional licenses would be required).

Based on our knowledge of the retail industry, ScienceSoft also suggested enriching the solution-to-be with advanced analytics capabilities. For that, we delivered a proof of concept for a recommendation engine (the predictive model behind the engine was to boost the Client’s cross-selling and up-selling opportunities for their online store), as well as a time-series prediction model to forecast sales.

Implementing a data analytics solution

The implemented analytical solution consisted of the following components:

• A data hub to store both structured and unstructured data from 15 data sources.
• About 100 ETL (extract-transform-load) processes.
• A data warehouse to combine and aggregate data.
• An analytical server with 5 OLAP-cubes and about 60 dimensions overall.
• Reporting.

We split the implementation process into several releases to ensure that the Client could already benefit from interim deliverables. Overall, we developed 90+ reports for the Client’s different business directions and user roles.

Managing data quality

As data integration from multiple systems is useless without a well-established data quality management process, ScienceSoft’s team came up with the rules applied during the ETL processes and intended to:

• Merge master data like customer profiles from different systems.
• Bring data to one format (for example, to have either ‘male’ or ‘female’ instead of ‘1’ and ‘2’, ‘M’ and ‘F’, ‘m’ and ‘f’ values taken from the sources systems).

With these rules, the Client had their data management process running mostly automatically. However, manual interventions by a data steward were still possible.

Setting user access control

To ensure data security, ScienceSoft also elaborated on user access control. We analyzed highly flexible and tunable access model envisaged by the Client before the project start and came to the conclusion that it would be unsuitable, as it would negatively affect the analytical solution’s performance (it would take too long for the system to produce the desired reports). Therefore, we recommended a less complicated, though still highly efficient 3-level access model (for a business unit, a department, and a certain employee). The implemented model didn’t have any negative impact on the system’s work.

Supporting a data analytics solution

In the course of the delivered data analytics services, ScienceSoft’s team also provided the Client with comprehensive support. For example, we provided training on configuring and working with OLAP cubes and adjusted ETL processes after the Client’s third-party analytical vendors introduced some changes on their side.

Results

With the developed analytics solution, the Client benefited from a 360-degree customer view across all channels and business directions, as well as robust retail analytics, which allowed them to create a personalized customer experience. The Client was also able to optimize internal business processes by improving their stock management and assessing employee performance.

360-degree customer view across all channels and business directions:

Having all their data integrated, the Client was able to:

• Analyze their customers’ behavior and shopping preferences.
• Assess the clients’ recency, frequency, and monetary value.
• Identify their top clients.

Retail analytics (for both online and offline channels):

The Client was able to analyze the following:

• Traffic and conversion rates (i.e., most/least visited pages, pages with no traffic, pages with high traffic but low conversions).
• Online store visitors’ engagement.
• Wish list products, sales, and cart abandonment.

Stock management optimization:

Instead of having a shared document depicting the stock level and the necessity in constant clarifications on the phone, the Client was able to track the actual stock level both at the warehouse and in the stores almost in real time. This transparency in the stock level has also positively influenced the ordering and logistics processes.

Employee performance:

With KPIs and goal management reports, the Client was able to define the employees’ quality of work.

Technologies

Microsoft SQL Server, Microsoft SQL Analysis and Integration Services, Python, Microsoft Power BI.