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…
In 2024, Safari Connect, a Nairobi-based bus and matatu booking platform, began generating substantial booking history data. The CEO tasked the Operations Director with cleaning the data, loading it into the database, analyzing it, and presenting findings on Friday. The source material details the process of transforming a 290-row CSV file with 23 categories of issues into six clear business answers, supported by Kenyan shilling figures.
Initially, the raw CSV was loaded into a staging table named "staging_bookings" with all columns as TEXT to prevent type mismatches. The analyst then created a cleaned table, "clean_bookings," with appropriate data types for each column, including a VARCHAR(10) PRIMARY KEY for booking_id. This allowed for the creation of views and the presentation of actual SQL output in a database GUI like DBeaver.
The analyst identified 23 categories of dirty data within the export. These issues included inconsistent passenger names, such as extra spaces and capitalization errors, which were resolved using SQL's INITCAP(TRIM(...)) function.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
