Project Overview
Objective
To uncover actionable business insights from a bakery’s sales data and present findings in an interactive dashboard that helps decision-makers understand product performance, customer patterns, and operational trends.
Context
The bakery operates daily and records every item sold by time and transaction ID. They sought to gain a deeper understanding of peak hours, popular products, and sales patterns to inform their production, staffing, and promotional decisions.
Duration
1 week
Roles
- Data Cleaning
- Data Analysis
- Dashboard Design & Visualisation
- Business Insight Generation
Tools & Methodologies
- Excel for data cleaning and transformation
- Power Query for preliminary exploration
- Power BI for dashboard creation
- DAX for dynamic KPI calculations
- Data Visualisation Best Practices for User-Friendly Design
The Approach and Process
Data Processing
Loaded transactional sales data into Excel
- Removed duplicates.
- Split the timedate column into two(Time and Date).
- Create new columns( hour and weekday).
- Clean inconsistencies in item names.
Exploratory Data Analysis (EDA)
- Top and Least performing items
I analyzed item frequencies to identify the most and least sold products. while some niche items had very low sales. This helped highlight what to stock more of and what may need to be promoted or removed.
Result:
- Coffee, Bread, and Pastries ranked highest
- Adjustment, Bacon and Chicken Sandwich has the lowest ranking.
- Sales by Hour and Day of the Week.
By extracting the hour from each sale, I found peak sales occurred between 10 AM and 2 PM. These insights guide staff scheduling and product readiness during busy periods.
Weekend sales, especially on Saturdays, were the highest. Mondays and Tuesdays were the lowest. This helps the bakery plan staffing and use weekday promotions to increase early-week traffic.
- Trend over Time
To identify seasonal patterns, growth trends, and peak performance periods, I analysed the sales data across daily, monthly, and yearly timelines. This analysis was important for forecasting, inventory planning, and understanding the bakery’s business cycle over time.
The results showed that the 11th day of each month consistently recorded the highest number of transactions, likely aligning with customer payday cycles or specific bakery events. At the monthly level, March recorded the highest overall sales, which may reflect strong customer demand after the start of the year.
Comparing across years, sales in 2017 outperformed 2016, indicating positive growth. Additionally, I noticed no transactions occurred from the 5th to the 10th of each month, which suggests the bakery was likely closed during that window — a crucial insight for accurate performance evaluation and planning.
- Transactions by the Time of Day
To determine when the bakery experiences the highest customer activity, I analysed transactions based on different time segments: morning, afternoon, evening, and night, split across weekdays and weekends.
Result:
The afternoon time slot records the highest number of transactions overall, particularly on weekdays (3.3K) and also notable on weekends (1.8K).
Morning follows closely with 2.6K weekday and 1.5K weekend transactions. Evening and night show minimal activity, indicating low foot traffic or reduced hours during those periods.
Insight: This helps in scheduling staff and promotions during peak periods, mainly weekday afternoons.
- Product and Transaction Summary
To get a snapshot of the bakery’s performance and product distribution, I reviewed key metrics like the number of unique items, total items sold, and transaction counts.
Result:
- 94 unique items were available.
- A total of 18.89K items were sold over the analysis period.
- There were 9,465 unique transactions.
- On average, customers bought 2.63 items per transaction.
Insight: This summary helps assess customer buying behaviour and the effectiveness of product offerings.
Challenges & Solutions
Challenge | Solution |
Inconsistent time formatting | Used Power Query to standardize it |
Too many unique low-selling items | Grouped similar items and focused on top 10 |
Limited data fields (e.g., no prices) | Focused on frequency-based insights instead |
Cluttered dashboard layout initially | Used slicers, clean titles, and interactive filters for clarity |
Recommendations
- Top and Least performing items • Push coffee pairings: 32% of sales come from coffee—promote ‘Coffee + Pastry’ combos during peak hours (11 AM). • Bread is #2 (22%): Test subscription models for daily bread buyers.
• Revamp low performers: Brownies/cookies contribute <7% combined—rework recipes or marketing.
- Sales by Day of the Week
• Supercharge Saturdays: 17% above average - add limited-time weekend specials.
• Boost Wednesdays: 25% below average? Try 'Hump Day Happy Hour' promotions.
• Friday Focus: Strong pre-weekend sales (16.5%) - ideal for new product launches.
- Sales by Hour • Double staff at 11AM–12PM(30% of sales in 2 hours). • Pre-pack 10 AM combos for late breakfast crowds. • Test discounts at 3 PM–4PMto boost slower hours.
- Transactions by the Time of the Day • Afternoon = Cake O’Clock: Bundle slices with coffee (55% of sales). • Morning Optimisation (44.6%): Dedicate one register to coffee-only lines (8 AM–10 AM).
Outcome & Impact
The bakery analysis provided clear visibility into product performance, customer buying behavior, and peak business hours. These insights empowered strategic decision-making—such as adjusting stock levels based on demand patterns, optimizing staff scheduling during peak hours, and focusing promotions on high-performing items. As a result, the business is better positioned to improve sales, reduce operational inefficiencies, and enhance the overall customer experience through data-driven planning.
Next Steps
- Optimise inventory and production for high-performing items like Coffee and Bread, particularly during peak weekday afternoons.
- Re-evaluate or phase out underperforming products that consistently record minimal or no transactions.
- Investigate the 5th–10th inactivity period each month to confirm whether it’s due to scheduled closures, system errors, or other reasons.
- Launch targeted promotions on quieter days like Tuesday to Thursday to balance customer flow.
- Consider adding more product bundles or deals that encourage multi-item purchases, leveraging the 2.63 items-per-transaction average.
Conclusion
This analysis provided a comprehensive overview of the bakery’s performance between 2016–2017. Key insights into customer preferences, sales cycles, time-of-day trends, and product performance have uncovered actionable opportunities. By focusing on what sells best, optimising operational hours, and removing poor performers, the bakery can boost profitability and deliver a more focused customer experience.