bookings

The bookings schema has one row per room booking (a stay), cancelled ones included, with the days it was booked, arrives, departs and was cancelled. Use it for how many bookings come in, how many cancel, how far ahead guests book and how long they stay.

rooms counts nights. bookings counts the bookings themselves: a five-night stay is five room nights in rooms and one booking here.

Use cases

• Booking volume. Count bookings and room_nights_booked by channel and month.
• Cancellations. Compare cancellation_rate across channels, or count cancellations ON cancellation_date by cancellation_source.
• Booking window. Track avg_lead_time_days and avg_length_of_stay, or filter on lead_time_days for last-minute bookings.
• Arrivals and departures. Count stays and guests ON arrival_date, and departures on their own date.
• Check-in. Set checked_in_stays against stays ON arrival_date.

Examples

Bookings and cancellations by channel

FROM bookings
  SHOW bookings, cancellations, cancellation_rate
  DURING last_quarter
  GROUP BY channel
  WITH TOTALS

Who cancelled last month

FROM bookings
  SHOW cancellations
  ON cancellation_date
  DURING last_month
  GROUP BY cancellation_source

How far ahead guests book

FROM bookings
  SHOW avg_lead_time_days, avg_length_of_stay
  DURING last_year
  GROUP BY sales_channel

This week's arrivals, day by day

FROM bookings
  SHOW stays, guests
  ON arrival_date
  DURING this_week
  TIMESERIES day

Bookings made this year against last year

FROM bookings
  SHOW bookings, room_nights_booked
  DURING this_year
  TIMESERIES month
  COMPARE TO previous_year

What it counts

One row per room booking (stay), cancelled ones included, with its booking, arrival, departure and cancellation dates.

Dates

Rows are dated by booking_date (default), arrival_date, departure_date or cancellation_date. ON picks one. Without DURING or SINCE, a query covers this_month. AS OF reads what was on the books at the end of an earlier day, and covers the 12 months from that day when the query has no range.

Metrics

Metrics you can use in FROM bookings.

• bookings · Number

  Room bookings made, including ones cancelled later.

  Dated by booking_date, or by arrival_date or departure_date with ON.

• stays · Number

  Room bookings that are not cancelled.

  Dated by booking_date, or by arrival_date or departure_date with ON.

• cancellations · Number

  Room bookings that were cancelled. Use ON cancellation_date to count by the day they were cancelled.

• cancellation_rate · Percent

  Cancellations divided by bookings.

  Dated by booking_date, or by arrival_date or departure_date with ON. Doesn't add up over time, so WITH CUMULATIVE can't take it.

• room_nights_booked · Number

  Nights of the room bookings made, including cancelled ones.

  Dated by booking_date, or by arrival_date or departure_date with ON.

• avg_lead_time_days · Days

  Average days between booking and arrival, over bookings that are not cancelled.

  Dated by booking_date, or by arrival_date or departure_date with ON. Doesn't add up over time, so WITH CUMULATIVE can't take it.

• avg_length_of_stay · Days

  Average nights per booking, over bookings that are not cancelled.

  Dated by booking_date, or by arrival_date or departure_date with ON. Doesn't add up over time, so WITH CUMULATIVE can't take it.

• departures · Number

  Room bookings that are not cancelled, counted on their departure day.

  Dated by departure_date only.

• guests · Number

  Adults and children on room bookings that are not cancelled. Infants are not counted.

  Dated by booking_date, or by arrival_date or departure_date with ON.

• checked_in_stays · Number

  Room bookings not cancelled that were checked in in SERVOS, dated by arrival unless ON says otherwise. AS OF a day, a stay counts as checked in from its check-in day.

  Dated by arrival_date, or by booking_date or departure_date with ON.

Dimensions

Dimensions you can group and filter by in FROM bookings.

• room_type · Text

  The accommodation (room type) a stay or capacity belongs to.

• channel · Text

  Where the booking came from: an OTA such as Booking.com, POS, B2B, or Direct.

• sales_channel · Text

  The kind of channel a booking was sold on: direct, ota, pos or b2b.

  Values: direct, ota, pos, b2b.

• cancellation_source · Text

  Who cancelled: manual, ota, system, customer_request, or order_edit when an order edit removed the stay.

  Values: manual, ota, system, customer_request, order_edit.

• lead_time_days · Number

  Days between the booking and the arrival, for every booking, cancelled ones included.

• length_of_stay · Number

  Nights booked, for every booking, cancelled ones included.