Skip to content

DataPricing & revenue management

Analyse hotel booking lead time: a template by channel and season

Knowing how many days before arrival your guests book helps you change prices, minimum stays and restrictions at the right time. This guide is for owners and managers of small and medium-sized hotels. It provides an analysis template you can recreate in a spreadsheet.

STRYK editorial teamUpdated 11 min read

In short

Booking lead time is the number of days between the booking date and the arrival date. Calculate it for each booking that has not been cancelled, then analyse the median and the distribution across lead time bands, separately for each channel and season. An overall average mixes groups that book at very different times, so it is not a sound basis for pricing decisions.

Key points

  • Booking lead time is the arrival date minus the booking date, counted in days for each booking, excluding cancellations.
  • The median and lead time bands reveal more than the average because a few very early bookings pull the average up.
  • Analysis by channel and season separates guests who book early from those who decide at short notice. Treat results from groups with fewer than about 30 bookings as a rough guide only.
  • Each finding leads to a decision with a date, such as when to raise prices, how long to offer an early booking rate or what minimum stay to set in the final days.

What booking lead time measures

A booking made on 3 May for an arrival on 2 August has a lead time of 91 days. A booking made the evening before arrival has a lead time of one day; a booking made on the day of arrival has zero days.

Lead time describes your guests' behaviour across many dates. Pickup and pace, by contrast, describe how bookings for a single date develop over time. The two analyses complement each other. The guide to hotel pickup and pace explains how to read pickup and pace.

Lead time answers a question that matters for every price change: how long will a price apply before most guests have already booked?

Prepare the data for analysis

You need an export of all bookings with an arrival date in a selected period, ideally the past two years. Filter by arrival date, because the season you are pricing is the season of arrival.

Five columns per booking are enough for an initial analysis. The table lists them and explains what to check in each one.

Five columns are enough for the first analysis
ColumnContentsWhat to check
Booking dateThe day the booking was createdDo not use the date of the last change
Arrival dateThe first night of the stayThe same for each room in a multi-room booking
ChannelYour own website, telephone and email, each booking platformKeep booking platforms separate and combine them later if needed
StatusConfirmed, cancelled, no-showRemove cancelled bookings from the main analysis
Nights and roomsLength of stay and number of roomsNeeded if you want to weight by room nights

Find the lead time for each booking in six steps

These steps work in Excel, LibreOffice and Google Tabellen. The result is a table you can update with new data each quarter.

Calculate and group lead times

  1. Get the export

    Download all bookings with arrival dates in the selected period, including the five columns in the table.

  2. Separate cancellations

    Copy cancelled bookings and no-shows into a separate sheet. You will need them later to calculate the cancellation rate for each lead time band.

  3. Calculate lead time

    In a new column, subtract the booking date from the arrival date and format the result as a number, not a date.

  4. Assign a season

    Use the arrival date to assign each booking to your price tier, such as high season, low season or winter. The seasonal pricing guide explains how to define tiers.

  5. Create lead time bands

    Sort bookings into bands: 0 to 7 days, 8 to 14, 15 to 30, 31 to 60, 61 to 90 and more than 90 days.

  6. Build a pivot table

    Place channel in the columns and season in the filter, then count the bookings in each band. Calculate the median for each group with the MEDIAN function on the relevant filtered range.

The analysis template by channel and season

The template uses the same metrics for each channel and season group. It fits on one page and can be updated each quarter.

The median is the middle value in a series: half the bookings have a shorter lead time and half a longer one. It is less affected by extreme values than the average. A returning guest who books the next year on departure raises the average but changes the median very little.

Decide in advance whether a multi-room booking counts as one booking or as multiple room nights, and apply that choice consistently. Otherwise the years cannot be compared.

Five metrics for each channel and season group
MetricCalculationWhat it shows
Number of bookingsRows in the group after excluding cancellationsWhether the other figures have a sound basis: with fewer than about 30 bookings, use them as a rough guide only
Median lead timeMiddle value of the lead times in daysWhen the typical guest books
Share within 7 daysBookings with a lead time of 0 to 7 days divided by all bookings in the groupHow much demand arises shortly before arrival
Share over 60 daysBookings with a lead time of more than 60 days divided by all bookings in the groupHow much demand is secured early
Cancellation rate by bandCancelled bookings in a band divided by all bookings in the same bandWhether early bookings are cancelled more often

Worked example: Hotel Nordlicht in summer and winter

Suppose Hotel Nordlicht, with 24 rooms, analyses confirmed bookings with arrivals from June to August and from November to February. The figures in the table are an example, not industry benchmarks.

In summer, two profiles emerge. Guests who book direct have a median lead time of 38 days, and just over a quarter book more than 60 days ahead. Guests booking through platforms book considerably later, with a median of 21 days.

In winter, bookings move closer to arrival. 44% of bookings through platforms are made in the final week before the stay, compared with 35% through the direct channel. A single figure across all 420 bookings would not have shown any of these four profiles.

Four groups at Hotel Nordlicht have four different lead time profiles
GroupBookingsMedian lead timeWithin 7 daysOver 60 days
Summer, direct9038 days11%26%
Summer, booking platforms15021 days18%9%
Winter, direct4014 days35%5%
Winter, booking platforms1409 days44%3%

