Schemas

A schema is a set of data you can query, with the metrics you can measure and the dimensions you can split them by. FROM picks the schema, and the schema decides which names the rest of the query can use. Each page lists every metric and dimension, with what it counts and what it leaves out.

The editor calls a schema a dataset.

Accommodation

rooms

Room nights sold, available and blocked per room type and night: occupancy, ADR, RevPAR.

bookings

Every room booking, cancelled ones included: cancellations, lead time, length of stay, arrivals.

Activities

activities

Every ticket and appointment sold, with its revenue.

activity_capacity

Seats each event time slot and class session offers, against seats sold, check-ins and no-shows.

Customers

customers

Every customer who has ordered, by first order: new customers, lifetime spend, consent.

Finance and payments

payments

Every payment received and refund paid out, on the day the money moved.

Sales revenue

revenue

Every order line sold, with its VAT split: revenue, discounts, VAT, orders and customers.

Which schema to pick

Question	Schema
How much did we sell, of what, with how much VAT?	`revenue`
How full were the rooms, and at what rate?	`rooms`
How many bookings came in, and how many cancelled?	`bookings`
Which activities sold, and what did they earn?	`activities`
How full were events and classes?	`activity_capacity`
How much money came in, and how was it paid?	`payments`
How many new customers, and what do they spend?	`customers`

Some questions fit two schemas, and the answers differ because they count different things. Room revenue in rooms is spread over the nights of each stay. revenue dates each line by the day it is used, and payments dates the money by the day it moved. A booking in bookings is one stay, and in rooms it is as many room nights as the stay is long.

Find what you sell

A schema answers one kind of question, so one thing you sell can appear in several. A stay shows up as nights in rooms, as a booking in bookings and as revenue in revenue. Start from what you sell:

What you sell	Schema	Narrow it with
Room stays	`rooms` for nights, occupancy and rates, `bookings` for bookings and cancellations	`room_type`, `channel`
Add-ons to a stay, such as breakfast	`revenue`	`WHERE line_kind = 'stay_addon'`
Event tickets	`activities` for sales, `activity_capacity` for seats	`WHERE activity_kind = 'event'`
Service bookings: appointments and classes	`activities` for sales, `activity_capacity` for class seats	`WHERE activity_kind = 'service'`
Products	`revenue`	`WHERE line_kind = 'product'`
Passes and memberships	`revenue`	`WHERE revenue_category = 'Passes'`
Fees, such as cancellation fees	`revenue`	`WHERE line_kind = 'fee'`
Packages	`revenue`, `rooms` or `activities`	`GROUP BY package`
Orders	`revenue` for orders and order value, `payments` for what was paid
Guests	`customers` for who they are, `rooms` for guest nights, `bookings` for guests per stay

Products sold last month

FROM revenue
  SHOW units_sold, net_revenue
  WHERE line_kind = 'product'
  DURING last_month
  GROUP BY product

Breakfast and other add-ons

FROM revenue
  SHOW units_sold, net_revenue
  WHERE line_kind = 'stay_addon'
  DURING last_month
  GROUP BY product

Everything in a package, by what it was

FROM revenue
  SHOW net_revenue
  WHERE package IS NOT NULL
  DURING last_month
  GROUP BY package, revenue_category

Fields

Every schema page ends with the same sections.

What it counts says what one row is, and what the schema leaves out.
Dates lists the time axes, the default range, and whether AS OF works.
Metrics lists what you can SHOW, with its type: money, a number, a percentage or days. A metric that only groups and filters by some dimensions, or only works on some time axes, says so.
Dimensions lists what you can GROUP BY and filter on in WHERE, with the fixed values where there are any.

Metrics count only your own hotel's data. Money is in your organization's currency.

Overview