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

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..

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, DataDesignCenter’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.

DataDesignCenter’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).

DataDesignCenter 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).

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

As data integration from multiple systems is useless without a well-established data quality management process, DataDesignCenter’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)

To ensure data security, DataDesignCenter 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.

In the course of the delivered data analytics services, DataDesignCenter`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.

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.

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

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

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

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