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 |
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.
- Identify the shipments with
schedule_auto_update = 1</code andbooked_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.
- Identify 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)
Identify Prequotes, quotes, bookings and confirmed bookings
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