Blinkit Grocery

Blinkit Grocery

Business Requirement: To conduct a comprehensive analysis of Blinkit’s sales performance, customer satisfaction and inventory distribution to identify the key insights and opportunities for optimization using various KPIs and visualizations in Power BI.

KPIs Requirements:

Total Sales: The overall revenue generated from all items sold.
Average Sales: The average revenue per sale.
Number of items: The total count of different items sold.
Average Rating: The average customer rating for items sold.

Aim:

To identify the patterns in consumer purchase behavior.
To identify which items generates high profits and which items needs attention.
To identify the factors affecting the total sales.
To build strategy for improving the overall profits for the organization.
To find out correlation.

Objectives:

To analyze the impact of fat content on total sales.
To identify the performance of different items in terms of total sales.
To evaluate how outlet size, location type and segment affects the total sales.
To identify how the age of outlet establishment influences the total sales.
To find out the correlation between Avg Rating and Avg Visibility for different items and Total Sales and Avg Visibility.

This project involves creating interactive dashboards to analyze Total sales, Average Sales, Number of Items sold and Average Rating. The dashboard focuses on analyzing distribution of fat content, Outlet size, Outlet Type and Outlet establishment year based on above four metrics. It provides a comprehensive overview of all item types, highlighting total sales, sales trends, item ranking by sales, and similar distribution metrics for fat content. These dashboards facilitate data-driven decision-making by providing clear visual representations of sales performance and item distribution. The project aims to help stakeholders understand market trends and optimize inventory management.

Steps Involved:

1. Cleaning Data

Changing column Fat Content: The multiple values with same meaning are converted into one common value in column Fat Content.

2. Data Analysis

Used DAX functions, measures, and calculated columns to perform detailed analysis.

The DAX functions for three measures created are:

Total Sales: SUM('BlinkIT Grocery Data'[Sales])

Avg Sales: AVERAGE('BlinkIT Grocery Data'[Sales])

Number of Items: COUNTROWS('BlinkIT Grocery Data')

Avg Rating: AVERAGE('BlinkIT Grocery Data'[Rating])

Observations:

3. Metrics

The total number of sales transactions made is $1.20M.
The average sales is $ 141M.
The average customer rating is 3.92 out of 5.
The total number of items available for sale is 9K.
Fruits and Vegetables has the highest sales of 178K.
Snack Foods has 173K sales is second highest.
The total sales is highest for medium outlet size with 42.3% and least sales of 20% in high outlet size indicating the preference of consumers for medium size outlets. There is an opportunity to improve the sales in high outlet size.
The outlet establishment year influences the sale with peak sales in the year 2020 and decreased sales in other years which remained constant.

4.Insights:

Sales of Low/Regular Fat content items: Doughnut chart

The total sales of low-fat and regular items is shown below. The chart displays that 64.6% of total sales are of low-fat items and 35.4% of regular items. This chart indicates that the consumer are health-conscious preferring low-fat products over regular fat products. The similar behaviour is depicted in the bar chart of Outlet Type.

Sales of Item Type: The sales chart of different item is shown below. It indicates that highest total sales is of Fruits and Vegetables, followed by Snack Foods, Household items and Dairy products.

Box Plots: Item Type The sales distribution for each item is analyzed in figure below.

No outliers are present in the dataset.
The average sales of household items is highest with a value of 149 sales following with the sales of dairy products and starchy products with a value of 148.
The median sales of Starchy foods is highest which is 167 and Baking goods is lowest median value of 112. The next higher sales median is of following items: Seafood, Breakfast items and Household with values 158, 155 and 153 respectively. The next items after lowest sales median are Health and Hygiene and Bread with values 128 and 129 respectively. This indicates that sales performance of Starchy foods, Seafood, Breakfast items and Household is relatively higher as compared to Baking goods, Health and Hygiene and Bread.
The boxplot of Health & Hygiene is smallest as compared to other boxplots where 75% of sales are below 164. All other item categories have bigger boxplots with more Q3 percentile.
The average sales of baking goods is minimum of 126. It is followed by Health & Hygiene and Soft Drinks with values 131 for both.
The variability of sales is highest in dairy products and household items as compared to other items. It indicates that sales for these is large variation in sales of these products.

Analyzing Items category to improve sales:

