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 Quotation[id] values that satisfy both of the following conditions: Quoted timestamp is not blank (quoted_at is not null) Quotation type equals 8 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: Quoted timestamp is blank (quoted_at is null) Booked timestamp is not blank (booked_at is not null) Quotation type equals 8 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 Quotation[customer_id] values where all of the following conditions are met: Quoted timestamp is not blank (quoted_at is not null) Quotation ID is not blank (id is not null) Quotation type equals 8 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.
Direct Booking	Direct Booking = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[id]), NOT ISBLANK(Quotation[quoted_at]), NOT ISBLANK(Quotation[booked_at]), Quotation[type] = 8 ),0)	Direct BookingB measures the number of unique quotes that were directly converted into bookings. These are cases where a quote was issued and subsequently booked without being lost or deleted.
Chargeable weight quoted in kg	Chargeable_Weight quoted kg = COALESCE( CALCULATE( SUM(Quotation[chargeable_weight_kg]), NOT ISBLANK(Quotation[quoted_at]), Quotation[type]=8),0)	Chargeable_Weight Quoted (kg) calculates the total chargeable weight, in kilograms, associated with all valid quoted shipments.
Total Revenue quoted in euro	Revenue Quoted EUR = COALESCE( CALCULATE( SUM('Quotation'[reporting_db quote_revenue.Quoted_customer_invoice_currency_sell_total_Euro]), NOT ISBLANK(Quotation[quoted_at])),0)	Revenue Quoted (EUR) calculates the total quoted revenue in euros for all valid quotes.
Total Margin%	Margin (%) = DIVIDE( [Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)], [Total Revenue (EUR Booked or Confirmed)], 0 )	Margin (%) calculates the profitability percentage of booked or confirmed jobs by comparing revenue against cost.
Routes (o +D)	Origin_dest_Coount quoted = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[Origin _ Destination]), NOT ISBLANK(Quotation[quoted_at]), Quotation[type] =8),0)	This measure calculates the number of unique Origin _ Destination entries from the Quotation table where a quotation has been provided (quoted_at is not blank) and the quotation type equals 8. If no such entries exist, the measure returns 0.