A 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 and to optimize internal business processes. However, they couldn't achieve this with their data locked within multiple applications specific to each business direction.

Solution

Delivering a proof of concept

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

Preparing the conceptual solution design

INNERLUXES defined the high-level architecture components and outlined their main functions. The analytical solution was designed to be highly scalable: initially it would analyze five years of historical data, and in the future it would handle ongoing data growth. As the Client was concerned about data security, the solution was hybrid — hosted inside a private cloud in a data center.

Consulting on the data analytics solution

INNERLUXES recommended a technology stack that would satisfy the Client's requirements for scalability, performance, and availability for both mobile and desktop users. As some of the Client's legacy systems already ran on Microsoft SQL Server, we first checked whether this technology and the related Microsoft stack suited the solution — which would let the Client reduce implementation costs through fewer additional licenses.

Drawing on our knowledge of the retail industry, INNERLUXES also suggested enriching the solution with advanced analytics. For that, we delivered a proof of concept for a recommendation engine (the predictive model behind it would boost cross-selling and up-selling opportunities for the online store) and a time-series prediction model to forecast sales.

Implementing the 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 into several releases so the Client could benefit from interim deliverables. Overall, we developed 90+ reports for the Client's different business directions and user roles.

Managing data quality

As integrating data from multiple systems is useless without a well-established data quality management process, INNERLUXES defined rules applied during the ETL processes to:

• Merge master data such as customer profiles from different systems.
• Bring data to one format (for example, having either 'male' or 'female' instead of '1' and '2', 'M' and 'F', or 'm' and 'f' values from the source systems).

With these rules, the Client's data management process ran mostly automatically, while manual interventions by a data steward were still possible.

Setting user access control

To ensure data security, INNERLUXES also elaborated on user access control. We analyzed the highly flexible, tunable access model the Client envisaged before the project and concluded it would be unsuitable, as it would negatively affect the solution's performance (reports would take too long to produce). We therefore recommended a less complicated but still highly efficient three-level access model — for a business unit, a department, and an individual employee — which had no negative impact on the system's performance.

Supporting the data analytics solution

As part of the delivered data analytics services, INNERLUXES also provided comprehensive support. For example, we trained the Client's staff on configuring and working with OLAP cubes and adjusted ETL processes after the Client's third-party analytical vendors introduced changes on their side.

Results

With the developed analytics solution, the Client gained a 360-degree customer view across all channels and business directions, plus robust retail analytics, which allowed them to create a personalized customer experience. The Client was also able to optimize internal business processes by improving 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 customers' behavior and shopping preferences.
• Assess clients' recency, frequency, and monetary value.
• Identify their top clients.

Retail analytics (for both online and offline channels)

The Client was able to analyze:

• Traffic and conversion rates (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 relying on a shared document of stock levels and constant clarifications by phone, the Client could track actual stock levels both at the warehouse and in stores almost in real time. This transparency also positively influenced ordering and logistics processes.

Employee performance

With KPI and goal-management reports, the Client was able to gauge the quality of employees' work.

Technologies and Tools

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