The SafariConnect Data Story

How 290 rows of tangled booking data became six clear answers for a CEO who doesn't have time for excuses.

The Message That Started It All

Every data project has an origin story. This one starts with a WhatsApp-length message from an Operations Director who has run out of patience with a shared Excel file.

Safari Connect is a Nairobi-based bus and matatu booking platform. Think of it as the Bolt of long-distance travel. Passengers pick a route, choose a seat class, pay by M-Pesa, cash, or card, and turn up (mostly). Since 2024 the company has been growing fast, and its booking history has been growing right along with it, quietly, in a spreadsheet nobody has cleaned since the day it was created.

The brief was blunt: "I need you to clean this data, load it into our database, analyse it, and present your findings to the board... Make it look professional. Present on Friday. The CEO will be in the room."

No pressure.

What follows is the full journey of that data. From a 290-row CSV export riddled with 23 categories of mess, through a PostgreSQL cleaning pipeline, into a set of views, and finally into six business answers with real Kenyan-shilling figures behind them. Every query below is genuine SQL written for this project, and every result table underneath it is the actual output that query produces against the cleaned data, shown the way it would appear in a database GUI like DBeaver, row for row.

Note: To access the files I used in this project, check out this Github repository

Chapter 1: Landing the Raw Data

Before any cleaning can happen, the raw export has to land somewhere safe. The first move is to build a staging table, with every column typed as TEXT, so that a stray letter in a numeric column doesn't blow up the import.

-- Create safari_connect schema
CREATE SCHEMA IF NOT EXISTS safari_connect;

-- Set search_path to safari_connect schema
SET search_path TO safari_connect;

-- Set up staging table to match csv structure
-- Drop existing table if any
DROP TABLE IF EXISTS safari_connect.staging_bookings;

-- Create staging table with all columns as text to avoid type mismatches
CREATE TABLE safari_connect.staging_bookings (
    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
);
-- Sanity check for staging_bookings table
SELECT * FROM safari_connect.staging_bookings;

That "everything is text" decision looks lazy, but it's the opposite. It's the one rule that keeps a messy import from crashing on line one. 290 rows load without a single error. The mess gets dealt with afterwards, on the analyst's terms, not the file's.

With the raw data safely staged, a second table (clean_bookings), is created to receive the fixed version, with booking_id as a VARCHAR(10) PRIMARY KEY and every other column given a real type: NUMERIC for money and ratings, DATE for departure dates, INTEGER for seats and trip ratings. This table stays empty until the cleaning below earns its data.

--  Drop clean table if exists
DROP TABLE IF EXISTS safari_connect.clean_bookings;

-- Create clean table with appropriate data types
CREATE TABLE safari_connect.clean_bookings (
    booking_id VARCHAR(10) PRIMARY KEY,
    passenger_name VARCHAR(100),
    passenger_phone VARCHAR(10),
    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(2,1),
    departure_date DATE,
    departure_time VARCHAR(10),
    seat_class VARCHAR(20),
    seats_booked INTEGER,
    fare_per_seat NUMERIC(10,2),
    total_fare NUMERIC(10,2),
    payment_method VARCHAR(20),
    booking_status VARCHAR(20),
    trip_rating INTEGER
);
-- Santy check for clean table
SELECT * FROM safari_connect.clean_bookings;

Chapter 2: Twenty-Three Ways a Spreadsheet Can Lie to You

Upon further analysis, I found 23 categories of dirty data hiding in the export. This section shows how SQL was used to hunt each one down.

Names: shouting, whispering, and stray spaces

Passenger names arrive in three unhelpful states i.e. ALICE MWANGI, brian otieno, and Carol Wanjiku with leading and trailing whitespace baked in.

SELECT booking_id, passenger_name
FROM safari_connect.staging_bookings
WHERE passenger_name != INITCAP(TRIM(passenger_name));

Output:

One UPDATE fixes all three problems at once, because INITCAP(TRIM(...)) trims whitespace and title-cases in a single pass:

UPDATE safari_connect.staging_bookings
SET passenger_name = INITCAP(TRIM(passenger_name))
WHERE passenger_name != INITCAP(TRIM(passenger_name));

Note: BK0003 and BK0023 converge on the same clean value from two different kinds of mess. This proves that one well-chosen function beats three separate patches.

