Hotel Projection & Booking Pace Analysis Tool (SQL-Based)

Compare future performance against historical benchmarks and recent trends

This tool enables Revenue Managers to analyze On-the-Books (OTB) data, monitor pick-up trends, and assess forecast accuracy by leveraging historical data snapshots that simulate forward-looking performance — offering a more strategic view than relying solely on current data..

Booking Pace-to-Projection Tool is built for revenue managers to track On-the-Books (OTB), Pickup trends, and forecast accuracy — using real historical data snapshots instead of today's data to simulate forward-looking performance.

🧠 Importance of Booking Pace — Resort-Focused Analysis

Booking Pace Answering How do transient room nights booked for the next 90 days compare to the room nights on the books at the same time last year?:

• Are we ahead or behind compared to last year?
• Are bookings slower this year?
• Do we need to adjust pricing, promotions, or stop-sells?

For resorts with long booking windows (30–90+ days), pace analysis helps forecast shoulder/peak periods, identify booking slowdowns, and refine channel and pricing strategies.

🔄 What We are covering Here?

• On The Books (OTB) Mix to Last Year
• Current Mix To Last Year Forecast- LY Actual
• 📊 OTB Variance & Pickup Analysis
• 📉 On The Book Variances To 1 Period Ago "Month"
• 📌 Pick Up Variance To 1 Period Ago (Projection PU - 1PdAgo PU)
• 📈 OTB Mix Percentages
• 📐Measuring Segment ADR Change
• 🎯Segment ADR Change From Last Period "Month"

🔹 1. Performance Summary Comparison

• Projected Room Nights vs. Actual Pick-Up
• On the Books (OTB) now vs. OTB same days out in previous periods
• Variance over multiple time frames (Last Year, 1 Period Ago)

• 🤔How do transient room nights booked for the next 90 days compare to the room nights OTB for same time last year?:
• 😕How Bookings performance looking forwards compares to same time last year looking forwards.
• 📊 Variance between projected pickups and actual pickups from the same lead time (last year and last period)
• 📉 Variance in OTB now vs. what was on the books at the same days-out last year and last period

🔹 2. OTB Variance to Last Year

• How do transient room nights booked for the next 90 days compare to those on the books at the same time last year?
• Compare forward-looking OTB values now vs. forward-looking OTB values from the same date last year

💡 Why This Matters:

• 📈 Spot changes in weekday booking behavior
• 🧭 Understand seasonality by day of week
• 💰 Evaluate promo/rate effectiveness
• ⚠️ Flag underperforming dates early
• 📦 Layer segmentation for deeper insights (Direct, OTA, TO, etc.)

🔹 3. Pick-Up Variance Analysis

• Pick-Up Variance to Last Year (Proj PU - LY Actual PU)
Measures whether projected pick-up exceeds or trails last year's actual pick-up for the same days out
• Pick-Up Variance to 1 Period Ago (Proj PU - 1 Period Ago PU)
  Compares current projected pick-up to what was picked up 1 period ago, for the same arrival window.

Definitions:

• Proj PU = Projected pick-up required to meet target
• LY Actual PU = Last year's actual pick-up at same days out

🔹 4. OTB Mix % by Rate Type

• What is the weekly distribution of on-the-books room nights by rate type for the next 90 days?
• Helps track how rate mix shifts as arrival dates approach

🔹 5. Segment ADR Trends

• Segment ADR:
Average rate on the books per rate type, updated weekly for the next 90 days
• ADR Change from Last Week:
Week-over-week ADR variance by rate type, same days out perspective.

🔹 6. Detail Section

• These Sections covering:
Transient Actual vs. Projection
• Compare current projections to historical actuals from:
Last Year
1, 2, and 3 periods ago "Weekly" or "MOnthly"
• Transient On the Books
Compare current OTB to previous OTB values from:
1, 2, and 3 periods ago
Last Year
• Booked to Actual/Projection
Evaluate projected pick-up vs actual pick-up over the same timeframes
• Booked Segments
Transient room nights on the books for each rate type.

🔹 7. Rate and Price Sensitivity

• OTB ADR :
• Current vs. last week’s average rate for regular room nights.
• Price Sensitivity:
Percentage of price turndowns relative to total transient interest.
Formula:
• Price Turndowns / (Transient OTB + Price Turndowns)

🔹 8. Additional Demand :

🔹 1. Performance Summary Comparison Purpose:
Compare current performance against last year and recent trends (e.g., 1 period ago).

