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
Notice period for changes Sunsetting: minimum 2 months;
Versioning: minimum 1 month.
Support Support available via zdirect-tech-support@zalando.de.
No 24/7 support available.

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)
shipment_status string Current shipment status of the return parcel. Possible values: in_transit (picked up by carrier, not yet at warehouse), at_warehouse (delivered to partner warehouse).

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

Returns in transit by shipment status

Count of returns still in transit (picked up by carrier).

COALESCE(
  COUNT(DISTINCT CASE WHEN shipment_status = 'in_transit' THEN return_tracking_number END),
  0
) AS returns_in_transit

Returns at warehouse by shipment status

Count of returns already delivered to your warehouse.

COALESCE(
  COUNT(DISTINCT CASE WHEN shipment_status = 'at_warehouse' THEN return_tracking_number END),
  0
) AS returns_at_warehouse

Returns breakdown by shipment status

Distribution of returns across shipment statuses.

SELECT
  shipment_status,
  COUNT(DISTINCT return_tracking_number) AS return_count,
  SUM(quantity_items_original_order) AS total_items
FROM return_order_in_transit_snapshot
GROUP BY shipment_status

Example data

Sample rows showing the shipment_status column values:

order_number return_tracking_number return_initiated_date shipment_status target_return_completion_date time_to_complete_return
123456789 RTN001 2024-03-15 in_transit 2024-03-25 4
123456790 RTN002 2024-03-10 at_warehouse 2024-03-22 -1
123456791 RTN003 2024-03-22 in_transit 2024-04-01 8

Note: - in_transit: The return parcel has been picked up by the carrier and is currently being transported to your warehouse. - at_warehouse: The return parcel has been delivered to your partner warehouse.

Contact Support