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, for both the current and previous year.

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: Numerator: Counts distinct quotations that: Have a non-blank quoted_at date. Have a non-blank confirmed_at date. Are of type 8 (confirmed jobs). Fall within the current year-to-date (DATESYTD(Quotation[quoted_at])). Denominator: Counts distinct quotations that: Have a non-blank quoted_at date. Are of type 8. Fall within the current year-to-date.