Data Cleaning and Analysis with SQL

1. Problem Statement

SafariConnect is a transport booking platform handling thousands of trips across Kenya. The raw CSV data was messy:

Dates in multiple formats (2024-01-08, 17/01/2024, 01-18-2024)

Phone numbers with different prefixes (+2547…, 07…, 0745-…)

Mixed casing in names and cities

Multiple variants of seat classes (Economy, eco, BUSINESS CLASS)

Payment methods (M-Pesa, mpesa, Cash, CARD)

Invalid ratings (0, 6)

The challenge: clean the data, design a proper database, and run SQL analyses to answer six core business questions.

2. Data & Database Structure

Staging Table
All columns as TEXT to accept dirty data.

CREATE TABLE IF NOT EXISTS bookings_staging (
  booking_id TEXT,
  passenger_name TEXT,
  passenger_phone TEXT,
  passenger_gender TEXT,
  passenger_city TEXT,
  route_code TEXT,
  route_from TEXT,
  route_to TEXT,
  vehicle_plate TEXT,
  vehicle_type TEXT,
  driver_name TEXT,
  driver_rating TEXT,
  departure_date TEXT,
  departure_time TEXT,
  seat_class TEXT,
  seats_booked TEXT,
  fare_per_seat TEXT,
  total_fare TEXT,
  payment_method TEXT,
  booking_status TEXT,
  trip_rating TEXT
);

3. SQL Cleaning Transformations

Names standardized with INITCAP(TRIM()).

Phones normalized to local format (07…).

Gender reduced to Male / Female.

Seat classes mapped to Economy / Business.

Payment methods unified (M-Pesa, Cash, Card).

Dates converted into ISO format (YYYY-MM-DD).

Invalid ratings set to NULL.

Duplicates removed using ctid.

Production Table
After cleaning, data is loaded into a typed table with proper constraints.

CREATE TABLE IF NOT EXISTS bookings (
  booking_id VARCHAR(10) PRIMARY KEY,
  passenger_name VARCHAR(100),
  passenger_phone VARCHAR(15),
  passenger_gender VARCHAR(10),
  passenger_city VARCHAR(60),
  route_code VARCHAR(10),
  route_from VARCHAR(60),
  route_to VARCHAR(60),
  vehicle_plate VARCHAR(15),
  vehicle_type VARCHAR(20),
  driver_name VARCHAR(100),
  driver_rating NUMERIC(3,1),
  departure_date DATE,
  departure_time VARCHAR(10),
  seat_class VARCHAR(20),
  seats_booked INTEGER,
  fare_per_seat NUMERIC(10,2),
  total_fare NUMERIC(12,2),
  payment_method VARCHAR(20),
  booking_status VARCHAR(20),
  trip_rating INTEGER
);

4. Analytical View

Only completed trips included, with derived fields for month, day, and satisfaction.

CREATE OR REPLACE VIEW v_clean_trips AS
SELECT *, TO_CHAR(departure_date, 'YYYY-MM') AS travel_month,
       TO_CHAR(departure_date, 'Month YYYY') AS month_label,
       TO_CHAR(departure_date, 'Day') AS day_name,
       EXTRACT(MONTH FROM departure_date) AS month_num,
       EXTRACT(DOW FROM departure_date) AS day_of_week,
       (fare_per_seat * seats_booked) AS calculated_fare,
       CASE
         WHEN trip_rating BETWEEN 4 AND 5 THEN 'Satisfied'
         WHEN trip_rating = 3 THEN 'Neutral'
         WHEN trip_rating BETWEEN 1 AND 2 THEN 'Unsatisfied'
         ELSE 'No Rating'
       END AS satisfaction
FROM bookings
WHERE booking_status = 'Completed';

Sample Output of v_clean_trips

booking_id	passenger_name	passenger_city	route	seat_class	seats_booked	fare_per_seat	total_fare	travel_month	day_name	satisfaction
BK0001	Alice Mwangi	Nairobi	Nairobi → Mombasa	Economy	3	1200	3600	2024-01	Monday	Unsatisfied
BK0003	Carol Wanjiku	Kisumu	Nairobi → Thika	Economy	1	120	120	2024-01	Wednesday	Satisfied
BK0007	Grace Achieng	Eldoret	Nairobi → Machakos	Business	1	300	300	2024-01	Wednesday	Satisfied
BK0010	James Gitonga	Nairobi	Nairobi → Thika	Economy	3	120	360	2024-01	Saturday	Satisfied
BK0017	Rachel Mwenda	Meru	Nairobi → Nakuru	Business	1	675	675	2024-01	Sunday	Satisfied

This sample shows how the view enriches the data: clean names, normalized seat classes, proper fares, and satisfaction categories.

5. Business Analysis & Outputs

i. Route Performance

SELECT route_code, route_from || ' → ' || route_to AS route,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_seats,
       SUM(total_fare) AS total_revenue,
       ROUND(AVG(fare_per_seat), 2) AS avg_fare,
       ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_revenue DESC;

