Weekly flight risk brief for trips your team already booked

Every Thursday at 8am, build me a flight risk brief covering the trips my team has already booked, so we can fix bad legs while changes are still cheap. This is a pre-trip, predictive check, not day-of tracking. I want to know which booked itineraries are likely to go wrong several days before anyone leaves for the airport. Use a cron trigger for the Thursday 8am schedule.

Start in Airtable. Use List Records on my travel table to pull every upcoming trip whose departure date falls in the next 10 days. Each row holds the traveler, the flight number (for example BA286), the departure date, the route, the connecting hub and scheduled layover where there is one, and the meeting they are flying in for including that meeting's start time. Filter out rows outside the window and rows with no flight number, and page through the results if there are more than one page.

For each leg, confirm the schedule first. Call AeroDataBox Get Flight Status by Date with the flight number and that leg's departure date to lock in the scheduled departure and arrival times. AeroDataBox date path parameters are the local airport date in YYYY-MM-DD format, so use the departure airport's local date rather than my own. This call exists only to resolve the scheduled times for the booked date. Do not turn it into live status monitoring.

The core signal is history, not the live feed. Call Get Flight Delay Statistics for each flight number to pull that flight's historical on-time record: how often it runs late, and the typical and median delay when it does. AeroDataBox live tracking coverage is strongest in Europe while static schedules cover most of the world, so the historical statistics are the more globally reliable signal and should drive the ranking. Then call Get Airport Delays Over Period for the connecting hub, and for the departure airport when the itinerary is tight, across the last couple of weeks, to see whether the airport itself is trending bad rather than just this one flight. If Get Airport Delays Over Period is not available on my plan, fall back to Get Airport Delay Snapshot for a point in time read.

Rank every trip by risk, worst first, and give each leg a rating of high, medium, or low. Flag three things specifically. First, any flight whose typical delay would push the traveler past the start of their first meeting. Second, any connection where the scheduled layover is shorter than the median arrival delay of the inbound leg. Third, any hub whose delay trend over the period is congested or worsening. A leg that hits more than one of these ranks above a leg that hits only one.

Email the ranked brief to me with Gmail Send a Message, subject line dated for the week. Open with one short paragraph saying how many legs were checked and how many came back high risk. Then list the legs worst first, each as a compact entry with the traveler, flight number, route, date, and the meeting at stake, followed by one line explaining the risk in plain language and one concrete suggestion, such as moving to the earlier flight that morning, rebooking the connection with a longer layover, or shifting the meeting to the afternoon. Keep it scannable, no more than a couple of lines per leg.

Then write the result back into Airtable with Update Record on each trip row, setting the risk rating and a short reason so the whole team sees it in the table next to the booking. Only update rows you actually scored, and keep in mind Airtable allows roughly five requests per second per base, so pace the updates.

Degrade gracefully. AeroDataBox returns 403 when my plan tier does not include an endpoint, 404 for an unknown flight, and 429 when I hit quota. If a leg cannot be resolved, skip it, keep going with the rest, and still send the brief with whatever legs did resolve, listing the unresolved ones at the bottom as not checked with the reason. Never hold back the whole email because one lookup failed.