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 |
| brand | The QMS portal name |
| 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 |
| spot_rate | 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' |
| automated_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 |
| total_cost | The total cost of the shipment |
| total_sell_rate | The total sale rate of the shipment |
| total_cost_rate | The total cost rate of the shipment |
| total_sell_currency | The total sale currency |
| total_cost_currency | The total cost 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. |
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:
- Identify if shipment was automated or not using
automated_createdand confirming istruewhich means the automation has ran and the booking isn't manually processed. - Then retrieve the amount of times the automation has ran on the shipment via the
automated_created_countwhich will give you total times that the automation has ran on this shipment. - After this you would multiply the
automated_created_countby 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.
- Total the shipments with
schedule_auto_update = 1andbooked_athas a date value in it. - 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.
- Total the shipments that have time date stamps in
quoted_atandbooked_atand 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.
- Total the number of shipments that have
pre_quote_at"ed_atto store as your total pre quotes to quotes conversion. - Total the number of shipments that have
pre_quoted_at&booked_atto store as your total pre quotes to bookings conversion. - 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_atalone would mean currently the shipment is only a pre quote.quoted_atalone would mean the shipment wasn't pre quoted before being made as a quote and isn't a booking yet.booked_atalone 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_atwould mean the shipment was originally pre quoted and then it was booked.quoted_at&booked_atwould mean the shipment was quoted and then booked but isn't yet confirmed.pre_quoted_at,quoted_at&booked_atwould mean the shipment was pre quoted, quoted and then booked but it hasn't been confirmed yet.booked_at&confirmed_atit 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_atwould mean the shipment has been pre quoted, quoted, booked and has been confirmed by the airline system.
Columns: Removal Planned
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 |
|---|---|
| entity_name | This is just an ID that can't be utilized by lata lake |
| 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 | 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 |