> ## 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.
# Fulfilment
> Shipment and logistics analytics in Ocular — delivery performance, RTOs, courier efficiency, TAT breaches, and the pickup-to-delivery lifecycle.
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.
## What this data model represents
**Grain:** one row per `awb` (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.
```mermaid theme={null}
flowchart LR
R1["Shipment volume"]:::cat --- S
R2["Delivery performance"]:::cat --- S
R3["Pickup performance"]:::cat --- S
S(["Metrics"]):::root
S --- R4["RTO & returns"]:::cat
S --- R5["Shipment cost"]:::cat
S --- R6["Weight & dimensions"]:::cat
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#2563eb,color:#fff,stroke:#1d4ed8,stroke-width:1.5px
```
```mermaid theme={null}
flowchart LR
v1[Total Shipments]:::leaf --- Vol["Shipment volume"]
v2[Total Orders]:::leaf --- Vol
d1[Delivery Rate]:::leaf --- Del["Delivery performance"]
d2[On-Time Delivery Rate]:::leaf --- Del
d3[Avg Days to Deliver]:::leaf --- Del
p1[Avg Pickup Attempts]:::leaf --- Pick["Pickup performance"]
p2[First-Attempt Pickup Failure Rate]:::leaf --- Pick
Vol:::cat --- S
Del:::cat --- S
Pick:::cat --- S
S(["Metrics"]):::root
S --- Rto["RTO & returns"]:::cat
S --- Cost["Shipment cost"]:::cat
S --- Weight["Weight & dimensions"]:::cat
Rto --- r1[RTO Rate]:::leaf
Rto --- r2[Return Rate]:::leaf
Cost --- c1[Total Shipment Cost]:::leaf
Cost --- c2[Avg Shipment Cost]:::leaf
Weight --- w1[Avg Shipment Weight]:::leaf
Weight --- w2[Avg Weight Discrepancy]:::leaf
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#2563eb,color:#fff,stroke:#1d4ed8,stroke-width:1.5px
classDef leaf fill:#eff6ff,color:#1e3a8a,stroke:#bfdbfe,stroke-width:1px
```
**Dimensions, grouped by category** — every way you can split, filter, or group those metrics. Expand below for examples in each.
```mermaid theme={null}
flowchart LR
D1["Order & shipment"]:::cat --- M
D2["Brand & channel"]:::cat --- M
D3[Courier]:::cat --- M
D4[Status]:::cat --- M
D5[Payment]:::cat --- M
D6[Customer]:::cat --- M
D7["Pickup location"]:::cat --- M
M(["Dimensions"]):::root
M --- D8["Drop location"]:::cat
M --- D9[Product]:::cat
M --- D10["Dates & timeline"]:::cat
M --- D11["Delivery attempts"]:::cat
M --- D12["Pickup attempts"]:::cat
M --- D13["Dimensions & weight"]:::cat
M --- D14["Shipment cost"]:::cat
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#9333ea,color:#fff,stroke:#7e22ce,stroke-width:1.5px
```
```mermaid theme={null}
flowchart LR
os1[order_id]:::leaf --- OS["Order & shipment"]
os2[awb]:::leaf --- OS
bc1[brand]:::leaf --- BC["Brand & channel"]
bc2[sales_channel]:::leaf --- BC
co1[courier_partner]:::leaf --- Co[Courier]
co2[shipment_type]:::leaf --- Co
st1[unified_status]:::leaf --- St[Status]
st2[tat_breach]:::leaf --- St
pm1[payment_method]:::leaf --- Pm[Payment]
pm2[cod_value]:::leaf --- Pm
cu1[customer_id]:::leaf --- Cu[Customer]
cu2[customer_name]:::leaf --- Cu
pl1[pickup_city]:::leaf --- PL["Pickup location"]
pl2[pickup_state]:::leaf --- PL
OS:::cat --- M
BC:::cat --- M
Co:::cat --- M
St:::cat --- M
Pm:::cat --- M
Cu:::cat --- M
PL:::cat --- M
M(["Dimensions"]):::root
M --- DL["Drop location"]:::cat
M --- Pr[Product]:::cat
M --- Dt["Dates & timeline"]:::cat
M --- DA["Delivery attempts"]:::cat
M --- PA["Pickup attempts"]:::cat
M --- DW["Dimensions & weight"]:::cat
M --- SC["Shipment cost"]:::cat
DL --- dl1[drop_city]:::leaf
DL --- dl2[drop_state]:::leaf
DL --- dl3[zone]:::leaf
Pr --- pr1[sku]:::leaf
Pr --- pr2[quantity]:::leaf
Dt --- dt1[order_creation_date]:::leaf
Dt --- dt2[actual_delivery_date]:::leaf
DA --- da1[no_of_delivery_attempts]:::leaf
PA --- pa1[no_of_pickup_attempts]:::leaf
DW --- dw1[shipment_weight]:::leaf
DW --- dw2[courier_volumetric_weight]:::leaf
SC --- sc1[actual_shipment_cost]:::leaf
SC --- sc2[shipment_cost_discrepancy]:::leaf
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#9333ea,color:#fff,stroke:#7e22ce,stroke-width:1.5px
classDef leaf fill:#faf5ff,color:#581c87,stroke:#e9d5ff,stroke-width:1px
```
**Source:** Clickpost (logistics aggregator), which normalises tracking and fulfilment data across every connected courier partner into a single feed. Refreshed near real-time — records append on each status-change event. Requires the OMS (Shopify, Unicommerce / Uniware) for order and SKU linkage.
**What's special:** courier-level fragmentation is normalised away. Each courier has its own status taxonomy, cost structure, and SLA definition — this data model resolves them into a unified status schema, standardises cost across pre-tax and post-tax, and surfaces weight discrepancies between what the WMS booked and what the courier billed. So `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](/data-models/sales) |
| Pre-purchase browsing — sessions, pageviews, add-to-cart rate, traffic source, funnel CVR | [Customer Activity](/data-models/customer-activity) |
| Ad spend, ROAS, CPA by campaign / adset / ad | [Meta Ads](/data-models/meta-ads/overview) · [Google Ads](/data-models/google-ads/overview) |