Detail (W+M)
Summary
This page presents a comprehensive historical analysis of Booked and Confirmed bookings (type = 8), excluding lost and deleted jobs, on a weekly and monthly basis. It highlights key business KPIs, including jobs, customers, chargeable weight, revenue, gross profit, and margin.
Filters
| Filter | Description |
|---|---|
| Sites | filter by site name |
| Weekly/Monthly Button | Enables switching between weekly and monthly values. |
Data Transformation Applied
[To be added]
| Query | Calculations | Description |
|---|---|---|
| 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. |
| reporting db quotation totals | SELECT * FROM reporting_db_quotation_totals | This selects data from reporting_db_quotation_totals |
| 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 |
| TBC | TBC |
TBC |