Customer Overall TY
Summary
Year-to-Date Confirmed Jobs (Type = 8) Overview
This page provides a historical view of year-to-date confirmed jobs (type = 8), excluding lost and deleted jobs.
Key business KPIs tracked include:
Customers
Won quotes percentage
Direct bookings
Chargeable weight
Revenue
Gross profit
Routes
Carriers
Users
This page serves as a quick reference for monitoring performance trends and overall business growth.
Filters
| Filter | Description |
|---|---|
| Carriers | Filter data by carrier name |
Data Transformation Applied
[To be added]
| Query | Calculations | Description |
|---|---|---|
| Quotation totals from reporting database | SELECT * FROM reporting_db_quotation_totals | The query retrieves data from the quotation totals 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. |
| 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 |
|---|---|---|
| Won Quote% | Current YTD Won Quote % =
COALESCE(
DIVIDE(
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[quoted_at]) &&
NOT ISBLANK(Quotation[confirmed_at]) &&
Quotation[type] = 8
),
DATESYTD(Quotation[quoted_at])
),
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[quoted_at]) &&
Quotation[type] = 8
),
DATESYTD(Quotation[quoted_at])
),
0
),0) |
The Current Year-to-Date (YTD) Won Quote % measures the proportion of quotes that have been both issued and confirmed (type = 8) relative to all quotes issued (type = 8) so far in the current year.
Calculation Logic:
|
| Revenue | Confirmed_revenue YTD =
CALCULATE(
SUMX(
'Dimreporting_db quotation_totals',
'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
/ 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate]
),
FILTER(
'Dimreporting_db quotation_totals',
NOT ISBLANK( 'Dimreporting_db quotation_totals'[confirmed_at] )
&& 'Dimreporting_db quotation_totals'[confirmed_at] >= DATE( YEAR( TODAY() ), 1, 1 )
&& 'Dimreporting_db quotation_totals'[confirmed_at] <= TODAY()
)
) |
|
| Chargeable weight | Current YTD Chargeable Weight Confirmed (kg) =
COALESCE(
CALCULATE(
SUM(Quotation[chargeable_weight_kg]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[confirmed_at])
),
DATESYTD(Quotation[confirmed_at])
),
0
)
|
|
| Origin destination
pair |
each [origin] & " - " & [destination] |
The formula concatenate origin column and destination column |