I’ve been helping a friend plan a trip to Istanbul and back this July. We booked the outbound months ago, but getting back to San Francisco has been a bear. UA.com and the ANA search tool were coming up empty for days in either direction on ANY transatlantic flight on ANY star alliance carrier.

SFO-FRA (August and September 2014)

I can only look at calendars like this for so long

My friend is fairly new to the game and noticed my frustration, but he eventually found a flight through MXP, which I didn’t even know UA flew to. It spurred some thought that generally Europe is very good for award availability within the continent and that a vast majority of demand for award seats depends on three factors:

1. What do the search engines and agents prioritize or see first? - Typically alliance hub-to-hub routes since the algorithms are programmed to search them first and eventually time out.
2. What do mileage hackers (not my term) know about airline networks and how they use this knowledge to their advantage? - Generally searching from nicest carrier and most convenient hub on down, factoring in how historically generous/stingy carriers are with award availability.
3. What airports are just “big” airports? - Places like JFK, ORD, EWR as well as LHR, FRA, CDG are massive airports with a ton of flights going nearly everywhere. Even though they are hubs, many other carriers fly to them too and many people living in those cities have the money to fly internationally.

This presented a great opportunity to look at things differently. After looking at the 32 busiest airports in both Europe and North America as part of a 23-hour layover series, I noticed a few places weren’t “hub captive” or were less dominated by one carrier than I had originally thought, yet they still have sufficient traffic to have a number of bookable routes.

So instead of looking at award bookings the traditional way, e.g. “I live in SFO, so I should look at all SFO-Europe direct flights first (generally LX/LH/SK/UA in that order), then SFO-LAX/IAD/EWR/ORD-Europe (again preferring European carriers generally), I should start the opposite way, particularly looking for routes that connect at least one midsize-large city that isn’t part of a partner’s hub. We’re still looking for the transatlantic flight first, we just aren’t being very picky about which cities to connect through. Then, check on the intra-continental flights to get from our home city to the North American gateway city and from the European gateway city to our destination. Intra-Europe availability is usually pretty easy to come by and we always have discount carriers and avios at our disposal to make short hops.

Building the dataset:

I originally started to do this piecemeal, and found it to be exhausting and far from complete. Wouldn’t it be great if there was a database of ALL transatlantic (TATL) flights, so I could be more methodical? Seth at Wandering Aramean put together a great collection of maps of all TATL routes for Star Alliance and OneWorld, but there was no way to iterate or search over possibly destinations given your own criteria and his “anywhere to anywhere” search tool was shut down by United lawyers (acting beyond their scope IMHO, but I completely understand Seth not wanting to get into fights with people with deep pockets).

Rather than doing it piecemeal, I ended up just visiting the wikipedia pages of all hub airports (still one of the best resources for finding all routes out of an airport) between the two regions, and recorded all 403 TATL routes currently flying, on all alliance and non-alliance partners that you could redeem with without too many hoops (assuming you hold some combination of UA, AA, DL, US, AS, SQ, and BA miles or transfer partner points). If I have missed any fifth freedom flights, let me know. Amol has a great article here that should be bookmarked by any hardcore mileage aficionado.

Google Docs version of All TATL Routes Business Class Database

Excel Version of All TATL Routes Business Class Database

I compiled the original data set in a flat file with alliance, carrier, route, whether the seat is angle-flat, lie-flat or in the process of being retrofitted (since Gary Leff says Business Class is all about the seat 😛 ) and broke it rather bluntly into whether you should expect North American service standards (be glad they aren’t spitting in your food) or European/International service standards (prompt and courteous without the snark, though they may make up a rule or two). The flat file tab is mostly for the excel jockeys that just want a list to iterate over, that you or a automated friend could use as a dataset…

Every Star Alliance TATL Route

Don’t worry, I didn’t forget Skyteam, OneWorld or non-aligned carriers!

Obviously, there are a host of other factors that can tip the balance in favor or against a particular carrier or route, so for us mere mortals, I also formatted the data set by alliance and included a tab for non-alliance partners, since we generally have a mileage account ties to an alliance in mind when searching for award space. Then, to the best of my ability (either from my own experiences, or reading a number of reviews from people I know have a wide exposure to a number of business class offerings and therefore can compare more objectively) I tried to peg carriers on the following soft factors:

1. Food & Drink - Do people generally refer to it as swill or plastic or do they find it delicious and flavorful?
2. Service - Was food generally thrown at them or did the staff take the time to ensure passengers’ needs were cared for?
3. IFE - 1990’s or 2010’s?
4. Wi-Fi - Could use help here in the comments since my experience is pretty slim - Does it work as advertised? Is it fast?
5. Hub Airport Lounges - I don’t fault an outstation contract lounge for being lackluster as much as a home airport lounge where a carrier has more control
6. Award-availability - Reliable to find or scarcer than hen’s teeth? ::cough:: NZ ::cough::
7. Consistency - Is the airline known for a consistent experience across a large number of flights, crews and aircraft or is it a roll of the dice?

Obviously these factors are very very subjective, so think of the ratings as the midpoint of a fairly broad bell curve. If you fly 100 LX and 100 UA flights, you’ll probably conclude LX has a better offering across the board, but people place different weights on these things, so feel free to make reasoned comments or download the dataset and modify/delete to your liking.

Presenting it visually should allow you to exclude carriers you don’t particularly want to fly, while leaving the routes left to check in an easy to access visual format. Just right click a column and select “Hide Column.” No excel skills needed. Just print it out.

Lastly, the routes from airline hubs to non-hub destinations for that alliance are highlighted in blue. Those are the routes you should search first if the search engines don’t give you anything to work with and your favorite hubs have no availability.

