JOINs and ROLLUPs explained for hoteliers (no jargon)

Plenty of hotel people feel that data is someone else’s territory: spreadsheets that don’t reconcile, columns with strange names, technical words a consultant used once in a meeting. But the uncomfortable, and liberating, truth is that almost all of a hotel’s intelligence is built from just two moves. One is called “joining” (a JOIN). The other is called “totaling” (a ROLLUP). Understand those two verbs and you already understand the engine under any serious report. You don’t need to know how to code; you need to know how to think.

The real problem: the operation lives in pieces

A hotel’s operation isn’t born tidy. It’s born scattered. Reservations live in one place, payments in another, sales channels in another, guests in another, restaurant orders in another, staff shifts in another, cash movements in another. Each piece is correct on its own, but none tells the whole story. And the question that truly matters almost never fits inside a single piece.

Take a question as ordinary as “how much did each channel really bring in last month, once collected, and how far in advance did people book?”. That question touches at least four pieces at once: the reservation (what was sold and when it was requested), the payment (what was actually collected), the channel (where it came from) and the arrival date (to measure lead time). Answering it by hand, copying and pasting between sheets, is exactly where the errors creep in that nobody can later explain.

JOIN, or the art of joining things that share something

A JOIN, which we’ll simply call a “join”, means putting two tables together that share something in common, so a single row can say it all. That “something in common” is a key: a value that appears on both sides and acts as a bridge. The most typical key in a hotel is the reservation number, but it could be the guest identifier or a folio.

Picture two notebooks. In the first you write down every reservation: its number, the room, the dates, the channel it came through. In the second you write down every payment: which reservation it belongs to, how much, in what form, on what day. On their own, the first notebook doesn’t know how much was collected and the second doesn’t know who stayed or for how many nights. The join is, literally, setting both notebooks side by side and binding them by reservation number, so that each resulting line says, at a glance: this reservation, from this channel, for these nights, brought in this collected payment.

The bridge metaphor

A join is a bridge between two riverbanks that share a column. As long as that common column, the key, exists, the bridge can be built. If the reservation and the payment share the reservation number, they join effortlessly. If guests and reservations share the guest identifier, so do they. The mental rule is simple: to join two things, ask yourself “what value do they have in common?”. If there is one, there is a bridge.

Joins also reveal gaps, and that is a virtue, not a flaw. If joining reservations with payments turns up a reservation with no payment attached, that empty line is pointing at something real: an outstanding charge, a lost record, an account left open. Far from being a reporting error, it’s exactly the kind of thing you wanted to jump out at you.

ROLLUP, or the art of folding the many into the few

A ROLLUP, which we’ll call a “total” or “aggregation”, means folding many rows into a summary. Instead of looking at ten thousand reservations one by one, you group them by something that interests you and ask for a tally: sum them, average them, count them. The result is a handful of figures that actually fit in your head and actually fit inside a decision.

The key part of a total is the “by”: total by channel, by month, by guest, by room type. That “by” is the group. Change the group and you change the lens. “Revenue by channel” tells you where the money comes from. “Revenue by month” tells you how the year breathes. “Nights by guest” tells you who comes back. It’s the same operation, fold and summarize, just seen from a different angle.

And totals stack. You can ask for revenue by channel and by month at once, and get a grid where each cell is a crossing. Then you can fold that grid one level further and get the grand total. That, deep down, is what the word ROLLUP means: “rolling up” the subtotals until you reach the total of all totals, without losing the intermediate figures along the way.

Joining gives you the full detail; totaling gives you the meaning. The detail answers “what exactly happened”; the summary answers “what does it mean for me”.A principle of reading hotel data

Two hotel examples, end to end

A join example: the reservation with its payment

Suppose, and this is only an illustrative example, not a real figure, that you want the revenue already collected per reservation, not the amount billed. The reservation knows the agreed price; the payment knows what truly hit the cash drawer. You join both tables by reservation number and, suddenly, each line tells you: reservation 1042, direct channel, three nights, fully collected; reservation 1043, online agency, two nights, half collected. Without the join, you’d have two loose truths that never look each other in the eye.

A total example: revenue by channel and by month

Now take that same joined set and fold it. Group by channel and by month, and sum what was collected. The result might look, again, as an illustrative example, like this: in March, the direct channel brought in a certain amount; online agencies, another; the front desk, another. The table below shows the shape of that summary, not real data from any hotel.

Notice that last line: that “Total for the month” is the ROLLUP rolling up one more level, from the per-channel subtotals to the grand total. The same grid, read another way, already hints at questions: why does one channel bring more reservations but less revenue per night? That question is born on its own from crossing and totaling; you didn’t have to go looking for it.

In Spider Data you write none of this: you drag it

Here’s the most liberating part. Everything above has technical names because it comes from the world of databases, where you write in code. In Spider Data you write no code. You build the report by dragging and dropping, in Spanish, over the eight sources of your operation, reservations, cash, channels, payments, guests, orders, shifts and cash movements, and underneath, the join and the total happen without you ever having to name them.

When you drag a payments field next to a reservations field, you’re asking for a join: the system understands the common key and builds the bridge for you. When you group “by channel” and choose “sum”, you’re asking for a total. And because you need to speak the hotel’s language, not just the language of tables, Spider Data ships ready-made calculated fields, ADR, nights, the lead time of a booking, cash reconciliations, so you measure hotel concepts, not raw formulas.

• What a JOIN is, in one line: putting two tables together by a value they share, so a single row can say it all.
• What a ROLLUP is, in one line: folding many rows into a grouped summary, summing, averaging or counting by channel, by month, by guest.
• The key: the bridge value that makes the join possible (the reservation number, the guest identifier).
• The group: the “by” that defines the total (by channel, by month, by room type).
• The calculated field: a column that wasn’t there but is derived from the others (ADR, nights, lead time, reconciliation).

Live, open and comparable: the rest of the engine

Understanding join and total gives you the engine; the rest is what keeps it useful. In Spider Data the data is live: the crossing you build doesn’t look at last night’s close, but at what is happening right now, with filters that affect each other on the same board. Filter one month and the whole board reorders around it.

Nor is it a cage. Whatever you cross and total can flow out to your own tools, Power BI, Tableau, Looker, through an open connection with a bearer access token, so your finance or leadership team can consume it where they already work. And if you want to know not just how you did, but how you did compared with others, the R2-Index measures you against a reference index. All of this lives inside R2 OS, with human support in Spanish behind it.

It’s worth being honest about scope: Spider Data measures and explains, what happened and why, but it does not set your prices. It’s the layer that shows you the crossing between how far in advance a channel books and what it ultimately brings in, so that the rate decision stays yours, better informed. The tool illuminates; the hand that decides is still your own.

Two verbs, a new way to see your hotel

Fear of data is almost always fear of vocabulary. The moment you discover that behind the big words there are only two gestures, joining what is separate and folding the many into the few, the dashboard stops being a wall and starts being a window. Every question about your operation, however complex it sounds, can be broken down into “what do I join?” and “what do I total, and by which group?”.

That, in the end, is the promise of crossing data well: not more reports, but better decisions. Knowing how far in advance each channel books, which guest comes back, and how much each truly brings in once collected is not a luxury for large hotels; it is the result of two simple ideas put to work together. Joining and totaling are, quite literally, the engine of all your hotel’s intelligence. And that engine, at last, is yours to drive.