What Hotel Nordlicht takes from the analysis

For summer, prices need to be set before early bookers make their direct bookings. An increase that takes effect only in July will reach only guests with short lead times. The owner therefore sets summer prices in late autumn of the previous year and reviews them in spring.

The opposite applies in winter. Because half of the guests booking through platforms book only in the final nine days, there is little to go on early. Reception therefore reviews prices for the next 14 days every week and lowers or raises them according to actual occupancy.

The winter direct group has only 40 bookings. The hotel treats its figures as a rough guide and combines them with a second winter in the next analysis.

From finding to pricing decision

Each finding in the template leads to a decision with a date. The table shows typical pairings. They are starting points for testing, not benchmarks that apply to every property.

Lead time tells you when your guests book. A comparison with the previous year shows whether the current booking position for a particular date is good, as described in the pickup and pace guide. Find the corresponding weekly routine in revenue management week by week.

In STRYK, you put the decision into practice with price rules: adjust prices automatically by season, weekday, lead time or occupancy, and set the order in which rules run (set up price rules).

Five findings and the decisions they can lead to
FindingPossible decisionReview
Large share over 60 days in the direct channelSet seasonal prices early and offer an early booking rate for a limited periodShare of early bookings and their average price in the following year
Median below 10 daysReview prices for the next 14 days weekly and relax the minimum stay in the final daysOccupancy in the final 14 days before arrival
High cancellation rate for lead times over 60 daysReview cancellation terms or deposits for early bookingsCancellation rate by lead time band in the following year
Weekends booked early, weekdays at short noticeKeep the weekend minimum stay and set weekday prices laterLead time by day of the week of arrival
Median falls compared with the previous year in the same seasonRaise prices set early more cautiously and monitor pickup more closelyPace compared with the previous year

Put industry figures in context

Published industry figures offer a broad point of reference. They depend on the year, region, type of hotel and underlying data, and your property's lead time can differ substantially.

One example is Revinate's analysis for 2017 and 2018, reported by Hospitality Net in 2019. The study analysed around 12 million reservations from 600 hotels. Worldwide, 48% of reservations were made in the week before arrival, compared with 37% in Europe. In Europe, 16% booked more than 60 days in advance.

The figures are from before the pandemic and are seven years old. They are not suitable targets. Before using them, check whether newer figures are available for your market. Your own previous year in the same season and channel remains the most useful comparison.

Sources of error that distort the result

Common errors lie in the export and filters, not the formula. Check the following points before reading the results.

The threshold of 30 bookings per group is a practical rule of thumb, not a guarantee. The smaller the group, the more individual bookings can change the median.

Check before reading the results

  • The booking date is the date the booking was created, not the date of the last change. Check this against three bookings you know.
  • Cancelled bookings and no-shows have been removed from the main analysis and placed in a separate sheet.
  • Group and corporate bookings with room allotments are separate because they can substantially shift a season's lead time.
  • Repeat bookings by returning guests who book the following year on departure are marked.
  • For multi-room bookings, you have decided whether to count them as one booking or as room nights, and the same rule applies to every year.
  • Each group has about 30 bookings or more; otherwise its results are labelled as a rough guide only.
  • Trade fairs, public holidays and the start of school holidays are recorded as events so that unusual figures can be explained.

Update the analysis each quarter

Update the template once a quarter, after the end of a season. All arrivals will then have taken place, so no late bookings are missing from the result.

Save each analysis with the date and note alongside it what you changed as a result. At Hotel Nordlicht, the owner sets aside an hour for this on the first Monday after the season ends. At the next review, she can see whether the change has moved lead time or the average price.

Lead time is one of the metrics you do not need every week. Check pickup and occupancy weekly, and look at your guests' behaviour quarterly.

Frequently asked questions

What is a good booking lead time for a hotel?

There is no universally good figure. Lead time depends on location, guest type, channel and season. A meaningful comparison is your own previous year in the same season and channel.

How do I calculate booking lead time in Excel?

Subtract the booking date from the arrival date, for example with the formula =B2-A2 if the arrival date is in B2 and the booking date is in A2. Format the column as a number. Calculate the median for each group with the MEDIAN function on the filtered range.

Should I use the average or the median?

Use the median, supplemented by the shares in each lead time band. A few very early bookings pull the average up, while the median describes the typical guest.

How many bookings do I need for an analysis?

As a rule of thumb, about 30 per channel and season group. If a group has fewer, combine booking platforms or add a second year and treat the result as a rough guide only.

Do I include cancelled bookings?

No, not in the main analysis. Analyse cancellations separately and calculate the cancellation rate for each lead time band to see whether early bookings are cancelled more often.

How often should I analyse booking lead time?

Once a quarter, ideally after a season ends. Monitor pickup and occupancy weekly.

Sources

  1. Hospitality Net: Revinate study of booking lead times in 2017 and 2018 (report dated 11 February 2019), accessed October 14, 2026

Test STRYK with your real hotel day

14 days free, no setup fee. We set up your rooms, rates and first workflows together with you.