Schedule Change Analysis Companion Guide

Document Overview

This guide provides the analytical specification, parameter definitions, calculation rules, and natural language prompt templates for conducting flight schedule change comparisons. It supports weekly operational audits, network scenario planning, and what-if evaluation across both published industry feeds (SSIM) and private/draft schedule datasets over a forward horizon of up to 46 weeks.

1. Natural Language Prompts

Mode A: Published vs. Published (Weekly Industry Benchmark)

PROMPT

Compare published schedule changes between the two most recent weekly schedule datasets (Current vs. Previous) for station MLA across Both Directions for All Carriers. Filter for passenger scheduled services (service_type = 'J') and own-metal operating flights only (exclude codeshares). Run the analysis for 46 weeks starting from current week Monday. Display top net frequency increases, top reductions/cancellations, total station net weekly flight delta, and export the complete 46-week frequency delta matrix to CSV.

Mode B: Published vs. Private (Market Baseline vs. Draft Scenario)

PROMPT

Compare schedule changes between the baseline published schedule ssim_published_2025w24 and private scenario dataset scen_s26_draft_v3 for station LHR across Departures for carrier BA. Include only passenger scheduled services (service_type = 'J') and own-metal operating flights. Evaluate for 26 weeks starting from current week Monday. Summarize top route additions and reductions, and export the pivoted weekly frequency delta matrix to CSV.

Mode C: Private vs. Private (Scenario A vs. Scenario B / What-If Analysis)

PROMPT

Compare schedule frequency deltas between private scenario scen_w25_optimistic_v2 (Target) and scen_w25_baseline_v1 (Reference) for station DFW on Departures for AA. Filter for passenger scheduled flights (service_type = 'J'), own-metal operations only. Compare over a 26-week horizon starting from current week Monday. Output top 5 frequency additions, top 5 cuts, and generate a downloadable CSV matrix showing weekly flight delta per route.

2. Dataset Modes & Comparison Scenarios

Comparison Mode	Reference Dataset (A)	Target Dataset (B)	Primary Use Case
Published to Published	Previous Weekly SSIM (ssim_published_YYYYw[WW-1])	Current Weekly SSIM (ssim_published_YYYYwWW)	Weekly airline network monitoring and competitor schedule audit
Published to Private	Latest Published SSIM Snapshot	Internal Draft / Scenario Schedule	Evaluating impact of proposed network changes against market baseline
Private to Private	Scenario / Baseline Draft A	Scenario / Revised Draft B	Tradeoff evaluation between operational, commercial, or seasonal scenarios

3. Analysis Parameters & Filters

Parameter	Type	Required	Description / Rules
Target Station	String	Yes	Airport IATA/ICAO code (e.g., MLA, DFW, LHR).
Direction	Enum	Yes	Departures, Arrivals, or Both Directions.
Carrier Scope	List / String	Yes	All Carriers or specific IATA carrier codes (e.g., KM, LH, FR, BA).
Service Type	Char	Yes	Restricted to passenger scheduled services (service_type = 'J').
Operating Filter	Boolean	Yes	Own-metal operating flights only (operating_airline_disclosure IS NULL / exclude marketing records).
Time Horizon	Integer	Yes	Range from current week Monday up to a maximum of 46 weeks appearing in both datasets.

4. Calculation & Aggregation Logic

┌────────────────────────────────────────────────────────────────────────┐
│ Schedule Delta Pipeline │
│ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Reference Dataset A │ │ Target Dataset B │ │
│ └────────────┬─────────────┘ └────────────┬─────────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Calendar Expansion │ │ Calendar Expansion │ │
│ │ - operation_from/to │ │ - operation_from/to │ │
│ │ - days_operation (DOW) │ │ - days_operation (DOW) │ │
│ └────────────┬─────────────┘ └────────────┬─────────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Aggregate by Week │ │ Aggregate by Week │ │
│ │ - DATE_TRUNC(flight_date│ │ - DATE_TRUNC(flight_date│ │
│ │ WEEK(MONDAY)) │ │ WEEK(MONDAY)) │ │
│ │ - Carrier & Route │ │ - Carrier & Route │ │
│ └────────────┬─────────────┘ └────────────┬─────────────┘ │
│ │ │ │
│ └─────────────────┬─────────────────┘ │
│ │ │
│ ▼ │
│ ┌───────────────────────────────────────────┐ │
│ │ Frequency Delta = Target (B) - Ref (A) │ │
│ │ +N : Added Weekly Frequency │ │
│ │ -N : Reduced / Canceled Frequency │ │
│ │ Null/Blank : No Change (0 Delta) │ │
│ └───────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────────────────┘

1. Active Record Expansion:
   • Expand each flight schedule record into concrete calendar dates matching operation_from, operation_to, and the 7-character Day-of-Week bitmask (days_operation).
2. Weekly Bucketing:
   • Map each flight date to its calendar week start date (Monday): Week Date = DATE_TRUNC(flight_date, WEEK(MONDAY))
3. Delta Calculation: Delta = Target Weekly Flight Count - Reference Weekly Flight Count
   • Positive Delta (+N): Net flight additions in Target.
   • Negative Delta (-N): Net flight reductions or cancellations in Target.
   • Zero Delta (0): Displayed as blank/NULL for matrix clarity.

5. Output Specification & Deliverables

A. Summary Highlights (Chat Output)

• Top 5 Net Frequency Increases: Carrier, route, and cumulative net frequency additions.
• Top 5 Net Reductions / Cancellations: Carrier, route, and cumulative flight reductions.
• Station Aggregate Delta: Total net weekly flight volume variance across the target station.

B. Matrix Pivot Table (CSV Export)

• Row Dimensions: Operating Carrier, Route (e.g., MLA-FCO, LHR-JFK).
• Column Dimensions: Calendar Weeks (YYYY-MM-DD for each Monday, up to 46 columns).
• Cell Values: +N, -N, or [Blank] (when delta is 0).

Sample Matrix Layout

Operating Carrier	Route	2025-06-02	2025-06-09	2025-06-16	2025-06-23	2025-06-30
KM	MLA-FCO	+2	+2	+2	+2	+2
KM	MLA-LGW			-1	-1	-1
FR	MLA-BGY	+4	+4	+4	+4	+4
LH	MLA-FRA	-1	-1
BA	LHR-JFK	+7	+7	+7	+7	+7

6. Export Options

• Interactive Markdown Tables: Rendered directly in conversation for immediate review.
• CSV Artifact Generation: Full multi-week pivoted dataset downloadable as a standard .csv file.