Download User Guide

You can use this menu to navigate the User Guide:

1.     INTRODUCTION

        1.1    About the Tool
        1.2    Key Features
         1.3   The Reports
                  1.3.1     Traffic Reports (T100)
                  1.3.2     Origin & Destination Reports (DB1B)
                  1.3.3     Financial Reports (Form 41)

2.     GETTING STARTED

3.     T100 SEGMENT TRAFFIC REPORTS       
         3.1    USING THE T100 SEGMENT TRAFFIC POWERTABLE
                  3.1.1     T100 Segment Traffic Home Screen
                  3.1.2     Domestic vs International Data
                  3.1.3     Dimensions & Metrics
                  3.1.4     Choosing the Period
                  3.1.5     Types of Flights
                  3.1.6     Carrier Selection
                  3.1.7     Origin & Destination
                  3.1.8     Passengers and Freight
                  3.1.9     Equipment
                  3.1.10   Running a Report
         3.2     T100 SEGMENT TRAFFIC REPORT - WORKED EXAMPLE

4.     T100 MARKET TRAFFIC REPORTS
        4.1     USING THE T100 MARKET TRAFFIC POWERTABLE
        4.2     T100 MARKET TRAFFIC REPORT - WORKED EXAMPLE

5.     O&D TRAFFIC REPORTS
        5.1     USING THE O&D TRAFFIC REPORTS (DETAILED) POWERTABLE
        5.2     O&D TRAFFIC REPORT (DETAILED ) - WORKED EXAMPLE
        5.3     USING THE O&D TRAFFIC REPORTS (SUMMARY) POWERTABLE

6.     FINANCIAL REPORTS (FORM 41)
        6.1     USING THE FINANCIAL REPORTS POWERTABLE
        6.2     O&D TRAFFIC REPORT (SUMMARY) - WORKED EXAMPLE

7.     TRAFFIC SUMMARY REPORTS (FORM 41)
        7.1     USING THE TRAFFIC SUMMARY REPORTS POWERTABLE
        7.2     AIRLINE TRAFFIC REPORT
        7.3     T1 AIRLINE TRAFFIC - WORKED EXAMPLE
        7.4     T 2 AIRCRAFT TRAFFIC REPORT – WORLKED EXAMPLE
        7.5     T3 AIRPORT TRAFFIC REPORT – WORKED EXAMPLE               

8.     APPENDIX - DIMENSIONS, METRICS & CATEGORIES
        8.1     T100 SEGMENT TRAFFIC REPORTS – DIMENSIONS AND METRICS
        8.2     T100 MARKET TRAFFIC REPORTS – DIMENSIONS AND METRICS
        8.3     O&D TRAFFIC REPORTS (DETAILED) – DIMENSIONS AND METRICS
        8.4     O&D TRAFFIC REPORTS (SUMMARY) – DIMENSIONS AND METRICS
        8.5     FINANCE REPORTS (FORM 41) -  DIMENSIONS, METRICS & CATEGORIES
        8.6     TRAFFIC SUMMARY REPORTS (FORM 41) – DIMENSIONS AND METRICS

1       INTRODUCTION

1.1     About the Tool

The US Department of Transport (DOT) gathers valuable data on the US aviation market which is made available as three reports known as T100, Form 41 and DB1B O&D Traffic. To make it easier to extract market insights, and to manipulate data, OAG has created DOT Analyser which takes the hassle out of working with the data, so you can extract the business intelligence you need faster and more easily.

As the leading provider of business intelligence data for the aviation industry, OAG's DOT Analyser is the best, most flexible web-based tool available to navigate these key US DOT aviation datasets. DOT Analyser combines data from the T100, Form 41 and DB1B O&D Traffic reports so they are accessed via a single dashboard, and have a similar look and feel, enabling better and faster decision making. Whether you need to analyse airline trends in depth or need specific data about the financial performance of a carrier, DOT Analyser allows you to do it all with ease.

OAG DOT Analyser sits within the OAG Analyser Suite of products. Each product has been designed to have a high degree of commonality so that screens are visually comparable, and users of one will find it easy to use the other products and create outputs in similar and comparable formats where needed.

Back to top

1.2    Key Features

DOT Analyser allows you to access the latest data from each source and look back at historical trends - as far back as 1996.

• Fast and flexible with the highest level of interactivity – all the reports are available as power tables, meaning data can be extracted in the format required by the user.

• DOT Analyser allows you to create, save and schedule regular reports so that they are produced automatically, and sent to your email address for when you need them.

• Easy and intuitive to use – OAG offers a purpose designed tool that allows you to extract just the data you need and takes you straight to the analysis without the hard work of processing the data.

• Sits within OAG Analyser platform and so can be provided as an additional module in a dashboard, making the OAG Analyser suite of products your first and only port of call for air transport analysis.

Back to top

1.3    The Reports

