SafariConnect: From a Broken Excel Export to a Board-Ready Analysis in PostgreSQL

SafariConnect is a Nairobi bus and matatu booking platform. Passengers book seats online for routes like Nairobi to Mombasa or Nairobi to Eldoret and pay by M-Pesa, cash or card. Since 2024 every booking had been kept in one shared Excel file.

The brief from the Operations Director was short: clean the data, load it into PostgreSQL, and answer six questions. Which routes make money. Which drivers should be promoted. How revenue moves month by month. Where passengers come from. How much revenue cancellations cost. When the busiest travel times are. Then present it to the board.

This article is the whole project: the data as it arrived, the cleaning, the analysis, the findings, and the parts that went wrong. Every number below comes from running the queries.

The data as it arrived

One CSV, 290 rows, 21 columns. Booking ID, passenger name, phone, gender and city; route code, origin and destination; vehicle plate and type; driver name and rating; departure date and time; seat class, seats booked, fare per seat, total fare; payment method, booking status, trip rating.

The first thing I did was the audit: a SELECT DISTINCT or a pattern check on every column before changing anything. This is what came back.

SELECT passenger_gender, COUNT(*)
FROM bookings_staging
GROUP BY passenger_gender
ORDER BY COUNT(*) DESC;

Eight spellings of two genders. Nine spellings of two seat classes (Economy, economy, eco, ECO, economy class, Business, business, BUSINESS CLASS, BUS). Ten spellings of three payment methods. Eight of three booking statuses. Six of three vehicle types. Forty-three passenger names in the wrong case. Thirteen blank cities. Fares stored as KES 2400 or KES 1,200 in 28 rows. Trip ratings of 0, 6 and 7 in 13 rows. Booking BK0005 twice. One booking with -1 seats and a fare of -900.

Then two columns that were worse than the brief suggested, and the reason is instructive.

What Excel did to the file

The dates: 281 rows in DD/MM/YYYY and nine in MM-DD-YYYY. Not one row was in the YYYY-MM-DD form the database wants.

The phones were the real damage:

SELECT passenger_phone, COUNT(*)
FROM bookings_staging
WHERE passenger_phone !~ '^0\d{9}$'
GROUP BY passenger_phone
ORDER BY COUNT(*) DESC;

277 of 290 phone numbers failed the 07XXXXXXXX check. 249 of them had lost their leading zero (712345678 instead of 0712345678). Fifteen had become 2.54712E+11. Thirteen had dashes. That pattern has one cause: the CSV had been opened and saved in Excel. Excel reads 0712345678 as the number 712,345,678 and drops the zero; it reads 254712345678 as a number too big to display and shows it in scientific notation, and when the file is saved that's what gets written. The original digits are gone.

So before any SQL, the lesson: never open a CSV you're about to import in Excel. If you must look at it, import it into Excel as text, or open it in a text editor. None of the 15 scientific-notation phones could be recovered by any query, because the information no longer existed in the file.

Cleaning

The structure was the one I use for every messy file: a staging table with every column as TEXT, a working copy, a cleaning script, then a typed production table with constraints.

CREATE SCHEMA safari_connect;
SET search_path TO safari_connect;

CREATE TABLE 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
);
-- import the CSV with pgAdmin's Import/Export tool, then:
CREATE TABLE cleaning AS SELECT * FROM bookings_staging;

The category columns were CASE statements. Gender, for example:

UPDATE cleaning
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;

Same shape for seat class, payment method, booking status and vehicle type. Names and cities got INITCAP(TRIM(...)); blank cities became 'Unknown' rather than NULL so they'd still show up in a GROUP BY.

Phones: dashes stripped, the lost zero put back, the scientific-notation ones set to NULL because there was nothing to recover.

UPDATE cleaning SET passenger_phone = NULL
WHERE passenger_phone ~ 'E\+' OR TRIM(passenger_phone) = '';

UPDATE cleaning
SET passenger_phone = REGEXP_REPLACE(passenger_phone, '[^0-9]', '', 'g');

