# Presentation: 520193

## The Nature of Data

- Understanding Aviation Data
- 	“Tell me and I forget.
- 		Show me and I remember.
- 			Involve me and I understand.”

## Data Analysis:  What is Involved?

- Part I - Randy
- Data Collection
- Major Aviation Datasets
- Data Terminology
- Types of Data
- Where they reside
- How they are accessed
- Part II - Anita
- How to differentiate between the types of data and when to use them
- Step through a project
- Provide various examples

- Office of Aviation Analysis

## Data Collection

- Carriers report data directly to BTS’ Office of Airline Information (OAI)
- OAI checks the data for accuracy.  Corrections are made, or the carrier is notified it must re-file.
- OAI puts ‘clean’ data into:
- mainframe file formats we access for analytical needs
- their own massaged products for public use

- Office of Aviation Analysis

## Major Datasets

- O&D Survey
- Fare Increments
- T-100
- Market/Segment
- T-1, 2, 3
- Form 41
- 298-C
- Schedule Data

- Key: 	DBP-Database Products

- DBP, Mainframe

- DBP, Mainframe

- DBP, Mainframe

- DBP, Mainframe

- Mainframe

- Official Airline Guide (OAG)

- Office of Aviation Analysis

## O&D Dataset

## O&D Survey – aka DB1a or DB1b

- Quarterly 10% ticket sample
- Carriers report full itinerary of all tickets with TCN ending in 0
- Who reports?
- Reported by domestic carriers that fly aircraft with more than 60 seats
- Reported by foreign carriers in immunized alliances
- Confidentiality
- Domestic-only itineraries not protected.
- Records including a foreign point are released only to reporting carriers or to public in very summary format
- Foreign carrier submissions strictly confidential

- Office of Aviation Analysis

## O&D - Content

- Only source of fare data
- Fare reported is gross amount paid for entire itinerary...including taxes and passenger facility charges (PFC’s)
- Only source of true origin & destination
- Contains coupon-level detail including operated and ticketed carriers
- Each coupon = T100Market enplaned passenger

- Office of Aviation Analysis

## O&D Example

- John flies roundtrip from BOS to LAX with a connection each way in DEN:
- OAI puts a trip-break indicator in the itinerary so that we can determine true O&Ds within the record.
- John flew one directional trip from BOS – LAX for $250
- He then flew a directional trip from LAX – BOS for $250 for a total roundtrip cost of $500.
- We count John as 2 ‘market’ O&D passengers between BOS and LAX.  Someone flying one-way would only count once
- Because he connected to a flight with a different number, we know that John flew through DEN
- We usually prorate revenue on mileage

**BOS**

**DEN**

**LAX**

**DEN**

**BOS**

- $250

- $250

- (X)

- AA754

- AA236

- AA544

- AA956

- Office of Aviation Analysis

## O&D Example 2

- John flies roundtrip from BOS to LAX on a one-stop flight through DEN:
- OAI puts a trip-break indicator in the itinerary so that we can determine true O&Ds within the record.
- John flew one directional trip from BOS – LAX for $250
- He then flew a directional trip from LAX – BOS for $250, for a total cost of $500 roundtrip.
- We count John as 2 ‘market’ O&D passengers between BOS and LAX.  Someone flying one-way would only count once
- Because his flight number didn’t change in DEN, we have no info on his stop
- We usually prorate revenue on mileage

**BOS**

**LAX**

**BOS**

- (X)

- $250

- $250

- AA256

- AA256

- Office of Aviation Analysis

## T-100 Segment Dataset

## T100 Segment aka DB28DS and DB28IS

- 100% monthly reporting of nonstop segments
- Domestic and Intl segments are reported into separate databases, OAI combines into “T100Segment”
- Who reports?
- Reported by domestic carriers that fly aircraft with more than 60 seats
- Reported by foreign carriers for segments touching the US
- Confidentiality
- Domestic segments: none
- International-domestic segments: 6 months

- Office of Aviation Analysis

## T-100 Segment Data (continued)

- Supply of seats, equipment type, onboard passengers, freight and mail, number of departures, segment distance, freight capacity, block time and airborne time in Non-Stop markets.
- Seats are only available in Non-Stop Segments and do not relate to O&D markets.
- Expenses are incurred on Non-Stop Segments, whereas Revenues are generated by O&D’s.
- RPM’s, ASM’s and Load Factor are calculated.

- Office of Aviation Analysis

## T-100 Segment Data (continued)