There are six sets of reports available in DOT Analyser which provide traffic and financial data for airlines flying to and from US airports. The data can be used to analyse both operational and financial trends as a measure of an airline or airport’s performance.

All airlines that operate flights within, to or from the USA are required to report their traffic and financial performance to the Department of Transport (DOT). Including:

• Major & National Airlines
• Regional Airlines (required as of October 2002)
• Foreign Flag Airlines (required as of July 1990)
• Cargo Airlines
• Charter Airlines

The DOT obtains the performance information via three data collections referred to as T100, DB1B and Form 41. OAG’s DOT Analyser has created six power tables, two for each set of data, which make accessing and understanding the data straightforward.

1.3.1    Traffic Reports (T100)

• Recent and historical on-board passenger and cargo traffic data to, from and within the U.S.
• Includes flown passenger data and load factors for single plane flights.
• Contains traffic schedules by carrier, class and aircraft type – passengers and cargo carried, flight frequencies, seat and cargo capacity, origin and destination.
• Covers all US carrier data for US domestic segment (non-stops) and US domestic market (all carriers) data.
• Covers all carrier data for US international segment (non-stops) and US international market (all carriers) data.

Data reported monthly domestic flights reported with 3-month delay, international with 6-month delay. 

The Traffic Segment Reports are flown passenger data on single plane flights i.e. data for all the passengers and cargo carried between two points on a non-stop flight.

The Traffic Market Reports are local passenger data on single plane flights i.e. data for passengers and cargo flying only between the origin and destination of the flight, and not connecting at the origin or destination.

1.3.2    Origin & Destination Reports (DB1B)

The DB1B data collection refers to data collected after 1998 which the data collected prior to 1998 is called DB1A (or sometimes OD1A and OD1B).

• DB1B is a 10% Sample of an airline’s tickets, then ‘adjusted’ to estimate 100% of the market by multiplying the data by a factor of 10.
• Based on airline tickets, the data provides an estimate for total passengers and cargo transported by itinerary. The source data therefore includes the origin, destination, connecting points and US gateway for each itinerary, as well as the distance travelled.
• Analysis can be undertaken by operating, marketing and ticketing carrier.
• The data is a rich source of pricing information and includes average fares by itinerary and fare codes by segment. This means that data for Revenue Passenger Miles and Revenue Ton Miles is available.
• DB1B does not include aircraft data.
• Data is reported Quarterly.
• Non-US carriers do not have to report this data to the DOT and therefore access is restricted for non-US carriers to prevent them seeing the International data for their US airline competitors.

There are two sets of reports which use DB1B data. These are the O&D Traffic Reports (Detailed) and O&D Traffic Reports (Summary).

1.3.3    Financial Reports (Form 41)

The Form 41 data collection provides airline financial (Balance Sheet, Profit & Loss) data for US carriers. Reporting requirements are split into a number of separate ‘schedules’ or forms which are submitted:

Large Airlines with revenues >$20m

