Comprehensive Schedule & Seat Capacity Shift Analysis

1. Business Objective & Use Cases

When airlines file schedule revisions, capacity changes occur in two distinct ways:

1. Frequency Shifts: Adding or removing physical flights on a route.
2. Gauge Shifts: Keeping the same flight frequency but swapping aircraft models (e.g., replacing an A320 with an A321neo) or reconfiguring cabin layouts.

The Comprehensive Capacity Shift Analysis tracks both dimensions by comparing the two latest weekly schedule publications (Current vs. Previous). It decomposes schedule changes into three specialized reports: Carrier Capacity, Route O&D Seats, and Pure Gauge Substitutions.

Target Audience

• Airport Route Development Teams: Track net weekly seat supply and airline capacity shifts across all terminal operations.
• Airline Commercial & Revenue Teams: Benchmark competitor capacity expansions, equipment upgauging, and market withdrawals.
• Fleet Planning & Network Strategists: Monitor aircraft type allocation and gauge changes across key competitor markets.

---

2. Executable Natural Language Prompt

Copy and paste the prompt below into the analytics chat bar. Customize the airport code and target calendar week:

PROMPT

Compare the current and previous weekly published schedules for airport [Airport, e.g., FCO] for the week of [Start Date, e.g., 2026-09-14] to [End Date, e.g., 2026-09-20]. Include both inbound and outbound passenger flights operated on own metal or wet lease (excluding marketing codeshares). Please generate all three capacity reports: 1. Carrier-Level Capacity Shift: Total weekly flights, total weekly seats, and net changes by airline, with a bolded grand total. 2. Market-Level (O&D) Shifts: Weekly flight and seat changes for every route with frequency additions or reductions. 3. Aircraft Gauge Substitutions: All routes where flight frequency was unchanged but aircraft type or seat configurations shifted (showing upgauging or downgauging impact). Include an overall capacity KPI table and an executive summary explaining the commercial drivers behind the shifts.

3. How the Platform Works Under the Hood

The analytics engine performs a multi-stage BigQuery comparison that joins both published schedule datasets on route, carrier, and calendar day.

flowchart TD
    A[Current Schedule ssim_3] --> C[Expand Calendar Dates & Extract Seat Config]
    B[Previous Schedule ssim_3] --> D[Expand Calendar Dates & Extract Seat Config]
    C --> E[Full Outer Join on Carrier, Direction & Market]
    D --> E
    E --> F[Report 1: Carrier Capacity & Rollup Totals]
    E --> G[Report 2: Market O&D Seat & Flight Deltas]
    E --> H[Report 3: Gauge Shift Isolation ΔFlights = 0]

Technical Invariants & SQL Logic

Dimension	Source Field / Expression	Logic Description
Schedule Datasets	Dataset Resolution	Compares the latest dataset ssim_published_YYYYwWW against the preceding weekly dataset.
Operating Metal Filter	operating_airline_disclosure	Filter: operating_airline_disclosure IS NULL OR operating_airline_disclosure = 'X'. Excludes virtual marketing codeshares (L, S, Z) to prevent double-counting seats.
Calendar Expansion	GENERATE_DATE_ARRAY	Expands operation_from to operation_to matching the 7-character day mask days_operation for the target week.
Seat Resolution	aircraft_configuration_version	Parses cabin seat numbers from the configuration string (for example, C12Y150 = 162 seats). Applies validated aircraft type defaults when unconfigured.
Gauge Shift Isolation	Filtering Condition	Identifies pairs where flight delta = 0 and seat delta ≠ 0 (or aircraft type changed) to surface hidden equipment swaps.

4. Step-by-Step Customization Recipes Recipe A: Single Airline Capacity Audit To analyze the capacity shifts of a single carrier (e.g., British Airways at LHR):

Compare the current and previous weekly schedules for carrier BA at airport LHR for the week of 2026-10-12 to 2026-10-18. Generate the 3-tier capacity shift reports. Recipe B: Departures Only (Outbound Capacity) To evaluate only outbound departing seat capacity from a target hub:

Run a capacity shift analysis at airport FRA for departures only for the week of 2026-11-02 to 2026-11-08 comparing the two latest schedule publications. Recipe C: Material Shift Threshold (> 500 Seats) To filter out minor flight shifts and focus on major capacity moves:

Run a capacity shift analysis at airport CDG for the week of 2026-09-14 to 2026-09-20. Show only markets where the net seat change is greater than +500 or less than -500 seats.

5. Expected Output & Interpretation

The engine generates three synchronized reports and an executive summary:

Report 1: Carrier-Level Capacity Shift Summary

Airline Code	Airline Name	Prev Flights	Curr Flights	Net Flights	Prev Seats	Curr Seats	Net Seats
FR	Ryanair	200	190	-10	38,580	36,690	-1,890
IB	Iberia	92	88	-4	17,451	16,467	-984
LX	SWISS	28	30	+2	5,282	5,642	+360
GRAND TOTAL	Total Station	5,922	5,900	-22	1,204,707	1,195,313	-9,394

Report 2: Market-Level (O&D) Seat & Frequency Shifts

Market	City Name	Carrier	Prev Flts	Curr Flts	Net Flts	Prev Seats	Curr Seats	Net Seats	Primary Driver
BCN	Barcelona	FR	36	32	-4	6,804	6,048	-756	Frequency Reduction (737-800)
ZRH	Zurich	LX	28	30	+2	5,282	5,642	+360	Added 1 round-trip (A220/A320)

Report 3: Aircraft Gauge & Equipment Substitution Analysis

Market	Carrier	Prev Aircraft	Curr Aircraft	Weekly Flights	Net Seats/Flt	Total Net Seats	Shift Type
IST	TK	A321 / A330	A330 / 77W	56	+2.0	+114	Upgauged Widebody Mix
SOF	FB	A220-100	A320ceo	10	+7.4	+74	Upgauged Aircraft Model
PRG	QS	737-800	A220-300	12	-6.7	-80	Downgauged Aircraft Model

6. Exporting to CSV

To share this data with stakeholders or load it into Business Intelligence dashboards (Tableau, Power BI, Excel), the platform exports the combined dataset with all calculated seat metrics directly into a .csv file upon completion.