Return in transit
Overview
The partner_return_order_in_transit_snapshot contains selected metrics related to in-transit returned orders. Returned orders are added to the table the day after the carrier starts processing the parcel. As soon as the returned order is marked as received by you, it will be removed from the table. The information is available at a merchant and sales channel level. The table enables you to gain transparency around the return inflow and the return completion time expected based on CXM requirements.
By design, the partner_return_order_in_transit_snapshot does not include returns for ZFS, ZSS, ZRS sales channels and returns subject to a CXM exception (excluded orders).
Key information
| Property | Description |
|---|---|
| Data granularity | return_tracking_number |
| Primary keys | order_number, return_tracking_number |
| History available | Rolling 2 years |
| Update frequency | Daily |
| Data Retention | Daily Snapshot |
| SLOs | Daily at 8:00 AM UTC |
Table Reference
The table can be accessed using the following data reference from delta sharing client applications:
return_order_in_transit_snapshot_share.direct_data_sharing.return_order_in_transit_snapshot
Schema
| Column name | Format | Description |
|---|---|---|
| account_id | string | The Zalando Partner account ID |
| legal_entity_name | string | The name of the specific legal entity associated with the merchant |
| merchant_id | string | The merchant identifier. Also referred to as the Business Partner Identifier (BPID). For more information, see Merchant Identifier in the Developer Guide |
| country | string | The country of the sales channel through which the offers and the sales were made (e.g. DE for zalando.de; PL for zalando.pl) |
| order_number | string | The number of the order provided by Zalando |
| return_tracking_number | string | The tracking number of the return order provided by Zalando |
| return_initiated_date | date | The date when the carrier starts to process the parcel |
| quantity_items_original_order | int | The number of articles in the original order |
| target_return_completion_date | date | The expected date for the return to be fully processed based on the Reimbursement on Time (RoT) CXM target |
| time_to_complete_return | int | The number of days remaining until the expected Target Return Completion Date based on the Reimbursement on Time (RoT) CXM target. Negative values indicate the Target Return Completion Date has passed. |
| created_at | timestamp | The datetime when the data was written to the table (reference timezone: UTC) |
| updated_at | timestamp | The datetime when the data was updated to the table (reference timezone: UTC) |
Example measures
Below, we provide key KPIs that you can calculate using only the table return_order_in_transit_snapshot. To ensure you calculate the values correctly, please use the code snippets below.
Open returns
Number of return orders currently in transit.
COALESCE(COUNT(DISTINCT return_tracking_number), 0) AS open_returns
Open returned items
Total items currently in transit.
COALESCE(SUM(quantity_items_original_order), 0) AS open_return_items
Average time to target completion (working days)
Positive means days remaining; negative means overdue.
COALESCE(
SUM(time_to_complete_return::double) / NULLIF(COUNT(time_to_complete_return), 0),
0) AS avg_time_to_complete_return
Overdue returns
Count of returns already past target date.
COALESCE(
COUNT(DISTINCT CASE WHEN time_to_complete_return < 0 THEN return_tracking_number END),
0) AS overdue_returns
Due today / due in next 3 days
Useful for operational backlog views.
COALESCE(
COUNT(DISTINCT CASE WHEN time_to_complete_return = 0 THEN return_tracking_number END),
0
) AS due_today_returns,
COALESCE(
COUNT(DISTINCT CASE WHEN time_to_complete_return BETWEEN 0 AND 3 THEN return_tracking_number END),
0
) AS due_next_3d_returns