UPDATE cleaning SET passenger_phone = '0' || passenger_phone
WHERE passenger_phone ~ '^7\d{8}$';

Fares: strip everything that isn't a digit, a dot or a minus sign. Dates: one UPDATE per format, using the separator to tell them apart, the same method as in my Tembo Hotel article.

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

UPDATE cleaning
SET departure_date = TO_DATE(departure_date, 'MM-DD-YYYY')::TEXT
WHERE departure_date ~ '^\d{2}-\d{2}-\d{4}$';

Ratings outside 1 to 5 became NULL. The duplicate and the negative-seat row were deleted. 290 rows became 288.

Then the production table, with the rules written into it:

CREATE TABLE bookings (
    booking_id       VARCHAR(10) PRIMARY KEY,
    passenger_gender VARCHAR(10) CHECK (passenger_gender IN ('Male', 'Female')),
    vehicle_type     VARCHAR(20) CHECK (vehicle_type IN ('Bus', 'Matatu', 'Minibus')),
    departure_date   DATE,
    seat_class       VARCHAR(20) CHECK (seat_class IN ('Economy', 'Business')),
    seats_booked     INTEGER CHECK (seats_booked > 0),
    fare_per_seat    NUMERIC(10,2),
    total_fare       NUMERIC(12,2),
    payment_method   VARCHAR(20) CHECK (payment_method IN ('M-Pesa', 'Cash', 'Card')),
    booking_status   VARCHAR(20) CHECK (booking_status IN ('Completed', 'Cancelled', 'No Show')),
    trip_rating      INTEGER CHECK (trip_rating BETWEEN 1 AND 5)
    -- plus the name, phone, city, route, plate, driver and time columns
);

INSERT INTO bookings SELECT ... FROM cleaning;   -- with the ::DATE, ::INTEGER, ::NUMERIC casts

If any spelling had slipped through, this INSERT would have refused it. It didn't, which is the real test that the cleaning was complete.

Finally a view for the analysis. Cancelled and no-show bookings earn nothing, so almost every question is about completed trips only. Rather than write WHERE booking_status = 'Completed' twenty times, I put it in a view, along with the derived columns the questions needed:

CREATE OR REPLACE VIEW v_clean_trips AS
SELECT *,
       TO_CHAR(departure_date, 'YYYY-MM') AS travel_month,
       TO_CHAR(departure_date, 'Day')     AS day_name,
       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';

253 completed trips. Total revenue KES 227,810. 452 seats. Those three numbers are the ones I checked everything else against.

The six questions

1. Which routes make money?

SELECT route_code,
       route_from || ' -> ' || route_to AS route,
       COUNT(*)            AS bookings,
       SUM(seats_booked)   AS seats,
       SUM(total_fare)     AS revenue,
       ROUND(AVG(fare_per_seat), 0) AS avg_fare
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY revenue DESC;

!Revenue by route: RT001 first at 51,600, RT009 last at 7,300
RT001 Nairobi to Mombasa: KES 51,600 from 41 seats. RT004 Nairobi to Eldoret: 43,200. RT002 Nairobi to Kisumu: 38,250. Those three are 58.5% of all revenue. At the bottom, RT005 Nairobi to Thika earned 7,620, and it did that from 62 seats, the most seats of any route. That's the finding that matters: Thika is the busiest route and the worst earner, because the fare is KES 126 a seat against 1,292 for Mombasa. Popularity and profit are different questions.

A window function made the shares easy:

WITH route_rev AS (
    SELECT route_code, SUM(total_fare) AS revenue
    FROM v_clean_trips
    GROUP BY route_code
)
SELECT route_code, revenue,
       RANK() OVER (ORDER BY revenue DESC)                 AS revenue_rank,
       ROUND(revenue * 100.0 / SUM(revenue) OVER (), 1)    AS pct_of_total
FROM route_rev
ORDER BY revenue_rank;

SUM(revenue) OVER () with empty brackets is the total of the whole result, on every row, without collapsing anything. That one line replaces a subquery.

By vehicle type: Matatu 38.6% of revenue, Bus 37.1%, Minibus 24.3%. Minibus trips had the best average rating (3.71) despite earning least.

