How to Find Why Customers Cancel (Using SQL Pattern Analysis)

Hello ,

Here’s a question most analysts never think to ask: “Why are our customers cancelling?”

Everyone looks at bookings. Everyone reports revenue. But the data hiding in your cancelled orders often tells a more important story than your completed ones.

At Summit Adventures (the fake adventure tourism company I created to help people learn business analytics), nearly 46% of bookings end up cancelled. That’s a staggering number — and exactly the kind of pattern that, once you understand it, leads to real business improvements.

Let me show you how to investigate cancellation patterns step by step.

Step 1: Understand the Scale

Before diving into “why,” start with “how much”:

-- Cancellation rate overview
SELECT 
    status,
    COUNT(*) AS booking_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
FROM bookings
GROUP BY status
ORDER BY booking_count DESC;

For Summit Adventures, the breakdown looks roughly like:

Completed: ~830 bookings (52%)
Cancelled: ~730 bookings (46%)
In Progress: ~40 bookings (2%)

A 46% cancellation rate is a significant business problem. But that number alone doesn’t tell you what to do about it. You need to dig deeper.

Step 2: When Do Cancellations Happen?

Timing reveals a lot. Do customers cancel right away, or weeks later?

-- How quickly after booking do customers cancel?
SELECT 
    CASE 
        WHEN cancellation_date::date - booking_date::date <= 1 THEN 'Same day'
        WHEN cancellation_date::date - booking_date::date <= 7 THEN 'Within a week'
        WHEN cancellation_date::date - booking_date::date <= 30 THEN 'Within a month'
        ELSE 'More than a month later'
    END AS cancellation_timing,
    COUNT(*) AS cancellations,
    ROUND(AVG(total_amount), 2) AS avg_booking_value
FROM bookings
WHERE status = 'cancelled'
GROUP BY 
    CASE 
        WHEN cancellation_date::date - booking_date::date <= 1 THEN 'Same day'
        WHEN cancellation_date::date - booking_date::date <= 7 THEN 'Within a week'
        WHEN cancellation_date::date - booking_date::date <= 30 THEN 'Within a month'
        ELSE 'More than a month later'
    END
ORDER BY cancellations DESC;

Why this matters: If most cancellations happen within 24 hours, your checkout process might be creating buyer’s remorse. If they happen weeks later, customers might be finding better alternatives or facing scheduling conflicts.

Step 3: Which Expedition Types Get Cancelled Most?

-- Cancellation rates by expedition type
SELECT 
    e.expedition_type,
    COUNT(*) AS total_bookings,
    COUNT(*) FILTER (WHERE b.status = 'cancelled') AS cancellations,
    ROUND(
        COUNT(*) FILTER (WHERE b.status = 'cancelled') * 100.0 / COUNT(*), 
        1
    ) AS cancellation_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 cancellation_rate DESC;

This tells you whether specific types of expeditions have higher cancellation rates. If climbing trips cancel at 55% but cultural tours cancel at 35%, that’s actionable information. Maybe climbing trips need better expectation-setting, or maybe the difficulty descriptions need adjustment.

Step 4: Read the Cancellation Reasons

This is where qualitative data meets quantitative analysis:

-- Most common cancellation reasons
SELECT 
    cancellation_reason,
    COUNT(*) AS occurrences,
    ROUND(AVG(total_amount), 2) AS avg_lost_value
FROM bookings
WHERE status = 'cancelled'
    AND cancellation_reason IS NOT NULL
GROUP BY cancellation_reason
ORDER BY occurrences DESC
LIMIT 10;

Summit Adventures tracks reasons like “scheduling conflict,” “changed plans,” “found alternative,” and “financial reasons.” Each category suggests a different business response:

Scheduling conflict → Offer more flexible rebooking options
Financial reasons → Consider payment plans or early-bird pricing
Found alternative → Competitive analysis needed
Changed plans → This is normal attrition; focus elsewhere

Step 5: What’s the Revenue Impact?

The final question: how much money is this costing?

-- Revenue lost to cancellations
SELECT 
    e.expedition_type,
    COUNT(*) FILTER (WHERE b.status = 'cancelled') AS cancelled_bookings,
    SUM(CASE WHEN b.status = 'cancelled' THEN b.total_amount ELSE 0 END) AS lost_revenue,
    SUM(CASE WHEN b.status IN ('completed', 'confirmed') 
        THEN b.total_amount ELSE 0 END) AS kept_revenue,
    ROUND(
        SUM(CASE WHEN b.status = 'cancelled' THEN b.total_amount ELSE 0 END) * 100.0 / 
        NULLIF(SUM(b.total_amount), 0), 
        1
    ) AS revenue_lost_pct
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 lost_revenue DESC;

The story to tell your leadership: “We’re leaving hundreds of thousands of dollars on the table due to cancellations. Here’s where the biggest losses are, and here are three things we can do about it.”

That’s a fundamentally different conversation than just saying “our cancellation rate is 46%.”

The Pattern You Just Learned

This investigation followed a natural analytical progression:

1. How much? (scale of the problem)
2. When? (timing patterns)
3. Where? (which products/categories)
4. Why? (stated reasons)
5. What’s the impact? (revenue at stake)

This sequence works for any business problem — not just cancellations. Customer churn, support tickets, employee turnover, product returns. Same investigative pattern.

Try This At Your Job

Replace “cancellations” with whatever your company tracks that represents lost opportunity:

E-commerce: Cart abandonment or returns
SaaS: Subscription cancellations or downgrades
Retail: Refund requests
HR: Employee resignations

The queries adapt. The investigative pattern stays the same. Start with scale, then timing, then categories, then reasons, then impact.

Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))

—

P.S. This investigative approach — starting with a broad question and narrowing systematically — is at the heart of the Gordon Ramsay Blueprint in Module 8 of SQL for Business Impact. It’s about diagnosing problems under pressure. Check it out at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).

P.P.S. What’s the equivalent of “cancellations” at your company? What data do you wish you were analyzing but haven’t gotten to yet? Hit reply — I’d love to hear what you’re working on.