Home

Booking Pace Analysis: Step-by-Step Guide

Table of Contents

• Overview — Main Idea
• Source Table & Key Fields
• Realized Demand vs. Last Year
• On the Books – Transient Rate Type
• Transient Pace to Projection Tool
• Where to Find in Opera PMS
• OTB vs LY – Checked-In/Out?
• Metrics Explained
• Practical Steps (SQL & Excel)
• Final Output: Weekly Pivot
• Sample SQL & Pivots
• Closing Notes & Workflow

Introduction

This guide explains how to analyze hotel Booking Pace using data exported from Opera PMS (Property Management System) and tools such as SQL, Excel, and Tableau. It covers what each metric means, where to find it in Opera or your extracted dataset, how to substitute missing metrics, and how to pivot arrival dates into a weekly Booking Pace matrix (Friday → Thursday).

Overview — Main Idea

Booking Pace tracks how room nights build over time. In Opera, reservations, status updates, and night audits can give you an accurate view. The workflow is:

1. Export reservations and night audit data from Opera for a rolling window (e.g., 90 days back and forward).
2. Summarize by arrival date: rooms sold, occupancy, ADR (Average Daily Rate), revenue, channel, and segment.
3. Compare current On The Books (OTB) to the same days-out last year and prior periods.
4. Pivot daily arrivals into a Friday-to-Thursday matrix to see trends easily.

Source Table & Key Fields

From Opera, export reservation and stay data. Useful columns include:

hotel_id | hotel_name | arrival_date | departure_date | booking_date | status | market_segment | rate_code | distribution_channel | total_rooms | rooms_sold | ADR | revenue

Add fields like reservation_id, folio_post_date, room_nights, occupancy_flag for precision.

Realized Demand vs. Last Year

Opera PMS can show realized nights via Night Audit or Stay View:

• Definition: Room nights actually consumed. Opera often counts them at checkout but can be configured to count at check-in.
• Variants:
  • Night Audit count (preferred)
  • Checked-in/checked-out stays
  • Revenue postings by date
• If missing: Use night audit or folio postings. If not available, approximate: count stays where arrival_date ≤ date ≤ departure_date and status = 'IN-HOUSE' or 'CHECKED-OUT'.

Realized Demand vs. Last Year

When we talk about Realized Demand, we mean the actual rooms consumed—stays that have already happened. Depending on your property, this could mean checked-in rooms, checked-out rooms, or both (most hotels use checked-out data for historical reporting). This metric gives you a true picture of what business you actually captured, rather than what was on the books.
To compare against Last Year (LY), line up your historical data day-by-day or week-by-week to see if your demand is trending higher, lower, or about the same. This becomes your baseline for performance reviews and forecasting

Read More about  Future Demand and Supply

On the Books – Transient Rate Type

On the Books – Transient Rate Type

On the Books (OTB) means everything currently reserved for future dates. This includes all confirmed bookings sitting in your PMS but not yet realized.
Breaking it down by transient rate types (individual travelers rather than groups) is crucial because this segment tends to book closer to arrival and drives rate-sensitive revenue. By looking at the next 30, 60, or 90 days, you can see how fast your occupancy is building and whether you are pacing ahead or behind your goals.
In simple terms, OTB tells you: How much business do I have today for dates in the future, and at what rates?

On the Books in Opera is a snapshot of confirmed future reservations. To isolate transient pace (non-group):

• Filter reservations where status = 'RESERVED', 'CONFIRMED', or 'GUARANTEED'.
• Exclude group blocks or wholesale rates if needed.
• Capture OTB snapshots regularly (daily or weekly) for comparison.

Transient Pace to Projection Tool

This concept is about looking forward, not just backward.

• Where do you find it? In Opera or Fieldio PMS, it’s usually a combination of booking reports, pickup reports, and forecast modules. You can also export raw data and build your own views in Excel, SQL, or Tableau.
• What is Projection? Projection is your best estimate of what will happen by arrival date if current booking patterns continue.
• How is it different from Forecasting? Forecasting often uses broader data (market trends, events, weather, competitor analysis), while projection is more tactical and focused on if nothing changes, where will we land based on current pace?

This combines:

• Opera exports (reservations, night audit)
• Projections (budget or Long Range Plan)
• Historic OTB snapshots

It answers: Are we pacing ahead or behind target? Opera’s Reports module can give daily OTB; export to Excel or Tableau to build pace charts.

Why does it matter?
It lets you answer questions like:

• Are we ahead or behind last year?
• Are we building enough pickup to hit targets?
• Which rate types are helping or hurting?

Where to Find in Opera PMS

In Opera:

• Realized: Night Audit > Room Statistics or Folio Transactions report.
• OTB: Reservation Summary or Availability by Date report.
• Historical OTB: Save daily reports manually or automate extracts.
• Projection: Usually outside Opera (budget files) but can be uploaded for BI comparison.

OTB vs LY – Checked-In/Out?

Opera’s OTB reports normally exclude checked-in rooms for future dates. For analysis:

• Use pure OTB (future dates only).
• Use night audit realized for historicals.
• If combining in-house + OTB, label clearly.

Metrics Explained

• Current OY Realized Demand vs. LY: Compare this year’s actuals with LY at the same points to track growth or decline.
• Current OTB Rate: The average daily rate of bookings already secured.
• Last Week’s OTB Rate & 1 PD Ago OTB Rate: Shows how your rates are moving week over week or period over period.
• OTB Variance to LY and 1 Period Ago: Are you pacing ahead or behind compared to last year and your own recent performance?
• Pickup Variance: How many more rooms do you need to sell to match LY or to stay on track with your projection?
• Segment ADR & Mix Percentages: Which segments are paying more or less, and how is your business mix shaping up?
• Price Sensitivity & Additional Demand: Are you losing bookings because your rates are too high, or is there more demand to capture?

Each metric (Realized vs LY, OTB rate, pickup variances, mix percentages, ADR by segment, etc.) is detailed in this section. They align with Opera’s data points like Reservation Status, Room Nights, and Revenue Codes.

Practical Steps (SQL & Excel)

Includes:

1. Extract reservations & night audit tables.
2. Aggregate realized demand (rooms, ADR).
3. Snapshot OTB daily or weekly.
4. Join on arrival_date, compute days_out.
5. Pivot in Excel or SQL to Fri→Thu.

Final Output: Weekly Pivot

Example: Week rows, Fri–Thu columns, Total column. Useful for leadership and revenue teams.

Sample SQL & Pivots

SELECT
  arrival_date,
  (arrival_date - ((EXTRACT(DOW FROM arrival_date)::int - 5 + 7) % 7) * INTERVAL '1 day')::date AS week_of_fri
FROM reservations;
  

More examples included: aggregating rooms sold, ADR, and revenue.

Closing Notes & Workflow

• Define realized demand clearly; document assumptions.
• Snapshot OTB consistently and persist it.
• Automate exports from Opera to warehouse or BI.
• Pivot and visualize weekly pace for quick insights.