Phone numbers: dashes, country codes, and blanks

Phone numbers show up with dashes (0745-678-901), a +254 international prefix (+254756789012), and in 13 other cases, nothing at all.

SELECT booking_id, passenger_phone
FROM safari_connect.staging_bookings
WHERE passenger_phone LIKE '%-%';

Output:
| booking_id | passenger_phone |
|---|---|
| BK0004 | 0745-678-901 |
| BK0005 | +254756789012 |
| BK0006 | |
| BK0026 | 0767-890-123 |
| BK0027 | +254778901234 |
| BK0028 | |

Three targeted updates handle each variant:

-- Strip dashes and any non-digits
UPDATE safari_connect.staging_bookings
SET passenger_phone = REGEXP_REPLACE(passenger_phone,'[^0-9]', '', 'g')
WHERE passenger_phone LIKE '%-%';

-- Swap the +254 country code for a local leading zero
UPDATE safari_connect.staging_bookings
SET passenger_phone = '0' || SUBSTRING(REGEXP_REPLACE(passenger_phone, '[^0-9]', '', 'g'), 4)
WHERE passenger_phone LIKE '%+254%';

-- Empty strings become real NULLs, not invisible blanks
UPDATE safari_connect.staging_bookings
SET passenger_phone = NULL
WHERE TRIM(passenger_phone) = '';

That third update matters more than it looks. An empty string and a NULL behave identically to the human eye but very differently to a database. COUNT(), joins, and WHERE clauses all treat them apart. Making the blank explicit is what lets later analysis say "13 bookings have no phone on file" instead of silently miscounting.

Gender: seven ways to say two things

SELECT DISTINCT(passenger_gender) FROM safari_connect.staging_bookings;

Seven labels, two real categories. A CASE statement collapses them:

UPDATE safari_connect.staging_bookings
SET passenger_gender = CASE
    WHEN UPPER(TRIM(passenger_gender)) IN ('MALE', 'M') THEN 'Male'
    WHEN UPPER(TRIM(passenger_gender)) IN ('FEMALE', 'F') THEN 'Female'
    ELSE passenger_gender
END;

Output:
| passenger_gender | row_count |
|---|---|
| Male | 147 |
| Female | 141 |

Dates: three formats fighting for the same column

This is the one that separates a careful analyst from a hopeful one. The same column holds 2024-01-08, 17/01/2024, 01-18-2024, and 19-01-24 — four different shapes, and none of them announce which is which.

The fix has to be sequenced carefully. Slash-separated dates first, then short year-dashes, then anything left over gets checked digit-by-digit (a month value over 12 can only mean the format was MM-DD-YYYY, not DD-MM-YYYY):

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'DD/MM/YYYY')::TEXT
WHERE departure_date LIKE '%/%';

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'DD-MM-YYYY')::TEXT
WHERE departure_date LIKE '%-%' AND LENGTH(departure_date) = 8;

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'MM-DD-YYYY')::TEXT
WHERE departure_date LIKE '%-%'
    AND LENGTH(departure_date) = 10
    AND SPLIT_PART(departure_date, '-', 2)::INTEGER > 12;

Every one of those four rows now speaks the same language: YYYY-MM-DD, ready to be cast to a real DATE type.

Money hiding behind the word "KES"

SELECT booking_id, fare_per_seat, total_fare FROM safari_connect.staging_bookings;

27 rows across fare_per_seat and total_fare carry a KES prefix that turns a number into a string PostgreSQL can't do arithmetic on. REGEXP_REPLACE strips anything that isn't a digit:

UPDATE safari_connect.staging_bookings
SET total_fare = REGEXP_REPLACE(total_fare, '[^0-9]', '', 'g')
WHERE total_fare SIMILAR TO '%[^0-9]%';

Ratings that shouldn't exist, seats that go negative, and a booking that shows up twice

Three more categories of trouble, each fixed with a different weapon:

Impossible ratings. A 1–5 scale should never contain a 0 or a 6. 13 rows did.

SELECT DISTINCT(trip_rating) FROM safari_connect.staging_bookings;

UPDATE safari_connect.staging_bookings
SET trip_rating = NULL
WHERE TRIM(trip_rating) NOT IN ('1', '2', '3', '4', '5', '');