2. Which drivers should be promoted?

SELECT driver_name,
       COUNT(*)                     AS trips,
       SUM(total_fare)              AS revenue,
       ROUND(AVG(trip_rating), 2)   AS avg_trip_rating,
       MAX(driver_rating)           AS driver_rating
FROM v_clean_trips
GROUP BY driver_name
ORDER BY revenue DESC;

Isaac Korir led on revenue (KES 33,045 from 33 trips) with a platform driver rating of 3.8, the lowest of all eight drivers. Moses Kipchoge had the highest driver rating (4.8) and the lowest passenger trip rating (3.19). So I checked the question HR actually cared about:

SELECT CASE WHEN driver_rating >= 4.5 THEN 'High-rated (4.5+)'
            ELSE 'Standard (<4.5)' END          AS driver_group,
       COUNT(*)                                  AS trips,
       ROUND(AVG(trip_rating), 2)                AS avg_passenger_rating
FROM v_clean_trips
GROUP BY 1;

High-rated drivers: 95 trips, average passenger rating 3.38. Standard drivers: 158 trips, 3.62. The drivers the platform rates highest get lower ratings from passengers. Overall, average driver rating 4.27 against average trip rating 3.53. Whatever the driver rating measures, it isn't what passengers experience, and I'd stop using it on its own for promotion decisions.

Ranking drivers within their vehicle type used RANK() OVER (PARTITION BY vehicle_type ORDER BY revenue DESC). Isaac Korir was first overall and first among Matatu drivers; Kelvin Omondi second overall but first among Bus drivers. That distinction matters if the promotion is per fleet.

3. How is revenue moving month by month?

This is the CTE-plus-LAG query, and it's the one I'd defend in front of anyone.

WITH monthly AS (
    SELECT TO_CHAR(departure_date, 'YYYY-MM') AS month,
           COUNT(*)        AS bookings,
           SUM(total_fare) AS revenue
    FROM v_clean_trips
    GROUP BY 1
)
SELECT month, bookings, revenue,
       LAG(revenue) OVER (ORDER BY month)                       AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month)             AS change,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS change_pct,
       SUM(revenue) OVER (ORDER BY month)                       AS running_total
FROM monthly
ORDER BY month;

LAG(revenue) reaches back one row and fetches the previous month's number onto the current row. NULLIF(..., 0) stops a divide-by-zero if a month ever had no revenue. SUM(revenue) OVER (ORDER BY month) is a running total: because there's an ORDER BY inside the OVER, it sums everything up to the current row instead of the whole table.

The result: no trend. Revenue sat between KES 13,400 and 24,190 every month of 2024. August dropped 41.4% to 13,400, the worst month; October was the best at 24,190. The running total reached 226,125 by December. January 2025 shows 1,685 from three bookings, which is not a collapse, just a partial month at the end of the export, and it's the kind of row you have to explain before someone reads it as a crisis.

4. Who are the passengers?

Nairobi: 113 of 253 completed trips, KES 112,000, just under half of everything. Then Kisumu (26), Eldoret (24), Mombasa (20). Eleven trips had no city; they show as Unknown because I chose that over NULL during cleaning.

Women booked slightly more than men (137 to 116 trips) and brought in more revenue (120,885 to 106,925). Economy is about 80% of bookings for both.

Satisfaction, from the view's CASE column: 46.2% Satisfied, 28.9% Neutral, 19.4% Unsatisfied, 5.5% No Rating. Nearly one trip in five is rated 1 or 2 stars.

5. What do cancellations cost?

This question runs on the bookings table, not the view, because it needs the non-completed rows.

SELECT booking_status,
       COUNT(*)        AS bookings,
       SUM(total_fare) AS fare_value,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM bookings
GROUP BY booking_status
ORDER BY bookings DESC;

253 completed (87.8%), 21 cancelled (7.3%), 14 no-shows (4.9%). The fare value of the cancelled and no-show bookings: KES 32,150, which is 12.4% of what the company did earn. The Director's feeling that the cancellation rate was high was right, and now it has a price on it.

