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

Customer

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

Challenge

The Customer 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 Customer’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 Customer with a proof of concept: based on the Customer’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 Customer 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 Customer’s requirements to the solution, such as scalability, performance, availability for both mobile and desktop users. As the Customer 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 Customer 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 Customer’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 Customer could already benefit from interim deliverables. Overall, we developed 90+ reports for the Customer’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 Customer 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 Customer 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 Customer with comprehensive support. For example, we provided training on configuring and working with OLAP cubes and adjusted ETL processes after the Customer’s third-party analytical vendors introduced some changes on their side.

Results

With the developed analytics solution, the Customer 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 Customer 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 Customer 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 Customer 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 Customer 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 Customer was able to define the employees’ quality of work.

Technologies

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