The unified data model for shipment and logistics analytics. Covers the full lifecycle of every shipment — order creation, pickup, transit, delivery, return — with courier-level statuses normalised, costs standardised across pre/post-tax, and weight discrepancies resolved between WMS and courier billing.Documentation Index
Fetch the complete documentation index at: https://docs.ocular.dev/llms.txt
Use this file to discover all available pages before exploring further.
What this data model represents
Grain: one row perawb (Air Waybill), linked to order_id and sku. A single order with multiple SKUs or split shipments produces multiple AWB rows.
Metrics, grouped by category — every number you can compute on this data model. Expand below for examples in each.
Show sample metrics in each category
Show sample metrics in each category
Show sample dimensions in each category
Show sample dimensions in each category
unified_status, tat_breach, and avg_weight_discrepancy are accurate at any grain and across any courier — you don’t have to per-courier-decode.
Slice by
Every dimension you can group or filter by.| Dimension | Description |
|---|---|
| Order & shipment | |
order_id | Originating order identifier |
awb | Air Waybill — unique shipment tracking ID |
rto_awb | AWB for the Return to Origin shipment, if applicable |
| Brand & channel | |
brand | Brand associated with the shipment |
sales_channel | Channel the order was placed on (website, marketplace, …) |
source | Data-source identifier for the record |
| Courier | |
courier_partner | Courier company handling the shipment (Delhivery, Bluedart, Xpressbees, …) |
courier_service | Specific service tier within the courier |
courier_via_aggregator | Whether the courier was booked via a logistics aggregator |
shipment_type | Forward / Reverse / Exchange |
| Status | |
unified_status | Normalised delivery status across all couriers — Delivered, RTO, In Transit, Out for Delivery, … |
tat_breach | Boolean — shipment breached the courier’s committed TAT |
is_returned | Boolean — return was initiated |
return_reason | Customer- or system-provided return reason |
is_exchanged | Boolean — shipment resulted in an exchange |
exchange_reason | Reason provided for the exchange |
is_cancelled | Boolean — shipment was cancelled |
| Payment | |
payment_method | COD / Prepaid / … |
cod_value | Cash-on-delivery amount to be collected |
| Customer | |
customer_id | Unique customer identifier |
customer_name | Name provided at order time |
customer_phone | Phone number |
customer_email | Email address |
| Pickup location | |
pickup_address | Full warehouse / pickup address |
pickup_pincode | Pickup pincode |
pickup_city | Pickup city |
pickup_state | Pickup state |
pickup_country | Pickup country |
| Drop location | |
drop_pincode | Customer delivery pincode |
drop_city | Delivery city |
drop_state | Delivery state |
drop_country | Delivery country |
zone | Courier zone for the origin–destination pair (Local, Metro, A, B, C) |
| Product | |
sku | SKU identifier for the product in the shipment |
quantity | SKU quantity as recorded in the OMS |
clickpost_item_quantity | Quantity as recorded by Clickpost at shipment creation |
| Dates & timeline | |
order_creation_date | When the order was placed |
fulfillment_creation_date | When the fulfilment record was created in Clickpost |
pickup_date | When the shipment was successfully picked up |
expected_delivery_date | Courier’s committed delivery date |
actual_delivery_date | Date the shipment was actually delivered |
rto_mark_date | Date and time the shipment was marked for RTO |
updated_at | Last record update |
| Delivery attempts | |
delivery_attempt_first_date | First delivery-attempt timestamp |
reason_for_first_failed_delivery | Reason first delivery attempt failed (Customer Unavailable, Incorrect Address, …) |
delivery_attempt_latest_date | Most recent delivery-attempt timestamp |
reason_for_last_failed_delivery | Reason most recent delivery attempt failed |
no_of_delivery_attempts | Total delivery attempts made |
| Pickup attempts | |
pickup_attempt_first_date | First pickup-attempt timestamp |
reason_for_first_failed_pickup | Reason first pickup attempt failed |
pickup_attempt_latest | Most recent pickup-attempt timestamp |
reason_for_last_failed_pickup | Reason most recent pickup attempt failed |
no_of_pickup_attempts | Total pickup attempts made |
| Shipment dimensions & weight | |
shipment_length | Length declared to the courier (cm) |
shipment_breadth | Breadth declared to the courier (cm) |
shipment_height | Height declared to the courier (cm) |
shipment_weight | Weight billed by the courier (kg) |
uniware_length_cm | Length recorded in the WMS (Uniware) |
uniware_width_cm | Width recorded in the WMS (Uniware) |
uniware_height_cm | Height recorded in the WMS (Uniware) |
courier_volumetric_weight | Volumetric weight calculated by the courier for billing |
| Shipment cost | |
actual_shipment_cost | Final cost billed by the courier |
shipment_cost_without_tax | Shipment cost before tax |
shipment_cost_with_tax | Shipment cost including tax |
shipment_cost_discrepancy | Boolean — billing discrepancy between expected and actual cost |
approximate_cost | Boolean — cost is an estimate, not a confirmed billing amount |
Use it to answer
- What’s the delivery rate and RTO rate by courier partner — who’s actually performing?
- Which pincodes and cities have the worst first-attempt delivery failure rates?
- Which couriers are breaching their committed TAT most often, and by how much?
- What’s our shipment cost per order by zone and courier — where’s the spend going?
- Where are we being overbilled — weight discrepancies vs. WMS, or cost discrepancies vs. expected?
- What are the most common reasons for failed pickups and failed deliveries?
- How does COD vs. Prepaid delivery performance compare — RTO rate, attempts, TAT?
- Which SKUs or sales channels have the highest return, exchange, and cancellation rates?
Available metrics
Everything you can compute on this data model.| Metric | Formula |
|---|---|
| Shipment volume | |
| Total Shipments | Count of all AWB records (one per shipment) |
| Total Orders | Count of distinct order IDs across all shipments |
| Total Items Shipped | Sum of item quantity across all shipments |
| Delivery performance | |
| Delivered Shipments | Count of shipments with a successful delivery status |
| Delivery Rate | Delivered Shipments ÷ Total Shipments × 100 |
| On-Time Delivery Rate | Delivered shipments where actual delivery date ≤ expected delivery date, ÷ Delivered Shipments × 100 |
| TAT Breach Count | Count of shipments that breached the courier’s committed TAT |
| TAT Breach Rate | TAT Breach Count ÷ Total Shipments × 100 |
| Avg Days to Deliver | Avg(actual delivery date − fulfilment creation date) |
| Avg Delivery Attempts | Sum of delivery attempts ÷ Total Shipments |
| Pickup performance | |
| Avg Pickup Attempts | Sum of pickup attempts ÷ Total Shipments |
| First-Attempt Pickup Failure Rate | Shipments where first pickup attempt failed ÷ Total Shipments × 100 |
| RTO & returns | |
| RTO Shipments | Count of shipments marked for Return to Origin |
| RTO Rate | RTO Shipments ÷ Total Shipments × 100 |
| Returned Shipments | Count of shipments where a customer return was initiated |
| Return Rate | Returned Shipments ÷ Delivered Shipments × 100 |
| Exchanged Shipments | Count of shipments resulting in an exchange |
| Exchange Rate | Exchanged Shipments ÷ Delivered Shipments × 100 |
| Cancelled Shipments | Count of shipments that were cancelled |
| Cancellation Rate | Cancelled Shipments ÷ Total Shipments × 100 |
| Shipment cost | |
| Total Shipment Cost | Sum of actual shipment cost |
| Avg Shipment Cost | Total Shipment Cost ÷ Total Shipments |
| Total Cost (Without Tax) | Sum of shipment cost before tax |
| Total Cost (With Tax) | Sum of shipment cost including tax |
| Cost Discrepancy Count | Count of shipments flagged with a billing discrepancy |
| Cost Discrepancy Rate | Cost Discrepancy Count ÷ Total Shipments × 100 |
| Total COD Value | Sum of COD value across applicable shipments |
| Weight & dimensions | |
| Avg Shipment Weight | Avg of courier-billed weight (kg) |
| Avg Volumetric Weight | Avg of courier-calculated volumetric weight |
| Avg Weight Discrepancy | Avg(courier-billed weight − WMS-declared weight) |
Not available in this data model
If you need order economics or pre-purchase customer behaviour, query a different data model.| Looking for… | Use |
|---|---|
| Order-level revenue, AOV, discounts, return refund value, return COGS, Customer Lifetime Value | Sales |
| Pre-purchase browsing — sessions, pageviews, add-to-cart rate, traffic source, funnel CVR | Customer Activity |
| Ad spend, ROAS, CPA by campaign / adset / ad | Meta Ads · Google Ads |