☕ Café Sales Analysis

📊 End-to-End Data Analysis Project using SQL & Power BI

Author: Muhammad Akbar Khan (AkbarTheAnalyst)

---

🧩 Project Overview

This project explores café sales data to uncover key business insights such as:

• Top-selling items and revenue drivers
• Performance by location and payment method
• Daily sales trends and average transaction values

The complete workflow covers data cleaning with SQL, data analysis queries, and dashboard visualization in Power BI.

---

📂 Folder Structure

Cafe_Sales_Analysis/
│
├── data/             # Raw dataset (cafe_sales.csv)
├── sql/              # SQL scripts for cleaning and analysis
├── dashboard/        # Power BI dashboard file (.pbix)
└── README.md         # Project documentation

---

🗃️ Dataset Information

The dataset contains transactional sales data from a café, including product, location, and payment details.

Column Name	Description	Data Type
transaction_id	Unique ID for each sale	TEXT
item	Name of item sold	TEXT
quantity	Quantity sold	TEXT
price_per_unit	Price per item	TEXT
total_spent	Total amount spent	TEXT
payment_method	Type of payment used	TEXT
location	Café branch location	TEXT
transaction_date	Date of transaction	TEXT

---

🧹 Step 1: Data Cleaning (SQL)

The raw data was first loaded into a staging table and cleaned for analysis.

-- Step 0: Create the raw table
CREATE TABLE cafe_sales (
    transaction_id TEXT,
    item TEXT,
    quantity TEXT,
    price_per_unit TEXT,
    total_spent TEXT,
    payment_method TEXT,
    location TEXT,
    transaction_date TEXT
);

-- Step 1: Create a staging table with unique records
CREATE TABLE cafe_sales_staging AS
SELECT DISTINCT *
FROM cafe_sales;

-- Step 2: Data cleaning and transformation
CREATE TABLE cafe_sales_cleaned AS
SELECT
    transaction_id,
    item,
    CAST(quantity AS NUMERIC) AS quantity,
    CAST(price_per_unit AS NUMERIC) AS price_per_unit,
    CAST(total_spent AS NUMERIC) AS total_spent,
    payment_method,
    location,
    TO_DATE(transaction_date, 'YYYY-MM-DD') AS transaction_date
FROM cafe_sales_staging
WHERE quantity IS NOT NULL
  AND price_per_unit IS NOT NULL
  AND total_spent IS NOT NULL;

---

📈 Step 2: Data Analysis (SQL Queries)

🔹 Total Sales Overview

SELECT * FROM cafe_sales_cleaned;

🔹 Top-Selling Items

SELECT item, SUM(quantity) AS total_quantity
FROM cafe_sales_cleaned
GROUP BY item
ORDER BY total_quantity DESC;

🔹 Highest Revenue Items

SELECT item, SUM(total_spent) AS total_revenue
FROM cafe_sales_cleaned
GROUP BY item
ORDER BY total_revenue DESC;

🔹 Revenue by Location

SELECT location, SUM(total_spent) AS revenue
FROM cafe_sales_cleaned
GROUP BY location
ORDER BY revenue DESC;

🔹 Payment Method Insights

SELECT payment_method, ROUND(AVG(total_spent),2) AS avg_transaction_value
FROM cafe_sales_cleaned
GROUP BY payment_method
ORDER BY avg_transaction_value DESC;

---

📊 Step 3: Power BI Dashboard

The Power BI dashboard provides a visual summary of café sales performance:

• Top Items & Revenue — Quickly identify best-performing menu items
• Location Performance — Compare revenue across café branches
• Payment Trends — Understand preferred payment methods
• Daily Sales Trends — Monitor growth and fluctuations over time

📁 File: dashboard/Cafe_Sales_Dashboard.pbix

---

🧠 Interesting Questions Explored

1. Which items contribute the most to overall revenue?
2. Which locations perform best in terms of sales volume and revenue?
3. What is the average transaction value by payment method?
4. Are there any daily or seasonal sales trends?
5. Which product-location combinations generate the highest income?

These insights help café managers make data-driven decisions such as stock planning, menu optimization, and pricing strategy.

---

🛠️ Tools Used

• PostgreSQL – For data cleaning and SQL analysis
• Power BI – For dashboard visualization
• Excel / CSV – For raw data management
• GitHub – For project sharing and version control

---

🧾 Author

👤 Muhammad Akbar Khan
🎓 Master’s Student in Applied Mathematics
💻 GitHub: AkbarTheAnalyst

---

⭐ If you found this project useful, don’t forget to star the repository! ⭐