October, 2024
Tools Used: Excel & PowerBI
| $83.65K Total Revenue | $1.31K Average Transaction Value | 2623 Total Number of Customer | $31.89 Average Transaction Value | | --- | --- | --- | --- |
This report analyses 2,623 coffee sales transactions between January and December 2024 using Excel Power Query and Power BI. The shop generated $83.65k in total revenue with an average transaction value of $31.89. Key findings show that Latte and Americano with Milk are top performing products, card payments dominate at 96.61% and sales peak on Tuesday mornings and in October. Recommendations focus on optimising peak operations, boosting underperforming periods and sustaining revenue growth through data-driven decisions.
Coffee retail businesses often struggle to identify which products, time periods and payment preferences drive the most revenue. Without data-driven insights, business decisions are made based on assumption rather than evidence. This analysis examines coffee sales transactional data from January to December 2024 comprising 2,623 transactions to uncover patterns in customer behaviour, product performance, payment preferences and temporal trends. The goal is to provide actionable insights that enable coffee retail businesses to optimise operations, boost underperforming periods and sustain revenue growth.
The Coffee Sales dataset is a single CSV file sourced from Kaggle, containing transactional records of a retail coffee shop. The dataset comprises 2,623 rows and 6 variables including Date, Cash Type, Card, Money, Coffee Name and Time. Each row represents a unique customer transaction. During the data cleaning and transformation stage, 3 additional columns were engineered, Day Name, Time Range and Day Number to support temporal analysis and uncover sales patterns across different periods of the day and week.
| Variable | Type | Description | Null |
|---|---|---|---|
| Date | Date | Date of the transaction | 0% |
| Cash type | Varchar | Method of transaction | 0% |
| Card | Varchar | Details of Card Number | 3% |
| Money | INT | Price of Coffee | 0% |
| Time | Datetime | Time of transaction | 0% |
| Issue | Volume | Decision |
|---|---|---|
| Missing Value | 89 (3%) | Replace with ‘Not Available’ |
| Inconsistent format | All Text Column | Standardize all text value |
A five-stage pipeline using Excel and Power BI was adopted to ensures reproducibility and separation of raw from analytical data.
| Stage | Tool | Output | Key Decision |
|---|---|---|---|
| Ingestion | Excel | 2623 | Load CSV file into Excel Power Query |
| Exploration | Excel Power Query | Null count and date range audit | 3% null was found |
| Cleaning & Transformation | Excel Power Query | Coffee clean (2623 rows) + 3 new columns added | Standardize text value and replace nulls. Extracted Day Name, Time Range (Morning, Afternoon & Evening) and Day Number from Date Column to support trend analysis |
| Analysis | Power BI | 5 Descriptive questions answered | Connected clean Excel file to Power BI for analysis |
| Visualization | Power BI | Dashboard | Display insights through charts and graphs |
1. General Sales Performance:
The coffee shop recorded 2,623 transactions between January and December 2024, generating $83.6k in total sales with an average spend of $31.89 per customer. Americano with Milk (621) and Latte (618) were the top performing coffees, indicating strong customer preference for milk-based drinks. Morning sessions drove the highest sales at $27.6k, with Tuesdays being the peak day and Wednesdays the lowest.
Implication: