Network Market Additions & Deletions Audit

1. Business Objective & Use Cases

Airline schedules change on a weekly basis as network planning teams adjust seasonal dates, launch new routes, trim underperforming city pairs, or expand codeshare alliances.

The Added & Dropped Markets Audit automates a delta comparison between the two latest published weekly schedule datasets (Current vs. Previous). It instantly categorizes network changes into:

1. Mainline Own-Metal: Direct capacity adjustments on the carrier's primary fleet.
2. Regional Capacity Purchase Agreements (CPA): Changes across regional feeder partners (e.g., Delta Connection, American Eagle, United Express).
3. Marketing Codeshares: Virtual route expansion or pruning across international joint venture and alliance partners.

Target Audience

• Network Planners: Track competitor market entries and exits.
• Alliance & Codeshare Managers: Verify that partner marketed routes are published or removed as contracted.
• Commercial Strategy & Revenue Management: Identify shifts in competitor capacity before ticket sales ramp up.

---

2. Executable Natural Language Prompt

Copy and paste the prompt below into the analytics chat bar. Replace [DL] with any 2-letter IATA carrier code:

PROMPT

Compare the current and previous weekly published schedules for carrier [Carrier, e.g., DL]. Identify all added and dropped markets across their full published network. Group the results into three categories: 1. Mainline Own-Metal 2. Regional CPA Partners 3. International Codeshare Partners For each market, show the origin airport, destination airport, operating carrier/partner name, and commercial role. Include an executive summary table showing the total added, dropped, and net change for each operating category.

3. How the Platform Works Under the Hood

When this prompt runs, the analytics engine executes a multi-table BigQuery comparison using industry-standard SSIM (Standard Schedules Information Manual) business logic.

flowchart LR
    A[Schedule Publication Week N-1] --> C[Delta Query Engine]
    B[Schedule Publication Week N] --> C
    C --> D[Mainline Own-Metal]
    C --> E[Regional CPA]
    C --> F[Partner Codeshares]

Table Mappings & SSIM Invariants

Concept	Source Table	SSIM Fields & Evaluation Logic
Schedule Comparison	Dataset Resolution	Identifies the latest dataset ssim_published_YYYYwWW as Current and the second-to-last alphabetical dataset as Previous.
Mainline Own-Metal	ssim_3	flight_airline_designator = [Carrier] AND operating_airline_disclosure IS NULL.
Regional CPA / Wet Lease	ssim_3 + ssim_4	operating_airline_disclosure = 'X' (or 'L'). The true regional operator code is resolved from ssim_3.aircraft_owner, and the commercial DBA name is extracted from ssim_4 (Data Element Identifier 127).
Codeshare Partners	ssim_3 + ssim_4	operating_airline_disclosure IN ('S', 'Z', 'L'). The physical operating carrier and flight number are resolved by joining ssim_4 where data_element_identifier = 50.
Market Scope	ssim_3	Distinct directional pairs (departure_station -> arrival_station) operating in future active dates (operation_from <= operation_to).

4. Step-by-Step Customization Recipes

Encourage users to customize the base prompt for specialized reporting:

Recipe A: Restrict to a Specific Hub or Focus City To see only route additions and deletions touching a specific airport (e.g., Atlanta ATL or London Heathrow LHR):

Compare the current and previous weekly published schedules for carrier DL. Identify all added and dropped markets specifically touching hub airport ATL (departures or arrivals).

Recipe B: Mainline Own-Metal Only (Exclude Codeshares) To eliminate codeshare noise and focus purely on the airline's physical metal:

Compare the current and previous weekly schedules for carrier AA. Identify all added and dropped markets for mainline own-metal flights only, excluding all regional CPAs and codeshare partners.

Recipe C: Specific Seasonal Window To audit changes for an upcoming season (e.g., Summer 2027):

Compare the current and previous weekly schedules for carrier UA. Identify all added and dropped markets operating between 2027-03-28 and 2027-10-30.

5. Expected Output & Interpretation

The engine produces two structured deliverables:

1. Executive Summary Table Provides a high-level scorecard of net network growth or contraction:

Operating Category	Markets Added	Markets Dropped	Net Market Change
Mainline Own-Metal	0	0	0
Regional CPA Feeder	0	0	0
Codeshare Partners	11	3	+8
TOTAL NETWORK	11	3	+8

2. Detailed Market Tables (Added vs. Dropped) Lists every specific city pair with resolved partner identities and strategic functions:

Origin	Origin Airport	Dest	Destination Airport	Operator / Partner	Commercial Role
CDG	Paris Charles de Gaulle	AJA	Ajaccio (Corsica), France	Air France (AF)	European Leisure Feeder
GRU	São Paulo Guarulhos	MEA	Macaé, Brazil	LATAM Brasil (JJ/LA)	Brazilian Regional Hub Feeder

6. Common Questions & Troubleshooting

tip

Why is a market flagged as dropped if flights still operate? A market is flagged as dropped under Mainline if the carrier handed over operation of that route entirely to a Regional CPA partner (e.g., switching an A220 to an Embraer E175 operated by Republic Airways). The platform reports this as a mainline drop and a regional CPA addition.

note

How does the engine handle seasonal suspensions? If a seasonal route simply ended its scheduled summer run on its published end date, it is not flagged as dropped. A market is only flagged as dropped if future scheduled records that were present in the previous publication were deleted in the current publication.