A rating that can't be trusted is worse than no rating at all. This turns it into an honest NULL rather than a lie that would quietly drag down an average.

Negative seats. A booking for -1 seats makes no physical sense. You can't un-book a seat that was never booked in the first place, so the project rule is to delete rather than guess:

DELETE FROM safari_connect.staging_bookings
WHERE NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9-]', '', 'g'), '')::INTEGER < 1;

An exact duplicate. BK0005 — Esther Njoroge, same date, same seats appears twice:

DELETE FROM safari_connect.staging_bookings
WHERE ctid NOT IN
    (SELECT MIN(ctid) FROM safari_connect.staging_bookings GROUP BY booking_id);

ctid is Postgres' own physical row address and it's what makes this command safe. booking_id is supposed to be unique, so you can't self-join on it to find "the other one" without ambiguity; ctid never lies about which physical row is which, so keeping the MIN(ctid) per booking_id keeps exactly one copy and drops the rest.

Loading the cleaned data

To load and verify the cleaned data into the clean_bookings table, we use the following script:

INSERT INTO safari_connect.clean_bookings
SELECT 
        booking_id, TRIM(passenger_name),
        NULLIF(TRIM(passenger_phone), ''),
        passenger_gender, COALESCE(NULLIF(TRIM(passenger_city),''), 'Unknown'),
        route_code, route_from, route_to, vehicle_plate,
        INITCAP(TRIM(vehicle_type)), TRIM(driver_name),
        NULLIF(REGEXP_REPLACE(driver_rating, '[^0-9.]', '', 'g'), '')::NUMERIC,
        departure_date::DATE, departure_time, seat_class,
        NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9]', '', 'g'), '')::INTEGER,
        NULLIF(REGEXP_REPLACE(fare_per_seat, '[^0-9]', '', 'g'), '')::NUMERIC,
        NULLIF(REGEXP_REPLACE(total_fare, '[^0-9]', '', 'g'), '')::NUMERIC,
        payment_method, booking_status,
        NULLIF(trip_rating, '')::INTEGER 
FROM safari_connect.staging_bookings 
WHERE departure_date SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}'
AND NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9]', '', 'g'), '')::INTEGER > 0;

-- Verify script
SELECT count(*) FROM safari_connect.clean_bookings;
SELECT DISTINCT booking_status FROM safari_connect.clean_bookings;

Once we verify the script, we get the following outputs respectively:

Between the negative-seat row and the duplicate, the table drops from 290 rows to 283. These were small, deliberate cuts, each backed by a SELECT that confirmed what was about to be deleted before it was deleted.

Casing was fixed the same INITCAP(TRIM()) way for passenger_city, driver_name, and vehicle_type; seat_class, payment_method, and booking_status each got their own CASE WHEN to collapse abbreviations like ECO/BUS/BUSINESS CLASS down to the two or three values the business actually recognises. The pattern repeats, but the logic behind each one is worth the same scrutiny as the flashier fixes above.

Chapter 3: The View That Everything Else Stands On

Once clean_bookings holds trustworthy data, a view(v_clean_trips) sits on top of it and does the last mile of preparation. It contains the data of the trips that were completed. Here is the script used to create v_clean_trips:

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 safari_connect.clean_bookings
WHERE booking_status = 'Completed';

-- Test v_clean_trips
SELECT count(*) FROM v_clean_trips;

After running the test script, the rows are further cleaned to give an output of 248 rows:

The v_clean_trips table is what will be used to create the Power BI presentation for this project. Every query(except Q5) in the next section reads from it.

Chapter 4: Six Questions, Six Answers

This is where the cleaning pays off. The Operations Director asked six things. Here is what the data actually said, query by query, output by output.

Q1. Which routes earn the most, which are most popular, and which are most efficient?

Three angles on the same ten routes, because "best" means different things to different people in the room.

Highest earner:

SELECT route_code, route_from, route_to, SUM(calculated_fare) AS total_fare_per_route
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_fare_per_route DESC;

Output:

Nairobi–Mombasa (RT001) is the clear top earner, pulling in KES 62,400.00 — nearly 20,000 more than the second-place route.

Most popular by demand:

