End-to-End Analytics Project

End to End
Coffee Shop
Analytics.

Profit, Demand and Customer Insights — a four-year business intelligence investigation into why a coffee shop with strong sales was barely breaking even, and exactly what to do about it..

Industry

☕ Retail / Consumer

Tools

Excel · Power Query

Period

2019 – 2022

Dashboards

2 Interactive

3,551

Orders analysed across 4 years of trading

913

Customers across 3 countries — US, UK & Ireland

2.9%

Profit margin uncovered — despite consistent sales growth

ExcelPower QueryPivot Tables Dashboard DesignKPI AnalysisData Cleaning Business IntelligenceProfitability Analysis Customer BehaviourData Storytelling ExcelPower QueryPivot Tables Dashboard DesignKPI AnalysisData Cleaning Business IntelligenceProfitability Analysis Customer BehaviourData Storytelling

The Problem

Strong Sales.
Thin Profits.

A specialty coffee shop had four years of transaction data and no clear picture of what was actually working. Sales were growing. Customers were returning. But the profit margin told a different story — at just 2.9% on $45,134 in total sales, the business was generating revenue without generating meaningful financial return.

Management had no analytical framework to understand why, or where to look first. This project was built to change that — from the ground up.

Working end-to-end across the full analytical pipeline — raw data, cleaning, modelling, dashboards, and recommendations — the analysis surfaced a structural pricing problem hiding inside what looked like a success story.

$45K

Total sales generated over 4 years — yet only $1,301 in profit. The gap between revenue and return was the central question this analysis set out to answer.

3 🌍

Countries served — United States, United Kingdom, and Ireland — with vastly different profit contributions revealing where the business was over and under-invested.

4 ☕

Coffee types analysed — Arabica, Robusta, Liberica, and Excelsa — each with dramatically different sales-to-profit ratios that the business had never formally examined.

Key Findings

What the Data
Was Hiding

⚠️

The Sales Paradox

Robusta was the best seller and the worst profit generator. It consistently led every year in sales volume but returned the lowest profit across all three roast types. The business was working its hardest for its least financially rewarding product.

💡

The Hidden Champion

Liberica Light Roast delivered the highest profit margin in the entire range — yet was never prominently marketed. It was sitting in the data quietly outperforming everything else on a per-unit basis, invisible without analysis.

📦

The Size Opportunity

All four cup sizes attract roughly equal order volumes. Yet the 2.5litre size contributes 30% of total profit. It is not underperforming — it is underexposed. The opportunity is steering existing demand, not creating new demand.

🗺️

Geographic Concentration

The United States accounts for 78% of total profit. The UK contributes only 7% — suggesting either low market penetration or poor product-market fit that warrants a focused review before further investment.

🎴

Loyalty Programme Gap

Non-loyalty card holders slightly outpurchase cardholders — inverting the fundamental logic of the programme. The system designed to increase purchase frequency is not delivering on its core purpose.

📅

Predictable Seasonality

Order volumes peak consistently in February–March (400+ cups) and fall significantly in August–October (~200 cups). This four-year-consistent pattern gives operations a reliable planning window that had never been formally identified.

The Dashboards

Two Dashboards.
One Complete Picture.

Senior Management · Finance · Product

Where Is the Money? A Coffee Profitability & Sales Analysis

Sales & Profitability Intelligence Dashboard

Built to answer the business's most pressing commercial questions — which products are making money, which markets are worth investing in, and whether revenue growth is actually translating into profit.

Profit per Country — US, UK & Ireland comparative analysis

Coffee Type & Roast Combination Profitability

Size Performance Breakdown: Volume, Revenue & Profit Share

Sales by Year — 4-year revenue trend

% Profit Change by Coffee Type (Year-over-Year)

Dashboard Preview

Visuals

Countries

Coffee Types

Dashboard Preview

Coffee Shop Customer Behaviour Dashboard

Visuals

913

Customers

Top 9

Profiled

Marketing · Operations · Customer Success

Who Is Buying, What They Want, and When They Come

Customer Behaviour & Demand Intelligence Dashboard

Built to answer the human side of the business — who the customers are, what patterns drive their behaviour, and when and how demand moves across the year.

Monthly Order Volume Trend — seasonal peaks & troughs

Coffee Size Preference Shifts by Year

Coffee Type Demand Trends by Year

Loyalty Card Holders vs. Non-Members Purchase Comparison

Top 9 Customers by Cumulative Spend

Customer Favourite Coffee & Roast Type

Recommendations

Three Actions.
No New Customers Required.

💰

Reprice Robusta Immediately

The product that powers the most sales is silently eroding profitability. A pricing review or cost structure investigation is the single highest-priority action the business can take. Robusta's dominance in volume makes this problem invisible on the surface — and catastrophic if left unaddressed.

⭐

Promote Liberica Light Roast Deliberately

Feature it prominently on the menu, train staff to recommend it, and position it as a premium option. Redirecting even 15% of existing customer demand toward the highest-margin product would have a measurable impact on profit without requiring a single new customer to walk through the door.

🎯

Redesign the Loyalty Programme

Tie loyalty points specifically to 2.5litre purchases and Liberica orders. This fixes two problems simultaneously — activating an underperforming programme and steering customer behaviour toward higher-margin products. A programme that rewards margin-positive behaviour serves the business as much as the customer.

📦

Market the 2.5litre Size as Best Value

Display it prominently as the "Best Value" option. Offer a top-up deal — "Upgrade from 1 litre to 2.5 litre for just $X more." The 2.5litre generates 30% of total profit on equal order volume to other sizes. It is not underperforming. It is invisible to the customers who would choose it if prompted.

Project Details

Technical Overview

Tools Used

Microsoft Excel · Power Query

Techniques

Data cleaning · Pivot tables · % difference analysis · Dashboard design · Data modelling

Period Covered

January 2019 – August 2022

Dataset Size

3,551 orders · 913 customers · 3 countries

Dashboards Built

2 interactive Excel dashboards · 11 visuals total

Key Limitation

2022 data terminates in August — year-end figures are not fully representative

Analyst

Oluwatosin Olajumoke Ogunwale

Project Type

Independent portfolio project — end-to-end

Deliverables

2 dashboards · Written report · README · CV project entry · Cover letter

End to EndCoffee ShopAnalytics.

Strong Sales.Thin Profits.

What the DataWas Hiding

Two Dashboards.One Complete Picture.