By hand or in GroundZones, step by step
| Step | By hand | In GroundZones |
|---|---|---|
| 1.Start from the file as you received it | Add working columns, keep a clean copy | Upload the file, confirm the columns |
| 2.Clean the addresses before placing anything | Check and fix every row up front | Fix only the hotels that get flagged |
| 3.Put every hotel on the map | Add-on or script, then paste coordinates back | Automatic on upload, each pin checked |
| 4.Measure driving distance from the terminal | One route lookup per hotel, typed in | Measured from the terminal automatically |
| 5.Choose a zone scheme your rate sheet can explain | IF formulas and lookups; drawn areas by eye | One rule table: distances, cities, ZIPs, drawn areas |
| 6.Assign the zones, then look at the edges | Fill down, then sort and settle overlaps by hand | One click, overlaps resolved, shown on the map |
| 7.Check the pins you could not confirm | Find them yourself, then check each on a map | Already flagged, with a filter to list them |
| 8.Export in the original layout | Remove working columns, re-check row order | Export, original columns plus the zone |
| 9.Keep the zoned file for the next list | VLOOKUP the old zones, redo the rest | Upload into the project, duplicates skipped |
1.Start from the file as you received it
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.
By hand
Open the file in Excel or Google Sheets, add columns for latitude, longitude, driving distance and zone, and keep a clean copy of the original so the layout can be restored at the end.
In GroundZones
Upload the CSV or XLSX and confirm which column is which. Every column and its order is kept, including the ones GroundZones does not use.
2.Clean the addresses before placing anything
Most bad pins come from bad input: an address crammed into one cell, a hotel name or suite number in the street field, a missing ZIP code, or a street with no number.
By hand
Go through the sheet row by row before geocoding anything, because a spreadsheet gives no warning about which addresses will place badly.
- Split combined addresses into street, city, state and ZIP with formulas or by hand
- Strip hotel names and suite numbers out of the street field
- Look up missing ZIP codes; a city name alone repeats across states
In GroundZones
Upload the file as it is. Hotels whose address cannot be confirmed are flagged once they are placed, so you fix only those, in the table or on the map, instead of combing the whole sheet first.
3.Put every hotel on the map
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.
By hand
Excel and Google Sheets cannot turn a street address into coordinates on their own. It takes a geocoding add-on, a script calling a geocoding service (usually with an API key and a daily quota), or a batch geocoding website, and then the coordinates are copied back into the sheet.
None of those tell you when the answer is for the wrong building, so checking each result against its own address is a separate pass.
In GroundZones
Every hotel is placed on the map when the file uploads, and each result is checked against the address it was asked for. A different house number, or a city and ZIP code that both disagree, marks the pin as unconfirmed instead of passing it off as exact.
4.Measure driving distance from the terminal
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.
By hand
A spreadsheet has no way to calculate a driving route. Either look up each hotel in Google Maps directions, one at a time, and type the miles into the sheet (a 300-hotel file is 300 lookups), or write a script against a paid distance service.
Then sanity-check the column: a driving distance shorter than the straight-line distance is impossible, so it means a coordinate is wrong.
In GroundZones
Pick the airport from a list by city, name or IATA code. Driving distance to each hotel is measured from the terminal, not the middle of the airfield, with no route lookups and no API key.
5.Choose a zone scheme your rate sheet can explain
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. Most markets end up mixing the two: distance bands for the bulk of the hotels, with area-based exceptions on top.
By hand
Distance bands are a nested IF or a lookup table against the distance column. Area exceptions by ZIP code or city are another lookup. An area you would draw, such as a district that does not follow ZIP lines, cannot be expressed in a spreadsheet at all: a mapping tool can show the shape, but deciding which hotels fall inside it is done by eye.
In GroundZones
Set up a zone rule table: each zone with its name, a maximum distance, and optionally cities, ZIP codes or a boundary you draw on the map. Name the zones exactly as your rate sheet does.
6.Assign the zones, then look at the edges
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.
By hand
Fill the formulas down, then sort by distance to find the hotels near each boundary. Hotels that match two rules have to be spotted and settled one by one, because a formula keeps whichever rule it checks first.
In GroundZones
Apply the rules in one click and every hotel gets its zone, with the result colored on the map. Where rules overlap, the hotel goes to the tightest zone that contains it, and a ZIP code check shows any ZIP split across zones.
7.Check the pins you could not confirm
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.
By hand
Paste each doubtful address into a map, compare it with the coordinates in the sheet, and fix the row. Finding the doubtful ones in the first place means checking the geocoding results from step 3 one by one.
In GroundZones
Unconfirmed pins carry an amber ring on the map, with the reason in plain words, and a Check pins filter lists them in the table. Mark a pin as correct, or edit the address and it is placed again.
8.Export in the original layout
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.
By hand
Delete the working columns, check the rows still line up with the original after all the sorting, and save it in the format the partner expects.
In GroundZones
Export to XLSX or CSV. The file comes back in its original columns with the zone filled in.
9.Keep the zoned file for the next list
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.
By hand
Match the new list against the old one with VLOOKUP or XLOOKUP on the hotel ID, or on name and address where there is no ID, copy the known zones across, and run steps 2 to 7 again for the rest. Keeping earlier versions means saving dated copies of the file.
In GroundZones
Upload the new list into the same project. Hotels already in it are recognised by their hotel ID and skipped, only the new ones are placed, and up to 50 earlier versions are kept to restore from.
Frequently asked questions
What is a hotel association file?
It is the list of hotels a ground transportation operator serves around one airport, each assigned to a pricing zone. Operators who sell airport transfers through Expedia and other partners keep one for each airport, and update it whenever a new list of hotels arrives.
Should zones use driving distance or straight-line distance?
Driving distance. Straight-line distance treats a hotel across a river the same as one down the road, so it underprices the hotels that are hardest to reach. Measure it from the terminal rather than the center of the airfield.
How many zones should an airport have?
As many as your rate sheet has prices. Fewer, wider zones are easier to maintain and to explain; add a zone when a group of hotels consistently costs more to reach than the hotels around it.
Can I zone a hotel file in Excel?
Partly. A spreadsheet can hold the file and the zone column, but it cannot place street addresses on a map or calculate driving distance without add-ins or scripts, so the measuring happens somewhere else and the results are copied back in by hand.
What do I do when a new hotel list arrives?
Merge it into the file you already zoned rather than starting again. Match hotels by ID where the file has one, skip the ones already zoned, zone only the new hotels, and check any whose address changed.
Zone your own hotel file.
Your first 14 days are free, with no card. Upload the file and see your first zone in a few minutes.
GroundZones is independent and not affiliated with or endorsed by Expedia Group.