Quotations Extract: Difference between revisions
David.weller (talk | contribs) |
David.weller (talk | contribs) |
||
| Line 13: | Line 13: | ||
|- | |- | ||
|quotation_id | |quotation_id | ||
| | |Job id for prequotes, quotes and bookings | ||
|- | |- | ||
|brand | |brand | ||
| | |The instance the data is from '''(Need confirmation from Simon)''' | ||
|- | |- | ||
|customer_D365_code | |customer_D365_code | ||
| | |The customers dynamics 365 code | ||
|- | |- | ||
|origin_airport | |origin_airport | ||
| | |The origin airport of shipment | ||
|- | |- | ||
|destination_airport | |destination_airport | ||
| | |The destination airport of shipment | ||
|- | |- | ||
|product | |product | ||
| | |If the job is airport to airport, door to airport, door to airport or door to door | ||
|- | |- | ||
|customer_name | |customer_name | ||
| | |Name of the customer | ||
|- | |- | ||
|cargo_available_date | |cargo_available_date | ||
| | |The ready date of the shipment | ||
|- | |- | ||
|volumetric_weight | |volumetric_weight | ||
| | |The volumetric weight of the shipment | ||
|- | |- | ||
|gross_weight | |gross_weight | ||
| | |The gross weight of the shipment | ||
|- | |- | ||
|chargeable_weight | |chargeable_weight | ||
| | |The chargeable weight of the shipment | ||
|- | |- | ||
|total_pieces | |total_pieces | ||
| | |The total pieces on the shipment | ||
|- | |- | ||
|total_volume | |total_volume | ||
| | |The total volume of the shipment | ||
|- | |- | ||
|carrier_name | |carrier_name | ||
| | |The carrier the shipment is booked on | ||
|- | |- | ||
|site_name | |site_name | ||
| | |The site the shipment is on within QMS | ||
|- | |- | ||
|deleted_at | |deleted_at | ||
| | |If there is a date present, the shipment was deleted on the date specified. If NULL it isn't deleted | ||
|- | |- | ||
|cancelled | |cancelled | ||
| | |If the shipment was requested to be cancelled by customer | ||
|- | |- | ||
|lost | |lost | ||
| | |If there is a date present, the shipment was lost on the date specified. If NULL it isn't lost | ||
|- | |- | ||
|lost_reason | |lost_reason | ||
| | |The reason the shipment was lost | ||
|- | |- | ||
|spot_rate | |spot_rate | ||
| | |If the shipment was spot rated | ||
|- | |- | ||
|commodity_description | |commodity_description | ||
| | |The description of the shipment | ||
|- | |- | ||
|margin_value | |margin_value | ||
| | |The total sell minus the cost of the shipment to provide the margin. '''(Need confirmation from Simon)''' | ||
|- | |- | ||
|commission_value | |commission_value | ||
| | |Any additional commission made on the shipment | ||
|- | |- | ||
|WFCProductCode | |WFCProductCode | ||
| | |Linked WFC Product codes to shipment | ||
|- | |- | ||
|automated_created | |automated_created | ||
| | |If value greater than 0, the automation ran on shipment. Greater than 1 shows how many times the automation ran on the shipment. | ||
|} | |} | ||
| Line 95: | Line 95: | ||
|- | |- | ||
|length_unit | |length_unit | ||
| | |To show the length unit used on the shipment | ||
|- | |- | ||
|weight_unit | |weight_unit | ||
| | |To show the weight unit used on the shipment | ||
|- | |- | ||
|master_airwaybill | |master_airwaybill | ||
| | |The master airway bill on used on the shipment | ||
|- | |- | ||
|deleted_reasons | |deleted_reasons | ||
| | |If deleted, the reasons the shipment was deleted | ||
|- | |- | ||
|is_booking_request | |is_booking_request | ||
| | |If the shipment was a booking request | ||
|- | |- | ||
|type | |type | ||
| | |Used to isolate if the shipment is a master of the brand bookings. Identified using '8' | ||
|- | |- | ||
|group_type | |group_type | ||
| | |Used to isolate the sub-records of master records. Identified using '8' | ||
|- | |- | ||
|automated_created_count | |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 | |schedule_auto_update | ||
| | |To show the user if the automation schedule confirmation has ran | ||
|- | |- | ||
|schedule_auto_amount | |schedule_auto_amount | ||
| | |To show the user how many times the automation schedule confirmation ran | ||
|- | |- | ||
|sanction_id | |sanction_id | ||
| | |To show if shipment went through a sanctions approval process | ||
|- | |- | ||
|rebook_count | |rebook_count | ||
| | |How many times the shipment has been rebooked | ||
|- | |- | ||
|pre_quoted_at | |pre_quoted_at | ||
| | |If / when the shipment was prequoted | ||
|- | |- | ||
|quoted_at | |quoted_at | ||
| | |If / when the shipment was quoted | ||
|- | |- | ||
|booked_at | |booked_at | ||
| | |If / when the shipment was booked | ||
|- | |- | ||
|confirmed_at | |confirmed_at | ||
| | |If / when the shipment was confirmed | ||
|- | |- | ||
|total_sell | |total_sell | ||
| | |The total sale of the shipment | ||
|- | |- | ||
|total_cost | |total_cost | ||
| | |The total cost of the shipment | ||
|- | |- | ||
|total_sell_rate | |total_sell_rate | ||
| | |The total sale rate of the shipment | ||
|- | |- | ||
|total_cost_rate | |total_cost_rate | ||
| | |The total cost rate of the shipment | ||
|- | |- | ||
|total_sell_currency | |total_sell_currency | ||
| | |The total sale currency | ||
|- | |- | ||
|total_cost_currency | |total_cost_currency | ||
| | |The total cost currency | ||
|- | |- | ||
|hazardous_goods | |hazardous_goods | ||
| | |If the shipment is marked as dangerous goods | ||
|- | |- | ||
|cargo_only | |cargo_only | ||
| | |If the shipment is marked as cargo aircraft only | ||
|- | |- | ||
|customer_id | |customer_id | ||
| | |If shipment is using a contract rate. If the value is null, a contract tariff has not been used | ||
|- | |- | ||
|flag_promo | |flag_promo | ||
| | |If shipment is using a promotional tariff | ||
|} | |} | ||
| Line 185: | Line 182: | ||
|- | |- | ||
|entity_name | |entity_name | ||
| | |This is just an ID that can't be utilized by lata lake | ||
|- | |- | ||
|customer_id | |customer_id | ||
| | |This is the database ID of the customer which can't be utilized by data lake | ||
|- | |- | ||
|hos_quote_reference | |hos_quote_reference | ||
| | |As we are moving away from HOS this isn't required | ||
|- | |- | ||
|quote_creation_date | |quote_creation_date | ||
| | |Covered in the new pre_quoted_at, quoted_at, booked_at fields | ||
|- | |- | ||
|carrier_deleted | |carrier_deleted | ||
| | |It isn't relevant to data lake if the carrier is deleted | ||
|- | |- | ||
|carrier_parent_id | |carrier_parent_id | ||
| | |This isn't required for shipment level reporting | ||
|- | |- | ||
|customer_deleted_at | |customer_deleted_at | ||
| | |It isn't relevant to data lake if the customer is deleted | ||
|- | |- | ||
|carrier_site | |carrier_site | ||
| | |This is an ID that can't be utilized by data lake | ||
|- | |- | ||
|status | |status | ||
| | |High level status of the job isn't required | ||
|- | |- | ||
|HasPrequote | |HasPrequote | ||
| | |Covered in the new pre_quoted_at, quoted_at, booked_at fields | ||
|- | |- | ||
|HasQuote | |HasQuote | ||
| | |Covered in the new pre_quoted_at, quoted_at, booked_at fields | ||
|- | |- | ||
|HasBooking | |HasBooking | ||
| | |Covered in the new pre_quoted_at, quoted_at, booked_at fields | ||
|- | |- | ||
|currency | |currency | ||
| | |Only covers a single currency when there could be two | ||
|- | |- | ||
|total_cost_value | |total_cost_value | ||
| | |Not entirely sure where this value is captured from but captured in the "total_cost" | ||
|- | |- | ||
|total_sale_value | |total_sale_value | ||
| | |Not entirely sure where this value is captured from but captured in the "total_sale" | ||
|- | |- | ||
|average_buy_rate | |average_buy_rate | ||
| | |We are now providing the actual rate, not the average | ||
|- | |- | ||
|average_sell_rate | |average_sell_rate | ||
| | |We are now providing the actual rate, not the average | ||
|- | |- | ||
|automated_cancel | |automated_cancel | ||
| | |Deprecated and no longer used | ||
|} | |} | ||
Revision as of 12:01, 1 July 2025
『WORK IN PROGRESS - NOT COMPLETE』
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 instance the data is from (Need confirmation from Simon) |
| 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 sell minus the cost of the shipment to provide the margin. (Need confirmation from Simon) |
| commission_value | Any additional commission made on the shipment |
| WFCProductCode | Linked WFC Product codes to shipment |
| automated_created | If value greater than 0, the automation ran on shipment. Greater than 1 shows how many times the automation ran on the shipment. |
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 | If the 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 |
| 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 | If / when the shipment was prequoted |
| quoted_at | If / when the shipment was quoted |
| booked_at | If / when the shipment was booked |
| confirmed_at | 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 | If the shipment is marked as dangerous goods |
| cargo_only | If the shipment is marked as cargo aircraft only |
| customer_id | If shipment is using a contract rate. If the value is null, a contract tariff has not been used |
| flag_promo | If shipment is using a promotional tariff |
Example Calculations
Removed Columns
Here you will see the columns that have been removed from the export that existed prior to the new version being released.
| 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 |