- Contains the following data elements:
**Scheduled Departures:** Total Trips performed from Originating Airport to Destination Airports
**Onboard Passengers:** Passengers transported in the Non-Stop market from Originating Airport to U.S. Destination Airports.
**Seats Available:** Number of Seats Available in the Non-Stop Market from the Originating Airport to All U.S. Destination Airports.
**ASM:** Seats Available (multiplied by) Non-Stop Distance for the market.

- Office of Aviation Analysis

## T-100 Segment Data (continued)

**RPM:** Onboard Passengers (multiplied by) Non-Stop Distance for the market.
**Load Factor:** Ratio of RPM’s to ASM’s stated as a percentage
**Ave. Stage Length:** Total Aircraft Miles traveled (divided by) Number of Operations
**Block Hours:** Block to Block Time stated in Hours in the Non-Stop Market between the Originating Airport and the Destination Airport.
**Tons Available:** Passenger and Cargo Capacity of Operations in the Non-Stop Market between the Originating Airport and the Destination Airport.

- Office of Aviation Analysis

## T100 Segment Examples

- Example 1: John flies roundtrip from BOS to LAX, with a connection in DEN both ways.  John shows up four times in the T100Segment: BOS-DEN; DEN-LAX; LAX-DEN; DEN-BOS
- Example 2:  John flies the same itinerary, but instead of connecting he stops in DEN on a one-stop flight.  He shows up in the T100Segment the same way as above.
- A physical takeoff and landing comprise a segment.  A passenger on this segment is an ‘onboard’ passenger.

- 1

- 2

- 3

- 4

- 1

- 2

- 3

- 4

- Office of Aviation Analysis

## T-100 Market Dataset

## T100 Market aka DB28DM and DB28IM

- 100% monthly reporting of on-flight data
- Domestic and Intl on-flight data are reported into separate databases, OAI combines into “T100Market”
- Who reports?
- Reported by domestic carriers that fly aircraft with more than 60 seats
- Reported by foreign carriers for segments touching the US
- Confidentiality
- Domestic data: none
- International-domestic data: 6 months

- Office of Aviation Analysis

## T-100 Market Data (continued)

- On Flight Origin and Destination Data.
- Destination Airport is the airport that was either the ultimate destination or the airport at which there was a change of flight number (online connect point).
- The Two Airports constituting the market pair reported is different than the airport pair reported in the T-100 Segment data, which are Non-Stop segments.

- Office of Aviation Analysis

## T-100 Market Data (continued)

- Contains the following data elements:
**Enplaned Passengers:** Passengers Enplaned at the Originating Airport continuing to the On-flight Destination.
**Enplaned Freight:** Pounds of Freight Enplaned at the Originating Airport continuing to the On-Flight Destination.
**Enplaned Mail:** Pounds of Mail Enplaned at the Originating Airport continuing to the On-Flight Destination.

- Office of Aviation Analysis

## T100 Market Examples

- Example 1: John flies roundtrip from BOS to LAX, with a connection in DEN both ways.  John shows up four times in the T100Market: BOS-DEN; DEN-LAX; LAX-DEN; DEN-BOS
- Example 2:  John flies the same itinerary, but instead of connecting he stops in DEN on a one-stop flight.  He shows up in the T100Market twice:  BOS-LAX and LAX-BOS.
- A physical landing between two segments is ignored if the flight number doesn’t change. A passenger in the T100Market is an ‘enplaned’ passenger.

- 1

- 2

**BOS**

**DEN**

**LAX**

- 1

- 2

- 3

- 4

- Office of Aviation Analysis

## T-1, 2, 3 Dataset

## T-1,2,3 Data

- Quarterly summaries of T100 and Form 41 data created by OAI
- T1: Data summarized by carrier
- T2: Data summarized by carrier and aircraft type
- T3: Data summarized by carrier, aircraft type and airport
- Used by FAA for basis of hub classification (when combined with 298-C)

- Office of Aviation Analysis

## T-1,2,3 Data (continued)

- Scheduled Services
**Departures Scheduled:** Departures which are “Scheduled” to be operated from the airport.
**Scheduled Departures System:** Total Departures Performed in Scheduled Revenue Service from the Airport to All Destination including Int’l Destinations.
**Enplaned Passengers System:** Passengers Enplaned at the Originating Airport continuing to all Destination including Int’l Destinations.

- Office of Aviation Analysis

## T-1,2,3 Data (continued)

- Scheduled Services (continued)
**Enplaned Freight System:** Tons of Freight Enplaned at the Originating Airport continuing to All Destination including Int’l Destinations.
**Enplaned Mail System:** Tons of Mail Enplaned at the Originating Airport continuing to All Destination including Int’l Destinations.

- Office of Aviation Analysis

