Hey Reader,

Last month, we covered LEFT JOIN to find customers who never booked. That's one type of absence — people who never showed up.

But there's another kind that's even more expensive: people who showed up, committed, and then left.

The dreaded cancellation. Customers who said "yes", then said "actually, never mind".

In the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics), nearly 47% of all bookings end in cancellation. That's not a typo. Almost half of the revenue that was expected never materialized.

This is where the Empty Stadium Blueprint really earns its name. An empty seat in a stadium was sold. Someone had a ticket. Finding out why that seat is empty is more valuable than finding someone who never bought a ticket in the first place.

The Business Problem

Gabby from marketing asks: "We're spending a lot on customer acquisition, but our cancellation rate feels high. Where are we losing people, and is it worse in certain categories?"

This is a churn question. And the answer requires more than just "47% cancel". We need to know where, when, and for whom.

Step 1: The Baseline — How Bad Is It?

-- Overall booking status breakdown
SELECT 
    b.status,
    COUNT(*) AS booking_count,
    ROUND(
        COUNT(*)::numeric / (SELECT COUNT(*) FROM bookings) * 100, 1
    ) AS pct_of_total
FROM bookings b
GROUP BY b.status
ORDER BY booking_count DESC;

750 cancelled bookings out of 1,600 total. That's a significant portion of expected revenue that didn't materialize.

But "47% cancellation rate" is a single number. It doesn't tell you where to focus. Let's dig deeper.

Step 2: Where Are Cancellations Happening?

-- Cancellation rate by expedition type
SELECT 
    e.expedition_type,
    COUNT(*) AS total_bookings,
    COUNT(*) FILTER (WHERE b.status = 'cancelled') AS cancelled,
    ROUND(
        COUNT(*) FILTER (WHERE b.status = 'cancelled')::numeric 
        / COUNT(*) * 100, 1
    ) AS cancel_rate,
    COUNT(*) FILTER (WHERE b.status = 'completed') AS completed,
    ROUND(
        COUNT(*) FILTER (WHERE b.status = 'completed')::numeric 
        / COUNT(*) * 100, 1
    ) AS completion_rate
FROM bookings b
    INNER JOIN expedition_instances ei ON b.instance_id = ei.instance_id
    INNER JOIN expeditions e ON ei.expedition_id = e.expedition_id
GROUP BY e.expedition_type
ORDER BY cancel_rate DESC;

Now we can see something. Climbing expeditions have a 51.9% cancellation rate — the highest across all types. Cultural expeditions are the lowest at 40.6%.

That 11-point spread tells the story. If climbing could achieve cultural's cancellation rate, that's roughly 32 fewer cancelled bookings — potentially tens of thousands in recovered revenue.

Step 3: Is Difficulty Level a Factor?

Since climbing had the highest cancellation rate and tends to be more physically demanding, let's check if difficulty level correlates with cancellations:

-- Cancellation rate by difficulty level
SELECT 
    e.difficulty_level,
    COUNT(*) AS total_bookings,
    COUNT(*) FILTER (WHERE b.status = 'cancelled') AS cancelled,
    ROUND(
        COUNT(*) FILTER (WHERE b.status = 'cancelled')::numeric 
        / COUNT(*) * 100, 1
    ) AS cancel_rate
FROM bookings b
    INNER JOIN expedition_instances ei ON b.instance_id = ei.instance_id
    INNER JOIN expeditions e ON ei.expedition_id = e.expedition_id
GROUP BY e.difficulty_level
ORDER BY cancel_rate DESC;

Interesting! Cancellation rates are relatively even across difficulty levels. The difference between the highest (advanced, 48.6%) and lowest (expert, 44.5%) is only 4 percentage points.

This suggests that difficulty isn't the main driver of cancellations. Customers who book expert-level trips are actually less likely to cancel...probably because they know what they're getting into.

Putting It Together: The Empty Stadium Report

Here's how you'd present this to Gabby:

• The Situation: 47% of bookings cancel. That's roughly 750 bookings that generated no revenue.
• Where It's Worst: Climbing expeditions cancel at 51.9% — 11 points higher than cultural (40.6%). This isn't a difficulty problem (expert bookings actually cancel least).
• The Hypothesis: Something about how climbing expeditions are presented, priced, or scheduled may be creating mismatched expectations. Climbing may attract aspirational bookers who don't fully commit.

Recommended Actions:

1. Compare climbing marketing materials to cultural — is climbing overselling the experience?
2. Implement a confirmation step 2 weeks before departure for climbing bookings
3. Offer flexible rebooking instead of cancellation for climbing customers
4. Study the timing of cancellations — are they last-minute (cold feet) or early (changed plans)?

This is the Empty Stadium Blueprint in action. The empty seats (cancellations) tell a story. Your job is to read that story and figure out how to fill more seats next time.

The FILTER Clause: A Clean Alternative

Notice the FILTER (WHERE ...) syntax used throughout:

COUNT(*) FILTER (WHERE b.status = 'cancelled') AS cancelled

This is PostgreSQL's cleaner alternative to the CASE WHEN approach:

-- The traditional way (works everywhere):
SUM(CASE WHEN b.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
-- The FILTER way (PostgreSQL — cleaner and more readable):
COUNT(*) FILTER (WHERE b.status = 'cancelled') AS cancelled

Both produce the same result. FILTER is easier to read when you have multiple conditional counts in the same query.

Common Mistakes in Churn Analysis

Mistake 1: Reporting only the overall rate

"47% cancel" doesn't tell you where to act. Always break it down by the dimensions that matter (category, time period, customer segment, geography).

Mistake 2: Assuming correlation is causation

Climbing has high cancellation rates, but that doesn't mean climbing causes cancellations. Maybe climbing expeditions are priced higher, scheduled further in advance, or marketed differently. Dig into the why.

Mistake 3: Forgetting to quantify the impact

"51.9% cancel rate" sounds bad. "Climbing cancellations cost us an estimated $130K in lost revenue" creates urgency. Always translate rates into dollars when you can.

Try This at Your Job

Every business has cancellations, churn, or drop-offs:

• E-commerce: Abandoned carts by product category
• SaaS: Trial-to-paid conversion by signup source
• HR: Employee departures by department
• Education: Course drop-out by module

Find your "empty seats." Then figure out which section of the stadium empties out first.

Until next time,

Brian

Brian Graves, creator of Analytics in Action Say 👋 on X/Twitter, LinkedIn, or book a call with me. You can always reply to these emails. I check them all.

​

P.S. The Empty Stadium Blueprint is the core of Module 5 in SQL for Business Impact. You'll learn how to find what's missing — and more importantly, why it matters — across real business scenarios. Check it out at sqlforbusinessimpact.com.

P.P.S. What's the biggest "empty stadium" problem at your company? Where are customers (or employees, or users) disappearing? Hit reply and tell me. I read every response