Revenue and reports

Read cancellation rates without fooling yourself

Use a consistent booking cohort and show the counts behind your cancellation rate. Then check segment differences, resold nights and net revenue before testing a change to your terms.

Sixto team20 min read

Woman concentrating on a laptop at a wooden table with a notebook beside her.

A cancellation rate is a signal, not a policy instruction

Investigate what changed before tightening cancellation terms. A percentage needs a consistent cohort, a useful denominator and enough context to support a decision. The rate alone does not show that your policy is failing.

Calculate the booking-based rate as follows:

Cancellation rate = (cancelled eligible bookings / all eligible bookings in the same cohort) × 100.

A cohort is a group of bookings selected using one shared rule. Cancelled bookings remain in the eligible total. Do not remove them from the denominator just because they no longer appear on the upcoming arrivals list.

This rate counts bookings. It does not automatically measure the share of nights or revenue lost. Bookings can have different lengths and values, and released nights may sell again.

Before interpreting a report, check that it displays:

  • Question: the operational decision you need to inform.
  • Cohort: which bookings are included and which date rule selects them.
  • Cutoff: the date and time through which outcomes are known.
  • Counts: cancelled bookings and all eligible bookings, alongside the percentage.
  • Exceptions: the number of records with missing or unresolved information.

Keep three tasks separate: measure using a stable definition, investigate the bookings behind the result, then decide whether an intervention is justified.

Tip: If the definition is missing or you cannot reconstruct the counts, get the report corrected before changing terms.

Keeping the current policy is a valid decision when the evidence does not yet support a change.

Choose the question before the date filter

Your date filter determines what the report can tell you. Bookings created this month describe the business you captured. Bookings scheduled to arrive this month describe expected arrivals. Cancellations recorded this month describe operational workload. These are different populations.

1. State the question and select the date basis

  1. To follow the outcome of bookings you received, use a creation cohort: all eligible bookings created during the period, with their subsequent cancellations observed up to the cutoff.
  2. To review scheduled arrivals, use an arrival cohort: retain the eligible bookings due to arrive during the period, including those cancelled before the period began. Filtering for active bookings alone removes part of the story.
  3. To assess reception workload, count cancellation events recorded during the period. Do not divide those events by unrelated bookings created that month and call the result a booking cancellation rate.
Operational question Date basis Denominator Cutoff Interpretation limit
What happened to the bookings we received? Creation within the period All eligible bookings created in that period Stated date and time Open bookings may still cancel
What happened to scheduled arrivals? Arrival under the documented assignment rule All eligible scheduled bookings, including cancellations Stated date and time Results depend on how arrival changes are preserved
How many cancellations did reception process? Cancellation timestamp Not applicable to the event count Stated date and time Measures workload, not a rate against new bookings

2. Give each cohort equal observation time

Display “Outcomes observed as of [date, time and time zone]” beside the result. Label a cohort provisional while bookings remain exposed to cancellation. Treat observations as mature when the defined follow-up window has ended, not merely because the calendar month has closed.

Compare creation cohorts after the same amount of follow-up. A recent cohort with limited observation is not comparable to an older cohort followed through its final outcomes. If equal windows are unavailable, disclose the difference and do not describe the result as an improvement.

3. Preserve changes to scheduled arrivals

For an illustrative example, a booking for unit 402 moves from October to November. Decide whether your arrival report will retain the date captured in an initial snapshot or reconstruct membership from the booking’s date history. Document that rule and apply it consistently. Never silently move the booking between reporting months.

Tip: Before comparing percentages, check that both reports answer the same question over the same observation window.

If the date basis, cutoff or arrival-assignment rule differs, your next decision is to repair the comparison, not tighten booking conditions.

Keep the denominator visible and define what counts

Show cancelled bookings and total eligible bookings beside the percentage. Set the eligibility rule before calculating, then keep it unchanged throughout the comparison. A cancelled booking does not leave the denominator simply because it was cancelled.

1. Separate real bookings from supporting records

Count confirmed bookings that meet your cohort definition, including those subsequently cancelled. Exclude unconfirmed inquiries, test bookings and verified duplicates. Record each exclusion reason rather than deleting a row because it looks repetitive.

Keep one stable identifier per booking. Changes to price, length of stay or arrival date do not automatically create another booking. Preserve the history and apply your documented arrival-date change rule without silently moving records between periods.

2. Distinguish cancellations from other outcomes

