Episode #81: How to hack award flights on Avianca Airlines with Google Sheets

This post originally appeared on the KeyCuts blog.

I love a good deal, especially when it comes to getting a good flight. Ever since I got into churning a few years ago (signing up for credit cards to get rewards), I started booking flights primarily with reward points from these credit cards. I spend more time searching for flights with good point redemptions than I'm proud of, but when you find a great flight it's like finding an undervalued company whose stock is about to pop. Or for the millennials out there, it's like finding a meme-coin for $0.000001 before Elon Musk Tweets about it. This post definitely gets into the weeds of the reward hacking ecosystem, and may be too in-depth for some. But there are some Google Sheets skills you can gain from seeing the tool I built for this episode (copy Google Sheet here).

One of the most coveted seats one can book with award miles: Singapore Airlines suites. Source: The Points Guy

Award flights: from hack to hobby

The inspiration for this episode came from multiple places. Booking award travel started off as a hack I thought was only meant for people 1) with too much time on their hands and 2) who travel for a living (e.g. writers). Over the last few years, I realized my online and newsletter "diet" is slowly being taken over by these award travel blogs.

One Mile At A Time: one of my favorite news sources for award travel.

It first started with trying to understand the lingo in subreddits like /churning and /awardtravel. I even started going to meetups where like-minded award travel hackers congregate (shout out to Spencer Howard from STTP for organizing an NYC meetup earlier this year!). Recently, I've been listening to Chris Hutchins' All The Hacks podcast who I feel brings award travel hacking to the "mainstream." While Chris is an award travel aficionado at heart, he brings this "hacking" mindset to other aspects of his life. This brings me back to knowing rules of the game you play.

Knowing the rules to "win" a game

I wrote and spoke extensively on relating games you play in life to Squid Game in episode #80. I mentioned travel hacking as one of these games, but wanted to spend this episode diving more into the rules of this game. Knowing the rules of booking award flights can help you "win" a nice business class flight. When you explore the edges or extremes of these rules, that's where you can get a really good redemption or benefit. That's what makes award flight hacking so fun, in my opinion. It's kind of like taking a model or some code and testing for the edge cases. You don't want your model to output something that's ridiculous.

You hear about these "error fares" where the airline makes a mistake and you're able to book a flight for a fraction of its normal cost. This is not the same as using the award rules of airlines, their airline partners, travel alliances, and credit cards to find these good deals.

What I like about All The Hacks is that Chris applies this mindset to things like your 401(k), shopping online, getting a low interest rate on your mortgage, etc. These are all little games you can play to maximize and optimize your earnings or savings. Another community that really embodies this mindset is the FIRE community (Financial Independence/Retire Early). The best part about all these games is that you can encode them into a spreadsheet to find the path to winning, whatever that means for you. I'll leave you with this:

Playing by the rules of the game can get you far. But if you take the rules to the extreme, you will go farther.

Booking mixed-cabin award flights on Avianca Airlines' LifeMiles program

I'm currently looking up flights for a potential trip next year, and have points in various points "ecosystems" like Ultimate Rewards from Chase. One program I'm looking into more is Avianca Airlines' LifeMiles program. Avianca Airlines is an airline based in Colombia, but through their online portal, you can book flights from their partners in the Star Alliance. When booking an award flight with LifeMiles, you also never pay fuel surcharges which can add up to several hundreds of dollars per ticket.

Did some research and came across this post from FrequentMiler. There is an interesting hack with LifeMiles' program where you book a mixed-cabin ticket, and the number of miles you pay is less than the miles you would pay for booking a direct flight to the destination. Let's break this down.

A one-way ticket from New York to France in business class costs 63,000 miles

Now this depends on availability and all that, but according to the LifeMiles award chart (pulled from the FrequentMile post), a ticket from New York (United States 1 region) to France (Europe 2 region) costs 63,000 LifeMiles:

Source: FrequentMiler

I can't confirm if this award chart is still accurate. Nonetheless, it's still a good starting point to calculate potential costs for flights.

A multi-city ticket from New York to France in business class then from France to Turkey in economy class only costs ~50,000 miles

