From a Messy Spreadsheet to Business Insights: Analyzing a Hotel's Booking Data

How I cleaned 285 rows of real-world-style hotel data in PostgreSQL and turned it into revenue, occupancy, and guest-satisfaction insights in Power BI.

The brief

Tembo Hotel & Suites is a mid-range business hotel in Nairobi that has been running since 2023. Like a lot of small hospitality businesses, its booking records are kept in a spreadsheet and the spreadsheet was a mess.

The ask from the Hotel Director was simple to state and harder to deliver:

"I need you to clean this data, load it into our new database, analyse it, and present your findings to the management team in two days. I want to know: which rooms make us the most money, which months are busiest, how our staff are performing, and whether our guests are happy. Make it look professional — use Power BI for the visuals."

This is the story of that pipeline: from a dirty CSV export to a 3-page Power BI report, and the business decisions the numbers actually point to.

Step 1: Staging everything as text

The raw export had 285 rows and the kind of problems anyone who has cleaned real operational data will recognize: inconsistent capitalization, dates in two different formats mixed in the same column, currency values stored as strings like KES 1,000, phone numbers with stray country codes and dashes, ratings outside the valid 1–5 range, and duplicate bookings.

Loading data like this straight into a typed table is a losing game — a single erroneous row anywhere in the file kills the whole import. So the first step was a staging table where every column is TEXT:

CREATE TABLE tembo_staging(
    booking_id          TEXT,
    guest_name          TEXT,
    guest_phone         TEXT,
    ...
);

This guarantees the load succeeds, and pushes all validation and type-casting downstream, where it can be handled using SQL.

Step 2: Audit before you touch anything

Before writing a single UPDATE, I ran a set of diagnostic SELECT DISTINCT and pattern-matching queries to catalog exactly what was wrong:

• Casing inconsistencies in names and cities
• Non-standard category labels (Dlx instead of Deluxe, MPESA and M-PESA instead of one consistent label)
• Two date formats in the same column, distinguishable only by checking whether the day or month value exceeded 12
• Currency symbols and commas embedded in numeric fields
• Guest ratings outside the valid 1–5 range
• Exact duplicate booking_ids
• Negative values for nights_stayed

This audit-first approach matters for a simple reason: cleaning decisions should be based on what's actually in the data, not assumptions about what might be wrong. It also means every subsequent UPDATE targets a specific, confirmed problem — verified with a SELECT first, per the golden rule of data cleaning:

Always run a SELECT before an UPDATE or DELETE, so you know exactly which rows will change.

Step 3: Cleaning, one problem at a time

With the audit complete, the fixes were mostly a mix of TRIM, INITCAP, REGEXP_REPLACE, and targeted CASE statements:

Text standardization:

UPDATE tembo_hotel.tembo_staging
SET guest_name = INITCAP(TRIM(guest_name))
WHERE guest_name <> INITCAP(TRIM(guest_name));

Currency-as-text:

UPDATE tembo_hotel.tembo_staging
SET total_amount = NULLIF(REGEXP_REPLACE(total_amount, '[^0-9]', '', 'g'), '')::NUMERIC(10,2)
WHERE total_amount IS NOT NULL AND TRIM(total_amount) <> '';

Mixed date formats, resolved by checking which part of the date exceeds 12 (and can therefore only be a day, not a month):

UPDATE tembo_hotel.tembo_staging
SET check_in_date =
    CASE
        WHEN split_part(check_in_date, '/', 2)::int > 12
            THEN TO_DATE(check_in_date, 'MM/DD/YYYY')
        ELSE TO_DATE(check_in_date, 'DD/MM/YYYY')
    END;

Phone numbers with country codes normalized back to local format, and genuinely missing values set to NULL rather than left as empty strings — an important distinction, since empty strings and nulls behave differently in aggregations and joins.

Duplicates, removed while keeping the first physical row for each booking_id:

DELETE FROM tembo_hotel.tembo_staging
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM tembo_hotel.tembo_staging
    GROUP BY booking_id
);

After cleaning, 278 valid bookings remained from the original 285-row export.

Step 4: Building the production layer

Cleaned staging data moved into a clean tembo_bookings table, and on top of that, a view — vw_clean_bookings — that adds the derived fields Power BI needs for time intelligence and segmentation without recomputing them in DAX:

CREATE OR REPLACE VIEW tembo_hotel.vw_clean_bookings AS
SELECT
    ...,
    EXTRACT(YEAR FROM check_in_date) AS check_in_year,
    TO_CHAR(check_in_date, 'Month') AS check_in_month_name,
    CASE
        WHEN guest_rating BETWEEN 4 AND 5 THEN 'Satisfied'
        WHEN guest_rating = 3 THEN 'Neutral'
        WHEN guest_rating BETWEEN 1 AND 2 THEN 'Unsatisfied'
        ELSE 'No rating'
    END AS satisfaction
FROM tembo_hotel.tembo_bookings;

This view is the single source that the Power BI report connects to instead of raw tables scattered across the model.

Indexes were added to match the query patterns the analysis would actually use: a composite index on (check_in_date, booking_status) for time-based revenue filtering, (room_type, booking_status) for occupancy and cancellation queries, (staff_name, staff_department) for staff analysis, and a single-column index on guest_city for location queries.

Step 5: What the data actually says

