The company is one of the largest sellers on India’s biggest online retail platform. It sells millions of products across 22 different segments including apparel, books, beauty, clothing, home improvement, industrial, jewellery, musical instruments, oce products, shoes, watches, wireless accessories etc.
THE CHALLENGE
Sellers on the online retail platform are provided with comprehensive data related to their inventory, sales, fulfillment, returns, financials, and more, on a daily basis.
As one of the largest sellers on the platform, the company receives several gigabytes (GB) worth of this daily data, in the form of text files or compressed files. The company’s category managers, business, supply chain, procurement, and finance users accessed this information using Excel reports, which were not in an easy to consume format or conducive for prompt decision making. These existing reports included limited Key Performance Indicators (KPIs), which made it challenging for the users to analyze the vast quantities of data and derive suitable insights in a timely manner. The various teams needed real time visibility across multiple dimensions, which was not possible with their existing IT architecture.
As the company’s operations continued to grow, they partnered with hundreds of new suppliers from across the country, thus increasing the total number of products which they sold on the online platform. In addition to this, the company also extended into new segments by tying up with different suppliers. With this rapid expansion, the size of their daily platform data feed grew exponentially, which made it even more difficult for the users to leverage the available data for optimizing performance while driving sales, revenue growth and profitability.
Overall, the company needed a new data warehouse to consolidate all their data, and a comprehensive Business Intelligence solution which would provide its users with an extensive range of KPIs, as soon as the source data was made available. The company also needed recommendations on a wide range of new KPIs to augment the existing ones.
THE SOLUTION
The team of architects, data engineers and data visualization specialists from Prescience Decision Solutions first analysed the platform data feeds and the existing KPIs from the Excel reports. After understanding the data present in each input file, our team identified new KPIs which could be created by joining previously unused columns from these standalone source files. The proposed solution included creating intermediary mapping files, which were required for ingesting more data than before. Complex calculations on the merged data in these mapping files produced the new KPIs for the different groups of users.
The team built a scalable data warehouse on AWS Redshift which integrated data from different internal and external source systems. The end-to-end business intelligence solution included business and IT dashboards which were built on Qlik Sense. The company’s users could now analyze the exact orders which were shipped, the profit margins on each of them and the underlying driver responsible for each unit sale. The inventory dashboards helped users understand the rolling inventory and ageing of the current inventory over various time frames. There were different dashboards for the sales of in-house brands or private labels, glance views or page visits for the company etc. A set of dashboards captured the company’s performance during their sale seasons. The IT dashboards helped the company understand their user access history, utilization of technical resources etc.
The team also submits a set of weekly business review reports which gives users crucial insights into all the input and output metrics that impact business operations, sales, profitability etc. The input business review report focuses on forward looking details such as the products available for sale, how many products were out of stock, which regions the products were unavailable in, glance views and so on, which help the company’s users immediately take corrective action, when required. The output business review report includes detailed analysis on the underlying factors responsible for each sale, the net profit margin per unit sale, as well as per category and segment etc, which helps the company understand its performance.
The Prescience team has also updated the architecture to integrate a new source file format, automated the code for conducting a sanity check on the source file before processing and ingesting it, automated the processing and handling of mapping files, integrated the API for Qlik Sense, and provided immediate alerts to users when updated dashboards are available.
The different technologies used for this engagement included,
- AWS Redshift
- Qlik Sense
THE IMPACT
With this new scalable data warehouse and business intelligence solution, the company’s users have near real time visibility into the behaviour of diverse customer segments, overall inventory levels, sales, financial metrics etc. The creation of these business reports has made it easy for the company to disseminate information to more teams and enable cross-collaboration, directly leading to quicker decision making. Some key achievements with different dashboards created for the client are:
- The pricing dashboards identify the actual drivers for each unit sale, giving users crucial insights into patterns in customer behaviour.
- The seasonal sales dashboards help the company understand what promotions are working, what optimizations need to be made etc.
- With the inventory dashboards, the users can not only find the products which are out of stock, but they can see the age of their excess inventory and plan new promotions to reduce their stock.
The automation of various processes has reduced the manual efforts required to manage the data, thereby allowing the team to pivot to business focussed initiatives.