Classify the outcome before counting it:

  1. A full cancellation enters the numerator if the booking is eligible.
  2. A reduction in nights or units remains a modification or partial cancellation under your classification, not a full cancellation by default.
  3. A no-show keeps its own status. Do not automatically recode it as a cancellation.
  4. An operator-initiated cancellation stays separately identifiable. State whether your measure includes all cancellations or only guest-initiated ones.

When a guest cancels and rebooks, preserve both events and link the booking identifiers. Assess the replacement booking against its own eligibility criteria. It does not erase the original cancellation.

3. Choose your counting unit

For example, one booking covering two apartments for three nights represents one booking, two units and six unit-nights. These are different measures. If only one apartment is cancelled, record the partial scope. Do not count two cancelled bookings or classify the entire booking as cancelled without an explicit rule.

4. Keep missing information visible

A missing creation date may prevent assignment to a creation cohort. A known arrival date can still support assignment to an arrival cohort. Report unresolved records as an exception count, rather than inventing dates or treating missing values as zero.

Tip: With a zero denominator, display “Not calculable: no eligible bookings,” never a measured cancellation rate of 0%.

Before publishing, check:

  • Eligible cancelled bookings remain in the total.
  • Tests and duplicates have documented exclusion reasons.
  • Modifications, partial cancellations and no-shows remain distinguishable.
  • Unresolved records have a visible exception count.
  • Numerator and denominator use the same counting unit and eligibility rule.

If a classification is unresolved, flag it before comparing results. A tidy percentage is not a substitute for a defined population.

Build a record you can audit back to the booking

Build your sheet so another person can reproduce the result without asking which filters you used. Use booking IDs instead of guest names and keep a snapshot of the source records. You can recalculate a percentage. You cannot reliably reconstruct overwritten evidence.

1. Attach a report header

Copy this header into your working file. Extraction time tells you when the records were retrieved. The observation cutoff tells you how far the reported booking outcomes extend.

Question: [decision this report supports]
Definition: [eligible cancelled bookings / all eligible bookings × 100]
Cohort: [date basis and period]
Eligibility rule: [criteria and exceptions]
Timezone: [zone used for dates and times]
Observation cutoff: [date and time]
Extraction time: [date and time]
Source snapshot: [file or reference]
Owner: [role]
Version: [identifier and reason for revision]

2. Split the fields, not the booking history

These tables are parts of one record sheet, joined by booking ID. All entries are illustrative. Dates use year-month-day format, and times use America/Bogota. This example uses an arrival cohort with a common cutoff of 2026-10-15 at 12:00.

Booking ID Unit Cohort basis and date Arrival Cancellation timestamp Status at cutoff
R402 402 Arrival, 2026-10-20 2026-10-20 2026-10-14 09:00 Cancelled
R305 305 Arrival, 2026-10-21 2026-10-21 Not applicable Confirmed
R208 208 Arrival, 2026-10-22 2026-10-22 Not applicable Confirmed
Booking ID Channel Booking lead time Nights Rate terms Eligible Exception Source cutoff
R402 Direct 20 days 3 Flexible, version A Yes None 2026-10-15 12:00
R305 Online agency 10 days 2 Non-refundable, version B Yes None 2026-10-15 12:00
R208 Direct Unknown 1 Flexible, version A Yes Creation date missing 2026-10-15 12:00

Keep the source creation date so you can verify booking lead time. Preserve the exact terms behind each rate version. R208 can belong to this arrival cohort because its arrival date is known; do not manufacture a creation date.

Use a separate financial tab linked by booking ID for original value, refunds, retained amounts, commissions and net revenue. Link resale records through unit, dates, nights and replacement booking ID. Do not overwrite the original cancellation status or create another cancellation row for each financial transaction.

3. Reconcile before sharing

  1. Match booking IDs and eligible, cancelled and exception totals against the source snapshot at the same cutoff.
  2. Open any mismatched booking and inspect its history. Record the reason for each correction.
  3. Save a new version without replacing the original export. Assign review and editing permissions.

Tip: keep the report, its definition and its source snapshot together. A spreadsheet without context is not an audit trail.

Set access and retention arrangements for your operation. Check applicable data protection, access and retention guidance from Colombia’s Superintendencia de Industria y Comercio. Replacing names with IDs does not, by itself, remove identification risks.

Worked example: the same inventory, two different questions

This is an invented example. You manage an aparthotel and review two booking groups drawn from the same inventory. Both reports use the same information cutoff, but they answer different questions.

1. Count bookings created in the period

