Fastest Growing FMCG Brand Streamlines Sales Data and reporting system with MSBI and Power BI
Our client is an Indian food delivery company based in Noida, Uttar Pradesh. It is India's largest and fastest-growing food services company, with a network of 1400+ restaurants. The company is the market leader in the organized pizza market, with a 67% market share in India.
Intending to multiply its growth prospects, the stakeholders were looking for a solution to bring faster time to value on their sales analytics applications and decrease the reporting turnaround time.
They partnered with Polestar and implemented Microsoft Power BI for reporting and Power BI for dashboards to visualize and understand their data in a more unified way to ensure conformity.
Owing to increasing competitive pressures in the segment and customer loyalty, the client wanted to have a better understanding of their sales data by harnessing data at each level through a world-class data architecture
At org level and right till the granular level sales target are being achieved - the supply chain and the entire procurement works accordingly. They wanted to get accurate sales forecasting and achievable targets to optimize their sales data. They were encountering data volume challenges because their data was coming from different sources, due to this, they were not able to identify the buying behavior at each store. They were unable to analyze data pertaining to each invoice lining.
Given the huge volume of data, the client was encountering numerous problems in terms of the amount of data running through their systems. It became a challenge for them to analyze their sales, generate reports and optimize cube storage. As a result, there existed numerous data silos in the organization.
They were struggling with their reporting solutions as they didn't have any streamlined processes in place. The client was encountering issues in receiving humongous amounts of data around- 3 lakh orders per day. The turnaround time from the available reporting system was very high. The reporting process that was in place was taking a lot of time to give visibility to regular KPI's, which was around - 30 mins to 1 hour.
Furthermore, the client had to collate data from stores in multiple files and formats, which significantly reduced productivity and did not provide analysts with a holistic view in their Redshift (AWS) and existing order line. And, to run those pipelines, it took around 30-45 minutes, but they were not accurately receiving data.
Moreover, the client was struggling with erroneous coding and hardcoded values in queries which were consuming a lot of time.
In developing the solution, we have done complete process understanding by doing the bottlenecks analysis at a granular level. We thoroughly analyzed to identify areas for optimization, which helped eliminate downtime, get intuitive reports at a multi-dimensional level for intelligent decision making.
Initially, we have worked on their cube optimization for a month. Previously, the process which was taking around 3-4 hours to refresh the two cubes, now it takes just 20 minutes. The optimization is conducted on various parameters such as - removing calculated columns, partitioning tables and the parallel processing of partitions. Furthermore, we have divided the bandwidth into sections and users from scratch. And we have also added jobs in their existing system.
By refining, integrating and transforming all of the sales data into one system from different sources, the company no longer had to question the validity of its sales data. Our BI consultants have brought the data into the data lake where the data of POS: Sales & SKU wise data can be pulled out in seconds for all Restaurants and in an easy way. Now, the Cube file can be accessed from restaurants, cities, and regions. With the successful role-based access implementation, they can view and access the data of restaurants for different locations.
We’ve also implemented DMS of AWS into their system to read and maintain logs. And we've also set different tables and dumped all the data load into S3. Further, we've used AWS Lambda (Serverless technology) to easily integrate all CSV files in their stack to create folders and locate the data to automate the business process.
We thoroughly worked and optimized their existing pipelines (order lines) by collating data from different sources. And, with proper use of dimensions, our team worked hard to remove hardcoded values from queries to make the entire process smooth.
By streamlining and optimizing the architecture we ensure that data volume no longer remains a challenge and allows the organization right from the headquarters to the store level to get a view of their data at a rapid pace. They are now able to analyze raw data themselves, respond more quickly to changes in market trends and perform analysis to determine those shifts in the market. By securing quicker access to their data with the new solution, now the entire sales and reporting system is streamlined with more improved data architecture.
With our successful analytics solutions, we've given them an overview of their organization's sales performance from multiple aspects such as by customer, geography, product, sales representative, etc. It allows executives to get a pulse on how the business is doing while giving managers and analysts the ability to dive deeper into the data to efficiently identify what strategies are working and find out exactly where improvements are needed.
Furthermore, deeply insightful Power BI based analytics dashboards helped them to get a holistic view of their customer sales data to analyze the entire sales process. Keeping everything in a systemized way, we have enabled live reporting with Power BI in the organization. Now they can dig into KPIs within and across the organization.
With the new solution in place, the company can now process sales reports faster, reducing turnaround time, reports that used to take 3-4 hours, get delivered in just 10-20 minutes.
With our successful implementation in place, we have reduced around 2230 Man hours on a daily basis and 8.2L Man hours annually.