Skip to content
English
Contact us
  • There are no suggestions because the search field is empty.

Unified Profitability Analytics (On-Demand & Routed Work)

1. Target Objective

To create comprehensive financial analytics dashboards in Metabase that accurately reflect revenue, cost (driver pay), and profitability across the entire CXT platform. This requires merging Routed Work financial data into the existing On-Demand Order data table in Snowflake, enabling a unified view of company performance at a granular level.

2. Problem Statement

Currently, financial reporting handles On-Demand work seamlessly because revenue and cost are deterministically linked 1:1 to individual orders. Routed Work operates on a structurally different model:

  • Stops are known in advance and ultimately grouped into routes.

  • Driver pay is often calculated at the route or schedule level rather than per stop.

  • Client billing is often decoupled from individual execution, functioning via booked amounts per route or monthly rolling contracts. (other constraints?)

To achieve a unified dashboard, we must normalize Routed Work data to match the On-Demand "Order" granularity. This requires applying heuristic logic to distribute aggregated costs and revenues down to the individual stop level, which inherently introduces estimation into financial reporting.

3. Proposed Solution & Logic

The foundational data mapping will equate a Posted Route Stop to an On-Demand Order.

The CXT Data Engineer will extract routed data, transform it using the logic below, and append it to the existing On-Demand query. Snowflake will process this data at a 15-minute frequency (or as configured).

To ensure data lineage and transparency, two new columns will be added to the ORDERS target table:

  • work_type: 'Routed' | 'On-Demand'

  • allocation_method: 'Exact' | 'Heuristic'

Driver Pay (Cost) Allocation

  • Variable Pay (Exact Mapping): When pay settings vary by stop ("Per Stop", "Per Piece" (Pieces per each Routed Stop), “Percentage” (out of Total Amount per Routed Stop) or “Booked amount” (percentage out of Booked Amount per Routed Stop) or "Advanced Fallback" with stop-level variation), the exact price per stop is calculated and associated directly with the Posted Route Stop.

  • Flat Pay (Heuristic Mapping): When pay settings do not vary by stop ("Per Route", or static "Advanced fallback" where pay does not vary by stop), the total route pay is calculated and divided evenly by the total number of stops on that route.

  • Decoupled Pay (Per Schedule): The pay can be “Per Schedule”, where the courier sets specific days or frequency for driver payment. This frequency may not be in the days the routes are posted. We can associate this with a recurrent revenue (salary) for that driver associated to that route, with occasional reductions (route reversal) when there is no post for that particular pay day.

    • For a given data extraction moment for each route on this driver pay mechanism,

      • For past posted routed stops (until present date), calculate the total pay for each route given the pay schedule, amount and conditions (max, min, day of the week, route reversal). Then for each route total, split by total number of executed route stops.

      • For future posted routed stops (for today and future), look at the schedule and see if the posted routed stops are on routes that have associated scheduled pay days, and sum those for each route. Calculate the total pay per route (given the conditions) and split equally by number of stops per route.

    • Driver Pay for these routed stops may change when these stops migrate from future to past.

Revenue (Billing) Allocation

  • Per-Route Billing: If billing is tied per Route, the total revenue is divided evenly among the stops on that route.

  • Decoupled Billing (Rolling Contracts - Past Cycles): For completed billing cycles, the total cycle revenue is divided evenly by the total number of Posted Route Stops executed within that cycle.

    • Alternatively we could use weighted average (weighted by driver pay per stop) instead of average.

  • Decoupled Billing (Rolling Contracts - Current Cycles): For in-progress cycles, revenue is calculated based on the elapsed time fragment of the billing cycle at the moment of data extraction. This fragmented revenue is then divided by the number of Posted Route Stops executed/posted up to that point. Snowflake will update these historical stop values continuously as the cycle progresses and new data is pulled.

    • Alternatively we could use weighted average (weighted by driver pay per stop) instead of average.

4. Risks & Mitigations

Risk 1: Fluctuating Financials in Open Periods Calculating in-progress rolling contracts based on elapsed time vs. executed stops means the assigned revenue per stop will constantly fluctuate throughout the current billing cycle.

  • Mitigation: This is an accepted heuristic for the MVP?. Finance departments may dislike shifting historical figures within an open period, but any alternative (like expected revenue) is equally flawed until the end-of-cycle true-up.

  • Action: Present this behavior to couriers to ensure they understand why current-cycle profitability is an estimate.

Risk 2: Unweighted Distribution of Cost/Revenue Applying an even split for route-level or contract-level financials assumes all stops require equal effort. A stop 2 miles away gets the same revenue/cost allocation as a stop 40 miles away.

  • Mitigation: To avoid overly complex guesswork and maintain a pragmatic first iteration, we are deliberately keeping the calculation simple.

  • Action: Validate with couriers if an unweighted split is acceptable.

Risk 3: Handling of Exception Statuses The current logic relies heavily on dividing totals by the "number of stops." It is currently undefined how cancelled, skipped, or failed stops impact the denominator.

  • Mitigation: Do not finalize the calculation logic until the business rules are confirmed.

  • Action: Require confirmation from stakeholders and clients on how couriers handle exception statuses in relation to driver pay and billed revenue.

Risk 4: Retainer Scope Overlap (Critical) Confirm whether monthly rolling contracts exclusively cover Routed Work, or if they could also absorb On-Demand orders.

  • Mitigation: N/A.

  • Action: If contracts include On-Demand work, we must define a technical mechanism to identify and flag those specific orders to prevent diluting the revenue per stop in the routed work.

Risk 5: Per schedule driver pay (Critical). The drivers get paid on a specific schedule, decoupled from the actual work execution. It may be that payment is accumulated without scheduled stops.

  • Mitigation: N/A.

  • Action: Agree with stakeholders the proposed solution and its downsides.