Program Management

Program Management

Air Detail

This is a detailed report that will show ticket information: ticket numbers, ticket amounts, airline names, departure dates, etc.

Report Category: Program Management

Report Format: Excel

Prompts:Date Range: MTD

Currency Code

Questions answered with this report include:

Are passengers booking the lowest fare ticket or are they spending too much?
Why aren’t passengers booking the lowest fare?
Are passengers booking coach, business, or first class tickets?

Data Fields and calculations displayed in this report:

FIELD NAME / DESCRIPTION / CALCULATION
Ticket Number / 10 digit number given by the airline for each ticket. / N/A
Traveler Name / Name of the passenger. / Last Name / First Name
Total Ticket Amt / Total amount of the airline ticket. / N/A
Low Fare / Lowest fare available at the time the ticket was issued. Travel agents manually input this information. / N/A
Lost Opportunity / Difference between the ticket amount and the lowest fare. / Total Ticket Amt – Low Fare
Reason Description / Reason lowest fare wasn’t booked (descriptions provided by agency). / N/A
Exception Code / Code given by each agency to denote the reason the lowest fare wasn’t taken. / N/A

Air Detail continued

FIELD NAME / DESCRIPTION / CALCULATION
Class of Service / The first letter of the fare basis code that the first flight was booked in. / N/A
Class Description / The part of the plane the passenger is flying in (Economy, Business, First). / N/A
Itinerary / City/airport codes for each arrival/departure city on the ticket (routing). / N/A
Departure Date / Date that the first flight departs. / N/A
Trip Length / Number of days between the departure date and the last travel date on a ticket. / N/A
Airline Name / Name of the airline. / N/A
Orig Country / The country that travel originated in. / N/A
Department / The department that the passenger works in. / N/A
Days Advance / Number of days prior to the departure date that a ticket was issued. / N/A
Issue Date / Date a ticket was issued. / N/A
Record Locator / 6 letter/number code given by the GDS when a booking is saved/confirmed. / N/A

Sample Report – Air Detail

Air Package XD

This report will display the top markets and the top destinations based on the number of tickets purchased. It will show the amount of tickets sold and the amount spent on those tickets. It will break out the type of tickets purchased; First, Business, and Coach.

Report Category:Program Management

Report Format: Excel

Prompts:Date Range – MTD

Date Range – Prior MTD

Questions answered with this report include:

How many tickets were purchased in First, Business, and Coach for the top destinations and markets?
How much did our company spend on First, Business, and Coach tickets for the top destinations and markets?
How much did our company spend per airline?
How many days prior to departure did our employees purchase their tickets?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
GRAPH
Segments / Number of airline segments. / Sum (Num Segments)
Coach, Business, First / Number of airline segments booked in each cabin of the airplane. / Sum (Num Segments) if Domestic Cabin = ‘First’, ‘Business’, or ‘Coach’
TOP 15 BI-DIRECTIONAL MARKETS
Rank / Top 15 Bi-Directional Market Pairs based on the true ticket count. / N/A
Codes / 3 letter codes for the departure and arrival market airport. / N/A
Market Pair / Names of the departure and arrival airports for a market (city pair in alphabetic order). / N/A
First Segs / Number of airline segments booked in First class. / Sum (True Ticket Count) When (Domestic Cabin) = First
First Amt / The total amount of all First class tickets. / Sum (Total Amount) when (Domestic Cabin) = First
First Avg / Average amount spent on First class tickets. / Sum (First Spend / First Segs)
Business Segs / Number of airline segments booked in Business class. / Sum (True Ticket Count) when (Domestic Cabin) = Business
Business Amt / The total amount of all Business class tickets / Sum (Total Amount) when (Domestic Cabin) = Business
Business Avg / Average amount spent on Business class tickets. / Sum (First Spend / First Segs)

Air Package XD continued

