<aside> 📌
Role: Growth & Marketplace Analytics Lead Platform: Flipkart Seller Ecosystem (Ads, FBF, Big Billion Days / BBD Prep) Category: Apparel / Home Goods (Mid-tier seller, multi-city dispatch) Duration: 8 Weeks (Leading into Peak Season)
</aside>
On paper, the seller looked fine. GMV was growing, orders were coming in, and the dashboard showed a business trending in the right direction. But the unit economics told a different story: net margins were quietly eroding, and nobody had gone looking for the cause.
The culprit was RTO leakage. Return to Origin rates were eating into profitability in ways the blended averages were hiding, and with Big Billion Days approaching, scaling ad spend or inventory on top of a broken foundation would have made things significantly worse, not better.
The approach was to go upstream: audit warehouse logs, RTO patterns, and pin-code-level buyer behavior using SQL and BigQuery before touching a single rupee of ad budget. Build the BBD strategy on solid ground, then scale.
<aside> 🏆
Headline Results
Before making any changes, I ran a full SQL and BigQuery audit joining three data sources: Flipkart seller dashboard exports, third-party logistics delivery logs, and historical RTO records broken down by pin-code, payment method, and SKU category. What came back identified three structural problems.
<aside> 🔴
The RTO Sinkhole. Blended return rates looked manageable at the account level. But when segmented by pin-code and payment method, Tier-2 and Tier-3 COD orders were driving return rates above 30%. These weren't random. Specific pin-codes had chronic repeat-return behavior that was being averaged out of visibility. During a normal month, this was painful. During BBD, with order volumes spiking 4x to 6x, the same pin-codes would have turned into a cash drain at scale.
</aside>
<aside> 🔴
Blind Ad Spending. The PLA campaigns were running on broad, unsegmented keywords with no match-type discipline. High-volume generic terms were eating budget and delivering weak conversion rates. ACoS on these broad terms was well above profitable thresholds, and nobody had segmented performance by keyword intent or product category. The ad account was optimizing for impressions, not margin.
</aside>
<aside> 🔴
Inventory & Fulfillment Drag. Self-fulfilled orders dispatched through third-party logistics were clocking delivery delays of 5 to 7 days in several corridors. Flipkart's search ranking algorithm penalizes sellers with poor delivery SLAs, which was quietly suppressing organic search placement across key categories. Heading into BBD, poor SRP rankings meant paid traffic would have to do even more heavy lifting, at a higher cost.
</aside>
Three phases, sequenced to protect margin first, then scale from a stable base.
<aside> 🎯
Goal: Stop the RTO bleed before scaling anything else.
</aside>