Optimising Coffee Shop Performance with Data Visualisation

Project Overview

A coffee chain with three locations in Coventry recently changed ownership. The new owners wanted to better understand customer purchase behavior and streamline operations using transaction data collected over six months (January–June 2023).

Dataset used

• Dataset

Tools

• Excel
• Tableau

Questions (KPI)

• How has monthly revenue changed over the six-month period?
• What are the busiest days of the week for transactions?
• At what hours of the day do customers buy the most?
• Do weekend sales differ significantly from weekday sales?
• Which category generates the highest number of transactions?
• What are the top-selling products by volume and revenue?

Data Preparation

Data Profiling:

• The dataset contained 149,455 unique transactions.
• Transaction timestamps ranged from 6 AM to 8 PM daily.
• Product prices ranged from £0.80 (syrup) to £45.00 (premium coffee product type).
• No missing data, outliers, or misclassified categories were found.

Calculated Columns Added:

• Revenue
• Month
• Month Name
• Weekday
• Weekday
• Hour of Transaction

Data Visualization & KPI Analysis

Data was exported to Tableau for analysis and visualisation

Tableau Workbook Open Workbook

Dashboard

View the Interactive Dashboard

View Dashboard

Project Insight

• There was a 6.77% decline in revenue from January to February.
• Revenue steadily increased from February to June, with growth rates ranging from 6.23% to 31.77%.
• Friday had the highest number of transactions, followed by Thursday and Monday. The margin is very close
• Saturday and Sunday had the lowest sales volumes, indicating potential weekend sales gaps.
• 10 AM was the peak sales hour, with the highest number of transactions.
• Sales were lowest at 6 AM and 8 PM, aligning with the start and close of business hours.
• Coffee was the top-selling product category, accounting for 39.18% of total transactions.
• Brewed Chai Tea was the best-selling product type by transaction count.
• Barista Espresso generated the highest revenue among all product types

Recommendations

• Implement weekend promotions or loyalty discounts to increase transactions on Saturday and Sunday.
• Introduce limited-time offers or bundled deals during peak hours to boost sales further
• Weekend-specific promotions (e.g., "Weekend Brunch Specials" or "Buy One, Get One Free") to attract more customers.
• Promote Barista Espresso as a premium offering to maintain high revenue margins.
• Reduce staffing during 6 AM and 8 PM due to low customer traffic.
• Offer early morning discounts (6-8 AM) and evening happy hour deals (6-8 PM) to boost sales during slow periods.