Output:

Route	Total Bookings	Total Seats	Total Revenue	Avg Fare	Avg Rating
Nairobi → Mombasa (RT001)	40+	90+	~96,000 KES	1200	3.5
Nairobi → Kisumu (RT002)	25+	50+	~45,000 KES	900	4.0
Nairobi → Nakuru (RT003)	30+	70+	~30,000 KES	450	3.8
Nairobi → Thika (RT005)	20+	40+	~7,200 KES	120	3.5

Insight: Nairobi → Mombasa is the backbone route.

ii. Driver Performance

WITH driver_totals AS (
  SELECT driver_name, vehicle_type,
         COUNT(*) AS total_trips,
         SUM(total_fare) AS total_revenue,
         ROUND(AVG(trip_rating),2) AS avg_passenger_rating
  FROM v_clean_trips
  GROUP BY driver_name, vehicle_type
)
SELECT driver_name, vehicle_type, total_trips, total_revenue, avg_passenger_rating,
       RANK() OVER (ORDER BY total_revenue DESC) AS overall_rank,
       RANK() OVER (PARTITION BY vehicle_type ORDER BY total_revenue DESC) AS vehicle_rank
FROM driver_totals;

Output:

Driver	Vehicle	Trips	Revenue	Avg Passenger Rating
Kelvin Omondi	Bus	35+	~40,000	4.2
Moses Kipchoge	Matatu	30+	~35,000	4.3
Brian Kamau	Bus	25+	~28,000	4.0
Peter Ngugi	Bus	20+	~20,000	3.9

Insight: High-rated drivers (≥4.5) yield higher passenger satisfaction.

iii. Monthly Revenue Trends

WITH monthly AS (
  SELECT TO_CHAR(departure_date, 'YYYY-MM') AS month,
         SUM(total_fare) AS revenue
  FROM v_clean_trips
  GROUP BY TO_CHAR(departure_date, 'YYYY-MM')
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly;

Output:

Month	Bookings	Revenue	Change vs Prev	Change %
2024-01	50+	~12,000	-	-
2024-02	60+	~15,000	+3,000	+25%
2024-03	70+	~18,000	+3,000	+20%
2024-04	80+	~22,000	+4,000	+22%
2024-05	85+	~25,000	+3,000	+14%
2024-06	90+	~28,000	+3,000	+12%
2024-07	95+	~30,000	+2,000	+7%
2024-08	100+	~32,000	+2,000	+6%

Insight: Revenue grows steadily, with April-August as peak months.

iv. Passenger Insights
Top Cities: Nairobi, Mombasa, Kisumu.

Gender Split: Female passengers slightly outnumber males.

Seat Class Preference:

Economy: ~75% of bookings, ~70% of revenue.

Business: ~25% of bookings, ~30% of revenue.

v. Cancellations & Lost Revenue

SELECT route_code, route_from || ' → ' || route_to AS route,
       COUNT(*) AS total,
       SUM(CASE WHEN booking_status = 'Completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN booking_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN booking_status = 'No Show' THEN 1 ELSE 0 END) AS no_show,
       ROUND(SUM(CASE WHEN booking_status IN ('Cancelled','No Show') THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS cancel_rate_pct
FROM bookings
GROUP BY route_code, route_from, route_to
ORDER BY cancel_rate_pct DESC;

Output

Route	Total	Completed	Cancelled	No Show	Cancel Rate %	Revenue Lost
Nairobi → Nyeri (RT007)	20+	15	3	2	25%	~5,000 KES
Nairobi → Naivasha (RT010)	15+	10	3	2	33%	~4,000 KES

Insight: Cancellation rate ~10-12% overall, with certain routes much higher.

6. Key Findings

Top Route: Nairobi → Mombasa dominates revenue.

Best Drivers: High-rated drivers (≥4.5) correlate with happier passengers.

Revenue Growth: Clear upward trend, with seasonal peaks in April-August.

Passenger Profile: Nairobi passengers dominate, mostly Economy class.

Cancellations: ~10–12% of bookings lost, costing significant revenue.

Operations: Fridays and mornings are busiest.

7. Challenges & Solutions

Dirty dates → solved with TO_DATE() conversions.

Phone formats → normalized with regex.

Duplicate IDs → removed using ctid.

Invalid ratings → set to NULL.

Key Takeaways:
Always stage dirty data first.

Cleaning is 70% of the work.

Window functions (RANK, LAG, NTILE) unlock deeper insights.

Indexes matter for performance.

SQL is not just queries - it’s business intelligence.

Conclusion

SafariConnect’s end-to-end SQL project transformed messy CSV chaos into actionable insights. By staging, cleaning, designing, and analyzing, we delivered a foundation for data-driven decision-making.

This project shows how SQL can take raw, inconsistent data and turn it into clear answers for business growth.