☕ Office Building Coffee Vending Machine Sales & Performance Analytics (March 2025) 

1. Executive Summary

This project delivers a Month-to-Date (MTD) Sales & Performance Dashboard designed for vending machine operations management within an office building complex. The report evaluates sales performance during the current operating period (March 1–23, 2025) against February 2025 total revenue (₴13,215). Management established a target revenue growth of at least +20% over February (minimum target: ₴15,858).

Raw transactional data was cleansed and processed using SQL (MySQL) to ensure strict data integrity. The cleaned dataset was connected to Tableau featuring dynamic interactive filters (Select Month, Select Year, Coffee Name) for seamless cross-period comparisons. As of March 23, 2025, recorded revenue stands at ₴9,986 (-24.43% vs past month) with 8 operational days remaining. Based on the analysis, I recommend pursuing the ₴15,858 target through a canister space reallocation strategy, shifting inventory capacity from slow-moving drinks to top sellers (Americano & Latte) alongside sales optimization during peak operating hours and days.

2. Business Problem 

Operational Background: The vending machine operates in an office complex with active hours matching standard working schedules (06:00 AM – 09:00 PM). Leadership requires an MTD performance report (as of March 23) to drive the sales strategy toward hitting a +20% growth over February (₴13,215), aiming for a minimum of ₴15,858.

Key Analytical Questions:

• Data Integrity & Pipeline (SQL): How do we ensure data is free of duplicates, formatted with precise DATETIME and DECIMAL types, and properly handles missing values in cash transactions?

• Revenue vs Target (Tableau): What is the exact revenue gap between MTD March sales (₴9,986) and management's minimum target (₴15,858)?

• Accumulated Peak Days & Hours (Tableau): What are the peak cumulative revenue days (Best Day) and operating hours (Best Time) during working hours (06:00 AM – 09:00 PM)?

• Product Performance & Inventory Strategy (Tableau): How does each coffee variant contribute to overall revenue, and how can canister space reallocation prevent out-of-stock scenarios for top sellers?

3. Data Architecture, Preparation & Tech Stack

• Tools: SQL (MySQL Workbench), Tableau Desktop.

• Data Cleansing Pipeline (MySQL):

  • Table Staging: Created a staging table (sale_st) from raw data (sale) to preserve raw transactional records.

  • Deduplication Check: Isolated duplicate transactions using GROUP BY card, date, datetime HAVING COUNT(*) > 1.

  • Standardization & Type Casting:

    • Audited string consistency across cash_type, card, and coffee_name using LENGTH() and TRIM().

    • Parsed date strings into proper DATE types via STR_TO_DATE(date, '%Y-%m-%d').

    • Parsed timestamp strings into DATETIME types via STR_TO_DATE(datetime, '%Y-%m-%d %H:%i:%s.%f').

    • Cast the money column to DECIMAL(10,2) for financial precision.

  • Null Handling & Imputation: Inspected NULL/blank values and imputed card = 'none' for cash transactions.

• Advanced Analytics & Visualization (Tableau):

  • Interactive Global Filters: Equipped with dynamic controls (Select Month, Select Year, Coffee Name) enabling multi-period performance analysis.

  • KPI Cards with Target Tracking: Displayed MTD revenue (₴9,986) alongside variance indicators (-24.43% vs Past Month).

  • Product Rank (Lollipop Chart): Mapped nominal revenue contribution per coffee item.

  • Accumulated Best Day & Best Time (Area Charts): Visualized aggregated revenue based on day of the week and operating office hours.

  • Payment Type Ratio (Donut Chart): Displayed payment method proportions (100% Card / ₴9,986).

 4. Key Insights & Dashboard Metrics

📊 Target vs Actual Revenue Performance (MTD March 2025)

• Actuals & Target Gap: MTD revenue as of March 23, 2025, reached ₴9,986 (-24.43% vs February ₴13,215). Achieving management's growth target (+20% = ₴15,858) requires capturing an additional ₴5,872 across the final 8 days of March (an average requirement of ~₴734 / day).

☕ Product Revenue Breakdown

• Top Performers (Primary Revenue Drivers):

  • Americano: ₴2,648 (Best Seller)

  • Latte: ₴2,038

  • Americano with Milk: ₴1,605

  • Cappuccino: ₴1,538

• Mid-to-Bottom Performers:

  • Cocoa: ₴1,073

  • Hot Chocolate: ₴822

  • Espresso: ₴211

  • Cortado: ₴52 (Lowest Seller)

🏢 Patterns of Office Hours & Day Accumulation

• Accumulated Best Day (Cumulative Revenue by Day of Week):

  • Friday (Best Day): ₴1,896 — End-of-week workday marks the peak of coffee consumption.

  • Wednesday & Monday: ₴1,759 and ₴1,693 — High activity during the start and middle of the work week.

  • Tuesday & Thursday: ₴1,444 and ₴1,333.

  • Saturday & Sunday (Worst Days): ₴1,065 and ₴796 — Significant revenue drop due to lower weekend office occupancy.

• Accumulated Best Time (06:00 AM – 09:00 PM Operating Schedule):

  • 04:00 PM / 4 PM (Best Time): ₴1,149 — Highest cumulative revenue matching afternoon breaks and end-of-day preparations.

  • 06:00 AM / 6 AM (Worst Time): ₴26 — Lowest transaction volume at early machine startup.

5. Business Recommendations

Inventory Canister Space Reallocation (Canister Space Optimization):

• Cortado (₴52) and Espresso (₴211) combined generate only ~2.6% of total revenue. Reduce canister allocation for these slow-moving variants and reassign space to hold additional coffee bean and milk capacity for Americano (₴2,648) and Latte (₴2,038). This eliminates stockout risks for high-demand variants during peak office hours.

Achieving the ₴15,858 Target in the Final 8 Days (March 24–31):

• Target a daily average revenue of ~₴734 across the remaining 8 days. Because weekday sales routinely exceed ₴1,300–₴1,800, this target is fully achievable provided ingredient stock is maintained without machine downtime.

Peak Hour Restocking & Operations:

• Ensure operations staff perform ingredient refills before 04:00 PM (4 PM) on Fridays, Wednesdays, and Mondays to capture peak cumulative demand (₴1,149 at 4 PM and ₴1,896 on Fridays).

Afternoon Promotion Strategy (Happy Hour Office Deal):

• Implement limited afternoon bundling deals from 02:00 PM to 04:00 PM to stimulate pre-commute purchases while elevating mid-tier variants like Cappuccino and Cocoa.