That's a 20% savings in points for basically getting the same flight (New York to France in business class). You also get this extra leg in economy class from France to Turkey. If your final destination is Turkey, this might be a great way to get there. How is this possible?

Unlike other airlines, Avianca does this weighted-average approach to using miles based on the total miles flown in each segment. So here's how the math works:

• EWR to CDG = 3,649 miles
• CDG to IST = 1,379 miles
• Total miles flown = 5,028 miles
• EWR (US1) to CDG (Europe2) in business class = 63,000 LifeMiles
• CDG (Europe2) to IST (Europe3) in economy class = 15,000 LifeMiles
• "Weighted average" actual LifeMiles cost = 3,649 / 5,028 X 63,000 + 1,379 / 5,028 X 15,000 = 49,835 LifeMiles

You would think that the total cost should just be 63,000 + 15,000 = 78,000 LifeMiles. This is an example of how knowing the rules of the game can help you save a decent amount of money (let's face it, award miles are a form of currency these days).

Creating a tool to calculate multi-city routes to take advantage of this mixed-cabin hack

As you can imagine, this hack allows you to fly first class to one city, then business or economy class to another city, and the resulting cost will be less than a direct flight. The distance in that 2nd leg where you're in a lower cabin basically determines how much you save.

Those screenshots in that FrequentMiler post tells me that the author (or Avianca) originally had this award chart in Excel. I copied over some of the relevant numbers for a flight originating from New York (United States 1 region) into this Google Sheet. Here's a screenshot of the tool with relevant sections called out:

Structuring the data

Before building the actual miles calculator, we have to lay out the data in a way that makes sense for the tool. This was the original "structure" of the country to region mapping in the FrequentMiler post:

Source: FrequentMiler

While this layout above is great to quickly see which region your country resides in the award chart, it's not a great structure for creating a dynamic tool or model. We just need a simple two-column table mapping the country to the region to do lookups. You'll notice I left out United States 2 and United States 3 since I'm only interested in flights originating from New York (United States 1).

The actual award chart is also in a format that is easy to read to find a specific number, but not great for our model. So we need to transpose that award chart into a four-column table that looks like this. This table contains all the different combinations of a "from" and "to" region that make sense for this tool:

Calculating LifeMiles for a direct flight

The first step is to quickly find the number of miles it takes to go from US1 to any country in Europe for a given cabin. The first thing to do is make the input for country and cabin as error-free as possible. These two inputs are dropdowns so that the user can only select specific values for these two cells:

To create these dropdowns, you can click on Data->Data Validation and just reference all the countries that we want as selectable options:

Once we have the destination country and the cabin, this formula gives us the number of LifeMiles for that flight:

=filter(L2:L10,J2:J10=vlookup(B4,F2:G41,2,0),K2:K10=B5)        

Breaking down this formula:

1. The vlookup pulls the region for a given country based on our country->region table in columns F and G
2. The filter formula pulls the number of miles only from the range J2:L10
3. The first condition in the filter formula matches the region in J2:J10 with whatever the vlookup finds
4. The second condition in the filter formula matches the class in column K2:K10 with the class the user enters in cell B5

Once you have the unique combination of the "to" region and the class, the filter formula return the number of miles in L2:L10.

We could've done an INDEX/MATCH to find the miles, but the FILTER formula was the first one that came to mind. I'd say this is not a normal use of the FILTER formula because you usually use this when the data that is returned is an array. You then would apply a SUM or AVERAGE or some other aggregate function on the data that comes back in a format like this:

[20000, 60000, 87000]        

Regardless, we got our LifeMiles needed for a direct flight!

Calculating LifeMiles for the mixed-cabin hack

What we really care about is how many miles we can save if we do the mixed-cabin hack discussed above. The functionality is similar as the direct flight portion. The main difference is that we need to get the number of miles flown between two regions:

Finding distance (miles) between two cities

Originally I tried finding some Google Sheets add-on for geo-coding to automatically calculate the number of miles (flying) if you enter in two cities:

Unfortunately, all these add-ons utilize the Google Maps API which means you'll only get the number of driving miles between two cities. Not really useful when you're trying to find the distance between New York and Paris.

I even considered using something like the SkyScanner API and creating a Google Apps Script to pull in the miles I need. Figured it was too much work for a simple tool to calculate LifeMiles savings, so you have to Google the distances between the two cities you're interested in.

Surprisingly, Google Maps gives you the option to select an airplane as the mode of travel, but it doesn't tell you the number of miles between the two cities. Ended up using this random Air Miles Calculator site to get the number of flying miles between two cities.

Formula for calculating mixed-cabin miles

With the inputs from the user, we now can create this formula to calculate the LifeMiles cost for a mixed-cabin multi-city flight:

=B12/(B12+B16)*filter(L2:L10,J2:J10=C10,K2:K10=B11)+B16/(B12+B16)*filter(L11:L37,I11:I37=C10,J11:J37=C14,K11:K37=B15)        

The formula looks super long but when you break it down, it does something similar to the formula for calculating a direct flight:

1. The first filter formula is pretty much the same as the direct flight formula. Only difference is that the first condition J2:J10=C10 references cell C10 (Europe 2 in this case). Decided to just do the vlookup in cells C10 and C14 for simplicity.
2. Before the first filter formula, the B12/(B12+B16) handles the proportion of the flights that happen in business class. We take this percentage and multiply it by the first filter formula to get the proportion of flown miles that happen in business.
3. The second filter function has conditions that match the "from" region in column I and the "to" region in column J to where the second flight happens. The user only has the option to select another city in Europe since it's unlikely you'll do another transatlantic flight for your second leg (I guess it might be possible if you really want to lower the cost of the trip, but Avianca probably doesn't allow this in their multi-city search).
4. The B16/(B12+B16) does the same thing as step 2 for the second filter function: it finds the proportion of miles flown in the second leg in the lower class (in this case economy).

Label shows European cities you are from from and to

A small user experience feature I added to help the user know which cities they are flying from and to is in cell A16:

This cell simply changes text based on what cities the user selects. This just helps the user know which cities to look up online to find the number of miles between the two cities

Final notes on this calculator

The big drawback to this tool is that you can't enter in cities where you want to travel and the number of miles between two cities doesn't automatically calculate. It also doesn't account for other regions in the United States since I (selfishly) only care about the United States 1 region.

From a modeling perspective, I think it highlights a few best practices for analysts:

1. Structuring your data (columns F to L) is the first thing you should do before writing any formulas
2. Inputs should be in yellow and use data validation to prevent human error
3. Helper cells (or comments) can inform the user how to use the tool (column C)
4. The filter function can be used to find a specific value when you don't want to use multiple vlookups or index/matchs

There are probably a bunch of other thing we could do to improve this tool like showing a summary table that shows how many LifeMiles you are saving. For small tools like this that are meant to help you do one thing and one thing well, making the tool "good enough" is all that matters.

Using crypto to buy flights?

Holding (excuse me, hodling) award miles is a lot like holding crypto. I suppose the big difference is that you eventually do spend your award miles on flights once you find a sweet spot redemption.

But what if miles worked more like crypto? What if airlines gave you a way to stake your miles like you can in many defi platforms? Let's say you know you won't be traveling for the next 1-2 years, so you decide to "lock up" your miles in a smart contract that earns interest every week or month. For the airline, they might be able to convert your miles to cash and add this to their balance sheet. Or maybe they can act like banks and lend out the miles at a higher rate to other travelers.

Transferring your rewards from any of the big points ecosystems to travel partners feels a lot like converting your ETH into other ERC20 tokens or bridging them over to other layer 2 protocols like Polygon. I think there's a startup idea in here somewhere on being able to convert your award miles into crypto and vice versa. If that happens, there will be a lot more people flying business and first class, so maybe the airlines are averse to this idea.

Or perhaps a new airline emerges that only accepts crypto as a form of payment like how Gary Vaynerhuck is opening an NFT restaurant where "members" can only access it if they own the NFT:

Whatever the case may be, travel hackers will be ready to play the game.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• The Tim Ferriss Show #538: How I Built The Tim Ferriss Show to 700+ Million Downloads

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS