Fulfilment - Ocular

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.

Show sample metrics in each category

Dimensions, grouped by category — every way you can split, filter, or group those metrics. Expand below for examples in each.

Show sample dimensions in each category

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
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

​What this data model represents

​Slice by

​Use it to answer

​Available metrics

​Not available in this data model

What this data model represents

Slice by

Use it to answer

Available metrics

Not available in this data model