Your first group contains 20 eligible bookings created during the selected period. Four have cancelled by the cutoff. Those four remain part of the eligible total:

4 ÷ 20 × 100 = 20%.

This answers: “Of the bookings created in this period, what share had cancelled by the cutoff?” It does not necessarily describe arrivals during that period. Some bookings may be for stays much further ahead.

2. Count bookings scheduled to arrive

Your second group contains 10 eligible bookings scheduled to arrive during the period. Three cancelled before arrival. Keep them in this group, including bookings created in an earlier period:

3 ÷ 10 × 100 = 30%.

The question is now: “Of the bookings scheduled to arrive in this period, what share had cancelled by the cutoff?”

3. Do not turn a difference into a trend

These are different populations. They can overlap. For example, a booking for unit 402 created during the period and scheduled to arrive within it could belong to both groups. Shared records do not make the populations interchangeable.

The 20% and 30% results therefore do not establish an increase. To assess a trend, compare the same cohort type using equivalent observation windows.

Tip: Before interpreting a difference, say which bookings each percentage includes and how long you have observed them.

4. Identify the result that can still change

If some of the 20 newly created bookings are still awaiting arrival, their outcomes remain open. A later cancellation would change the numerator at a new cutoff. Keep the earlier reading and label the open cohort provisional rather than treating its current rate as final.

Likewise, moving from one cancellation to two tells you little without each group's eligible booking total. Twice as many cancellations does not automatically mean twice the cancellation rate.

Decision: repair the comparison design before considering a policy response. Two percentages answering different questions are not evidence that your cancellation terms need tightening.

Segment before changing the policy

Break the headline rate down by channel, booking lead time, stay length and rate terms before tightening conditions. Keep eligibility rules, cohort definitions and observation windows consistent. Otherwise, the segments may look comparable while measuring different things.

1. Build comparable groups

  1. Start with channels. Show cancelled bookings and eligible bookings beside each channel’s rate: cancelled bookings divided by eligible bookings, multiplied by 100.
  2. Within the channel you need to investigate, compare fixed booking lead-time bands. Keep their boundaries stable rather than adjusting them to highlight a difference.
  3. Check booked stay length and the terms that applied when each reservation was made. Include cancellation flexibility, the cancellation window and whether collected amounts were refundable. Today’s policy does not describe yesterday’s booking.
  4. Add further breakdowns only when the resulting groups remain useful. Splitting every dimension at once can leave you examining isolated bookings rather than a pattern.

Booking lead time runs from booking creation to scheduled arrival. Cancellation notice runs from cancellation to scheduled arrival. A booking made well in advance can still cancel just before arrival. These measures answer different operational questions.

Tip: Read the counts before the percentages. When a group contains few bookings, inspect the individual records and acknowledge uncertainty. There is no universal minimum sample that makes every comparison dependable.

2. Separate a mix shift from worsening performance

Consider an illustrative case: a channel with a higher cancellation rate supplies a larger share of your bookings. The overall rate can rise even if neither channel’s own rate worsens. Check each channel’s share of eligible bookings alongside its cancellation rate before blaming its performance.

Also review policy versions, guest mix, seasonality and property mix. More bookings at a different property can change the combined result. Treat these as competing explanations, not causes established by the report.

3. Decide which bookings deserve inspection

Use these questions to choose the underlying records to review:

  • Does the difference remain with matching cohorts and observation windows?
  • Is it concentrated in one rate condition or spread across the channel?
  • Which bookings account for the change, and what terms applied?
  • How much notice did those cancellations give? Is there evidence that the released nights were resold?
  • Is the pattern strong enough to justify testing one change in a specific segment?

If a few cases or a different booking mix explain the movement, leave the policy unchanged while you investigate. Segmentation should narrow the decision, not manufacture a culprit.

Worked example: a cancellation is not the same as a loss

A cancelled booking remains a cancellation even if you sell its nights again. Track recovery separately. Booking outcomes and inventory performance answer different questions.

Illustrative example: two cancellations in unit 402

The first cancellation releases three nights. A replacement booking takes two of those nights in the same unit. The remaining night goes unsold.

The second cancellation has a different arrival date. It releases one night in unit 402, which also remains unsold.

Together, these bookings produce:

  • Two cancelled bookings.
  • Four cancelled nights.
  • Two resold nights.
  • Two unrecovered nights.

For this example, define the night-recovery ratio as resold nights divided by cancelled nights: 2 ÷ 4 × 100 = 50%. This measures nights recovered, not revenue recovered. It does not turn two cancelled bookings into one.

