Author: K M Kadir Koushik
Domain: Risk Analytics | Buyer Fraud & Behavioral Risk
Stack: ODPS SQL (MaxCompute) | Alibaba Cloud Data Warehouse
Venture: South Asian E-Commerce Platform
This project investigates a recurring pattern in doorstep delivery failures where the same buyer accumulates multiple consecutive failed delivery (FD) attempts with no successful delivery in between, despite all orders being processed and shipped within SLA.
Between 18% and 25% of all SLA-compliant failed deliveries were found to follow this behavioral pattern, representing a significant and previously undetected source of logistics cost leakage.
A buyer with a strong historical delivery success rate can shift into a streak of repeated doorstep rejections without triggering any risk rule. This happens because the current rule engine evaluates a buyer's aggregate behavior over a static 6-month window, meaning recent behavioral deterioration is diluted by historical performance.
By the time the system reclassifies a buyer as high risk, the business has already absorbed the cost of multiple failed delivery attempts.
Timeline of a buyer slipping through the rule gap:
Month 1-5 → Strong success rate, classified as Good buyer
Month 6 → Begins rejecting orders at doorstep repeatedly
Month 6+ → Rule still sees a "Good" buyer based on 6-month average
FD costs accumulate before any flag is triggered
- All deliveries where order creation to first delivery attempt was completed within SLA
- This deliberately excludes late deliveries to isolate pure buyer behavioral signals
- Analysis window: April 2026, May 2026, June 2026 (with 6-month trailing lookback per month)
Buyers were segmented based on their delivery attempt behavior after their last successful delivery:
| Cohort | Definition |
|---|---|
| 10+ Consecutive FD | 10 or more failed deliveries with no success in between |
| 5-10 Consecutive FD | 5 to 10 failed deliveries with no success in between |
| 3-4 Consecutive FD | 3 to 4 failed deliveries with no success in between |
| Exactly 2 FD | Exactly 2 consecutive failures after last success |
| Non-Consecutive | At least 2 FDs and 1 SD, last 2 packages not both failed |
Anchor month approach: For each monthly cohort, buyers are identified by having at least one failed delivery in the anchor month. The lookback window then traces back 6 months to find their last successful delivery and count consecutive failures after it.
Exclusion of buyers with no prior success: Buyers who had no successful delivery in the trailing 6-month window are excluded. This isolates behavioral transition cases, buyers who previously demonstrated normal behavior but have shifted.
Package-level grain: All metrics are counted at the tracking number level, not order level, to accurately reflect logistics cost per delivery attempt.
Delivery channel split: DEX (platform-owned last mile) and 3PL (third-party logistics partners) are analysed separately since their failure rate profiles differ structurally.
The following dimensions were investigated to determine whether the consecutive FD pattern was driven by a specific factor rather than pure buyer behavior:
| Dimension | Finding |
|---|---|
| Product category | No significant concentration |
| Specific product | No significant concentration |
| Delivery region | No significant concentration |
| Buyer-seller collusion signals | No significant concentration |
| Order value bucket | No significant concentration |
| Affiliate sourced orders | No significant concentration |
| Campaign vs non-campaign period | No significant concentration |
Conclusion: The pattern is not attributable to any product, operational, or commercial factor. It is a buyer-level behavioral signal, which means the intervention must target buyer risk classification rather than product or logistics policy.
The consecutive FD cohort follows a predictable monthly pattern. Across April, May, and June 2026, the distribution of buyers across cohorts remained stable, suggesting this is a structural behavioral segment of the buyer base, not a one-off anomaly.
The critical gap is systemic: the rule engine classifies buyers based on a static trailing window. A buyer who was genuinely reliable for 5 months and has started rejecting orders in month 6 looks identical to a stable buyer in aggregate. The rate of change in behavior is entirely invisible to the current system.
The current buyer risk rule evaluates aggregate behavior over a fixed historical window. It has no mechanism to detect:
- Behavioral velocity: how quickly a buyer is transitioning from good to bad
- Recency weighting: giving more weight to recent delivery outcomes than older ones
- Transition signals: early indicators that precede a shift in buyer classification
Replace the static lookback with a predictive algorithm that tracks the rate of transition from Good or Descent buyer status toward Bad.
The approach would work as follows:
- Compute a rolling FD rate using a moving average (e.g., last 10, 20, 30 delivery attempts)
- Empirically test what rate-of-change threshold reliably precedes a shift to Bad classification across the buyer base
- If a threshold can be validated for 90%+ of buyers, codify it as a proactive flag that triggers before the buyer formally shifts to Bad
This converts the rule from a lagging indicator (reacts after damage) to a leading indicator (flags during the transition period).
Next step if aligned: Raise formal request with Data Science team, prepare PMO documentation and Business Requirements Document (BRD).
As an interim measure before the predictive model is built, weight the success rate calculation to give more influence to recent delivery outcomes versus those from 5 to 6 months ago. This is a lower-complexity change that can be implemented within the existing rule framework.
- Investigate the reverse transition: buyers moving from Bad back to Good, to avoid over-blocking recovering buyers
- Prioritise intervention on cohorts with highest monetary (GMV) impact rather than highest volume
- Explore pre-RTS cancellation behavior as an early indicator of impending FD streaks
ecommerce-consecutive-failed-delivery-analysis/
├── README.md # This file
├── sql/
│ ├── 01_base_foundation.sql # Core data foundation and outcome classification
│ ├── 02_cohort_bucketing.sql # Monthly cohort analysis with FD behavior buckets
│ ├── 03_delivery_channel_split.sql # DEX vs 3PL breakdown per cohort
│ └── 04_monthly_fd_trend.sql # Monthly failed delivery trend by channel
├── docs/
│ ├── methodology.md # Detailed analytical methodology
│ ├── findings.md # Pattern-level findings across cohorts
│ └── solution_proposal.md # Full solution proposal with stakeholder inputs
└── assets/
└── cohort_framework.md # Visual explanation of cohort definitions
- Advanced ODPS SQL: Multi-CTE query architecture, window functions (ROW_NUMBER, LAG), self-joins for rolling window calculations, conditional aggregation
- Risk Analytics Design: Cohort-based behavioral segmentation, trailing window analysis, false positive management
- Business Problem Framing: Translating an operational pain point into a measurable, analytically tractable problem
- Stakeholder Communication: Presenting findings at both technical and executive level with clear recommendations
This project was conducted as part of a G2N (Gross-to-Net) risk initiative at a major South Asian e-commerce platform. All table names, column names, and data values in the SQL files have been anonymised for public sharing. The analytical logic, methodology, and findings are original work.