• B1 – Balance Sheet (Quarterly)
• 2 – Profit and Loss ((Quarterly)
• 1 & P5.2 – Aircraft Operating Expenses (Quarterly or Semi Annual)
• P6 and P7 – Operating Expenses (Quarterly)
• P10 – Detailed Mthly Employee Stats (Annually)
• P1(a) – Annual Employee Totals (Annually)
• P12(a) – Fuel (Monthly)

Small/Medium Airlines with revenues <$20m

• 1 – Balance Sheet (Semi Annual)
• 1 – Profit and Loss (Semi Annual)
• P10 – Detailed Mthly Employee Stats (Monthly)
• P1(a) – Annual Employee Totals (Annual)
• P12(a) – Fuel (Monthly)

DOT Analyser Financial Reports allow the user to quickly and easily extract data about financial performance for airlines.

• Full airline financial history - financial information includes balance sheets, income statements, employee counts and traffic schedules.
• Revenues from Tickets and Ancillaries.
• Costs such as Staff, Aircraft, Fuel,

In addition, the Financial Reports (Form 41) include Traffic Summary Reports which are derived from T100 data but aggregated to the point where sensitivities about data disclosure are removed. They provide a high-level view of airline (T1), aircraft (T2) and airport (T3) performance.

• Reported monthly - Domestic flights reported 3 months delayed and International flights reported 6 months delayed

2      GETTING STARTED

This guide provides all the information you need to start using OAG DOT Analyser. It has been designed to walk you through all of the steps involved with getting the data you need from DOT Analyser and can be used on its own, or in conjunction with the User Guide Videos which are accessible from the toolbar within DOT Anlayser. You can also find a Glossary of Terms and Frequently Asked Questions there.

If you have any login or account issues please contact Analyser-Support@oag.com. If you would like to give feedback on your experience of using DOT Analyser or if you have any problems using this tool please contact your account manager and they will be able to help you or pass you on to the revelant department to help. When you log into OAG Analyser you will see a screen like this which is the dashboard for the OAG Analyser suite of products.

Each of the products you have a subscription to can be accessed via the relevant box in the centre of the screen, or via the icons in the top right of the screen, and your unique subscription number is shown in the top left corner. 

To open DOT Analyser, click on Open DOT Analyser under the DOT Analyser icon. On logging into DOT Analyser, you will see this screen which is the landing page:

The look and feel of this home screen is similar to the other OAG Analyser modules. There are six reports available, under three headings of Traffic Reports (T100), Origin & Destination Reports (DB1B) and Financial reports (Form 41).

Below these reports are some ideas for new reports. Please click on these and use the questions presented to provide OAG with feedback about what would be useful in future reports for DOT Analyser.

Underneath the area for New Potential Reports, you can access the Job Bin, Saved Reports and Scheduled Reports.

There are 3 main sections in DOT Analyser. This User Guide describes how they work here and provide you with a worked example of each report in this section. You can also access short video guides from our User Guide that replicate the content provided here. From the home screen you can click on any of the report names and this will take you to a query screen where you can create a report tailored to your requirements.

Back to top

3       T100 SEGMENT TRAFFIC REPORTS

3.1      USING THE T100 SEGMENT TRAFFIC POWERTABLE

3.1.1  T100 Segment Traffic Home Screen

The T100 Segment Traffic Reports provide insight into the capacity and trafifc trends at an airport or on a flight. Segment data covers all traffic on a flight, and does not take account of where passengers or freight originated from or is destined for, but simply what is being carried between the two airports for a flight.

To start using the T100 Segment Traffic Reports click on Segment Traffic Reports on the home screen. You should click through to a screen which appears like this.

If you already use the other OAG Analyser modules this will be familiar to you. It is a power table in which you create the report you need using the various Dimensions, Metrics and Parameters available to you.

The central area of the screen is where you select these, and the area to the right summarises your Current Report Selections. On the left is a list of short cuts to the various types of parameters available to you.

Back to top

3.1.2    Domestic vs International Data

The US Department of Transport requires airlines to report data for all domestic and international flights. At the top of the screen you can choose whether you need data from the Domestic data, the International data, or both.

At the top right of the screen, there is a Data Overview box. Clicking on that opens a pop up which shows what data is included in each T100 report, and what the latest report period is.

3.1.3    Dimensions & Metrics

The Dimensions and Metrics define the rows and columns of your data, and you can select as many as you need from the options provided. With each selection you make, the Current Report Selections adds the selection to the blue area on the right.

Each of the Dimensions and Metrics are grouped into categories and clicking on the arrow to the left of each opens up a list of options associated with that category. A full list of the options is available in the APPENDIX.

Back to top

3.1.4    Choosing the Period

For each report you must select a time period for which you want to extract data. If you leave this blank, then the report defaults to all the data contained in DOT Analyser. There are options to see data by month, quarter or year and in time series or aggregated into a single number for the time period selected.

There is a time lag between flights taking place and when the DOT makes data available, but you can see the most recent data available listed below the data options. A full list of the most recent data available for each of the DOT Analyser reports is available in the Data Overview, located at the top right of the Current Report Selections area of the screen.

3.1.5   Types of Flights

There are three types of flight – Passengers (with Cargo), Cargo only or both. This area of the screen also provides options to select scheduled or non-scheduled flights and civil or military flights. The default option is for both which is the All option.

3.1.6  Carrier Selection

If you choose not to select a carrier here, data for all carriers will be included in your report. If you have not chosen Carrier as a Dimension, then the data will be aggregated for all airlines. Specifying the Carrier as a Dimension will create a report with data by Carrier.

If you want to specify a carrier, or group of carriers, there are two ways of inputting carrier names – either by 2 letter code separated by a comma i.e. AA, BA, WN, etc., or by typing in the carrier’s name which will produce a drop-down list to select from.

If you want to exclude a particular carrier, or group of carriers, you can do so. Click on Add Exclusion below the carrier box and, as before, select a single carrier, or group of carriers, in the exclude field by using their two letter code, or name.

Another way to choose a group of carriers is by clicking on the link logo at the end of the field. This opens up a new box as pictured to the right.If you want to run reports for the same group of airlines many times you can create a group and name it so that it is available to use in future.

Firstly, type in the airline two letter codes or names in the Search by Carrier field. Select each one as the name appears once you start typing and click on the green + sign to add it to the list of carriers in the group you are creating. When you have selcted all the carriers you want, type in a name for the group in the Save Group field, and then click on Save. In this example the group created is called US Big 4 and contains the four largest American airlines.

In the Carrier set of parameters, you can also choose the Domicile Country or Region for all carriers you want to see in your report.

3.1.7  Origin & Destination

As before, if you leave the Origin and Destination fields blank, data for all origins and destinations will be included. When you select an origin and a destination you are choosing to look at all the data from the origin to the destination.

Sometimes you might just want to look at all data to and from one specific airport, city, country or region. Clicking on To/From allows you to do this and you can look at everything from an origin point.

At the bottom of the query screen on the left, there is an option to choose specific O&D Pairs. When you click on it, it opens up the dialogue box shown on the left here.

You can use this box to enter specifc O&D Pairs, or you can use the bulk origin destination pairs field which lets you paste in a selection of origin and destination codes from another another source.

In the Origin and Destination field you can also choose to restrict the data you see to just one way, two way, or aggregate data for both directions by selecting your preferred option from the drop down under Direction. The distance of a flight can also be used as a filter, either from 0 to a maximum distance, or within a certain range.

3.1.8  Passengers and Freight

As airlines are required to report all flights to the DOT, there will be data included for unusual flights such as flights to a maintenance base. This area of the query screen allows you to specific minimum or maximum numbers of passengers or tons of freight to qualify for inclusion in your report and is useful if you need to exclude these anomalous flights.

3.1.9  Equipment

The final parameter which can be used to define your report is Equipment. This allows you to select flights which used specific aircraft types. The blue Link options allow groups of multiple equipment types to be included.

3.1.10 Running a Report

Once all the Dimensions, Metrics and Parameters have been selected, you are ready to run the report by clicking Run, on the left. You can also use the Export option but it is often helpful to run the report first, check that it provides the data you needed and expected, and then export the data for more detailed interrogation of the data after that.

3.2      T100 SEGMENT TRAFFIC REPORT - WORKED EXAMPLE

We’ll work through an example now to demonstrate what outputs this report can provide. In this example we will look at what has happened to air travel once air services were resumed between the United States and Cuba. We want to see what capacity is being provided on flights between the two countries since January 2016, and how it has changed since then.

We’ll select carrier name and O&D (Airport Pair) as dimensions, and then under metrics we’ll choose total passengers transported, passenger load factor and seats.

We’ll choose month as the period and select January 2016 as the starting point and then August 2017 as the end point.

Scrolling down in the main section, we don’t want to add any carriers as we want to see the whole market, so will leave that blank.

We do want to add the origin and destination data to limit the data to just US-Cuba. In Origin we click on country and enter US, and then in Destination we do the same and enter Cuba.

It’s that simple. Once we’ve done that, we can check what appears in the My Report section on the right.

The data shown in My Report at this stage is ‘dummy data’ and is illustrative of the data that the report will produce.

You could export the report straight from this page but it’s always worth running it to check that the data you are expecting is what is generated by the report. This is how the results appear.

From here you can choose to export the report to carry out your own analysis, or schedule it to run again in future or save it if you know you want to look at it or run it again.

From here you can choose to export the report to carry out your own analysis, or schedule it to run again in future or save it if you know you want to look at it or run it again.

Clicking on Export opens up the box on the left and you can name the report and choose what type of format you want to export the data in.

Once you’ve done this, another window appears prompting you to either continue or go to the Job Bin. Continuing lets you amend this query or close it and run another one.

The Job Bin is where all reports are sent to be processed and you can access it either from here, or from the DOT Analyser home page, or the menu bar on the top right of the screen. This is what it looks like:

From the Job Bin, you have the option to either view the report, which will take you back to the original query screen, download it, or delete it. When you download it and open the file in Excel, there are two tabs. One, as pictured below is a summary of the Search Criteria, and the other is the Data Export.

The Data Export is the second tab and simply contains the data we requested. We can then carry out some analysis to see how the US-Cuba market evolved over the time period we selected. We’ve done some quick analysis displayed below (and shown above as new tabs) that shows how the number of routes operated between Cuba and the US peaked in early 2017, and then declined slightly, with seat capacity following a similar profile. The data we extracted also allows us to show which airline carried most passengers and what their load factor was.

The User Guide link from DOT Analyser for this report takes you to our website where there is a short video with a worked example of how to create and extract data using the T100 Segment Reports.

These match up with the worked examples that are used here, so you can use this guide and the videos to complement each other if necessary.

Back to top

4        T100 MARKET TRAFFIC REPORTS

4.1      USING THE T100 MARKET TRAFFIC POWERTABLE

This section considers how to generate reports in the next of the T100 reports, the Market Traffic Reports. In this section descriptions of functionality that have been covered above will not be repeated.

T100 Market Traffic Reports cover traffic on single plane flights between two airports where the passengers or freight are simply travelling between those two airports. This is also sometimes known as ‘local traffic’.

As before, to start using the T100 Market Traffic Reports, click on Market Traffic Reports from the home screen. When you click through the following screen appears.

This report is also structured as a power table. There is one less Dimension in this report, as Equipment is not covered, and this time only Passengers and Freight appear in the Metrics options. All of the Dimensions and Metrics for each table are listed in the APPENDIX at the end of this document.

4.2      T100 MARKET TRAFFIC REPORT - WORKED EXAMPLE

We’ll do another worked example to demonstrate the data that this report can provide. This time we’ll look at the passenger data by airline and routes between New York and Los Angeles. We’ll choose to just look at the Domestic Report, so you can see from the screenshot below we’ve selected that. We want the results to be displayed by airline name, so we’ll click on the arrow next to carrier and then choose carrier name from the options displayed below. For this example, we want to look at data for November 2017, so we select Month (the default) in Period, and then change the date to Nov 2017 in both boxes. You can see that the selections we’ve made so far appear on the right-hand side.

Then we need to select a further dimension to display the routes. We’ll open the origin and destination arrow and scroll down the options to O&D Pair (Airport). We’ll then add a metric, in this case we want Passengers Local (Total).

In the next section, Types of Flights, we’re going to filter out military flights, so we’ll choose civilian from the drop- down box under civil/military (the default is all).

We don’t need to make any other changes to Types of Flights or Carrier, so we’ll leave those sections unchanged, but we could have chosen to include cargo flights, or restrict the data to scheduled flights only.

The last step is to select the origin and destination we want to see, in this case the origin is New York and the destination is Los Angeles. We’re going to look at these two destinations at a city level, so we click on City under Origin and then start typing New York.

We do the same for Los Angeles and then we’re ready to run our query.

Now that we’ve included everything we need in the query, we can check the Current Report Selections and then click on run.

Here’s our data – as before we can export it to carry out some further analysis in Excel, or schedule it to run again next month, or save it for future use.

Clicking on Schedule opens up the following box where we can give the report a name and description, indicating how frequently we want it to be run – either monthly or quarterly for this report - whether we want it to be static in terms of the dates, or update as new time periods are added, and then what format we need it in. Finally, we can enter an email address(es) that we want to have the report sent to.

Once you’ve scheduled a report, you can review it anytime by going to the My OAG tab at the top of the screen and selecting scheduled reports from the dropdown. This lets you see all the reports you have scheduled, and you can then view individual reports, edit them or delete them from here. You can also see when the report was last run.

We’ll go back now to the report output screen.

As before, we’ll export the data to Excel and some quick analysis allows us to see the most popular routes and the market share each carrier has of passengers between New York and Los Angeles. 

The User Guide link from DOT Analyser for this report takes you to our website where there is a short video with the same worked example of how to create and extract data using a T100 Market Report.

Back to top

5            O&D TRAFFIC REPORTS

5.1            USING THE O&D TRAFFIC REPORTS (DETAILED) POWERTABLE

The O&D Traffic Reports use DB1B data and represent a 10% sample of all passenger trips taken. There are two O&D Reports, a detailed one and a summary level one. The O&D Traffic Reports (Detailed) has a few more options than the O&D Traffic Reports (Summary), allowing you to delve deeper and see data in terms of the ticketed coupons. There’s the addition of Ticketed and Reporting carriers and a field to specify the connecting location.

The main difference is that the simplified report provides data at an Origin and Destination, or O&D level, and does not include data about how the passenger travelled between the Origin and Destination. What this means is there is no detail about the connecting airports or the itinerary in the Summary report.

DB1B data is also only collected from US carriers so there is no international data available for foreign carriers. International data for US carriers is only available to US parties.

Back to top

5.2        O&D TRAFFIC REPORT (DETAILED ) - WORKED EXAMPLE

We’ll look firstly at the detailed report. You will see from the dimensions and metrics that these look a little different to those in the Traffic reports. In addition to the standard Carrier and Origin and Destination options, we now have 3 additional dimensions. These are Ticketed Revenue, Distance and Coupons. These are shown in more detail in the boxes above.

We’ll run through a worked example here to demonstrate how best to access data from this report.

We’re going to look at how people travel between New York and Hawaii. We’ll be able to see which routings are most popular, what the average fares are by airline and how they differ according to the choice of routing.

We start by choosing the dimensions and metrics which define the structure of our report. We want to see Carrier so we’ll select Reporting Carrier Name. Then we’ll open the Origin and Destination dimensions and scroll down to Passenger Itinerary and select that. On Metrics, we want to see data for all passengers, so we’ll open the Ticketed Revenue option and select Number of Passengers (Adjusted). This is the number in the sample data which has been scaled up by a factor of 10.

It’s worth noting here that we have both the number of passengers and an adjusted number of passengers because the US DOT’s O&D data comes from a sample of the whole market. The actual data is the passenger numbers in the dataset, while the adjusted figure is the data adjusted as if it were the whole market. In this instance because the survey is a sample of 10% of all tickets it simply means that the adjusted passenger number is 10 times higher than the actual passengers in the dataset. It will usually be the adjusted number, which reflects the whole market, that you will need for analysis.

We also want to look at average fares and yields so we’ll select Total Revenue Adjusted and Average Yield.

The structure of our report is starting to take shape. Now we need to select some more parameters as if we click to run our report at this stage, we’ll get data for every airline and every route in the database and every time period. First, we’ll choose the time period we want to look at in this case, and we’ll use the default option which is the 3^rd Quarter 2017 at the tie we are working on this example. The time periods for this report are Quarters or Years.

We want to see all carriers so we don’t need to select anything here, but we could have chosen to specify the

Reporting Carrier or carriers, or the Ticketed Carrier, or the Operating Carrier. In each of these fields you can choose one, or a number of carriers, or select carriers that are domiciled in a particular country or region. You can also exclude carriers in this section by clicking on Add Exclusion which opens up a new box as shown below.

The next set of parameters are the Origin and Destination. We want the origin to be the city of New York and the destination to be the state of Hawaii. We don’t need to specify a connecting point here as we want to see all possible routings taken by passengers, but we could do if we wanted to see a specific routing. 

The last parameters in the O&D Traffic Reports (Detailed) are towards the bottom. They are Coupons, Ticketed Revenue and Cabin Type. The Coupon Count allows you to specify the maximum, minimum or exact number of coupons per ticket. This equates to the number of trip segments in a true O&D passenger trip. The Fare Class option allows you to select the different coupon fare class to be included in your search. This allows you to specify or exclude Frequent Flyer coupons. The Trip Qualifier allows you to select coupons that are either the first, last, middle or only coupon within a ticket. Users may select one or more entries in this field. You can also select operating carrier and/or ticketed carrier in this field too.

You can also filter the Adjusted number of passengers transported here using Ticketed Revenue function. This allows you to exclude data where the number of passengers/freight is very small and may not reflect a significant market size.

Lastly Cabin Type allows you to select First, Business or Economy, or use the default option which is all ticket classes.

In our worked example we won’t amend any of these. Now we can check the current report selections to make sure it has all the fields we need and, if so, click on run.

Once we click run this is what the report looks like and we can export, schedule or save it for use another time.

If we click on the small paper icon at the bottom of the data report, this allows us to download the data straight to MS Excel without going via the export function. This functionality is only available where there are less than 1,000 rows of data. The default display here is for 10 rows of data, but you can change that to show 25, 50 or 100. You can also sort each column by highest to lowest by clicking on the up and down arrows to the right of each column heading. You can also filter the results using the box next to the Number of Rows field, perhaps by carrier, or for a specific passenger itinerary.

Exporting this data to a spreadsheet would allow you to carry out some analysis on how pricing of direct routes compares with connecting routes.

The user guide link from DOT Analyser for this report takes you to our website where there is a short video with the same worked example of how to create and extract data using the O&D Market Traffic Reports (Detailed).

Back to top

5.3          USING THE O&D TRAFFIC REPORTS (SUMMARY) POWERTABLE

The other O&D reporting option is the O&D Market Traffic Reports (Summary). As we noted in the section above this report is at a summary level and does not include data about how passengers travel between an Origin and a Destination.

The main differences between the summary and detailed reports are in the Dimensions and Metric sections. The Summary report has the following dimensions and metrics.

The remaining parameters in the O&D Traffic Reports (Summary) are similar to those of the O&D Traffic Reports (Detailed), with one main difference. The coupon section just has the coupon count and fare class options.

If we were to replicate the query we ran in the previous section, choosing Airport O&D instead of itinerary (which isn’t available to us in this report), the output looks like this:

Back to top

6          FINANCIAL REPORTS (FORM 41)

6.1         USING THE FINANCIAL REPORTS POWERTABLE

The Financial Reports in DOT Analyser enable you to carry out fast and straightforward analysis of the airline financial data gathered by the US Department of Transportation via its Form 41.

The Form41 data is a key source of financial information about US airlines including balance sheets, income statements, employee counts and traffic summaries. US Federal law requires that most American passenger and cargo airlines report financial and operating information on a monthly, quarterly or semi-annual basis.

There are two options in the Financial Reports (Form 41) section, the Financial Reports and Traffic Summary Reports.

The Traffic Summary Reports are derived from T100 data but aggregated to remove sensitivities about data disclosure. They provide a high-level view of airline (T1), aircraft (T2) and airport performance (T3).

Like the Traffic & O&D reports, it has numerous options to tailor the data request to what you need, but not every box needs to be filled in every time. Often you may only need to fill in a few of the options, so it’s worth spending a bit of time familiarising yourself with the query screen.

To get to the query screen you can click through on one of the report headings. Let’s go firstly to the Financial Reports. This is where you can quickly and easily extract data about airline financial performance. There are 9 tables for Large Airlines (revenue >$20m) and 5 tables for Small/Medium Airlines (revenues <20m).

For each table, the dimensions and metrics change according to what’s in each table. The next two screenshots show how they change between the P5.2 Aircraft operating expenses table and the P12(a) Fuel table. In the APPENDIX we list each of the associated dimensions and metrics for each table, so you can refer to those if necessary.

Each table has a different reporting period, so if you open Data Overview on the right of the screen, in the My Report section, you can see at a glance what the latest reporting period is for each table.

Back to top

6.2          O&D TRAFFIC REPORT (SUMMARY) - WORKED EXAMPLE

We’ll take a look at an example now which is the best way to illustrate how to use these reports. We’re going to compare fuel use by the four largest airlines in the US, both in terms of gallons and cost, and then we can compare that with data about the miles flown for the same period from the traffic report, to get a sense of which airline is managing to keep their fuel costs lowest.

To do this we’ll start with the P12(a) report for the large airlines. This is the report that provides data about fuel use.

The dimensions and metrics for this report are quite simple. We’ll select just Carrier Name and then looking at metrics we can see there are just two, which are Gallons and Cost.

We want to see both of these in the data, so we’ll select them both.

Now we need to define the time period that we want the data to be extracted for, so we move down to time period. For this report, data is available for month, quarter, half year and year. The latest data available for this report is September 2017 which is the default month, so we’ll use that.

We could choose to split this data out into scheduled or charter airlines or by civilian or military flights, but in this case, we’ll leave the defaults as they are, as we will specify the airlines we want to consider below in the Reporting Carrier field.

We’ll enter American Airlines, United Airlines, Delta Air Lines and Southwest Airlines.

We don’t need to add anything else to this query as we have all we need. We can check what parameters have been selected by reviewing the My Report - Current Report Selection area of the screen.

We can see the dimensions and metrics at the top, followed by which table we are extracting data for, then the time period and the carriers we want to include.

If we decided we wanted to remove any of the dimensions and metrics, clicking on the red cross in each field allows us to do that.

For now, though, we’ll click run as we’re happy with the current report selection.

Here’s the data.

You can see that the gallons and cost for each airline is displayed and as before, you can export, schedule or save this query. Clicking on the paper symbol in the bottom right hand corner allows you to directly export this data, opening it up as a CSV file. It doesn’t export the parameters or report information, just the raw data and headings as shown below, but if you’re doing some quick analysis this might be all you need.

As we noted above, we could easily now run a Traffic Report to identify the miles flown for these carriers for this time period to identify which has the lowest respective fuel costs.

The User Guide link from DOT Analyser for this report takes you to our website where there is a short video with the same worked example of how to create and extract data using a Form41 Financial report.

Back to top

7.          TRAFFIC SUMMARY REPORTS (FORM 41)

7.1            USING THE TRAFFIC SUMMARY REPORTS POWERTABLE

The Traffic Summary Reports in DOT Analyser summarises the T100 data which is also available elsewhere in DOT Analyser, in the T100 Traffic Reports. This data is available back to 1981 for US domestic traffic and from 1990 for international traffic if your subscription includes that. You may ask why this is useful given that this data can be accessed in the Traffic Reports section. The answer is partly because the data here is summarised, or aggregated, and so sensitive data that was not available to all users can now be shown in the summarised form.

For example, data reported for international flights is included here where it is not available in the T100 data for non-US carriers which are not permitted to access the international data in T100.

If we click through to the Traffic Summary Reports from the DOT Analyser Home screen this is what we see.

There are three Traffic Summary tables.

• The first is the T1-Airline Traffic report which provides monthly summaries of airline traffic contained in the T100 data by carrier and service class. Although the T1 report contains data on passenger numbers, there is no data about their routings, or where they got on or off their flights.
• The second is the T2 – Aircraft Traffic report which provides quarterly traffic data by carrier, aircraft type, service class and region. Again, there is no routing data.
• The third report is the T3-Airport Traffic report which provides a quarterly summary of airport data.

We’ll look at each of the reports in turn.

Back to top

7.2          T1 AIRLINE TRAFFIC REPORT

Make sure the T1 Airline Traffic report option is selected at the top of the screen, above dimensions and metrics. You’ll see there are 9 dimensions and 5 metrics, all relating to airlines and flight types. We’re going to walk through running a report here to best demonstrate how this report works.

7.3          T1 AIRLINE TRAFFIC - WORKED EXAMPLE

We’ll take a look at Alaska Airlines and Virgin America through 2016 and 2017 to understand what their combined capacity and traffic was in the run up to the announcement that Alaska Airlines would be acquiring Virgin America, and through the following months. For this query we just need to select carrier name in dimensions so we’ll just click on carrier name.

For metrics, we want to see passengers and capacity. When we click on Passengers, we can see there are 5 options. The first, Revenue Passengers enplaned is simply the number of passengers who board flights. The other passenger options are variations on revenue passenger miles and non revenue passenger miles. Revenue Passenger Miles (RPMs) are another way used by the industry of measuring traffic. We’ll use Revenue Passenger Miles as we want to look at RPMs.

Similarly, there are 3 different options for Capacity, this time these are related to the cabin class. We’ll choose the first one, Available Seat Miles.

Now we need to select the time period. We want to look at months, and see how the data changes over the months, so we’ll choose month as the time period (this is the default) and the range from January 2016 to December 2017. As we want to see each month displayed, we’ll make sure that Use Time Series is ticked, as otherwise, the data would be aggregated into a single figure.

The last selection we need for this worked example is the Carriers. We enter Virgin America and Alaska Airlines in the reporting carrier field, either using the airline two letter codes or starting to type in the full name.

That’s our query populated, and we can check the Current Report Selections to see that we’ve added all the required dimensions, metrics and parameters.

And here’s the data:

If we’re happy with what we’ve generated, then as before, we can export, schedule or save the data. If we wanted to change anything, we’d click on Back to report fields which takes us back to the report query screen. If at any time in the output page you want to check the parameters of the query, if you click on My Report, on the right of the screen, it opens up the Current Report Selection summary page. You can then either close it if you are happy, or you can click on Back to report fields and amend your query as necessary.

Back to top

7.4          T2 AIRCRAFT TRAFFIC REPORT – WORKED EXAMPLE

Now we’ll take a look at the T2 Airline Traffic report. The dimensions and metrics for this report are different to the previous one. Full details of the Dimensions and Metrics for each table are outlined in the APPENDIX. You’ll remember that we noted at the beginning of this section that this report provides quarterly summaries. There are some financial and network ratios in this report, so it could be useful if we were looking for some indicative cost figures to include in a business case for example.

To highlight what data this report can provide, we’ve included another short worked example. In this case, we want to look at the difference between crew costs per block hour for Frontier Airlines and Spirit Airlines by aircraft type.

First, we’ll choose the dimensions we need. These are Carrier Name and Equipment Type. Next, we’ll select the metrics we need. These are Financial Ratios, and from there, we’ll choose Crew Cost/ Block Hour.

We’ll leave the period as it is, as we want to see the most recently available data and then move down to the Carrier parameters. We’ll add Frontier Airlines and Spirit Airlines in the Reporting Carrier field and that’s all we need for this relatively simple query.

Now we’ll click on run and see how the data looks.

Straightaway you can see there are some significant differences between the two carriers on some of the aircraft types so in this case you might want to do some more analysis and look at some other measures.

Back to top

7.5          T3 AIRPORT TRAFFIC REPORT – WORKED EXAMPLE

Now we’ll take a look at the third report in the Traffic Summary reports. T3 Airport Traffic data is specific to an individual airport so you have to select an airport to run the query for. The screenshot below shows that there are numerous dimensions, and all of these are shown in the APPENDIX for each report.

We’re going to show you how this report works by looking at the relative proportion of scheduled v’s non-scheduled, or charter, traffic an airport handles.

We’ll scroll down to the last option in dimensions which is Scheduled/Non-scheduled and choose that. Then for metrics we’ll simply choose Revenue Passengers Enplaned. We’ll choose the most recent time period, which is the 3^rd Quarter of 2017 (remember this report is Quarterly).

We’re going to look at Chicago O’Hare in this example so we can either enter the airport three letter code (ORD) in the Origin field, or start typing the airport name.

We could also have chosen to specific a carrier here, or a group of carriers, but we’ll leave the carrier option blank this time.

Once again we can check the Current Report Selections in My Report to make sure we’ve got everything there we need and then we can click to Run the report.

Here are the results. We can see very quickly that Chicago O’Hare has a much larger share of scheduled traffic and that charter traffic is a very small element of traffic.

You can access a video walking through these examples by going to the User Guide section at the top right-hand corner of the screen in the DOT Analyser. You’ll find some worked example video guides for each type of report. These match up with the worked examples we use here, so you can use this guide and the videos to complement each other if necessary.

Back to top

8.          APPENDIX - DIMENSIONS, METRICS & CATEGORIES

8.1           T100 SEGMENT TRAFFIC REPORTS – DIMENSIONS AND METRICS

Back to top

8.2          T100 MARKET TRAFFIC REPORTS – DIMENSIONS AND METRICS

8.3          O&D TRAFFIC REPORTS (DETAILED) – DIMENSIONS AND METRICS

Back to top

8.4          O&D TRAFFIC REPORTS (SUMMARY) – DIMENSIONS AND METRICS

Back to top

8.5          FINANCE REPORTS (FORM 41) - DIMENSIONS, METRICS & CATEGORIES

Back to top

8.6          TRAFFIC SUMMARY REPORTS (FORM 41) – DIMENSIONS AND METRICS

Back to top