revenue

The revenue schema holds every order line your hotel sold: stays, add-ons, tickets, appointments, products, passes and fees. Each line carries its amount after discounts, its VAT split, and the channel, package and customer it was sold to. Use it for what you earned, from what, and from whom.

Use cases

• Revenue by department. Show net_revenue and total_revenue by revenue_category and month.
• Discounts. Set gross_sales against discounts and net_revenue to see what discounts cost, per channel or package.
• VAT. Split vat_amount and net_revenue by vat_code, vat_rate or account.
• Best sellers. Rank product by net_revenue or units_sold.
• New and returning guests. Compare orders, customers and average_order_value by new_or_returning_customer.

Examples

Revenue and VAT by category, month by month

FROM revenue
  SHOW net_revenue, vat_amount, total_revenue
  DURING last_quarter
  GROUP BY month, revenue_category
  WITH TOTALS

What discounts cost per channel

FROM revenue
  SHOW gross_sales, discounts, net_revenue
  DURING last_month
  GROUP BY sales_channel
  WITH TOTALS

The 20 best-selling products

FROM revenue
  SHOW net_revenue, units_sold
  DURING last_month
  GROUP BY product
  ORDER BY net_revenue DESC
  LIMIT 20

Order value of new and returning guests

ON booking_date counts each order on the day it was booked, not over the nights of its stays.

FROM revenue
  SHOW orders, customers, average_order_value
  ON booking_date
  DURING last_quarter
  GROUP BY new_or_returning_customer

VAT by rate and account

FROM revenue
  SHOW net_revenue, vat_amount
  DURING last_month
  GROUP BY vat_rate, account
  WITH TOTALS

What it counts

Every order line sold, as posted after discounts and package allocation, with its VAT split. Stay lines are spread over their nights.

Dates

Rows are dated by service_date (default), posting_date or booking_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 revenue.

• net_revenue · Money

  Revenue excluding VAT, from each line's VAT split. Lines without a VAT split are not included.

• gross_sales · Money

  Sales excluding VAT, before discounts. Gross sales less discounts is net revenue. A line paid with a pass counts as zero. Lines without a VAT split are not included.

• discounts · Money

  Discounts given, excluding VAT, including discounts on packages. A package priced below its parts is not a discount. A line paid with a pass counts as zero. Lines without a VAT split are not included.

• total_revenue · Money

  Revenue including VAT, after discounts. Net revenue plus VAT, and the line total of a line without a VAT split.

• vat_amount · Money

  Output VAT on the lines sold.

• units_sold · Number

  Items sold (stays, tickets, appointments, products, passes). Meaningful within one revenue category.

• orders · Number

  Orders with a line in the result, each counted once per row. An order with lines on several days or in several categories counts in each of them. A cancelled order that kept a cancellation fee counts, with the fee as its revenue.

  Doesn't add up over time, so WITH CUMULATIVE can't take it.

• average_order_value · Money

  Revenue excluding VAT, after discounts, divided by orders. An order whose lines have no VAT split counts with no revenue. ON booking_date dates each line by when it was booked, so a stay added to an order later counts on its own day; by service date, an order whose stay runs over two months counts in both.

  Doesn't add up over time, so WITH CUMULATIVE can't take it.

• customers · Number

  Customers with an order in the result, each counted once per row. Anonymous walk-in customers aren't counted. Guests booked through an OTA often arrive as a new customer for every booking.

  Doesn't add up over time, so WITH CUMULATIVE can't take it.

Dimensions

Dimensions you can group and filter by in FROM revenue.

• room_type · Text

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

• revenue_category · Text

  What was sold: Accommodation, Events, Services, Products, Passes, Fees or Other. Stands in for departments until SERVOS has them.

  Values: Accommodation, Events, Services, Products, Passes, Fees, Other.

• line_kind · Text

  The kind of order line.

  Values: stay, stay_addon, ticket, appointment, appointment_addon, product, pass, fee, other.

• product · Text

  What was sold, by its current catalog name: room type, add-on, event, service, product, pass or fee.

• product_type · Text

  The product type set on catalog products.

• activity · Text

  The event or service (experience) sold.

• activity_variant · Text

  The ticket or service variant sold. In activity_capacity only seats have a variant: grouped by it, each activity adds a row with no variant and 0; HAVING seats_sold > 0 hides it.

• event_category · Text

  The category set on an event (concert, workshop, …).

• vat_code · Text

  The tax group a line was taxed under.

• vat_rate · Number

  The VAT rate in percent, e.g. 25 or 12.

• tax_level_code · Text

  The optional code set on a tax group for the accounting system.

• account · Text

  The ledger account a line posts to, from its tax group.

• 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.

• package · Text

  The package a line was sold in, if any.

• new_or_returning_customer · Text

  Whether the order was the first the customer booked (new) or a later one (returning), by booking time, so a second booking is returning even when its stay comes first. A cancelled first order still counts as the first. Empty for anonymous walk-in customers. Guests booked through an OTA are often new every time.

  Values: new, returning.