INSIGHTS FOR A RETAIL COMPANY: A USE CASE ANALYSIS.
By Paula Obeng-Bioh — Business Intelligence Associate
- INTRODUCTION
In today’s competitive retail environment, an in-depth understanding of sales data is vital for informed business decisions. This report was generated using sample data from a supermarket to highlight how data can be harnessed for data driven decision making to optimize operations and increase revenue.
The dataset includes historical sales data covering a three-month period (January to March 2019) from three branches; Branch A, B and C of a retail company located in different cities.
The objective of this exercise was to gain insights on the overall performance of the supermarket but more specifically,
Identify the most performing branch.
Assess popular product categories.
Examine customer satisfaction.
2. SUMMARY FINDINGS
Insight 1: There is a positive customer rating; with an average of 7/10. Which suggests that the supermarket offers a good shopping experience.
Insight 2: Branch C stands out as the leader in sales.
Insight 3: The food and beverage category produces the highest number of sales.
Insight 4: E-wallet is the favorite payment method among customers.
Insight 5: There is a positive correlation between the number of products available and total sales generated per month.
Insight 6: Branch C received the highest rating and generated the most sales among all branches. Hence it should be considered for future expansion of the company.
Insight 7: Among all the product categories, Food and beverages had the highest rating from customers.
Insight 8: Sales go up significantly on Saturdays and Tuesdays.
Insight 9: Across all branches, daily transactions surge between 2pm and 4pm.
Insight 10: Branch C records the highest gross income for the company.
3. DATA PREPROCESSING
After examining the data, it was preprocessed to determine its quality and suitability for the analysis. We checked the number of rows and columns and looked out for missing values; the data had no missing values. Also, other transformations like parsing dates and creating additional columns were performed.
4. ANALYSIS AND INSIGHTS
Univariate analysis is the analysis of a single(‘uni’) variable without considering its relationship with other variables. Its major purpose is to describe; it takes data, summarizes that data and finds patterns in the data.
INSIGHT 1: SKEWNESS OF CUSTOMER RATING
The rating distribution is uniform, no skewness to the left or right side of the distribution. The mean and percentiles of the rating were also plotted. This showed a mean rating of 7 across all products.
INSIGHT 2: PAYMENT METHODS USED
The most used payment method is Ewallet.
Bivariate analysis is the simultaneous analysis of two(‘bi’) variables. It explores the concept of relationship between two variables, whether there exists an association and the strength of this association, or whether there are differences between two variables and the significance of these differences. This can help to draw important conclusions about the data.
INSIGHT 3: SALES PERFORMANCE OF THE 3 BRANCHES
Branch C has the highest sales. However, the difference in sales across the three branches is not much.
INSIGHT 4: MONTHLY PERFORMANCE
The first quarter exhibited fluctuating sales performance, commencing with a robust start in January where sales reached around $117,000. However, February witnessed a decline, with sales dropping to about $97,000, reflecting a dip in revenue compared to January. March showed a positive turn, rebounding with approximately $112,000 in sales, hinting at a promising recovery and potential for sustained growth in the subsequent months.
INSIGHT 5: QUANTITY OF PRODUCTS SOLD
The graph reveals a correlation between the number of products available and number of products sold since it follows a similar pattern to the monthly sales graph.
INSIGHT 6: CUSTOMER RATING OF BRANCHES
Among the branches, Branch A garnered the most favorable rating, evident in its tapered shape centered between values 6 and 9. In contrast, Branch C exhibited a balanced distribution of positive and negative ratings, spanning the ranges of 4 to 6 and 8 to 10. On the other hand, Branch B faced the least favorable scenario, marked by the most negative rating, attributed to the tapered shape concentrated between values 4 and 6. These distinctive patterns highlight varying levels of performance and satisfaction across the branches.
Correlation is a statistical measure that indicates the extent to which two or more variables are linearly related. A positive correlation indicates the extent to which those variables increase or decrease in parallel; a negative correlation indicates the extent to which one variable increases as the other decreases. Zero correlation implies no correlation.
INSIGHT 7: CORRELATION ANALYSIS
In the visual representation, black blocks signify minimal correlation between values in the columns, while orange blocks denote a positive correlation. Notably, the unit price demonstrates a significant correlation of 0.63 with cost of goods sold (COGS), and quantity exhibits a higher correlation of 0.70 with gross income. Pale blocks indicate perfect correlation between values within the same columns. Additionally, white bars represent null values, specifically in the comparison of gross margin percentage versus gross margin percentage. This analysis provides insights into the relationships and correlations among various data points, offering a comprehensive view of the dataset.
Further Analysis for Business Questions
Question 1: What category of products sold the most?
Answer: Food and beverages stand out as the most sold category, closely followed by Sports and travel products. This indicates customer preference within the supermarket product offering.
Question 2: What category of products should the supermarket focus on?
Answer: Food and beverages have a higher rating from customers than other categories. It was observed earlier that this same category makes the most sales. Hence it should be given more focus.
Question 3: What days and times are best to display an advertisement to maximize revenue?
Answer: Sales go up on Saturdays and peak again in the middle of the week across all branches with high customer engagements between 1pm and 7 pm.
Question 4: Which branch should be chosen for expansion?
Answer: Based on gross income, Branch C produces more hence the expansion plan should be based on this branch.
5. SUMMARY AND RECOMMENDATIONS
Summary:
In this analysis, Branch C came up as the sales leader, showcasing its excellent performance. The food and beverage category proved to be the highest revenue generator, highlighting its value in the supermarket’s product mix. E-wallet was displayed as the preferred payment method among consumers, implying a trend towards digital transactions.
Recommendations:
1. Prioritize Branch C: Allocate resources and techniques to further enhance the success of Branch C, leveraging its performance to drive a general sales growth.
2. Optimize Food and Beverage Offerings: Given its high sales, consider expanding or refining products within the food and beverage category to capitalize on customer preferences.
3. Enhance E-wallet usage: Promote and ensure seamless E-wallet transactions. By offering incentives to encourage a continuous use of this payment method by customers.
5. Maintain a Commending Customer Experience: With an average of 7/10 customer rating, ensure the maintenance and improvement of the supermarket’s shopping experience. Track customer feedback and attend to any areas that may affect satisfaction.
These recommendations seek to capitalize on the identified strengths and leverage opportunities to advance sales and customer satisfaction.