## T-1,2,3 Data (continued)

- Non-Scheduled Service
**Non-Scheduled Departures System:** Total Departures Performed in Non-Scheduled Service from the Airport to All Destination including Int’l Destinations.
**Non-Scheduled Enplaned Passengers System:** Passengers Enplaned at the Originating Airport continuing to All Destination including Int’l Destinations.

- Office of Aviation Analysis

## T-1,2,3 Data (continued)

- Non-Scheduled Service (continued)
**Non-Scheduled Enplaned Freight System:** Tons of Freight Enplaned at the Originating Airport continuing to All Destination including Int’l Destinations.
**Non-Scheduled Enplaned Mail System:** Tons of Mail Enplaned at the Originating Airport continuing to All Destination including Int’l Destinations.

- Office of Aviation Analysis

## Form 41 Dataset

## Form 41

- 100% reporting of financial data.
- Group III reported quarterly by carriers whose operating revenues are greater than $1 billion
- Group II reported quarterly by carriers whose operating revenues are greater than $100 million
- Group I reported quarterly by carriers whose operating revenues are greater than $20 million
- Group I reported semi-annually by carriers whose operating revenues are less than $20 million
- Who reports?
- Reported by domestic carriers that fly aircraft with more than 60 seats
- Confidentiality
- Select schedules (for example, B-43)

- Office of Aviation Analysis

## Form 41 (continued)

- Data includes financial statements of all major, national, and large regional airlines which report to the USDOT pursuant to CFR Part 241.
- These include Balance Sheets, Income Statements, Operating Costs by Equipment Type, and Summary Operating Statistics by Equipment.

- Office of Aviation Analysis

## 298C Dataset

## 298C

- 100% Quarterly reporting of scheduled passenger service
- Traffic statistics for scheduled passenger operations.
- Revenue traffic by online Origin and Destination.
- Financial data for the overall or system operations.
- Aircraft operating expenses and related statistics.
- Non-scheduled passenger enplanements.
- Who reports?
- Reported by domestic carriers that fly directly or indirectly aircraft with 60 seats or less.
- Reported by U.S. Commuter Air Carrier and Small Certificated Air Carriers.
- Confidentiality
- Domestic segments: none
- Financial data: 3 years

- Office of Aviation Analysis

## Schedule Data (OAG) 

## Official Airline Guide

- OAG Flight Guide Worldwide is a comprehensive reference guide printed monthly containing over 550,000 direct and connecting flight schedules worldwide.
- OAG Flight Guide North America printed monthly has over 220,000 direct and connecting flights covering nearly 7,200 cities in North America.

- Office of Aviation Analysis

## Where is Data Located?

**Database **
**Products**

**DOT**
**Mainframe**

**OAG**

- DOT Server
- Accessed via User ID
- Using Cobol
- Downloaded onto PC
- Access
- Excel

- Set of Diskettes
- O&D Plus
- HUB
- International O&D
- Gateway
- Superset
- Onboard
- Form 41

- Set of Guides
- Published Monthly
- City to City Scheduled Service
- Daily Scheduled Flights
- Frequency
- Leave/Arrive Time
- Airline Flight Number
- Distance

- Office of Aviation Analysis

## Database Products

- Provides O&D Plus, Onboard (T-100 Segment and Market Monthly, T-3, and T-100 Market Quarterly Data), International, Form41, HUB, Gateway and Onboard Int’l data.
- O&D Plus, T-100 and T-3 are considered Detail Databases.
- Onboard and T-100 Market Quarterly are considered Summary Databases.
- Onboard combines T-100 Market and Segment data and are summed quarterly.
- T-100 Market Quarterly is a quarterly summary of the T-100 Market.

- Office of Aviation Analysis

## Database Products (continued)

- Combined Airport Databases are also available as T3/T100 Combined.
- Includes T3 and T-100 Domestic by Airport and Carrier, as well as, Summary 298C (data reported by small carriers and commuters) and INS.

- Office of Aviation Analysis

## Cautions When Interpreting Database Product Data

- Beware of “cross-posted data”--when each passenger trip shows up in a database twice, once as outbound and once again as inbound.
- Outbound data represents Originating Enplanements.  Does not necessarily represent original points of departure.
- Inbound data represents Destination Deplanements.  Does not necessarily represent original points of departure.

- Office of Aviation Analysis

## Mainframe Data

- Old System: Accessed via an account and Cobol queries
- Anita has downloaded various sets of O&D data that will allow for more efficient or faster access to data.
- available via Microsoft Access or Excel
- We are in the process of transitioning to a new system which BTS is handling.
- *New System: ITDBNet, should be ready for use in late February 2002.

