Two-Way Hub Seasonal Frequency Profile Analysis

1. Business Objective & Use Cases

Airline capacity is highly dynamic across an IATA season. Airlines launch summer-only leisure routes, ramp up holiday frequencies, pause low-season winter services, or operate asymmetric weekend positioning flights.

The Two-Way Hub Seasonal Frequency Profile Analysis measures how airline flight frequencies evolve week-by-week across a future time horizon (up to 52 weeks) relative to a selected baseline week within a single schedule publication.

flowchart LR
    A[Schedule Publication] --> B[Base Week F_base]
    A --> C[Future Weeks 1 to N]
    B & C --> D[Compute Weekly Delta: F_i - F_base]
    D --> E[Pivoted Weekly Matrix +N / -N]
    D --> F[Identify Route Launches & Terminations]

Target Audience

• Airport Commercial Teams: Forecast seasonal runway movements, peak terminal passenger flows, and seasonal route starts/ends. Airline Network Strategists: Benchmark competitor seasonal capacity ramps and identify market voids left by seasonal suspensions. Slot Coordinators & Ground Handlers: Anticipate seasonal staffing requirements based on two-way inbound/outbound flight trajectories.

2. Executable Natural Language Prompt

Copy and paste the prompt below into the analytics chat bar. Customize the hub airport, carrier scope, and analysis horizon:

PROMPT

Run a two-way seasonal frequency analysis (Departures and Arrivals) for airport [Hub, e.g., MLA, FCO, LHR, DFW] using the latest published schedule. 1. Scope & Horizon: - Carriers: [All Carriers / Specific Carriers, e.g., AF, BA, FR, LH, KM, U2, W4]. - Service Type: Scheduled passenger services only (own-metal and wet lease, excluding codeshares). - Timeframe: Set the base week to [Base Date: YYYY-MM-DD, e.g., 2026-09-14] and analyze weekly frequency changes for the next [N, e.g., 26 or 45] weeks. 2. Calculation & Delta Logic: - Calculate weekly flight frequency for the base week and each subsequent week by Direction (Departure / Arrival), Paired Airport, and Carrier. - Compute weekly frequency deltas relative to the base week (Week_i - Base_Week). - Format increases as '+N', decreases as '-N', and leave zero changes or unserved weeks blank. 3. Output & Delivery: - Generate a pivoted matrix with Direction, Paired Airport, and Carrier as rows, and Calendar Weeks (Monday dates) as columns. - Display an executive summary and top frequency shifts in chat, and export the full pivoted dataset to CSV.

3. How the Platform Works Under the Hood

The analytics engine applies pre-scan pruning, expands active operating records across discrete calendar weeks, and constructs an SQL PIVOT table.

Technical Invariants & SQL Logic

Step	Operation	Technical Invariant
1. Fast Pre-Scan Pruning	Table Scan Filter	Restricts table scan before date expansion: (departure_station = [Hub] OR arrival_station = [Hub]) AND operation_to >= [Base Date] AND operation_from <= [Horizon End Date]. Prevents query timeouts on large datasets.
2. Direction Tagging	Direction Labeling	Outbound: departure_station = [Hub] -> Direction = Departure, Paired_Airport = arrival_station. Inbound: arrival_station = [Hub] -> Direction = Arrival, Paired_Airport = departure_station.
3. Operating Metal Scope	Codeshare Exclusion	operating_airline_disclosure IS NULL OR operating_airline_disclosure = 'X'.
4. Calendar Week Grouping	DATE_TRUNC	Truncates each active flight date to the week's starting Monday: DATE_TRUNC(flight_date, WEEK(MONDAY)).
5. Intra-Schedule Delta	Baseline Comparison	Delta equals Frequency(Week_i) - Frequency(Base Week). Formatted with explicit +, -, or NULL values.

4. Step-by-Step Customization Recipes

Recipe A: Low-Cost Competitor Benchmark at a Hub To track low-cost carrier capacity evolution (e.g., Ryanair, Wizz Air, EasyJet at Rome Fiumicino):

Run a two-way seasonal frequency analysis at airport FCO for carriers FR, W4, and U2 for 30 weeks starting from 2026-10-05.

Recipe B: Departures-Only Outbound Capacity View To track strictly outbound departing capacity:

Run a seasonal frequency analysis for departures only from hub airport LHR for all carriers across the next 26 weeks starting from 2026-11-02.

Recipe C: Summer-to-Winter Season Transition Audit To monitor how routes wind down during the October season transition:

Run a two-way seasonal frequency analysis at airport MLA for carrier KM across 16 weeks starting from 2026-09-07 to track the summer-to-winter schedule changeover.

5. Expected Output & Interpretation

The engine delivers an executive summary and a pivoted weekly matrix:

1. Pivoted Weekly Frequency Matrix (Sample CSV Output)

Direction	Paired Airport	Carrier	Base Week (Sep 14)	Sep 21	Sep 28	Oct 05	Oct 12	Oct 19	Oct 26 (W26 Start)	Nov 02
Departure	FRA	LH	21						-7	-7
Arrival	FRA	LH	21						-7	-7
Departure	PMI	FR	7				-3	-3	-7 (Suspended)
Arrival	PMI	FR	7				-3	-3	-7 (Suspended)
Departure	HEL	AY	0						+4 (New Launch)	+4
Arrival	HEL	AY	0						+4 (New Launch)	+4

2. Executive Summary Lifecycle Classification

• Service Launches (New Winter Services):
  • HEL (Finnair AY): Ramps up from 0 weekly flights in the base week to 4 weekly round-trips starting October 26.
• Seasonal Suspensions (Summer Leisure End):
  • PMI (Ryanair FR): Drops from 7 weekly round-trips in September to 0 flights at the end of October.
• Trunk Frequency Adjustments:
  • FRA (Lufthansa LH): Reduces daily flights from 3x daily (21 weekly) to 2x daily (14 weekly) for the winter season.
• Directional Asymmetry Detection:
  • Automatically highlights any week where outbound frequency does not match inbound frequency (e.g., 4 departures vs. 3 arrivals during holiday repositioning weeks).

6. Big Data Handling & Pivot Delivery

A full two-way hub analysis across 45 weeks typically spans 50 to 150 unique routes, generating a wide 48-column matrix.

tip

Automatic CSV Export To keep the chat response clean and easy to read, the platform displays the Executive Summary and Top 10 Shifts in chat, while automatically saving and providing a direct download link to the complete pivoted 45-week .csv matrix.