PROJECT

Description

The dataset I used was obtained from Kaggle and contained raw data regarding café transactions. The original data was messy and required significant cleaning to be useful for analysis. I started by using SQL to:

  • Remove duplicates and irrelevant data
  • Handle missing values
  • Standardize product names and payment methods
  • Aggregate the data to calculate total sales per product and identify trends

After cleaning the data, I moved to Tableau to create several key visualizations. Some of the main insights included:

  • Sales trends over time, helping to identify peak days and times for business
  • Top-selling products, which helped the café understand customer preferences
  • Breakdown of payment methods, comparing cash vs. credit transactions
  • Comparison between dine-in and takeout sales, offering insight into customer behavior

The outcome of this project was a series of interactive dashboards and graphs that allowed the café to make data-driven decisions about product offerings, marketing strategies, and operational adjustments.

Python Code

SQL

                    
                        CREATE TABLE cafe LIKE `tableconvert.com_q1n0gf`;

INSERT cafe
SELECT *
FROM `tableconvert.com_q1n0gf`;

ALTER TABLE cafe
DROP COLUMN ID;

UPDATE cafe
SET payment_method = NULL
WHERE payment_method LIKE '%ERROR%' OR payment_method LIKE '%UNKNOWN%';

UPDATE cafe
SET total_spent = NULL
WHERE total_spent  LIKE '%ERROR%' OR total_spent  LIKE '%UNKNOWN%';

UPDATE cafe
SET total_spent = NULL
WHERE total_spent  LIKE '%ERROR%' OR total_spent  LIKE '%UNKNOWN%';

UPDATE cafe
SET Location = NULL
WHERE Location  LIKE '%ERROR%' OR Location  LIKE '%UNKNOWN%';

UPDATE cafe
SET Item = NULL
WHERE Item LIKE '%ERROR%' OR Item LIKE '%UNKNOWN%';

UPDATE cafe
SET transaction_date = NULL
WHERE transaction_date LIKE '%ERROR%' OR transaction_date LIKE '%UNKNOWN%';

UPDATE cafe
SET payment_method = 'Credit Card' 
WHERE payment_method LIKE 'Digital%';

UPDATE cafe
SET total_spent = price_unit * Quantity
WHERE price_unit IS NOT NULL AND Quantity IS NOT NULL;

DELETE FROM cafe
WHERE Item IS NULL OR Item = "";

ALTER TABLE cafe
MODIFY COLUMN transaction_date DATE;

ALTER TABLE cafe
MODIFY COLUMN transaction_date DATE;

SELECT * 
FROM cafe;
                        CREATE DATABASE Cafe;
USE Cafe;
SET SQL_SAFE_UPDATES = 0;

CREATE TABLE cafe 
LIKE dirty_cafe_sales;

SELECT *
FROM cafe;

INSERT INTO cafe 
SELECT *
FROM dirty_cafe_sales;

SELECT `Transaction ID`, COUNT(*)
FROM cafe 
GROUP BY `Transaction ID`
HAVING COUNT(*) >1;
-- No repetead transactions 

SELECT `Transaction ID`, `Total Spent`
FROM cafe 
WHERE `Total Spent` = '' 
OR `Total Spent` = 'ERROR'
OR `Total Spent` = 	'UNKNOWN';

UPDATE cafe 
SET `Total Spent` = NULL 
WHERE `Total Spent` = '' 
OR `Total Spent` = 'ERROR'
OR `Total Spent` = 	'UNKNOWN';

SELECT `Total Spent`
FROM cafe 
WHERE `Total Spent` IS NULL;

SELECT  Quantity, `Price Per Unit`,`Total Spent`
FROM cafe;

UPDATE cafe 
SET  `Total Spent` = Quantity * `Price Per Unit`
WHERE `Price Per Unit` IS NOT NULL OR Quantity IS NOT NULL; 

SELECT `Payment Method`
FROM cafe;

UPDATE cafe
SET `Payment Method` = 'Credit Card'
WHERE `Payment Method` = 'Digital Wallet';

Select *
FROM cafe;

UPDATE cafe 
SET `Transaction Date` = NULL
WHERE `Transaction Date` = ''
OR `Transaction Date` = 'ERROR'
OR `Transaction Date` = 	'UNKNOWN';

ALTER TABLE cafe
MODIFY COLUMN `Transaction Date` DATE;

UPDATE cafe 
SET Item = NUll
WHERE Item = ''
OR Item = 'ERROR'
OR Item = 'UNKNOWN';

DELETE FROM cafe 
WHERE Item IS NULL;

Select *
FROM cafe;

UPDATE cafe 
SET `Payment Method` = NULL 
WHERE `Payment Method` = ''
OR `Payment Method` = 'ERROR'
OR `Payment Method` = 'UNKNOWN';


UPDATE cafe 
SET `Location` = NULL 
WHERE `Location` = ''
OR `Location` = 'ERROR'
OR `Location` = 'UNKNOWN';

Documentation