Contributed By: VAIBHAV SHANKDHAR
I am Vaibhav Shankdhar. I am from Lucknow, Uttar Pradesh. I have almost 5 years of experience in the analytics field. Currently, I am working with WNS global services in Gurgaon as a Lead Data Analyst. I help our clients with dashboards, reports and insights generated from the same. All this helps the stakeholders take informed decisions about the next steps. Before PGP-DSBA, I was working in the Data Analytics domain itself. The tools used by me were Power BI and Advanced Excel. With this course, I wanted to explore the data field more.
Problem Statement: I work for a pharma client and help them in doing descriptive analytics for consumption sales of their products. We came across a requirement from one of the stakeholders that along with the normal EDA that we do of the past 36 months of data, we shall also include a forecast of the next 6 weeks into the future. The data we receive is from the past 36 months and is bifurcated on the basis of country, category, sub-category 1, sub-category 2, sub-category 3, Brand, Manufacturer, Trade sector, stock keeping units (SKU), value sales, unit sales, normal distribution etc. We usually do a detailed descriptive analysis of this data for our clients.
Now, we received a requirement of forecasting the consumptions sales of their products, 6 weeks into the future. This forecast was to be showcased on a dashboard along with the trend shown in the past 36 months. This problem was a new challenge to us as we haven’t had a requirement like this before. It was the trust that the client had shown toward our capability. The stakeholder who asked for it had already got a lot of projects for us and we wanted to finish this for him as well.
Tools and Techniques Used: The first step was to get the entire data extracted into an excel file. Understand the variables and KPIs and check for errors in data. The next step was to import the dataset onto python and perform data cleaning. Once the data was cleaned, deep dive into the dataset was done. Understanding the distribution of continuous variables and frequency of categorical ones. Then the dataset was made model-building ready. The outliers were treated with lower and upper ranges by making sure it was okay from the client. The missing values were treated accordingly and the final data frame was ready to build the model. In the next step, a time series model was built which predicted the value sales for the next 6 weeks based on the past 36 months’ sales that we had. This was then fed to the power BI dashboard where it was neatly visualized.
Insights: The basic insight was to get knowledge of how the sales would turn up in the near future in a given market and a given category so that informed decisions could be taken by the upper management.
For example, let us suppose Sensodyne’s consumption was going well till Feb and it showed a decline in March and April and has started rising again. If our prediction shows that it will decline again in the next 6 weeks, the management can check the decline in March and why it happened so that the same pattern is not repeated again.
Also, if the pain category is showing a decline in a particular country, the management can dive deep into the sub-category, to the Brand to the Products and find out which product or brand or sub-category is responsible for this downfall and they can start working on it. Let us suppose the Pain category has 2 products, Iodex and Volini. And although Iodex shows a good increase in the upcoming weeks, Volini is bringing down the overall sales due to which pain category is showing an overall decline, so the management can work on Volini distribution and its sales.
Solutions/Recommendations: There were a lot of recommendations provided across various markets using this Forecast Dashboard. New product sales were tested across markets using this dashboard.
Driver and Drainer products were identified using this dashboard. Trade sectors like Pharmacy and Ecommerce were compared which is driving and which is draining the sales.
The impact generated: A lot of products were bringing down the sales of the entire category or entire market. It became very easy to identify the drainer products and check for the shortcomings involved in their sales. The standoff was a new product launched by our client which was a drug-free product and was assumed to be doing good across all markets was found to be only doing extremely well in a few markets while its performance was bad in the majority of the markets. It had an overall increase in sales only because of a handful of markets but it was poor in the majority which was a bad sign.
The Forecast Dashboard was highly praised and a lot of appreciation was given by a lot of people. The dashboard is in great use today as well and people are working on its betterment every day which is a huge compliment in itself for me.
