Quotations Extract

Overview

Captures all job-related data from QMS, including every stage of the shipment lifecycle—from pre-quotes and formal quotations to bookings and automation sync events. This extract offers a complete view of how a job progresses through the system, making it ideal for tracking and analyzing conversion rates, booking efficiency, and automation coverage. Users can leverage this data to monitor performance, identify trends in quoting and booking behavior, and gain insight into the timing and effectiveness of automation within the booking process.

Extract Data

Existing Extract (Kept Columns)

Column	Description
quotation_id	Job id for prequotes, quotes and bookings
customer_D365_code	The customers dynamics 365 code
origin_airport	The origin airport of shipment
destination_airport	The destination airport of shipment
product	If the job is airport to airport, door to airport, door to airport or door to door
customer_name	Name of the customer
cargo_available_date	The ready date of the shipment
volumetric_weight	The volumetric weight of the shipment
gross_weight	The gross weight of the shipment
chargeable_weight	The chargeable weight of the shipment
total_pieces	The total pieces on the shipment
total_volume	The total volume of the shipment
carrier_name	The carrier the shipment is booked on
site_name	The site the shipment is on within QMS
deleted_at	If there is a date present, the shipment was deleted on the date specified. If NULL it isn't deleted
cancelled	If the shipment was requested to be cancelled by customer
lost	If there is a date present, the shipment was lost on the date specified. If NULL it isn't lost
lost_reason	The reason the shipment was lost
spotrate	If the shipment was spot rated
commodity_description	The description of the shipment
margin_value	The total commission made on a job based the invoice currency
commission_value	Any additional commission made on the shipment
WFCProductCode	Linked WFC Product codes to shipment
automated_created	True / False. 0 = False, and 1 = True.

Existing Extract (New Columns)

Column	Description
length_unit	To show the length unit used on the shipment
weight_unit	To show the weight unit used on the shipment
master_airwaybill	The master airway bill on used on the shipment
deleted_reasons	If deleted, the reasons the shipment was deleted
is_booking_request	True / False. 0 = False, and 1 = True. If true, shipment was a booking request.
type	Used to isolate if the shipment is a master of the brand bookings. Identified using '8'
group_type	Used to isolate the sub-records of master records. Identified using '8'
automation_created_count	If value greater than 0, the automation ran on shipment. Greater than 1 shows how many times the automation ran on the shipment
schedule_auto_update	To show the user if the automation schedule confirmation has ran, which would be signified by a 1 if it has ran.
schedule_auto_amount	To show the user how many times the automation schedule confirmation ran
sanction_id	To show if shipment went through a sanctions approval process
rebook_count	How many times the shipment has been rebooked
pre_quoted_at	Date/time stamp. If / when the shipment was prequoted
quoted_at	Date/time stamp. If / when the shipment was quoted
booked_at	Date/time stamp. If / when the shipment was booked
confirmed_at	Date/time stamp. If / when the shipment was confirmed
total_sell	The total sale of the shipment (customer invoice currency)
total_cost	The total cost of the shipment (customer invoice currency)
total_sell_rate	The total sale rate of the shipment (customer invoice currency)
total_cost_rate	The total cost rate of the shipment (customer invoice currency)
total_sell_currency	The total sale currency (customer invoice currency)
total_cost_currency	The total cost currency (customer invoice currency)
hazardous_goods	True / False. 0 = False, and 1 = True. If true, shipment is a dangerous goods shipment
cargo_only	True / False. 0 = False, and 1 = True. If true, shipment is cargo aircraft only.
tariff_customer_id	If shipment is using a contract rate. If the value is null, a contract tariff has not been used
flag_promo	True / False. 0 = False, and 1 = True. If true, shipment is using a promotional tariff
invoice_currency	The currency of the customer and what they invoice in.
customer_UUID	Unique customer id from the master customer project. Can be "NULL" if no uuid provided.

Extract Requested Fields By Man Lee (Approved)

Column	Description
wfc_entity_id	WFC identification numbers (changed back to entity_name at the request of ML)
route	Route with IATA codes separated by '-' (e.g. LHR-JFK-EMA)
flight_numbers	Full route of flight numbers, separated by '-' if available
carrier_class	Determine the carrier class (airline, truck, sea freight etc)
is_cass	True/False. If customer is CASS.
handling_code	3-letter cargo type code (e.g. PER, DGR) (changed to just handling_code at request of ML)
actual_flight_dates	Actual Flight Dates, they will be in the following format: depart/arrive - depart/arrive - depart/arrive
planned_flight_dates	Planned Flight Dates based on what is booked, they will be in the following format: depart/arrive - depart/arrive - depart/arrive
currency_lcy	Entity currency used on job (site currency)
cargo_type	3-letter cargo type code (requested to be added by ML)
service_type	Service of the tariff used on the job (requested to be added by ML)
arrived	Date / Time and if not arrived yet will display null (requested to be added by SB)
invoiced	Date / Time and if not invoiced yet will display null (requested to be added by SB)
is_gsa	Yes / No - If the carrier used on the job was GSA or not (requested to be added by ML / SB)

Extract Requested Fields By Man Lee (Not Approved, In Discussion)

