In this project, I brewed up some powerful insights and stirred in a bit of business strategy to help our favorite coffee spot thrive! Here's a quick overview of the project and how I percolated through the data to uncover meaningful results. Grab a cup of your favorite brew, and let’s get started!
Excel (Pivot Tables, Slicers, Dashboards)
Power Query Editor (Data Transformation)
Data Visualization (Interactive Dashboards)
I also made an iteraactive dashboard with slices, graphs, charts, kpi's to demonstrate the different measures and data.
The goal of this analysis is to uncover key business insights from the coffee shop’s sales data. I wanted to dig deeper into customer behavior, product performance, and revenue trends to brew up recommendations that can:
Boost revenue
Optimize product offerings
Improve customer experience
Maximize store efficiency
Whether it’s figuring out the best times to brew more coffee or highlighting star products, this analysis brings a full-flavored understanding of the shop’s operations!
Our dataset is like a perfectly crafted latte—it’s got all the good stuff! Here’s what’s in it:
Transactions: Every sale, large or small, across different stores.
Dates & Times: When each cup was sold—useful for identifying daily trends and rush hours.
Product Details: From gourmet coffee to baked goods, each item has its own identity.
Locations: Multiple stores across different areas, giving us a sense of regional performance.
This rich data set is a goldmine for uncovering patterns in customer behavior and store efficiency.
Here are some challenges I tackled:
Editing Formats: Standardizing date and time formats to make sure everything was in sync.
Handling Missing Values: Like finding a misplaced sugar packet, I filled the gaps where needed and checked for any null values. (because bad data leads to bad brews).
Data Cleaning: We removed any duplicates and irrelevant info to ensure we were working with clean, high-quality data—just like a well-brewed cup of coffee.
I focused on key metrics that give a snapshot of the shop’s overall performance. Here’s what I measured:
Total Sales: The shop's lifeblood, broken down across time frames (monthly, daily, etc.).
Total Footfall: How many people are walking through the doors? Are they window shopping or actually making purchases?
Average Revenue Per Person: What’s the average amount a customer spends when they visit? Are we getting just coffee or adding those delicious pastries to the mix?
Average Order Per Person: How much is each person buying per visit? Are people sticking to single items or indulging in full meals?
1. Monthly Revenue
Insight: Sales peak during the winter months, suggesting that cozy drinks like hot chocolate and chai tea become fan favorites during colder weather.
Recommendation: Launch seasonal promotions around popular drinks, offering limited-time flavors or deals during the colder months.
2.⏱ Hourly Orders & Revenue
Insight: Early morning (7–9 AM) is the busiest time—no surprise here, people need their coffee fix! But interestingly, there’s a second smaller rush between 2–4 PM, likely the post-lunch, mid-afternoon pick-me-up.
Recommendation: Maximize staffing during these peak hours and offer special discounts on smaller snacks or beverages during the afternoon slump to increase sales.
3.🏆 Top 3 Products by Revenue
#1 Ethiopia Rg Gourmet Coffee: This premium brew brings in the most revenue.
#2 Spicy Eye Opener Chai: People love their chai with a kick—perfect for starting the day strong!
#3 Dark Chocolate Hot Chocolate: A top contender, especially popular during cooler months.
Recommendation: Stock up on these winners and feature them more prominently in marketing efforts, as they drive a big portion of the revenue.
4.📊 Store-wise Revenue & Footfall
Insight: The Lower Manhattan location consistently outperforms others in terms of footfall and revenue, likely due to its high-traffic location.
Recommendation: Invest in advertising or loyalty programs specific to this store to capitalize on its high performance. Alternatively, investigate underperforming locations and consider changes in marketing strategy or product offerings.