1. Match recovery to the original inventory

  1. Keep the original booking IDs and identify each released night by unit and date.
  2. Link the replacement booking only to matching nights in unit 402. A sale for another date does not recover these nights.
  3. Count each recovered night once. If the replacement booking also cancels, update its outcome at the report cutoff rather than leaving it marked as recovered.
  4. Document any unit substitution. A booking placed in another unit is not automatically evidence that these nights were recovered. If you cannot trace the allocation, label the attribution uncertain.

Matching records supports a claim that the released inventory sold again. It does not prove the cancellation created an additional sale.

Tip: Keep the cancellation and the replacement booking as separate records. Link their nights instead of overwriting the original outcome.

2. Reconcile the money separately

Have the receptionist check booking IDs, dates and statuses for both the cancelled and replacement reservations. Ask the housekeeping lead to check occupied nights and flag unit moves. Housekeeping records support the occupancy check; they do not establish what was paid. As manager, reconcile the financial entries.

Keep the original booked value, refunds, retained amounts, replacement revenue and commissions distinct. Adding the full original booking value to replacement revenue can count money you never collected or subsequently refunded.

Compare net revenue on a consistent basis, using the same included items and commission treatment. Recovering 50% of the nights does not establish that you recovered 50% of the money. Before changing cancellation terms, identify the nights left unsold and the net result your records actually support.

Choose between paper, spreadsheets and software

Choose the method that lets you trace the result back to individual bookings, not the one with the most charts. You need to recover the cohort definition, reporting cutoff, eligible bookings and cancelled bookings.

Option Best for Strengths Watch-outs
Paper Capturing exceptions during a shift Lets you record a cancellation or discrepancy without a connection Recalculating cohorts and tracking amendments takes manual review
Spreadsheet Inspecting cohorts and sharing a transparent calculation Makes rows, filters and formulas visible Requires version control and protection against accidental edits
Reservation or reporting software Organising records and repeating queries Makes grouped records easier to review Results depend on definitions, exports and synchronisation

1. Trace one booking through the method

Test the workflow before relying on it. For example, take a cancelled booking for unit 402:

  1. On paper: match the booking identifier in the original entry to the cancellation note. Check dates and status. If a page is missing, reconstruct the entry from an available source and flag anything unresolved.
  2. In a spreadsheet: find the identifier and check that it appears once, retains its eligibility and feeds the correct counts. Keep a copy of the source file so you can rebuild the calculation if a formula breaks.
  3. In software: compare the booking detail and history with an export using the same cutoff. If access or exports fail, keep a provisional manual log with the identifier, dates, status and source. Do not silently merge it with records that synchronise later.

Tip: Keep a verifiable record outside the dashboard. A number you can see is not necessarily a number you can reconstruct.

2. Choose a workflow you can maintain

Stay with paper while you can review every entry without losing amendments. Use a spreadsheet when recalculation and comparisons become routine. Consider software when maintaining those records takes too much work, but require booking-level access and clear definitions. Automation cannot repair a badly defined cohort. Whichever method you choose, make sure another team member can follow the same records and reproduce your count.

What to do when the report does not reconcile

If the report disagrees with the booking records, pause the policy decision. Establish whether you have a calculation error, a different cutoff or a different definition before changing any terms.

1. Preserve the original export

Save the untouched file, filters, timezone and extraction time. Investigate in a copy. You need to reconstruct the discrepancy, not overwrite the evidence while trying to fix it.

2. Locate the mismatch

Compare booking IDs rather than totals alone. Identify records missing from either source. Check synchronization delays, duplicate imports and events around midnight. The same timestamp can fall on different reporting dates when sources use different timezones.

3. Inspect the booking history

Check how source statuses map into your report. A modification or no-show should not automatically become a cancellation. For changed arrival dates, apply your documented history or cohort-snapshot rule. When a cancellation is followed by a new booking, retain both events and link their IDs. The guest eventually staying does not erase the original cancellation.

Illustrative example: unit 402

The night receptionist finds a booking marked cancelled in the channel but active in the unit 402 report. Before editing it, they compare update times and booking history. A cancellation after the report cutoff may explain the difference without any error. A cancellation before the cutoff that has not synchronized is an unresolved exception: record it and label the result provisional.

4. Repair or isolate the records

Make corrections only when the source supports them, and log each change. Never turn missing dates into zero or guess which period they belong to. An unknown creation date may prevent creation-cohort assignment even when the arrival date is known.