• 🤔How do transient room nights booked for the next 90 days compare to the room nights OTB for same time last year?:
• 😕How Bookings performance looking forwards compares to same time last year looking forwards.
• 📊 Variance between projected pickups and actual pickups from the same lead time (last year and last period)
• 📉 Variance in OTB now vs. what was on the books at the same days-out last year and last period

• How do transient room nights booked for the next 90 days compare to those on the books at the same time last year?
• Compare forward-looking OTB values now vs. forward-looking OTB values from the same date last year

🔹 3. Pick-Up Variance Analysis

• Pick-Up Variance to Last Year (Proj PU - LY Actual PU)
Measures whether projected pick-up exceeds or trails last year's actual pick-up for the same days out
• Pick-Up Variance to 1 Period Ago (Proj PU - 1 Period Ago PU)
  Compares current projected pick-up to what was picked up 1 period ago, for the same arrival window.

Definitions:

• Proj PU = Projected pick-up required to meet target
• LY Actual PU = Last year's actual pick-up at same days out

🔹 4. OTB Mix % by Rate Type
Question:

• What is the weekly distribution of on-the-books room nights by rate type for the next 90 days?
• Helps track how rate mix shifts as arrival dates approach

🔹 5. Segment ADR Trends

• Segment ADR:
Average rate on the books per rate type, updated weekly for the next 90 days
• ADR Change from Last Week:
Week-over-week ADR variance by rate type, same days out perspective.

🔹 6. Detail Section
Scope:
90-day forward-looking view, comparing projections to historical data over multiple timeframes.

• These Sections covering:
Transient Actual vs. Projection
• Compare current projections to historical actuals from:
Last Year
1, 2, and 3 periods ago "Weekly" or "MOnthly"
• Transient On the Books
Compare current OTB to previous OTB values from:
1, 2, and 3 periods ago
Last Year
• Booked to Actual/Projection
Evaluate projected pick-up vs actual pick-up over the same timeframes
• Booked Segments
Transient room nights on the books for each rate type.

🔹 7. Rate and Price Sensitivity

• OTB ADR :
• Current vs. last week’s average rate for regular room nights.
• Price Sensitivity:
Percentage of price turndowns relative to total transient interest.
Formula:
• Price Turndowns / (Transient OTB + Price Turndowns)

🔹 8. Additional Demand :
Compares additional demand generated this year to the same period last year.

Since we’re using historical data, we simulate what OTB looked like at a specific point in time (e.g., 2019-06-01), instead of relying on CURRENT_DATE.

🚫 Please do not get lost while reading — Too Much KPIs together, but we’ll break it down step-by-step.

📐 Dataset Logic Overview

• Snapshot Date: e.g., 01-Aug-2018
• Filter: Only bookings on file as of the snapshot
• Arrival Month: Used for grouping
• Weekday Split: From Friday to Thursday
• Output: Total room nights per weekday per month

📌 Example Output (Room Nights)

Month Of   Fri  Sat  Sun  Mon  Tue  Wed  Thu  Total
Jan-19     527  656  673  649  764 1024  858   5151
Feb-19     821  578  854  644  696  739  899   5231
...
Dec-19     681  765  980  777  996  517  459   5175
  

🧠 Strategic Takeaways

• Weekday Strategy: If Wednesdays perform better than Fridays, promote shoulder nights or raise ADR midweek.
• Compression Forecast: Identify months at risk of early sellout.
• Rate Sensitivity: Later combine this with ADR to evaluate rate-volume trade-offs.
• Forecasting Accuracy: This sets the foundation for comparing projected vs. actuals.

How This Query WORKS

compares monthly room nights between what was forecasted and what was actually realized, for better visibility into forecast accuracy and performance.
🔍 Key Features:

