Hub Feeder & Multi-Carrier Connection Builder Analysis
1. Business Objective & Use Cases
When an airline operates a long-haul trunk flight into a major alliance hub (e.g., Virgin Atlantic or Delta arriving into Atlanta ATL from London Heathrow LHR), the commercial success of that flight relies on how effectively passengers can connect to the outbound domestic and regional bank.
The Hub Feeder Connection Builder Analysis takes a single inbound flight and maps every valid outbound connection across a consortium of partner airlines throughout a full month or season.
flowchart LR
A[Inbound Trunk Flight: LHR -> ATL] --> B[Hub Transfer Bank at ATL]
B --> C[Outbound Domestic Feeder: ATL -> MCO / MIA / TPA]
B --> D[Outbound Transcontinental: ATL -> LAX / SFO / SEA]
B --> E[Outbound Latin America: ATL -> CUN / BOG / GRU]
Target Audience
- Alliance & Joint Venture Managers: Measure the commercial transfer value delivered to partner networks.
- Hub Network Planners: Synchronize inbound arrival times with high-value outbound domestic departure waves.
- Revenue Management & Interline Teams: Discover unserved connecting markets and missed connecting banks.
2. Executable Natural Language Prompt
Copy and paste the prompt below into the analytics chat bar. Customize the carrier, flight number, route, month, and partner list:
PROMPT3. How the Platform Works Under the Hood
This analysis combines temporal scheduling rules, partner metal resolution via SSIM DEI records, and spatial geospatial formulas in BigQuery.
Technical Invariants & SQL Logic
| Step | Operation | Technical Logic |
|---|---|---|
| 1. Inbound Isolation | Filter Inbound Leg | Matches flight_airline_designator = [Carrier], flight_number = [Number], departure_station = [Origin], arrival_station = [Hub], and operating_airline_disclosure IN (NULL, 'X'). |
| 2. Outbound Fan-Out | Outbound Candidate Join | Queries all flights departing [Hub] where the marketing carrier is in [Partner List]. |
| 3. Metal Validation (DEI 50) | SSIM 4 Join | For partner marketing records (operating_airline_disclosure IN ('L','S','Z')), joins ssim_4 on data_element_identifier = 50 to resolve physical operating metal and filter out ghost flights. |
| 4. Dual MCT & Window Guard | Time Arithmetic | Layover must be greater than or equal to the greater of the airport MCT or [X] minutes, and less than or equal to [Y] hours. |
| 5. Geospatial Circuity Filter | Haversine Formula | Circuity equals (GCD(Origin, Hub) + GCD(Hub, Final Dest)) / GCD(Origin, Final Dest) and must be less than or equal to 1.50. |
4. Step-by-Step Customization Recipes
Recipe A: SkyTeam Transatlantic Feeder Study To audit feeder connectivity from Paris into Delta's JFK hub:
Run a connectivity analysis for Air France flight AF 022 from CDG to JFK for November 2026. Outbound partners: DL, AF, KL, AM. Max layover: 3.5 hours.
Recipe B: Star Alliance Transpacific Hub Audit To audit ANA flight NH 106 landing at Los Angeles:
Run a connectivity analysis for carrier NH flight 106 from HND to LAX for October 2026. Outbound partners: UA, AC, CM. Circuity limit: 1.40. Export to CSV.
Recipe C: Pure Domestic Onward Connections To evaluate strictly domestic US feeder markets:
Run a connectivity analysis for BA 283 from LHR to LAX for October 2026 connecting only to American Airlines (AA) domestic US destinations.
5. Expected Output & Interpretation
The analysis generates two core deliverables:
1. Connection Builder Dataset (CSV Export Sample)
| Flight Date | In Carrier | In Flt | Origin | Hub | In Arr | Out Carrier | Out Flt | Out Op Metal | Dest | Out Dep | Layover (Mins) | Connection Type | Circuity Factor |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-10-05 | DL | 123 | LHR | ATL | 14:15 | DL | 1482 | DL (Mainline) | MCO | 16:30 | 135 | ID (Int'l -> Dom) | 1.02 |
| 2026-10-05 | DL | 123 | LHR | ATL | 14:15 | DL | 5840 | 9E (Endeavor) | SAV | 16:45 | 150 | ID (Int'l -> Dom) | 1.01 |
| 2026-10-05 | DL | 123 | LHR | ATL | 14:15 | AM | 694 | AM (AeroMexico) | MEX | 17:10 | 175 | II (Int'l -> Int'l) | 1.08 |
2. Executive Summary Metrics
- Reachable Network Scope:
- Total Unique Destinations: 128 global airports reachable from LHR via ATL.
- Domestic US Feeder Markets (ID): 104 cities (81.3% of connections).
- International Onward Markets (II): 24 cities across Latin America and the Caribbean (18.7%).
- Schedule Quality & Banking Insights:
- Optimal Connect Wave: 74% of onward flights depart between 90 and 150 minutes after inbound arrival, achieving ideal bank efficiency.
- Missed Connection Opportunities: Outbound departures to West Coast trunk markets (SEA, SFO) departing at 15:10 fall 5 minutes short of international-to-domestic MCT (requires 60 minutes).
6. Big Data Scale & Row Limit Overrides
Because a major hub like Atlanta (ATL), Dallas (DFW), or Chicago (ORD) operates hundreds of connecting departures daily, expanding a single inbound flight across an entire month easily generates 3,000 to 8,000 viable connecting itineraries.
Automated Row Override The prompt automatically requests a query limit override of 10,000 rows (execute_sql_tool_override) and triggers an automatic artifact export to .csv so no connecting options are truncated.