SELECT route_code, route_from, route_to,
       COUNT(booking_id) AS num_bookings,
       SUM(seats_booked) AS total_seats_sold
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_seats_sold DESC;

Output:
| route_code | route_from | route_to | num_bookings | total_seats_sold |
|---|---|---|---|---|
| RT005 | Nairobi | Thika | 35 | 71 |
| RT007 | Nairobi | Nyeri | 31 | 59 |
| RT010 | Nairobi | Naivasha | 27 | 55 |
| RT004 | Nairobi | Eldoret | 28 | 53 |
| RT001 | Nairobi | Mombasa | 30 | 50 |
| RT003 | Nairobi | Nakuru | 29 | 49 |
| RT008 | Kisumu | Kakamega | 29 | 47 |
| RT006 | Mombasa | Malindi | 27 | 47 |
| RT002 | Nairobi | Kisumu | 28 | 45 |
| RT009 | Nairobi | Machakos | 24 | 43 |

Here the story flips: Nairobi–Thika (RT005) wins on volume(the most bookings and the most seats sold), even though it earns the least revenue of all ten routes. It's a short, cheap, high-frequency commuter run, not a premium long-haul product, and the two metrics were never going to agree.

Most efficient per seat:

SELECT route_code, route_from, route_to,
       SUM(seats_booked) AS total_seats_sold,
       ROUND(AVG(trip_rating), 2) AS avg_rating,
       ROUND(SUM(calculated_fare) / NULLIF(SUM(seats_booked), 0), 2) AS revenue_per_seat
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY revenue_per_seat DESC;

Output:
| route_code | route_from | route_to | total_seats_sold | avg_rating | revenue_per_seat |
|---|---|---|---|---|---|
| RT001 | Nairobi | Mombasa | 50 | 3.48 | 1,248.00 |
| RT002 | Nairobi | Kisumu | 45 | 4.0 | 970.00 |
| RT004 | Nairobi | Eldoret | 53 | 3.5 | 845.28 |
| RT003 | Nairobi | Nakuru | 49 | 3.59 | 486.73 |
| RT007 | Nairobi | Nyeri | 59 | 3.23 | 423.73 |
| RT006 | Mombasa | Malindi | 47 | 3.62 | 364.89 |
| RT010 | Nairobi | Naivasha | 55 | 3.33 | 290.18 |
| RT009 | Nairobi | Machakos | 43 | 3.57 | 216.28 |
| RT008 | Kisumu | Kakamega | 47 | 3.52 | 193.40 |
| RT005 | Nairobi | Thika | 71 | 3.5 | 123.38 |

RT001 wins a third time. It brings in the highest revenue, most efficient per seat. Nairobi–Mombasa isn't just the biggest number on the board; it's the healthiest one, generating more shillings per seat sold than any other route.

A window-function version pulls all three lenses into one CTE and adds a revenue-share column:

WITH route_metrics AS (
    SELECT route_code, route_from || ' - ' || route_to AS route,
           COUNT(DISTINCT booking_id) AS total_bookings,
           SUM(seats_booked) AS total_seats_sold,
           SUM(total_fare) AS total_revenue,
           SUM(total_fare) / NULLIF(SUM(seats_booked), 0) AS revenue_per_seat
    FROM v_clean_trips
    WHERE booking_status = 'Completed'
    GROUP BY route_code, route_from, route_to
)
SELECT route, total_revenue, total_seats_sold, total_bookings,
       ROUND(revenue_per_seat, 2) AS revenue_per_seat,
       ROUND(total_revenue * 100.0 / SUM(total_revenue) OVER (), 2) AS revenue_share_pct
FROM route_metrics
ORDER BY total_revenue DESC;

