Property penetration per corporate account for hotel groups and venues

A hotel group is the London hotel for a bank that also sends four thousand nights a year to Manchester, to someone else. The booking system knows every night the group sold; the RFP the bank issued knows every city it travels to. Joined, they answer the question a director of sales cannot answer today: for each corporate account, which of our properties do they use and which of their cities do we miss. This guide sets out the join and the roll-up.

The measures

Per corporate account:

Property penetration = properties booked ÷ properties in the account's footprint cities Gap value = Σ (unbooked footprint cities × typical nights × your average rate in that city)

Per property:

Segment mix = nights by corporate, group, leisure and agency, month on month

Per sales manager:

Coverage = accounts with a logged contact in the window ÷ accounts assigned

The rows you need

• Bookings: one row per stay or per account, property, segment and month, with nights and revenue. From Opera or the property management system.
• Footprint: cities and nights per account, from RFPs, reviews or the norm, with the source recorded.
• Account master: sector, size band, sales manager. From Delphi or the sales system.
• Contact log: optional.

Account identifiers only.

The roll-up

1. Account by property: nights, revenue, booked flag.
2. Account: properties booked against footprint cities, penetration, valued gap.
3. Property: segment mix, corporate share against the market norm.
4. Sales manager, region, group: assert that account nights and revenue sum to the manager, managers to the region, and the region to room revenue by property.

room revenue = Σ regions = Σ properties = Σ accounts = Σ segments

The by-property equality catches a property re-coded after a rebrand. The by-account equality catches an account under two sales managers after a territory change.

A worked example

One corporate account, footprint of five cities, four with a group property.

The group is the incumbent in London and absent in Chicago, where it has a hotel and the account sends six hundred nights. The sales manager's list opens with Chicago, valued, and the New York conversation after it.

Where it goes wrong

Footprint from RFP only. Accounts that never issued an RFP have no footprint and vanish from the measure. Use the norm for their band, labelled, so they appear as prospects.

Properties booked through agencies. Nights booked by a travel agency on the account's behalf appear under the agency. Map agency bookings to the corporate account where the booking carries the corporate rate code.

Rebrands and re-codes. A property that changed flag mid-year splits its history. Keep a mapping from every property code to the current one.

Group blocks counted as corporate. A conference block booked by the account's events team is group business, not transient corporate travel. Keep segments apart.

Every quarter, per sales manager

Mapped once, the booking export and the footprint data produce penetration per account, segment mix per property and coverage per manager every quarter, reconciled to room revenue. Covirage builds this from the export as it is, account identifiers only. The hospitality page describes the setup, and you can upload a sample booking export and see the roll-up on your own rows.