- *Note: A training session will be scheduled to discuss the new system in further detail.

- Office of Aviation Analysis

## Official Airline Guide

- Are available on a monthly basis and is located in the Office of Aviation Analysis, room 6417.
- Includes segment flight schedules of over 800 air carriers worldwide.
- Daily scheduled flights include daily, weekly and monthly frequency of flights, flight leave and arrival times, carrier and flight number, as well as flight distance.

- Office of Aviation Analysis

## OAG (continued)

- Origin and destinations are for city to city or airport to airport.
- Effective and discontinue dates for flights operating on a temporary basis are available.
- Equipment type, meal codes, stopovers, and fuel ports are also included.
- Carrier certification is identified, as are interchanges either to city or carrier.
- Total seats are also available.

- Office of Aviation Analysis

## Data Terminology

- Onboard
- Enplaned
- Market
- Segment
- Scheduled
- ASM/RTM
- RPM
- Load Factor
- Average Stage Length
- Yield
- Routes

- Fares/Fare Increments
- Off-Line/On-Line
- Origin and Destination
- Ten Percent O&D Survey
- T-100/T-3/Form 41
- Coupon
- Non-Stop
- Direct
- Connecting
- Change of Gauge
- Gateways

- Office of Aviation Analysis

## Service Types

**Non-Stop Service:** The airplane which takes off at your origin point does not land until it reaches your destination point.
**Connecting Service:** You must change aircraft between your origin and your destination.
**Off-Line Connection:** Not only do you need to change aircraft, but you must also change airlines.
**On-Line Connection:** You must change airplanes, but you remain on the same airline.
**Direct Service:** Other than connect service, may be Non-Stop or One-Stop or Multiple-Stops, but does not require changing aircraft.
**Change of Gauge:** You will change airplanes, but since the flight number does not change, it is not considered “connect” service.

- Office of Aviation Analysis

## Passenger Types

- Examples of Non-Stop Flights.
- John lives in LA, works for Disney and is going to Orlando on business
- (has 2-stop Direct Service-stops at DFW and MSY)
- Paul is from Chicago and is going to the Superbowl in New Orleans
- (has Off-line Connect service-UA to AA at DFW)
- George is from Dallas and is going to the Superbowl in New Orleans
- (has Non-stop Service)
- Ringo is from Dallas and is going to visit Disney World in Orland
- (has One-stop service-a Stop at MSY)

- Source: Database Products Training Manual

- Office of Aviation Analysis

## Explanation of Passenger Types

- John-LAX-MCO is a “**Through**” passenger at Dallas.  He did not need to “Enplane” at Dallas because the airplane was continuing on to his destination.
- Enplanes LAX and Deplanes at MCO.

- Source: Database Products Training Manual

- LAX

- DFW

- MSY

- MCO

- AA

- AA

- AA

- Office of Aviation Analysis

## Explanation of Passenger Types

- Paul-ORD-MSY is an “**Off-line Connecting**” passenger changing carriers and airplanes at Dallas.  He is counted as an “Enplaned” passenger at DFW.
- Enplanes ORD, Deplanes at DFW, Enplanes
-      DFW and Deplanes at MSY.

- Source: Database Products Training Manual

- ORD

- DFW

- MSY

- UA

- AA

- Office of Aviation Analysis

## Explanation of Passenger Types

- George-DFW-MSY is an “**Originating Enplaned Passenger**” at Dallas as opposed to Paul who was a “Connecting Enplanement”
- Enplanes DFW and Deplanes at MSY

- DFW

- MSY

- AA

- Source: Database Products Training Manual

- Office of Aviation Analysis

## Explanation of Passenger Types

- Ringo-DFW-MCO is an “Originating Enplaned Passenger” at Dallas and will be a “**Beyond Passenger**” at New Orleans since he will not need to change airplanes.
- Enplanes DFW and Deplanes at MCO.

- Source: Database Products Training Manual

- DFW

- MSY

- MCO

- AA

- AA

- Office of Aviation Analysis

## To Better Understand the Data:

- Be familiar with airport, carrier and market codes.
- Know the state of the market and status of carriers you are dealing with.
- Differentiate between low fare, regional, national and major carriers, understand codeshare and alliances
- Be familiar with the hub and spoke system and know which carriers dominate which hubs or airports.
- Know which airports are majors and which are alternates.
- Be aware of carrier strategies--pricing, marketing, and other business practices.

- Office of Aviation Analysis

## End of Part I Training

- Office of Aviation Analysis

## Part II Data Training

- Examples of Various Projects