Output:
| route | total_revenue | total_seats_sold | total_bookings | revenue_per_seat | revenue_share_pct |
|---|---|---|---|---|---|
| Nairobi - Mombasa | 62,400.00 | 50 | 30 | 1,248.00 | 24.0 |
| Nairobi - Eldoret | 44,800.00 | 53 | 28 | 845.28 | 17.23 |
| Nairobi - Kisumu | 43,650.00 | 45 | 28 | 970.00 | 16.79 |
| Nairobi - Nyeri | 25,000.00 | 59 | 31 | 423.73 | 9.62 |
| Nairobi - Nakuru | 23,850.00 | 49 | 29 | 486.73 | 9.17 |
| Mombasa - Malindi | 17,150.00 | 47 | 27 | 364.89 | 6.6 |
| Nairobi - Naivasha | 15,960.00 | 55 | 27 | 290.18 | 6.14 |
| Nairobi - Machakos | 9,300.00 | 43 | 24 | 216.28 | 3.58 |
| Kisumu - Kakamega | 9,090.00 | 47 | 29 | 193.40 | 3.5 |
| Nairobi - Thika | 8,760.00 | 71 | 35 | 123.38 | 3.37 |

That last column — revenue_share_pct, built with SUM(...) OVER (), is what turns "RT001 earns a lot" into a boardroom sentence: RT001 alone accounts for roughly a quarter of all route revenue. That's the kind of dependency a CEO needs to hear out loud.

Q2. Who are the best drivers, and does their rating actually affect how happy passengers are?

WITH driver_stats AS (
    SELECT driver_name,
           COUNT(DISTINCT booking_id) AS total_completed_bookings,
           SUM(total_fare) AS total_revenue,
           ROUND(AVG(driver_rating), 2) AS avg_driver_rating,
           ROUND(AVG(trip_rating), 2) AS avg_trip_rating,
           CORR(driver_rating, trip_rating) AS rating_correlation
    FROM v_clean_trips
    WHERE booking_status = 'Completed'
      AND trip_rating IS NOT NULL
      AND driver_rating IS NOT NULL
    GROUP BY driver_name
)
SELECT * FROM driver_stats ORDER BY total_revenue DESC;

Output:
| driver_name | total_completed_bookings | total_revenue | avg_driver_rating | avg_trip_rating |
|---|---|---|---|---|
| Isaac Korir | 33 | 33,045.00 | 3.8 | 3.39 |
| Kelvin Omondi | 32 | 30,855.00 | 4.5 | 3.32 |
| Brian Kamau | 26 | 29,340.00 | 4.2 | 3.73 |
| Tony Karanja | 34 | 28,250.00 | 3.9 | 3.74 |
| Samuel Gitonga | 33 | 28,235.00 | 4.6 | 3.59 |
| Hassan Abdi | 30 | 27,575.00 | 4.1 | 3.9 |
| Moses Kipchoge | 30 | 26,630.00 | 4.8 | 3.19 |
| Peter Ngugi | 35 | 23,880.00 | 4.3 | 3.41 |

Isaac Korir generates the most revenue of any driver, despite carrying a mid-table 3.8 platform rating. This is proof that a driver's star rating and their commercial value to the company are not the same conversation. The CORR() window function makes that gap official: the correlation between a driver's platform rating and the trip ratings passengers actually leave comes out at -0.101(essentially no relationship). A high driver rating does not reliably buy a happier passenger, which means promotion decisions built purely on stars would be pointing at the wrong signal.

Q3. How is revenue trending, and what were the best and worst months?

WITH monthly_revenue AS (
    SELECT date_trunc('month', departure_date) AS month,
           SUM(total_fare) AS revenue,
           COUNT(DISTINCT booking_id) AS bookings
    FROM v_clean_trips
    GROUP BY date_trunc('month', departure_date)
),
monthly_growth AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_monthly_revenue,
           ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) 
                  / LAG(revenue) OVER (ORDER BY month)) * 100, 2) AS growth_pct
    FROM monthly_revenue
)
SELECT to_char(month, 'YYYY-MM') AS month_label, revenue, growth_pct,
       RANK() OVER (ORDER BY revenue DESC) AS rank_month
FROM monthly_growth
ORDER BY month;

Output:
| month | revenue | bookings | growth_pct |
|---|---|---|---|
| 2024-01 | 18,475.00 | 20 | |
| 2024-02 | 19,945.00 | 22 | 7.96 |
| 2024-03 | 20,325.00 | 23 | 1.91 |
| 2024-04 | 18,390.00 | 23 | -9.52 |
| 2024-05 | 22,060.00 | 28 | 19.96 |
| 2024-06 | 23,425.00 | 25 | 6.19 |
| 2024-07 | 23,395.00 | 22 | -0.13 |
| 2024-08 | 16,740.00 | 18 | -28.45 |
| 2024-09 | 23,735.00 | 26 | 41.79 |
| 2024-10 | 25,230.00 | 28 | 6.3 |
| 2024-11 | 21,650.00 | 24 | -14.19 |
| 2024-12 | 22,205.00 | 25 | 2.56 |
| 2025-01 | 4,385.00 | 4 | -80.25 |

