Introduction
Starting on September 4th, I embarked on a journey into the world of data analytics While I had previously heard of data analytics, it wasn't until now that I truly grasped its significance.
Under the guidance of our knowledgeable instructor, we began with an in-depth exploration of Excel, and I must say, I was truly captivated. The depth of its capabilities and the wealth of resources it offers left me truly awestruck and eager to learn more.
Towards the end of the week, Jacob Ajala presented us with a challenging task - utilizing a dataset from a supermarket to create an insightful dashboard and deliver a compelling presentation, effectively narrating a story through our data.
Data Source
Here is the Link to the dataset.
Data Cleaning
I transformed the data into a table and then checked each column for blanks.
Modification of Data
Using the "text" and "IF/AND" functions, I converted the dates and times supplied in the original dataset into the days of the week and the time of day (Morning, Afternoon, Evening), respectively. I was also able to get the month(January, February, and March). This was done to make analysis easier.
For identification purposes, each newly formed column was presented in a different colour.
BUSINESS REQUIREMENTS
Recommended Analysis
What is the total income of the store for the provided records? (KPI)
How many orders were received for the provided records? (KPI)
What is the overall rating of the store? (KPI)
How many orders do we have each day? Are there any peak hours?
What time of the day do we get most orders? How much did we make in these times?
• morning is 9 am – 12noon
• afternoon is 12 noon – 4 pm
• evening is 4 pm – 9 pm
Break down the gross income by month, customer type, gender, product type, city and branch of store
Interactive review of the sales record using gender, customer type, city and branch of the store.
Key Insights:
Total revenue: $322,966.75
Gross income: $15,379.37
Average Rating: 7/10
Total number of orders: 1,000
Total number of items bought: 5,510
Overall best-selling product: Food and Beverages.
Time of day of most sales: Evening with 432 orders and $138,370
Peak Time and day of most orders: Saturdays with 164 orders and 7 PM with 113 orders.
It's worth noting that all three locations contribute nearly equal proportions of total income, emphasizing the importance of maintaining each one.
Additionally, I observed that morning sales are lower than other times of the day, likely due to people being occupied with work, business, or household chores.
Regarding monthly sales trends, January showed the highest figures, followed by a dip in February before another rise in March. This pattern may be attributed to New Year purchases, as customers often make substantial transactions at the beginning of each quarter.
Recommendations:
Consider hiring more employees to ensure adequate staffing levels during peak hours, reducing congestion and improving customer experience.
Strengthen customer service and actively gather feedback to identify areas for improvement, ultimately enhancing overall customer satisfaction and ratings.
Establish an online marketplace to facilitate convenient shopping from homes and workplaces, potentially addressing the sales dip during work hours.
Promote the benefits of membership rewards to incentivize non-members to join, potentially boosting customer loyalty and sales.
I'm pleased to share that I was able to derive valuable insights and formulate impactful recommendations based on the dataset. This experience has been eye-opening, and I'm eager to continue delving into the world of data analytics.
Thank you to Jacob Ajala
Looking forward to sharing more updates soon!
Warm regards,
Liz.