By route, RT006 Mombasa to Malindi had the worst rate (18.5%, mostly no-shows), RT004 the best (3.6%). RT004 is also the second-best earner, which is a route worth studying rather than a coincidence.

6. When is it busy?

SELECT EXTRACT(DOW FROM departure_date)  AS day_num,
       TO_CHAR(departure_date, 'Day')    AS day_name,
       COUNT(*)                          AS bookings,
       SUM(total_fare)                   AS revenue
FROM v_clean_trips
GROUP BY 1, 2
ORDER BY 1;

Monday to Thursday carry the business: 44 to 49 trips each, KES 36,000 to 47,000. Sunday has 10. By departure time, 09:00 and 06:00 are the fullest slots, but 19:00 earns the most (22,240) on fewer seats, so the evening departures carry the higher-fare passengers. Average seats per booking is under 2 for every vehicle type, so no vehicle type runs full on this data.

The views

Each question became a view, so the dashboard could point at a named object instead of a pasted query:

CREATE OR REPLACE VIEW v_route_performance AS
SELECT route_code,
       route_from || ' -> ' || route_to AS route,
       COUNT(*) AS bookings, SUM(seats_booked) AS seats,
       SUM(total_fare) AS revenue, ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
GROUP BY route_code, route_from, route_to;

CREATE OR REPLACE VIEW v_driver_performance AS
SELECT driver_name, COUNT(*) AS total_trips, SUM(total_fare) AS total_driver_revenue,
       ROUND(AVG(trip_rating), 2) AS avg_trip_rating, MAX(driver_rating) AS driver_rating
FROM v_clean_trips
GROUP BY driver_name;

CREATE OR REPLACE VIEW v_monthly_revenue AS
WITH monthly AS (
    SELECT TO_CHAR(departure_date, 'YYYY-MM') AS month,
           COUNT(*) AS bookings, SUM(total_fare) AS revenue
    FROM v_clean_trips GROUP BY 1
)
SELECT month, bookings, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS change_pct
FROM monthly;

CREATE OR REPLACE VIEW v_passenger_insights AS
SELECT passenger_city, COUNT(*) AS total_bookings,
       SUM(total_fare) AS total_revenue_per_city,
       ROUND(AVG(fare_per_seat), 0) AS total_average_fare
FROM v_clean_trips
GROUP BY passenger_city;

A view has no data of its own. Change a row in bookings and every view reflects it on the next query. That's the point of building the dashboard on views: the SQL lives in one place, in the database, and the BI tool only asks for results.

What I'd say to the board

Three routes make 58% of the money; Nairobi to Mombasa alone makes 22.7%. Add capacity there.

The Thika route is the busiest and the least profitable. Either the fare is wrong or the route exists for a reason other than revenue. Decide which.

Cancellations and no-shows cost KES 32,150, one shilling in eight. Mombasa to Malindi loses most; look at the no-shows there first.

The platform's driver rating does not predict passenger satisfaction. Use trip ratings and revenue for promotion decisions.

There is no growth trend across 2024. August is the month to fix.

What went wrong, and what I learned

The phones. I spent time trying to recover 2.54712E+11 before accepting that the digits were gone. The lesson is upstream of SQL: the export was damaged before I received it, and the fix is a rule about how CSVs are handled, not a cleverer query.

The dates looked like one problem and were two. 281 rows in one format, 9 in another, and the nine were the American order. If I'd converted everything as DD/MM/YYYY, PostgreSQL would have rejected 01-18-2024 (month 18) and I'd have caught it, but 01-05-2024 would have silently become 1 May instead of 5 January. Check formats by pattern before converting, every time.

The January 2025 row. Three bookings, KES 1,685, a 91.7% drop. On a chart it looks like the company died. It's a partial month. Any monthly analysis needs a sentence about where the data starts and stops.

And the one that changed how I read the data: "busiest" and "most profitable" pointed at different routes, and "best-rated driver" and "best-rated trips" pointed at different drivers. Every time a question has two reasonable measures, run both. The gap between them is usually the finding.