The sales of following items need to be improved: Health & Hygiene, Baking Goods and Bread.
As the majority of consumer behavior is health-conscious. Therefore, among above three items categories Health & Hygiene can be targeted for improving sales because other categories consists of unhealthy products. Therefore, Health & Hygiene category will be more appealing to health-conscious consumers.
Health & Hygiene: The average sales = 131 and median = 128. The sales follow a normal distribution. The boxplot is small indicating that sales is concentrated to average of 131 and spread is less varying between 95 and 164. The spread is less indicating the stability in sales for this category.

Boxplot: The box plots of different groups of Outlet size, Outlet type and Outlet establishment year are similar in respective categories. There is no significant difference in the sales distribution of categories of above (Outlet size, Outlet type and Outlet establishment year) variables.
Line chart: The line chart of total sales, Avg sales, Avg Rating and Avg Visibility w.r.t. outlet establishment year is shown in figure below. This indicates that total sales have increased from 2011 to 2012, after which it remained constant till year 2017. After 2017, total sales increases to $ 20K till 2018. The total sales are decreasing from 2018 till 2020, after which it remained constant. There is a need to improve total sales for improving the profits. The No. of items, Avg Sales, Avg Rating and Avg Visibility are constant throughout all the years without any variation.
Table: The table matrix of total sales, number of items, Avg sales and Avg Rating w.r.t. Outlet Type is shown in figure below. This indicates that Supermarket Type1 has highest total sales and number of items sold whereas all other outlet types have almost similar total sales and No. of Items. The Avg Sales, Avg Rating and Item visibility for all four categories of outlet type is similar with values 141, 4 and 0.06 respectively. The only exception is that Grocery Store has highest visibility of 0.1. Therefore, it is required to improve sales in Supermarket 2 and 3. The Avg visibility of Supermarket 1,2, and3 is less so improving the visibility can improve the sales. The Avg item visibility of Supermarket 1,2 and 3 needs to be improved. The Avg Visibility of Supermarket1 can be worked upon for increment which will further increase the sales in Supermarket1. As the visibility of grocery store is highest and Avg Rating is high, still total sales and No. of Items is less. It indicates that people prefer to visit Supermarket for shopping instead of Grocery store.
Table: The table matrix of total sales, number of items, Avg sales and Avg Rating w.r.t. Outlet Type is shown in figure below. It indicates that the High Outlet Size has minimum total sales and number of items. The Avg sales, Avg Rating and Item visibility are similar for all three sizes of outlets. Therefore, it is required to improve the sales in High Outlet Size.
Table: The table matrix of total sales, number of items, Avg sales and Avg Rating w.r.t. Outlet Location Type is shown in figure below. It indicates that the total sales and No. of Items is more for Tier 3 Outlet Location whereas Avg Sales, Avg Rating and Avg Visibility is same for all three Outlet Location Type. Hence, Tier 1 and Tier 3, Outlet Location Type require to be aimed for improving the sales.
Line Chart: The line chart of Avg Item visibility vs Outlet Establishment Year is shown in figure below. It indicates that Avg item visibility was highest in 2011 at 0.1 which dropped in 2012 till 0.06. After 2012, visibility remained constant till 2017 which increased to 0.08 in 2018 which again dropped to 0.06 in 2020 and since then continuing at constant visibility. The Avg Item Visibility of all items needs to be improved in order to improve the total sales.

Conclusions:

The current situation of business performance is good with more than $1M of total sales and average rating of 3.9
There exists a strong consumer preference for low-fat products indicating the health-conscious buying habits of consumers.
Fruits, vegetables and snack foods are the most popular categories suggesting the opportunities for expansion or promotion in these areas.
The Health and Hygiene category has least sales performance requiring attention.
It is required to improve sales in Supermarket 2 and 3. The Avg item visibility of Supermarket 1,2 and 3 needs to be improved. The Avg Visibility of Supermarket1 can be worked upon for increment which will further increase the sales in Supermarket1. As the visibility of grocery store is highest and Avg Rating is high, still total sales and No. of Items is less. It indicates that people prefer to visit Supermarket for shopping instead of Grocery store.

It is required to improve the sales in High Outlet Size. Tier 1 and Tier 3, Outlet Location Type require to be aimed for improving the sales. The Avg Item Visibility of all items needs to be improved in order to improve the total sales.

Model Link: https://drive.google.com/drive/folders/1LZ3hB-1X2Ne9PNg4WjeYfBO9YSI58cuG?usp=drive_link

Screenshots::

Download