Column	Description	Comments
total_sell_fsc	Sell fuel/security charges only	Discussing in spreadsheet
total_cost_fsc	Cost fuel/security charges only	Discussing in spreadsheet
sell_other_total	Sell surcharges excluding linehaul, fuel, security	Discussing in spreadsheet
cost_other_total	Cost surcharges excluding linehaul, fuel, security	Discussing in spreadsheet
AWB_Status	N/A	WFC team reviewing
GSA_Commission	Remuneration/Gross Profit per AWB	Discussing in spreadsheet
departed_origin_date	Actual date flight departed from origin	Discussing in spreadsheet, assumed not required as providing "flight_dates" which will include this

Example Calculations

Automation Time Saved (Booking Carrier Platform Sync)

To calculate the time saved transferring bookings from QMS into the carrier booking systems, you just need to follow the steps below:

1. Identify if shipment was automated or not using automated_created and confirming is true which means the automation has ran and the booking isn't manually processed.
2. Then retrieve the amount of times the automation has ran on the shipment via the automated_created_count which will give you total times that the automation has ran on this shipment.
3. After this you would multiply the automated_created_count by 5 to give you the total minutes saved.

automated_created_count * 5 = total minutes saved

Automation Time Saved (Schedule Booking Confirmation)

To calculate the time saved for retrieving confirmation status and schedule confirmation automatically from the airline systems.

1. Total the shipments with schedule_auto_update = 1 and booked_at has a date value in it.
2. Multiply the number by 5 to calculate the time saved in minutes.

(schedule_auto_update & booked_at) * 5 = total minutes saved

Automation Time Saved (Converting from quotes to bookings)

To calculate the time saved for converting a quote into a booking.

1. Total the shipments that have time date stamps in quoted_at and booked_at and multiply that by 5 to get your total minutes saved.

(booked_at & quoted_at) * 5 = total minutes saved

Automation Time Saved (Converting from prequote to quote / booking)

To calculate the time saved for converting a prequote into a quote or a booking.

1. Total the number of shipments that have pre_quote_at & quoted_at to store as your total pre quotes to quotes conversion.
2. Total the number of shipments that have pre_quoted_at & booked_at to store as your total pre quotes to bookings conversion.
3. Get the totals together and multiply by 5 to get the total minutes saved.

(pre_quoted_at & quoted_at + pre_quoted_at & booked_at) * 5 = total minutes saved

Identify Prequotes, quotes, bookings and confirmed bookings

Within the dataset you have a single shipment which can be a different combinations of jobs status and it can easily be identified what status it is currently in and what status it has come from or been converted from. The absolute end time stamp would be confirmed_at this tells you that the job was confirmed by the airline as booked. The additional time stamps that are used would be pre_quoted_at, quoted_at, booked_at and it's using these which can tell you where the job is or where it came from, and using the time stamp you can determine at what time and date the job was moved into these states. If there isn't a timestamp present it means the job hasn't go through this method or reached that far.

For example, if there isn't a time stamp for confirmed_at then the job hasn't been confirmed booked by the airline yet. You will not see confirmed_at without booked_at because the job is required to be booked in the QMS system to be confirmed by the airline.

• pre_quoted_at alone would mean currently the shipment is only a pre quote.
• quoted_at alone would mean the shipment wasn't pre quoted before being made as a quote and isn't a booking yet.
• booked_at alone would mean that the shipment was a direct booking with no pre quote or quote.

From here you can have different combinations that would mean different things, please see some examples:

• pre_quoted_at & booked_at would mean the shipment was originally pre quoted and then it was booked.
• quoted_at & booked_at would mean the shipment was quoted and then booked but isn't yet confirmed.
• pre_quoted_at, quoted_at & booked_at would mean the shipment was pre quoted, quoted and then booked but it hasn't been confirmed yet.
• booked_at & confirmed_at it would mean that the shipment was a direct booking that has been confirmed by the airline system.
• pre_quoted_at, quoted_at, booked_at & confirmed_at would mean the shipment has been pre quoted, quoted, booked and has been confirmed by the airline system.

Columns: Removal Planned

Note: The won't be removed until final confirmation from Sanders PBI team and until that time will continue to be included.

Here are the columns that we would like to remove from the extract because they are either deprecated or wouldn't provide any reporting data that could be used.

Column	Description
Brand	The QMS portal name
Entity_name	Name of the entity
customer_id	This is the database ID of the customer which can't be utilized by data lake
hos_quote_reference	As we are moving away from HOS this isn't required
quote_creation_date	Covered in the new pre_quoted_at, quoted_at, booked_at fields
carrier_deleted	It isn't relevant to data lake if the carrier is deleted
carrier_parent_id	This isn't required for shipment level reporting
customer_deleted_at	It isn't relevant to data lake if the customer is deleted
carrier_site_id	This is an ID that can't be utilized by data lake
status	High level status of the job isn't required
HasPrequote	Covered in the new pre_quoted_at, quoted_at, booked_at fields
HasQuote	Covered in the new pre_quoted_at, quoted_at, booked_at fields
HasBooking	Covered in the new pre_quoted_at, quoted_at, booked_at fields
currency	Only covers a single currency when there could be two
total_cost_value	Not entirely sure where this value is captured from but captured in the "total_cost"
total_sale_value	Not entirely sure where this value is captured from but captured in the "total_sale"
average_buy_rate	We are now providing the actual rate, not the average
average_sell_rate	We are now providing the actual rate, not the average
automated_cancel	Deprecated and no longer used