LAG() is doing the heavy lifting here. It reaches back one row (one month) without a self-join, which is exactly what month-over-month growth needs. The trend is choppy rather than a clean upward line. October is the standout month, September posts the sharpest single jump (+41.8%), and August is the year's quiet trough. The final row, January 2025, is a partial month in the export rather than a genuine collapse. This is worth flagging in the boardroom so nobody reads a half-month as a crisis.

Q4. Where do passengers come from, what do they book, and are they happy?

SELECT passenger_city, COUNT(DISTINCT booking_id) AS bookings, SUM(total_fare) AS revenue
FROM v_clean_trips
WHERE booking_status = 'Completed'
GROUP BY passenger_city
ORDER BY bookings DESC
LIMIT 10;

Output:
| passenger_city | bookings | revenue |
|---|---|---|
| Nairobi | 113 | 112,000.00 |
| Kisumu | 26 | 20,980.00 |
| Eldoret | 24 | 13,910.00 |
| Mombasa | 20 | 21,320.00 |
| Meru | 14 | 11,655.00 |
| Nakuru | 13 | 6,300.00 |
| Thika | 13 | 16,740.00 |
| Unknown | 11 | 9,425.00 |
| Nyeri | 10 | 8,220.00 |
| Machakos | 9 | 7,260.00 |

Nairobi dominates, contributing far more bookings and revenue than every other city combined, unsurprising for a Nairobi-headquartered platform, but worth quantifying rather than assuming.

Seat class tells its own story:

SELECT seat_class, COUNT(DISTINCT booking_id) AS bookings, SUM(total_fare) AS revenue,
       ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
WHERE booking_status = 'Completed'
GROUP BY seat_class;

Output:
| seat_class | bookings | revenue | avg_rating |
|---|---|---|---|
| Business | 52 | 38,210.00 | 3.65 |
| Economy | 201 | 189,600.00 | 3.5 |

Economy carries nearly four times the booking volume of Business, which is expected, but Business passengers rate their trips slightly higher on average, a small signal that the premium seat class is delivering on its premium.

Satisfaction, at a glance:

SELECT satisfaction, COUNT(booking_id) AS total_trips
FROM v_clean_trips
GROUP BY satisfaction
ORDER BY total_trips DESC;

Output:
| satisfaction | total_trips |
|---|---|
| Satisfied | 119 |
| Neutral | 74 |
| Unsatisfied | 50 |
| No Rating | 45 |

More passengers land in "Satisfied" than in any other bucket, but "Unsatisfied" and "No Rating" together are not a small slice of the pie. They're enough to be worth a follow-up on why so many trips go unrated at all.

And a cross-cut for completeness:

SELECT passenger_gender, seat_class, COUNT(booking_id) AS total_bookings
FROM v_clean_trips
GROUP BY passenger_gender, seat_class
ORDER BY passenger_gender, seat_class, total_bookings DESC;

Output:
| passenger_gender | seat_class | total_bookings |
|---|---|---|
| Female | Business | 29 |
| Female | Economy | 112 |
| Male | Business | 30 |
| Male | Economy | 117 |

There's no meaningful gender skew in seat class preference. Both groups book Economy at roughly the same rate. Sometimes the most useful finding is confirming that a variable doesn't matter, so nobody chases it later.

Q5. What's the cancellation rate per route, and what did it cost?

Note: we use the clean_bookings table for this query because v_clean_trips only contains completed trips

WITH route_totals AS (
    SELECT route_code,
           COUNT(*) AS total_bookings,
           COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS cancelled_bookings,
           SUM(total_fare) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_revenue
    FROM safari_connect.clean_bookings
    GROUP BY route_from, route_to, route_code
)
SELECT route_code, total_bookings, cancelled_bookings,
       ROUND(cancelled_bookings * 100.0 / total_bookings, 2) AS cancellation_rate_pct,
       lost_revenue