FIELD NAME / DESCRIPTION / CALCULATION
Coach Segs / Number of airline segments booked in Coach class. / Sum (True Ticket Count) when (Domestic Cabin) = Coach
Coach Amt / The total amount of all Coach class tickets. / Sum (Total Amount) when (Domestic Cabin) = Coach
Coach Avg / Average amount spent on Coach class tickets. / Sum (Coach Spend / Coach Segs)
Total Segs / Total number of airline segments booked in all cabins/classes. / Sum (True Ticket Count)
Total Amt / The total amount of all tickets. / Sum (Total Amount)
TOP 20 DESTINATIONS
Top 20 Destinations / Top 20 Destinations based on the number of segments to each destination. / N/A
# of Segments / Number of times anyone traveled to each city/airport. / Sum (True Ticket Count)
Total Amount / Total amount spent flying to each city/airport. / Sum (Segment Amount)
% to Total / Percent of all segments that are flown to each city/airport in the top 20. / Sum (Segment Amount) / True Ticket Count)
CARRIER USAGE
Rank / Top 10 airline carriers for domestic and for international based on the number of airline segments on each airline. Two different reporting periods are used for comparison. / N/A
AL Code / The 2 letter code used for each airline. / N/A
AL Name / The name of the airline. / N/A
Total Amount / Total amount spent on domestic and international airline segments. / Sum (Segment Amount)
% to Total / Percent of time each airline was booking compared to the total amount. / % to Total (Segment Amount)
Segs / Number of segments booked on each airline. / Sum (True Ticket Count)
Avg Amt / Average amount spent on each segment for each airline. / Sum (Segment Amount) / (True Ticket Count)
Avg Amt Index / Average amount spent on each segment for each airline using the average amounts from both reporting periods. / Avg Amt (Reporting Period / Avg Amt (Comparison Period)

Air Package XD continued

FIELD NAME / DESCRIPTION / CALCULATION
ADVANCE PURCHASE
Tickets Purchased / Number of tickets booked in each advance purchase group. / When (Adv Purch Group) = (each grouping), then Sum (True Ticket Count)
Total Amount / Total amount spent on airline tickets within each advance purchase group. / When (Adv Purch Group) = (each grouping), then Sum (Segment Amount)
Average Ticket Amount / The average amount spent on airline tickets within each advance purchase group. / When (Adv Purch Group) = (each grouping), then Sum (Segment Amount) / (True Ticket Count)
AIR LOST SAVINGS ANALYSIS
Reason Description / Reason why the passenger didn’t take the lowest fare. Descriptions are given by each company. / N/A
# of Trans / Number of transactions within the data. / Count
Total Amount / Total amount spent on all tickets. / Sum (Segment Amount)
Lost Opportunity / Difference between the fare booked on a ticket and the lowest fare available when booked (low fare entered by agency). / Sum (Segment Amount) – (Low Fare)
% Trans / Percentage of segments booked for each reason description. / % to Total (True Ticket Count) when (Reason Description) = each reason description

Sample Report – Air Package XD – Top 15 Bi-Directional Markets

SNAGHTML2ba7a8

Top 20 Destinations

SNAGHTML2c530b

Sample Report – Air Package XD

Carriage Usage

SNAGHTML2ad804

Air Lost Savings Analysis

SNAGHTML2d7331

Car Detail

This is a detailed report that will show car rental booking information including the car rental company, dates, car types, number of car days, the amount spent on car rentals and the location where the booking was made.

Category:Program Management

Report Format: Excel

Prompts:Date Range: Current

Client Code

PNR Locator

Confirmation Number

Last Name

First Name

Currency Code

Questions answered with this report include:

Is our company spending too much on car rentals?
What types of cars are being rented?
Who is booking cars in our company?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
PNR Locator / A 6-digit alpha-numeric code given to a reservation when it is saved in a GDS (travel reservation system). / N/A
Confirmation Number / The number given by the car company to confirm the reservation. / N/A
Passenger Name / The name of the person on the car rental reservation. / Last Name / First Name
Issue Date / The date the car rental was booked/invoiced. / N/A
Car Daily Rate / The daily rate of the car rental. / N/A
Car Total Amount / The total amount of the car rental. / (Number Cars) x (Number Days) x (Daily Rate)
Pick-up Date / The date the car rental will be picked up by the passenger. / N/A
Drop Off Date / The date the car rental will be dropped off by the passenger. / N/A
Num Days / The total number of days a car was booked. / (Number Cars) x (Number Days)

Car Detail continued

FIELD NAME / DESCRIPTION / CALCULATION
Num Cars / The number of cars booked on the car rental reservation. / N/A
Chain Code / The 2 letter code for the car rental company. / N/A
Chain Name / The name of the car rental company chain. / N/A
Pick up City Code / The 3 letter code for the city the car will be picked up. / N/A
Pick-up City Name / The name of the city where the passenger will pick up the car. / N/A
Intl Ind / The international indicator is a one letter code denoting whether the car rental is Domestic (D) or International (I). / N/A
Car Type / The type of car booked (full size, intermediate, compact, etc.) / N/A
Void Ind / The void indicator is a one letter code denoting whether the transaction has been voided (Y) or not (N). / N/A

Sample Report – Car Detail

(right side)

Country Analysis

This report will display information on the number of tickets and the amount spent on ticket per country. It will give a percent to total for the number of tickets and the amount spent per country.

Report Category:Program Management

Report Format:Excel

Prompts:Date Range:MTD

Date Range:YTD

Questions answered with this report include:

Which point of sale country sells the most tickets?
What is the percent of tickets purchased per country compared to all countries?
How much was spent on tickets per country?
What is the average price for tickets purchased per country?

Data fields:

FIELD NAME / DESCRIPTION / CALCULATION
POS Region / Regional Point of Sale – Region where the tickets were sold. / N/A
Agency / The name of the travel agency. / N/A
POS Country / Point of Sale Country – Country where the tickets were sold. / N/A
Tickets / Total number of airline tickets base on True Ticket Count (see definitions). / Sum (True Ticket Count)
% of Total / Percent of tickets for each Country compared to the total number of tickets. / % to total (True Ticket Count)
Amount / The amount spent on tickets per country. / Sum (Total Amount)
% of Total / Percent of amount spent on tickets for each country compared to the total spent on all tickets. / % to total (Total Amount)
ATP / The average price for all tickets issues for each country / Sum (Total Amount) / (True Ticket Count)

Sample Report – Country Analysis

Hotel Detail

This report displays the top hotel cities, chains and properties ranked by cost or room nights. This report has drill down capabilities allowing the user to drill down to each specific passenger’s hotel reservation.

Report Category: Supplier Management

Report Format: Excel

Prompts:Date Range: Current

Currency code

Questions answered with this report include:

Year-over-year total nights, spend, average length of stay and average rate per hotel, per city
Most used hotel in a specific city
Best average rate in a specific city and at which hotel

Data Field:

FIELD NAME / DESCRIPTION / CALCULATION
Top Chains
Rank / Sorts by cost or Number room nights. / N/A
Chain Name / Hotel Name.
Chain Code / Hotel 2-letter chain identifier.
# Nnights / Number of room nights booked. / Sum (Hotel nights xNum Rooms)
# Rrooms / Number of room booked. / Sum (Num Rooms)
Room Nts / Number of room nights booked. / Sum (Num Nights x Num Rooms)
Cost / Total cost for hotel bookings. / Sum (Num Nights x Num Rooms x Hotel Daily Rate)
Avg.Cost / Average rate per night. / Hotel Cost / Room Nights
Avg. Stay / Average length of stay for hotel booking. / Room Nights / Num Rooms

Hotel Detail continued

FIELD NAME / DESCRIPTION / CALCULATION
TOP CITIES
Rank / Sorts by cost or Number room nights. / N/A
City Name / Name of city hotel is located in.
City Code / 3-letter city identifier.
# Nights / Number of room nights booked. / Sum (Hotel Nights x Num Rooms)
# Rooms / Number of room booked. / Sum (Num Rooms)
Room Nts / Number of room nights booked. / Sum (Num Nights x Num Rooms)
Cost / Total cost for hotel bookings. / Sum (NumNights x Num Rooms x Hotel Daily Rate)
Avg. Cost / Average rate per night. / Hotel Cost / Room Nights
Avg. Stay / Average length of stay for hotel booking. / Room Nights / Num Rooms
TOP PROPERTIES
Rank / Sorts by cost or Number room nights. / N/A
Property Name / Name of hotel property.
Phone Number / Phone number for Hotel Property. / N/A
# Nights / Number of room nights booked. / Sum (Num Nights x Num Rooms)
# Rooms / Number of room booked. / Sum (Num Rooms)
Room Nts / Number of room nights booked. / Sum (Num Nights x Num Rooms)
Cost / Total cost for hotel bookings. / Sum (Num Nights x Num Rooms x Hotel Daily Rate)
Avg. Cost / Average rate per night. / Hotel Cost / Room Nights
Avg. Stay / Average length of stay for hotel booking. / Room Nights / Num Rooms

Report Filters: Filters exclude voided transactions.

Sample Report – Hotel Detail

Ops Trend Summary

This report will show data that can be used to see trends for air transactions, segment transactions, policy management, hotel, and car data. The graphs will show data for the Fiscal year to date.

Report Category: Policy Management

Report Format: Excel

Prompts:Date Range: Current YTD

Currency code

Report Prompts:Country

Region

Questions answered with this report include:

How much did we spend on domestic vs. international airline tickets?
How much did we spend in Economy, Business, and First class tickets?
Are we using our preferred airlines?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
REPORT SUMMARY
Air Transaction Data
Spend
Total Net Spend / Total amount of all tickets (does not include refunds). / Sum (Ticket Amount)
Domestic Net Spend / Total amount of all domestic (within the US) tickets. / Sum (Ticket Amount)
When Intl Indicator = D
International Net Spend / Total amount of all international (outside the US) tickets. / Sum (Ticket Amount)
When Intl Indicator = I
First, Business, Economy
Net Spend / Total amount of First class tickets, Business class tickets, and Economy class tickets. / Sum (Ticket Amount)
When Cabin = First, Business or
Economy
Tickets
Total Net Tickets / Total number of airline tickets (does not included refunded tickets). / Sum (True Ticket Count)
Domestic Net Tickets / Total number of domestic (within the US) airline tickets. / Sum (True Ticket Count)
When Intl Indicator = D
International Net Tickets / Total number of international (outside the US) airline tickets. / Sum (True Ticket Count)
When Intl Indicator = I
First, Business, Economy
Net Tickets / Total number of First class, Business class, and Economy class airline tickets. / Sum (True Ticket Count)
When Cabin = First, Business or
Economy

Ops Trend Summary continued

FIELD NAME / DESCRIPTION / CALCULATION
Average Ticket Price
Overall Blended ATP / Average Ticket Price for all airline tickets. / Sum (Ticket Amount) / (True Ticket Count)
Domestic ATP / Average Ticket Price for all domestic tickets. / Sum (Ticket Amount) / (True Ticket Count)
When Intl Indicator = D
International ATP / Average Ticket Price for all international tickets. / Sum (Ticket Amount) / (True Ticket Count)
When Intl Indicator = I
First, Business, Economy
ATP / Average Ticket Price for First class, Business class, and Economy class tickets. / Sum (Ticket Amount) / (True Ticket Count)
When Cabin = First, Business, or Economy
Segments
Net Segments / The total number of flight segments (does not include segments from refunded tickets). / Sum (True Ticket Count)
Domestic Net Segments / Total number of flight segments for domestic flights (flights within the US). / Sum (True Ticket Count)
When Intl Indicator = D
International Net Segments / Total number of flight segments for international flights (flights outside the US). / Sum (True Ticket Count)
When Intl Indicator = I
First, Business, Economy
Net Segments / Total number of flight segments for First, Business, and Economy class flights / Sum (True Ticket Count)
When cabin = First, Business, or Economy
Average Segment Price
Overall Blended ASP / The average price of a flight segment. / Sum (Segment Amount) / (True Ticket Count)
Domestic ASP / The average price of a domestic flight segment. / Sum (Segment Amount) / (True Ticket Count)
When Intl Indicator = D
International ASP / The average price of an international flight segment. / Sum (Segment Amount) / (True Ticket Count)
When Intl Indicator = I
First, Business, Economy
ASP / The average price of a First, Business, or Economy class flight segment. / Sum (Segment Amount) / (True Ticket Count)
When cabin = First, Business, or Economy

Ops Trend Summary continued