Joining onPeak housing data to the registration file

Hotel negotiations for next year open in six weeks and somebody needs a number: what share of registered attendees stayed in the official block. You have the housing file and you have the registration file, and the join looks like a morning's work.

It is not, and the reason has nothing to do with data quality. Joining onPeak housing data to the registration file fails on a structural point, which is that a hotel reservation identifies a guest and a registration identifies a delegate, and those two people are frequently different. Everything downstream follows from that.

What each file actually holds

The housing file describes stays. One row per reservation, with a guest name, an arrival and departure date, a hotel, a rate, a room type and usually a confirmation number. onPeak runs this on its own Compass Reservation System, alongside a Compass Dashboard for reporting, a Marketing Clipboard and its AXIS hotel system (onPeak, 2026). The reservation is the unit, and the guest is an attribute of it.

The registration file describes people who registered. One row per registration, with a badge type, a registration date, a company, a job title and a channel.

Neither file was designed to be joined to the other, and the field they share is a person's name and an email address that may belong to a different person than the one sleeping in the bed. That is the whole problem in one sentence, and it is why the answer to a simple question takes a week.

There is a second asymmetry underneath. A registration is one person one row, more or less by definition. A reservation can be one room holding two people, one guest holding three rooms, or one company holding forty. Before the join, decide whether you are counting reservations, rooms, guests or room nights, because those four numbers differ by a wide margin on the same file and each of them answers a different commercial question. A count of 500 reservations might be 500 rooms and 640 guests, and the hotel is negotiating on room nights while your marketing team is thinking about people.

Why does the email join drop the people you most want to count?

Email works reasonably well in bulk and fails in a pattern. It fails for the guest whose room was booked by an executive assistant using the assistant's own address, which describes a large share of your most senior buyers. It fails for the delegate booked by a group coordinator against a company card. It fails for the exhibitor whose stand manager reserved eight rooms for the build crew under one contact. Every one of those failures removes somebody whose stay you specifically wanted to count.

There is a technical trap underneath as well, and it is worth being precise because half of the pragmatic advice on this is wrong. RFC 5321 states that "The local-part of a mailbox MUST BE treated as case sensitive" and that "The local-part MUST be interpreted and assigned semantics only by the host specified in the domain part of the address" (Klensin, 2008). Strictly, Jane.Smith and jane.smith at the same domain are two mailboxes, and only that domain's mail server can say whether they are one.

In practice, the large providers fold case and most corporate mail systems do too, so lowercasing the local part recovers real matches and rarely creates false ones. Do it, and write down that you did it, because it is a judgement call with an authority pointing the other way. The full set of rules for normalising an email address before matching goes considerably further than case, and it belongs to its own discussion.

Working the join on 500 reservations

Take one show, one hotel-heavy edition, and 500 reservations. Illustrative numbers, but the shape holds up.

Of the 500 reservations, 468 carry a guest email address and 32 are blank, taken by phone or loaded from a group rooming list. An exact-match join against registration email returns 349 matches, which is 69.8 per cent of all reservations.

Fold both sides to lower case and rerun. You get 371, which is 74.2 per cent. Twenty-two reservations recovered for one line of SQL, which tells you something useful about how the two systems collect the same field.

Now add a second pass on surname plus company for the reservations that still have not matched. That recovers a further 44, taking you to 415, or 83.0 per cent. Look at the 44 and you will find most of them are the same person with a work address in one system and a personal one in the other.

The remaining 85 break down. Thirty-two have no email at all. Twenty-nine carry an email belonging to somebody who is not the guest, mostly assistants and coordinators. Twenty-four are people who genuinely never registered: spouses, a photographer, two contractors and a hotel-side coordinator.

That last group changes the denominator, and this is the step most reports skip. Of 500 reservations, 476 could in principle be tied to a registration. Matching 415 of 476 is 87.2 per cent, which is a different and more honest statement than 83.0 per cent of everything. Publish both, and label them.

At an average stay of 2.8 nights, the 85 unmatched reservations account for 238 room nights out of roughly 1,400. Seventeen per cent of your block is unattributed, which matters to the negotiation and matters more to anyone building an accommodation figure for an event footprint, where the same gap has to be handled with an explicit assumption rather than quietly dropped.

What do onPeak's integration partners already solve?

Before building any of this, check whether it exists. onPeak names Visit by GES, Maritz Global Events and Event Citadel, formerly CompuSystems, as integration partners, and describes the result as combined analytics giving "a complete and up-to-the-minute picture of registration and housing" (onPeak, 2026).

If your registration supplier is one of those three, the identifier problem may already be handled inside the partnership, and the exercise above is a rebuild of something you are paying for. Ask what the integration actually passes, in both directions, and ask specifically whether your registration reference is written into the reservation record or whether the two systems simply exchange counts. Those are very different things and both get described as an integration.

If your registration supplier is not on that list, the request to make is narrow and cheap: have the registration reference appended to the housing booking link as a parameter, and have onPeak persist it on the reservation. That single field turns a 87 per cent fuzzy join into an exact one, and it is a configuration conversation rather than a development project.

Keys worth asking for, in order

The registration reference carried through the booking link. Best available, exact, and free once configured. Ask for it before the housing site opens, because it cannot be applied retrospectively.

The housing confirmation number captured on the registration form. Works in the other direction and depends on delegates typing it correctly, which perhaps 60 per cent of them will. Useful as a secondary key, not a primary one.

Email, lowercased, with the caveat above. Your workhorse. Expect it to carry roughly three quarters of the file on its own.

Surname plus company, or surname plus arrival date and hotel. A recovery pass for what email misses. Review the marginal cases by hand rather than trusting the rule, because two people from the same large exhibitor arriving on the same day is a common false match.

Room list reconciliation with the hotel. Slow, manual and occasionally the only way to settle a dispute about pickup. Reserve it for the one or two properties where the money is.

The order matters more than any individual key, because each pass should only run on what the previous pass left behind. Running surname plus company across the whole file instead of the residual will merge two people at the same exhibitor who both stayed, and a false merge in a housing join quietly removes a room night from the count while looking like an improvement to the match rate.

Where this stops

A high match rate does not mean you know where people stayed. It means you know where people booked through you, and those are different populations. Attendees who booked directly with a hotel, used a corporate travel programme, stayed with a colleague or picked a serviced apartment are invisible to the housing file no matter how good the join is, and on a city-centre show that group can be larger than the block itself.

That is a real limit on what the number can be used for. Block participation is a fair measure of how well your housing programme competed for the bookings it could reach. It is a poor measure of total attendee accommodation, and quoting it as the second thing will eventually collide with a venue or a destination partner who has their own count.

The other limit is timing. Housing files move continuously until well after the show closes, through cancellations, no-shows and post-event adjustments, so a join run on the Friday and a join run three weeks later disagree. Pick a cut date, write it on the report, and keep the earlier extract. The reservation grain questions that decide what those movements even mean sit with modelling room blocks from the reservation data itself, and the general habit of pinning a cut date belongs to the rest of your integration practice.

This week, take 500 reservations from your last edition, run the email join on its own, and count the failures by reason. Twenty minutes of manual review across the failures will tell you which of the five keys above is worth asking for, and you will have the evidence when you ask.