Data Management and Analytics Solution for a Global Hair Care Brand

Client

A global hair care brand with an extensive retail presence

Industry

Retail, eCommerce

Services

Data architecture, data management, data platform

Tech

Snowflake, DBT, Stitch

Challenge

A renowned hair care brand operating globally aimed to gain deeper insights into product sales and customer behavior across locations. However, they faced a challenge sourcing these insights due to flaws in their data management infrastructure. The primary challenge lay in the fragmented nature of their data. Despite having a data warehouse implemented on Redshift, the company failed to establish it as a single source of truth. To fetch data from an ERP system and Shopify (where the company’s ecommerce solution was implemented), they relied on web scraping. Unfortunately, this method often resulted in data transfer errors, especially when changes occurred in the user interface of these data sources. Another factor contributing to the challenge was the absence of a standardized mechanism for data transfer from retailers. Retailer data was typically exchanged in Excel format, which heightened the risk of human errors during the process. Furthermore, the customer had already implemented a data analytics solution using Tableau. However, due to the frequent errors and discrepancies in the source data, this solution failed to provide reliable insights. To address the challenges, the brand turned to ITRex. Together, we embarked on a journey to overhaul their existing data infrastructure. This involved establishing fail-proof data transfer mechanisms, transforming their data warehouse into a single source of truth, and introducing a new data analytics service.

Specifically, ITRex took on the following challenges:

●

Enhancing the existing data warehouse solution

●

Optimizing extract, transform, load (ETL) processes

●

Optimizing data source integration

●

Improving retailer data management

●

Redesigning the existing data architecture for maximum reliability and confidence

Solution

To address the customer’s challenge, the ITRex team of data engineers orchestrated a complete overhaul of the existing data management and analytics infrastructure. The proposed solution involves several key components:

●

Data warehouse enhancement: Substantial improvements are made to the existing data warehouse to resolve recurring issues and improve data integrity. In particular, we are executing migration from Redshift to Snowflake, a single platform for data warehousing, data engineering, and data application development that boasts instant scaling and better maintenance as compared to the customer’s existing system.

●

ETL process optimization: ETL processes are redesigned using DBT, a data build tool that acts as an orchestration layer on top of a data warehouse. This will help streamline data transfer and ensure higher data quality.

●

Data source integration: We are replacing outdated data transfer methods, such as web scraping, with API-based data transfer. This will help ensure a more reliable and consistent data collection. The implementation of Stitch, a cloud-based, open-source platform for rapidly transferring data, will facilitate the connection between the newly migrated data warehouse and crucial enterprise data sources, including Shopify and the NetSuite ERP system.

●

Retailer data management: We are undertaking to design a new approach for retailers to automatically transmit sales data in a standardized format, eliminating manual data collection and errors.

Impact

●

The integration of modern data management practices and ETL optimization is set to provide the company with high-quality, reliable data that creates a sense of trust among the customer’s employees.

●

The transformation of the data warehouse into a source of truth paves the way for advanced analytics, enabling more informed decision-making.

●

The transition from web scraping to API-driven data acquisition ensures faster data collection, especially in response to changes in the source.

●

Enhanced data collection from retailers is expected to reduce manual work, errors, and data discrepancies.

●

The company is expected to gain deeper insights into product sales, regional performances, top retailers, top selling products, sales volume, prices, and more. These insights can drive strategic decision-making and help increase the bottom line.