FROM route_totals
ORDER BY cancellation_rate_pct DESC;

Output:
| route_code | total_bookings | cancelled_bookings | lost_revenue | cancellation_rate_pct |
|---|---|---|---|---|
| RT006 | 27 | 5 | 2,800.00 | 18.52 |
| RT009 | 24 | 4 | 2,000.00 | 16.67 |
| RT005 | 35 | 5 | 1,140.00 | 14.29 |
| RT003 | 29 | 4 | 3,150.00 | 13.79 |
| RT008 | 29 | 4 | 1,620.00 | 13.79 |
| RT001 | 30 | 4 | 10,800.00 | 13.33 |
| RT002 | 28 | 3 | 5,400.00 | 10.71 |
| RT007 | 31 | 3 | 2,800.00 | 9.68 |
| RT010 | 27 | 2 | 840.00 | 7.41 |
| RT004 | 28 | 1 | 1,600.00 | 3.57 |

SELECT SUM(total_fare) AS total_lost_revenue
FROM safari_connect.clean_bookings
WHERE booking_status IN ('Cancelled', 'No Show');

total_lost_revenue: 32,150.00

RT006 (Mombasa–Malindi) has the worst cancellation rate at over 18%, which is nearly one in five bookings on that route. But the bigger shilling figure sits with RT001: even a modest cancellation rate on the highest-earning route still bleeds real money, because 13% of a big number is bigger than 18% of a small one. Across the whole business, cancellations and no-shows cost Safari Connect KES 32,150.00, money that a firmer deposit or reminder policy could claw back.

Q6. When are we busiest, and when should we add more vehicles?

SELECT EXTRACT(HOUR FROM departure_time::time) AS hour_of_day,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_passengers
FROM v_clean_trips
GROUP BY EXTRACT(HOUR FROM departure_time::time)
ORDER BY total_passengers DESC;

10 a.m. is the single busiest departure hour, with 6 a.m. right behind it, exactly as you'd expect from a network anchored on Nairobi.

Breaking it down further by day and hour sharpens the picture:

SELECT day_name, EXTRACT(HOUR FROM departure_time::time) AS hour_of_day,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_passengers
FROM v_clean_trips
GROUP BY day_name, EXTRACT(HOUR FROM departure_time::time)
ORDER BY total_passengers DESC;

Thursday at 10 a.m. is the single busiest slot in the entire dataset. If Safari Connect is going to add a vehicle anywhere, it's that slot, and the Monday evening rush right behind it is where it pays for itself fastest.

Chapter 5: What the CEO Actually Needs to Hear

Strip away the SQL and six sentences remain:

1. RT001 (Nairobi–Mombasa) is the crown jewel. It brings in the highest revenue, highest revenue-per-seat, and roughly a quarter of all route income. Protect it.
2. RT005 (Nairobi–Thika) is the volume play. It holds the most bookings, most seats, lowest revenue. It's a different business model wearing the same uniform so price it like one.
3. Driver star ratings don't predict passenger satisfaction (correlation ≈ -0.101). Promote on revenue and completed trips, not on stars alone.
4. Revenue is trending up but noisy, with October as the peak and August as the trough. It's not a straight line, so month-to-month swings shouldn't trigger panic on their own.
5. Cancellations cost KES 32,150.00 across the year, concentrated on RT006 and, in absolute terms, RT001. A deposit or reminder policy targeted at these two routes is the highest-leverage fix available.
6. Thursday 10 a.m. is the network's busiest single slot. That's where the next vehicle belongs.

The Part That Doesn't Show Up in a Query

None of the numbers above exist without the tedious work that came first i.e. the TRIM(), the REGEXP_REPLACE, the careful SELECT before every DELETE. It's tempting to treat data cleaning as a chore standing between an analyst and the "real" analysis. This project is a good argument against that instinct: every single business answer above; the top route, the best driver or the busiest hour would have been wrong, or at least unreliable, on the raw 290-row export. 99% of the credibility in this presentation was earned in Chapter 2, not Chapter 4.

Matatus and buses move millions of Kenyans every day. Somewhere behind the app, the M-Pesa prompt, and the seat selection screen, there is a table like this one, quietly deciding whether the person running the company can trust their own numbers. In this instance, for Safari Connect, it finally can.