With the pipeline in place, here's what the numbers show.

Revenue is concentrated in the Suite, not the most-booked room

Total confirmed revenue across 246 checked-out stays is KES 7,584,900, averaging KES 30,833 per stay. But the breakdown by room type tells an interesting story:

Room Type	Bookings	Revenue (KES)	Avg. Nights
Standard	94	1,595,500	2.9
Deluxe	80	2,165,700	3.0
Suite	53	2,596,100	3.2
Penthouse	19	1,227,600	2.5

Standard is booked most often — it's the volume driver — but the Suite generates the most revenue despite being booked far less frequently, thanks to its rate and the longest average stay of any room type. If the hotel wants to grow revenue rather than just occupancy, Suite is the room type worth protecting and promoting.

The Penthouse has a serious cancellation problem

This is the sharpest signal in the dataset. Cancellation and no-show rates by room type:

Room Type	Cancellation Rate	Revenue Lost (KES)
Suite	3.6%	120,000
Deluxe	8.1%	170,000
Standard	15.3%	320,300
Penthouse	24.0%	565,000

The Penthouse — the hotel's second-highest-priced room — is cancelled or no-showed almost a quarter of the time. Those lost bookings alone account for nearly half of all lost revenue (KES 1,175,300 total), from a room type that makes up just 9% of total bookings. This is the single highest-leverage fix available: a deposit or full prepayment policy plus a pre-arrival confirmation call for Penthouse bookings specifically would likely recover a meaningful chunk of that KES 565,000.

Standard's 15.3% rate is lower per-booking, but because it's the highest-volume room type, the absolute loss (KES 320,300) is still worth addressing with the same approach.

The most profitable room has the least satisfied guests

Average guest rating by room type:

Room Type	Avg. Rating
Penthouse	3.12
Standard	3.11
Deluxe	2.93
Suite	2.83

Suite guests are the least satisfied of any room type — worse than even the budget Standard rooms. Out of Suite guests who left a rating, 23 were unsatisfied against only 17 satisfied. This matters because Suite is also the top revenue earner: a guest paying the most is having the worst relative experience. Left unaddressed, this is a retention and reputation risk sitting directly on top of the hotel's most valuable segment. Overall, guest sentiment across the hotel is close to a coin flip — 41% satisfied vs. 38% unsatisfied — so this isn't a Suite-only issue, but it's most acute there.

Guests barely use paid extras — and one service massively outperforms the rest

66% of all bookings (185 of 278) included no extra service at all. Of the services that were used, Conference Room bookings stand out sharply:

Service	Times Used	Total Revenue (KES)
Conference Room	13	195,000
Spa Treatment	13	45,500
Airport Pickup	13	32,500
Breakfast Buffet	14	16,800
Laundry	13	10,400
Room Service	14	7,000
Swimming Pool	13	6,500

Conference Room earns roughly 4x the next best-performing service from a similar number of bookings — unsurprising for a hotel positioned as a business hotel, but the low attach rate (13 bookings out of 278) suggests it's undersold rather than unwanted. Bundling it into corporate packages, rather than leaving it as an opt-in extra, is a low-effort revenue lever.

Revenue is growing, but volatile and heavily dependent on one city - Nairobi.

Monthly revenue roughly doubled between late 2023 and 2024, though the dataset only starts in June 2023, so that first partial year isn't a fair year-over-year baseline — it likely reflects the hotel ramping up rather than a seasonal dip. Within 2024 alone, revenue swings 30–40% month to month with no single clean seasonal driver, though December and June are consistently the strongest months.

On the guest side, 45% of all bookings come from guests based in Nairobi, with the next largest cities — Eldoret and Kisumu — each contributing roughly a fifth as much. This concentration is a natural byproduct of being a Nairobi-based business hotel, but it also means the guest base has limited geographic diversification.

Recommendations for management

1. Require a deposit or prepayment for Penthouse bookings and a 48-hour pre-arrival confirmation call. This single change targets the largest revenue leak in the business.
2. Investigate the Suite guest experience — review comments and staff feedback tied to Suite stays. A premium room underperforming on satisfaction is a compounding risk: it affects both repeat bookings and referrals.
3. Package and actively market the Conference Room to corporate guests, bundled with catering — it's already the highest-earning add-on per booking, just underused.
4. Introduce a simple post-stay follow-up (SMS or email) that does two things at once: offers an add-on service and captures a guest rating. This addresses both the 66% no-extras rate and the 5% of stays with no rating recorded.
5. Extend the deposit/confirmation policy to Standard rooms — with a lower cancellation rate than Penthouse, but high volume means the loss is still significant.
6. Make guest_city a required field at intake — 14 bookings (5%) currently have no recorded city, which limits the accuracy of any geographic analysis going forward.
7. Explore targeted outreach in Eldoret and Kisumu before spreading marketing spend across many small feeder cities — they're already the next-largest segments after Nairobi.

What this project demonstrates

Beyond the specific findings, this project is a fairly complete walkthrough of a real analytics workflow: staging dirty data safely, auditing before cleaning, applying targeted and verifiable fixes, building a clean semantic layer for BI tools to consume, and translating query results into decisions a non-technical audience — in this case, a Hotel Director.

---

If you're working through a similar hotel/hospitality dataset or have questions about the cleaning approach, I'd love to hear from you in the comments.