Tip: Withhold the percentage when unresolved exceptions prevent you from establishing its numerator or denominator. Show verified counts and outstanding exceptions instead.

5. Rerun and document the revision

Keep the definition unchanged. Record who revised the report, why, the cutoff and the corrected version. Label results provisional while synchronization or validation remains outstanding.

Before using the result, check for three mistakes: averaging segment percentages without weighting by eligible bookings, dropping cancelled rows from the denominator, and treating a before-and-after comparison as proof of causation. Reconciliation makes the report usable for investigation. It does not prove that a policy caused the change.

Turn the review into a bounded action

Inspect the bookings behind the result before changing your terms. Then record a decision: hold steady, investigate or test one change in one segment.

Give each review an owner

Use this schedule as an adaptable operating rhythm, not a mandatory deadline. One person can cover several roles, but every decision needs a named owner.

Review point Owner Input Check Recorded decision
Report preparation Reservations lead Cohort records and exceptions Definition, cutoff and counts reconcile Publish or mark provisional
Before proposing a change Manager Cancelled bookings and scorecard Pattern is localized; alternatives considered Investigate, hold or test
While the test runs Test owner New bookings and outcomes Change stays within the agreed segment Continue or stop
Once comparable observations accumulate Manager Test and comparison cohorts Booking volume, cancellations and net revenue Keep, reverse or observe longer

Keep a compact scorecard

Use the same cohort definition and a visible cutoff across these measures:

  • Cancelled and eligible booking counts, alongside the booking cancellation rate: cancelled ÷ eligible × 100. If there are no eligible bookings, label the rate as not calculable.
  • Cancellation notice: time between cancellation and scheduled arrival. State whether you show a median or another summary, and how many records contain the necessary timestamps.
  • Cancelled nights and resold nights, shown separately. Recovering a night does not erase the original cancellation.
  • Exception count and eligible booking volume. Neither unknown records nor a fall in bookings should disappear behind a percentage.
  • Comparable net revenue, with consistent treatment of refunds, retained amounts, commissions and other included costs. Identify replacement revenue separately so you do not count it twice.

Tip: Write your stop condition before the test starts. Fewer cancellations are not automatically a win if bookings or net revenue also fall.

Put boundaries around the test

For example, you could test a clearer message explaining existing terms within one booking segment. Do not change the price and cancellation policy at the same time. A focused test makes the result easier to investigate, although it does not establish causality on its own.

Complete this note before introducing the change:

Hypothesis: [expected improvement and reason]
Segment: [channel, booking lead time and rate terms]
Single proposed change: [intervention]
Owner: [role or name]
Observation rule: [cohort, cutoff and follow-up window]
Comparison cohort: [matching basis and known differences]
Stop or rollback condition: [signal and response]
Next review: [review point or trigger]

Reassess when enough comparable observations exist to interpret the counts, not when you reach an invented universal sample threshold. If cohorts remain open or booking volume is sparse, record observe longer without expanding the change. Keeping the current terms is a valid decision when the evidence does not support a change.

Finish the review with these checks

Copy this checklist into your review log before approving a change. An unchecked box does not prove your policy is failing. It identifies evidence you still need.

  • Cancelled bookings remain in their cohort’s eligible booking count.
  • Open cohorts are labelled provisional and show an as-of cutoff.
  • Unknown values appear as exceptions, not zeros.
  • Compared segments use consistent definitions and observation windows.
  • Resold nights have supporting records for the same unit and dates, without double counting.
  • Booking volume and consistently defined net revenue support the conclusion.

1. Leave a decision you can revisit

  1. Choose one operational question and freeze its definition.
  2. Reconcile one cohort against source records before interpreting its rate.
  3. Inspect the underlying cancellations that explain the result, rather than stopping at the percentage.
  4. Record a bounded decision, assign an owner and specify what will trigger another review.

When evidence is weak, keep the current terms. Assign the outstanding checks instead of tightening conditions to make the report look better. Waiting for comparable evidence is a valid decision when someone owns the follow-up.

Use this handover note so the next shift can continue without reconstructing your reasoning:

Question: [question]
Definition and cutoff: [cohort, eligibility, date]
Reconciled result: [cancelled / eligible; exceptions]
Decision and scope: [keep or test; segment]
Outstanding evidence: [record needed and owner]
Next review: [owner and review trigger]

Practical operations guidance, not legal or tax advice.

ShareWhatsAppLinkedInX