• Forecast vs. Realized: It uses forecast type e.g( "OTB", "Realized", Forecast", "Projected") to differentiate the predicted vs. actual room nights.
• Days Out: Calculates lead time from the snapshot (reservation_status_date) to the actual arrival.
• As-of Date (Snapshot): Keeps track of the point-in-time when the data was valid, useful for pacing.
• Status Filter: Includes only meaningful statuses (Confirmed, Check-in, Check-out) to avoid noise from cancelled/no-shows (modify this logic if needed).
• Date Range: Filters only from May to September 2019 for the analysis window.
• Pivoted Format: Data is restructured to show Forecast, Realized, and their Difference side by side for each month.

Purpose:

This query compares forecasted vs realized room nights sold per month, helping you identify how accurate your forecasting was.

Used Tools: SQL Server, Excel, Tableau

Author: Ayman H. Salem – Revenue Strategist

Understanding the Difference Between the Two Queries

🤓 Don’t worry — it only looks scary. You've got this!

✅ 1. "On The Book Variances To 1 Period Ago"
How does On the Books performance looking forwards compare to performance 1 period ago.: How does On the Books (OTB) performance looking forward compare to how it looked one period ago?
Data Source: forecast_type = 'OTB'
Metric: Projected room nights
Method: Compare the current OTB for future arrival dates to what the OTB was one period ago (e.g., a week or a month ago).
Output: Absolute values by month and weekday (no subtraction).
Use it to: Assess if your booking pace is improving or slowing down.

🤓 Don’t worry — it only looks scary. You've got this!

📌 Pick Up Variance To 1 Period Ago (Proj PU - 1 Period Ago Actual PU)

💡 Why is there a difference?
Because you’re comparing two different behaviors:
- OTB Variance: What’s already booked now vs. what was booked last period
- PU Variance: What you forecast will be booked vs. what was actually booked last period

😲 Rememebr

"On The Book" Variance To 1 Period Ago

Pickup Variance To 1 Period Ago

✅ Final Output: A month-by-month report showing:

• Month
• Current OTB
• Last Month OTB
• OTB Variance
• MoM % Change

📊 Why This Is Important for a Revenue Manager:

• Tracks Booking Momentum: Shows whether the current month booking volume is up or down vs. the previous month. Helps assess performance trends and booking pace.
• Channel Contribution Focus: Focuses on specific distribution channels (OTA, Direct, etc.) for mix analysis and to prioritize high-performing sources.
• Revenue Forecasting Insight: OTB room nights are a leading indicator of future revenue. Declining MoM bookings may indicate weak demand, prompting pricing or promotional adjustments.
• Supports Seasonality & Strategy: Can incorporate seasonal logic (currently commented out in the query) for better planning during peak or low periods.
• Data-Driven Decisions: The MoM % change helps quantify trends to guide:
  • Marketing spend decisions
  • Revenue optimization
  • Targeted sales initiatives

🧠 Summary

This is a MoM On-The-Books booking trend analysis by distribution channel. It helps revenue managers identify shifts in booking patterns, adjust strategies, and optimize revenue opportunities in real time.

Segment Overview

OTB Mix %

Segment ADR

Segment ADR % Last "Month"

📊 Resort OTB Mix % (Historical, Monthly)

OTB Mix %

What this shows: This query helps simulate how your resort's On-The-Books (OTB) looked 90 days before arrival, broken down by month and rate segment.

How it works:

• 🗂️ Grouped by: Month of arrival and rate segment (e.g., TO, OTA, Direct, etc.)
• 📊 Output: Each segment’s share (%) of the total OTB room nights for that month
• 📅 Timeframe: Typically filtered for high season, e.g., arrival_date BETWEEN '2020-06-01' AND '2020-08-31'
• 📁 Snapshot logic: Only bookings confirmed 90 days before arrival (resorts often rely on longer lead times)

🧠 Resort-Specific Notes

• Replace market_segment with your actual segment or channel (e.g., rate_type)
• Make sure snapshot_date (or status_date) is correct — this defines what was considered "on the books" at that time
• This logic works well with stacked bar charts or area graphs in Tableau for clear visualization

📘 Example Table Explanation

This table provides a monthly view of OTB mix by segment. All figures are as of 90 days prior to the arrival month. Example:

💡 Strategic Interpretation

• High TO %: Suggests reliance on Tour Operators. Consider diversifying to improve ADR.
• Strong OTA months: May indicate short-lead FIT demand — ideal time to test last-minute rates.
• Group/Corporate activity: Peaks in shoulder months — align with event/conference calendar.
• Direct underperformance: Explore digital campaigns or loyalty offers to grow direct share.

📈 Use Cases for Revenue Strategy

• 📌 Identify seasonality shifts and adjust segmentation mix accordingly
• 💰 Align pricing with channel sensitivity — e.g., hold rate on strong Direct months
• 📉 Reduce dependency on low-ADR segments like TO during shoulder or peak months
• 🧠 Inform marketing campaigns, promo timing, and group strategy

🔗 Want to go deeper? Read the full breakdown here on the blog.