There are a few instances, particularly with Air Canada and Delta, where is made more sense to list FRA, LHR, CDG or AMS as pseudo-hubs for those airlines than aggregate random routes under additional columns. Not sure how else you would categorize a LAX-LHR flight when DL has more int’l flights out of LHR than LAX.

Data Conclusions:

This is where things get interesting. I put together a pivot table that may or may not translate to Google Spreadsheet very well. If it doesn’t, just download to excel, highlight the flat file and click “Pivot Table” on the Data tab/menu.

Ideally, the goal of this was to find airports that had less demand for TATL routes because the residents of that city aren’t all holding the same mileage currency and airline search engines generally aren’t smart enough to search availability through their non-hub airports, connecting to a partner’s non-hub-to-hub route. Then, at that partner’s hub, you could connect to your final destination, leveraging the city as a hub, but avoiding the bottleneck created by artificially high demand.

Once you get to Shannon, connect to any Star Alliance hub to get to your final destination.

EWR-SNN-FRA-NCE, for instance would allow you to use FRA to get you nearly anywhere in Europe but you bypass the huge demand looking for flights from UA hubs to it. I doubt many agents or search engines will pull up Shannon, Ireland as a viable option, but UA operates seasonal flights there and you have the added bonus of pre-clearing customs in Ireland on the way back.

Alliance Differences and the Importance of Non-Aligned Partners:

Star Alliance has 42% of all routes, with UA, AC and LH providing the most seats. DL, AA, US and BA together fly 2 of out 3 routes over the North Atlantic.

When putting this together, I noticed that Star Alliance coordinates their flying to partner hubs (nearly half of all TATL routes are hub to hub) considerably more than OneWorld or Skyteam (at about 30% hub-to-hub each). In addition to also having 42% of all routes, it’s quite a connected powerhouse when demand is low. But when availability dries up, there are comparatively fewer “off-the-beaten-path” options that aren’t checked. On the other end of the scale, Skyteam and OneWorld don’t seem to plan their routes on where their partners are as intentionally, so I imagine demand is considerably softer there, particularly since both AA and DL don’t have the most proactive search tools. You would think they would prefer to connect more focus cities to partner hubs to increase the chances of onward through traffic.

Interestingly, VS adds a lot of strength to Delta Skymiles members since they fly to a number of leisure cities that are cheap airfare markets and aren’t hub captive (LAS, MCO, MIA, BOS, LAX). Similarly, Aer Lingus seems to be pursuing a similar strategy. And they partner with UA and BA, making it easy to get to those markets!

Virgin Atlantic Routes

Yes, there are surcharges, but this is blunted by the fact that TATL prices on VS to the UK are 90k roundtrip in business! Connect to other Skyteam partners to go further.

Aer Lingus Routes

EI flies to a lot of hubs that UA, AC and AA/US have good access to. From there, continue on a partner into the continent or hop easyJet or Ryanair to vacation destinations.

Changing your search behavior:

Being able to look at the entire dataset, I tried to come up with a metric for measuring cities with high capacity but low demand. In a perfect world, this would be a city with a lot of flights across the ocean spread evenly across the three major alliances and some non-alliance partners thrown in. We’re basically looking for “big airports” that aren’t too picky about where they link up across the pond.

To do this, I modified a Least Squares Regression model to minimize the difference between a particular airport and it’s “ideal” mix of flights. Changing the parameter to 0.33 assumes no non-alliance partners (and you can’t really make it go higher, since the alliances have 100% of the market share) and 0.25 assumes they are treated the same as alliance flights. The market is currently at 0.31, corresponding roughly to the 7% of routes that are unaligned. Yes, the math isn’t completely correct, but we’re going for ballpark/gut feeling/directionally correct. Happy to chat about more reliable methods via email.

So, to improve your chances of finding a seat, we sort by the cities that are the least hub-captive. This yields a list with cities likely to have the least competition for mileage awards, because the resident airline is a smaller percentage of flights than typical hubs but there are still a lot of flight options that loyal customers there wouldn’t necessarily have access to. I’ve also included the number of TATL routes each one has.

Europe:

• MXP 8
• SNN 6
• FCO 16
• VCE 4
• ATH 4
• MAN 10
• DUB 14
• LHR 65
• TLV 7
• BRU 12
• MAD 21
• BCN 8

North America:

• LAX 17
• BOS 13
• SEA 4
• MIA 21
• JFK 58
• MEX 9
• ORD 33
• MCO 4
• LAS 4
• SFO 12
• DEN 2
• YVR 9

At the back of the pack, you’ll see some old favorites that you’ve probably searched for hundreds of times, airports whose availability gets eaten up by all the automated search queries, mile-rich loyal customers and common knowledge about alliance hubs:

Europe:

• MUC
• FRA
• AMS

North America:

• CLT
• PHL
• EWR
• ATL

Assumptions and Caveats:

The premise of this dataset is that you’re pretty agnostic to the particular routing, as long as you get over the ocean. I made it easier to take note of and filter our inferior business class products, but at the end of the day, folks like UA, AC, LH, DL, BA, US and AA are going to be providing the majority (66%) of routes. At least the North American carriers are improving their seats and quality of service.

Secondly, I understand that this doesn’t look at overall lift (actual number of seats) since some carriers put 747 of certain routes and other use 757s, but this should give you a good picture of places to start looking. Carriers up gauge and down gauge routes all the time, so trying to account for that would severely reduce the utility and readability of the dataset.

Conclusion:

I hope this analysis changes how you hunt and peck for award availability and saves you time finding seats in what it otherwise a tedious process. Start with the cities highlighted in blue that get you close to your destination instead of banging your heads against hubs with no space.

So take a look, leave some comments and keep it in mind next time you’re making a trip to Europe. I’m putting something similar together for other regions in the next few weeks.