How to zone an airport hotel association file

The finished file usually has to go back in the layout it arrived in, so keep the columns and their order, and add the zone rather than rebuilding the sheet around it.

Note which columns hold each part of the address, and whether each hotel has an ID. When the next list arrives, that ID is the most reliable way to tell a new hotel from one you have already zoned.

• Hotel name
• Street address, city, state and ZIP code, ideally in separate columns
• A hotel ID, if the file carries one
• The zone column you will fill in

Most bad pins come from bad input, and they are far cheaper to fix in the sheet than on the map.

• Split an address crammed into one cell into street, city, state and ZIP
• Remove the hotel name or a suite number from the street field
• Fill in missing ZIP codes; a city name alone repeats across states
• Expect an address with no street number to land on the street, not on the building

Turning an address into coordinates is called geocoding. The catch is that a geocoder can return a confident answer for the wrong building: when it does not know the exact house number, it may return the nearest one it does know, several blocks away, and still report a precise match.

So check the answer against the request. If the house number that came back is not the one you asked for, treat that pin as unconfirmed rather than correct, and set it aside for a person to look at.

Zone on driving distance, not straight-line distance. Two hotels the same distance from the airport can be very different drives once a river, a rail line or a limited-access highway is in the way.

Measure from the terminal curb, not from the airport's official center point. That point is the middle of the airfield, and a route calculated from it can start on a service road inside the perimeter, which adds a consistent error to every hotel.

Record driving time as well if your prices follow time. And sanity-check the results: a driving distance shorter than the straight-line distance is impossible, so it means a coordinate is wrong.

Distance bands, such as 0 to 5 miles, 5 to 10 and 10 to 20, are the simplest scheme to build and the easiest to defend when someone asks why a hotel costs what it costs.

Areas work better where geography breaks the pattern: a downtown core, an island or a district across a bridge that costs more to reach than its mileage suggests. Build those from ZIP codes, city boundaries or a boundary you draw.

Most markets end up mixing the two: distance bands for the bulk of the hotels, with area-based exceptions on top. Name the zones exactly as your rate sheet names them.

Apply the scheme, then look at the hotels it was least sure about. A hotel just over a band boundary, or one that falls inside two areas at once, is where a zoning mistake costs money.

Decide which rule wins when two apply, so exceptions override the bands rather than the other way round, and preview the result before you commit it to the file.

A wrong pin produces a wrong distance, and a wrong distance produces a wrong zone. Before exporting, go through every hotel whose address could not be confirmed, and every hotel no driving route reaches.

For each one, either confirm the pin is on the right building or correct the address and place it again.

Write the file back with the same columns in the same order and the zone filled in. Spot-check a handful of rows against the map before you send it.

Hotel lists change. When a new one arrives, merge it into the file you already zoned, skip the hotels you have seen before, and zone only what is new. Keep earlier versions, so a bad merge can be undone.