Quotes Overview (W+M)
Summary
This page provides a comprehensive historical overview of quotes (type = 8), excluding lost and deleted jobs, on a weekly and monthly basis. It highlights key business KPIs, including total quotes, converted bookings, quoted customers, won quotes percentage, direct bookings, chargeable weight, revenue, gross profit, routes, and margin.
Filters
| Filter | Description |
|---|---|
| Sites | filter by site name |
| Weekly/Monthly Button | Enables switching between weekly and monthly values. |
| Date picker | Filters by date. |
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 |
|---|---|---|
| Total Quotes | Total Quotes =
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
NOT ISBLANK(Quotation[quoted_at]),
Quotation[type]=8),0) |
Returns the number of unique quotes that were actually issued (i.e., where a quoted timestamp exists).
Logic The measure counts distinct
If no records meet these criteria, the measure returns 0. |
| Converted Bookings | Converted BookingB =
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
ISBLANK(Quotation[quoted_at]),
NOT ISBLANK(Quotation[booked_at]),
Quotation[type] = 8
),0) |
The measure counts distinct Quotation[id] values that meet all of the following conditions:
If no records meet these criteria, the measure returns 0. |
| Quoted Customers | Quoted Customers =
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[customer_id]),
NOT ISBLANK(Quotation[quoted_at])&&
NOT ISBLANK(Quotation[id]),
Quotation[type]=8),0) |
Calculates the number of unique customers who received at least one valid quote
The measure counts distinct
If no records match these conditions, the measure returns 0. |
| Won Quote % | Won Quote24 % =
COALESCE(
VAR TotalQuotes =
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
NOT ISBLANK(Quotation[quoted_at]),
Quotation[type] = 8,
ISBLANK(Quotation[deleted_at]),
ISBLANK(Quotation[lost_at]),
Quotation[created_at] >= DATE(2024, 1, 1)
)
VAR ConvertedBookings =
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
NOT ISBLANK(Quotation[quoted_at]),
NOT ISBLANK(Quotation[booked_at]),
Quotation[type] = 8,
ISBLANK(Quotation[deleted_at]),
ISBLANK(Quotation[lost_at]),
Quotation[created_at] >= DATE(2024, 1, 1)
)
RETURN DIVIDE(ConvertedBookings, TotalQuotes, 0)
,0) |
The formula calculates the conversion rates of quotes showing quotes that successfully resulted in booked or confirmed bookings. |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |