Quotes Summary (W+M)
Summary
This page provides a comprehensive historical overview of quotes (type = 8), excluding lost and deleted jobs, aggregated on a weekly and monthly basis. It highlights key business KPIs, including:
- Total quotes
- Number of customers
- Percentage of won quotes
- Quoted gross profit
- Total quoted sell value (local currency)
- Total quoted revenue (local currency)
- Chargeable weight
- Revenue
- Gross profit
- Routes
- Margin
This report serves as a central reference for monitoring quotation performance and evaluating business trends over time.
Filters
| Filter | Description |
|---|---|
| Routes | Filter the quotations by origin destination route pair |
| Customer | Filter the quotations by customer name |
| Weekly/Monthly Button | Enables users to switch between weekly and monthly |
Data Transformation Applied
[To be added]
| Query | Calculations | Description |
|---|---|---|
| Quote revenue from reporting database | SELECT * FROM reporting_db_quote_revenue | The query retrieves data from the quote revenue table in the reporting database. |
| Quotation table | SELECT
q.id, q.booked_at, q.confirmed_at, q.quoted_at, q.pre_quoted_at, q.created_at, q.user_id, q.booking_user_id, q.lost_reason, q.customer_id, q.customer_code, q.shipper_status, q.main_carrier_id, q.deleted_at, q.lost_at, q.delete_reasons, q.chargeable_weight, q.gross_weight, q.spotrate, q.type, q.schedule_auto_update, q.automation_created_count, q.schedule_auto_amount, q.status, q.booking_sync_state, q.cargo_type, q.house_airwaybill, q.product, q.origin, q.destination, q.master_airwaybill, q.hazardous_goods, q.commodity_description, q.weight_unit, q.cargo_only, CASE WHEN q.weight_unit = 'lb' THEN ROUND(q.chargeable_weight * 0.453592, 2) ELSE q.charge_weight END AS chargeable_weight_kg FROM quotations AS q WHERE q.created_at >= '2024-01-01' AND q.type = 8 AND q.lost_at IS NULL AND q.deleted_at IS NULL;
|
This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries. |
| Sites | SELECT id, name FROM sites | This selects site id and name from the site table. |
| Customer | SELECT id, name FROM customers | This query selects customer id and name from customer table |
| Users | SELECT id, name FROM users | This query selects User id and name from user table |
Calculations
| Reference | Calculation | Description |
|---|---|---|
| TBC | TBC |
TBC |