Multi-leg QFF Points & SC Calculator

[sudoer]

Many of you would be aware that the QFF points and status credit calculator was overhauled in late January with a slew of 'enhancements' in true Qantas style. Most notably, the new calculator is slower and less intuitive than the one it replaced, and no longer offers calculation of multi-city itineraries. Despite being a downgrade in many aspects, one positive to arise from the revamp was a publicly accessible API (credit to @AisleSeat for spotting this)

I've put together a Google Sheet which provides the following features using the API:

• Simple calculation of QFF points and status credits for up to 16 legs
• Addition of status bonus points and/or promotional SC where applicable
• Validation of city pairs, carriers on specific city pairs, and SC earn categories
• Retrieval of SC calculation method used for each leg
• Retrieval of API's 'debug distance' - useful for troubleshooting unexpected earn rates (e.g. KUL-BKK) or calculating approx iitinerary mileage

To use the Sheet-based calculator, follow these steps:

1. Have a valid Google account with access to Google Drive - free Gmail accounts can be used
2. Click this link and follow the prompts to make a copy of the sheet in your own Google Drive
3. Ensure your QFF status is correctly selected in Cell I1 (defaults to Plat)
4. Start calculating!

To reset the sheet to its original state click the Reset Data button. On first run, this function will ask for authorisation to modify the sheet and show some ominous warnings. All script code can be reviewed by clicking Tools > Script Editor, however if you would prefer not to use the script simply return to this thread and make another copy of the sheet or reset the values manually.

I'd love to hear feedback or suggested improvements to the sheet and hope to continue adding features if there is sufficient interest.

Known issues

Cell drop-down load times: When using the drop-down menus for Origin, Destination, Carrier or Class, the menu will sometimes show a "Loading.." message but never update. This is due to clicking through menus faster than the API can respond, and Google Sheets drop-down menus not updating in realtime. When this occurs, simply select another cell and then reselect the drop-down menu - the API will have most likely responded by this time and the list should populate. The drop-downs can also be skipped entirely by manually typing the three digit airport codes and two-letter airline code in upper case. Use of the drop down for Class is recommended as acceptable input varies by carrier and route.

Missing routes: Unfortunately the new calculator and underlying API are still missing some routes. The Google sheet is beholden to the data provided by the API so is also affected by any omissions/inaccuracies. Adding a database of routes/carriers may be something to look at in the long-term, but isn't a high priority at this stage.

 

[justinbrett]

You can still access the old calculator - Frequent Flyer - Flying Qantas & Partner Airlines - Points Calculators - Earning Points

It still works fine.

 

[sudoer]

You can still access the old calculator - Frequent Flyer - Flying Qantas & Partner Airlines - Points Calculators - Earning Points

It still works fine.

Yes, thanks, I'm aware. It would seem unlikely Qantas will maintain two calculators moving forward, and they've redirected all other URLs that pointed to the previous calculator, so that URL will probably die at some point too.

In any case I'm not forcing anyone to use this sheet, just sharing it for the benefit of the community.

 

[samh004]

Pretty nifty, but a shame that the missing routes can't be auto filled based on distance and then if a specific airline has a rule that overrides that logic (such as routes that compete with QF metal), it is shown instead. For instance, both the QF calculator and your document will not calculate a QR route DUB-DOH despite it existing. I would imagine it falls under the same distance that LHR would (away from DOH), so that's how I estimated my SC earn and a calculator could pick out which airports are in a range from each other to allow for them to be selected – of course, whether an airline flies the route is another story. Still, good work.

 

[larrymull]

I can vouch that this is a fantastic spreadsheet, it is my go to option now, well played.

 

[Rob E]

This is a great tool, thanks very much. Not sure what routes are missing but appears to be pretty comprehensive from what I can see, have you updated it?

I'm flying DUB - LCY in a couple of months and wasn't expecting it to be an option considering LCY doesn't show up in the QF flight search but it worked and had the pleasant surprise of realising that I'd get SCs for it on BA.

 

[Kremmen]

This is a great option, given that the new Qantas page itself doesn't work for me at all. (After putting in first city, it just shows dots forever on second city. It could be relevant that my ad-blocker is blocking 25% of the page's URLs, which is also a good reason not to use the page anyhow.)

Also, great to see the old calculator link. It still works fine and doesn't have a million ads/trackers loaded.

I do have a couple of questions.

1) "The drop-downs can also be skipped entirely by manually typing the three digit airport codes and two-letter airline code in upper case." A bit tedious. Why not allow lower case?

2) Why is the API calculating things differently to the old calculator?
e.g. Let's keep it simple. BKK-SGN on BL for basic member is 800 on old calculator and 450 on this sheet. Minimum Points Guarantee table says 800 for Jetstar, so is the new API simply wrong?

 

[MarcB]

Thanks. This is great!

 

[Ade]

An amazing piece of work and I use it almost every time I book a trip. Used it extensively today to help my mates calculate some SC and I'm now seen as a hero at work

Thanks, @sudoer

Cell drop-down load times:

In my case, I was doing the same route again and again, so I simply copy-pasted the routes from earlier trip and the SC's were calculated. I realize not everyone will do the same routes every time, but for those who do, a quick tip

 

[sudoer]

Glad to see this is getting some use!

For instance, both the QF calculator and your document will not calculate a QR route DUB-DOH despite it existing. I would imagine it falls under the same distance that LHR would (away from DOH), so that's how I estimated my SC earn and a calculator could pick out which airports are in a range from each other to allow for them to be selected – of course, whether an airline flies the route is another story.

I will look into whether this is possible. The API's origins response includes coordinates of each supported airport so there might be a way I can calculate distance between two lat/long addresses and calculate SC from that. Can't say for sure how accurate the distance calc will be though, and there's the obvious caveat of a region/city specific QF earn table overriding the distance calc.

Not sure what routes are missing but appears to be pretty comprehensive from what I can see, have you updated it?

I haven't updated it - the sheet only calculates routes the API supports for now. That list is quite extensive already but there are a few random partner routes missing. No doubt there will be plenty more missing when RAM joins oneworld.

"The drop-downs can also be skipped entirely by manually typing the three digit airport codes and two-letter airline code in upper case." A bit tedious. Why not allow lower case?

Since writing that I've found only the airline code needs to be upper case. Try loading the below URL

https://services.unique.qantas.com/calculator/v2/segment/SYD/MEL/QF/business.json?status_tier=Platinum&date=2020-03-05

Changing SYD to syd or MEL to mel works, but switching QF to qf will throw an error. In any case (pun fully intended) I can add support for lower case airline codes using the UPPER function to convert to all caps. Will put that on the list for the next revision.

2) Why is the API calculating things differently to the old calculator?
e.g. Let's keep it simple. BKK-SGN on BL for basic member is 800 on old calculator and 450 on this sheet. Minimum Points Guarantee table says 800 for Jetstar, so is the new API simply wrong?

Good spot - it seems the API doesn't calculate minimum points guarantee at all for QF/JQ/3K/BL/GK. This must be handled by the frontend serving the new calculator. I'll see what can be done about this in the next revision.