Refunds

Lost Parcel Recovery: Fetch Alerts Workflow

A common headache for dropshippers is the lost parcel. It's not just a customer service issue; it's a direct hit to your profit margin if you don't recover the cost. AliExpress parcels can go missing for various reasons: incorrect address, customs issues, or simply getting stuck in transit. The key to mitigating these losses is early detection and a structured recovery workflow.

The Problem: Detecting Lost Parcels Proactively

Manually checking every order's logistics status for potential issues is not scalable. You need a system that flags problematic orders before the buyer even realizes there's an issue or, worse, before the refund window closes on AliExpress. Fetch Order Tracking, integrated with your Google Sheet, provides the data points and automation necessary to build this system.

Specifically, we're looking for two primary logistics statuses that indicate a high probability of a lost or undeliverable parcel:

• DELIVERY_FAILED: The carrier attempted delivery but was unsuccessful. This can mean the package is being held at a local post office, returned to sender, or simply stuck.
• EXPIRED: The tracking number has been active for an extended period, but no further updates or delivery attempts have occurred. This often means the package is lost in transit or significantly delayed beyond typical delivery times.

Building the Recovery Workflow with Fetch Alerts

Your Fetch-powered Google Sheet already pulls in the logistics_status for all your AliExpress orders. The goal is to set up conditional formatting and automated alerts that bring these specific statuses to your attention immediately.

Step 1: Identify Key Data Fields

Ensure your Fetch setup includes these columns:

• order_id (AliExpress order ID)
• logistics_status
• order_status
• gmt_create (AliExpress order creation timestamp)
• gmt_refund (Timestamp of refund request, if any)
• end_reason (Reason for order closure, if any)
• tracking_number

Step 2: Set Up Conditional Formatting for Visual Alerts

In your Google Sheet, apply conditional formatting to the logistics_status column (e.g., Column G). This provides an immediate visual cue.

1. Select the entire logistics_status column.
2. Go to Format > Conditional formatting.
3. Add a new rule:
   • Format rules: 'Custom formula is'
   • Formula: =OR(G1="DELIVERY_FAILED",G1="EXPIRED") (Adjust G1 to your actual column letter)
   • Formatting style: Choose a bright, attention-grabbing color (e.g., red fill, white text).

This makes any 'DELIVERY_FAILED' or 'EXPIRED' status immediately visible.

Step 3: Create an Automated Email Alert (Optional but Recommended)

While conditional formatting is good, an email alert ensures you don't miss anything. Use Google Apps Script to send daily or weekly summaries of problematic orders.

1. Go to Extensions > Apps Script.
2. Paste the following script (modify as needed):

   function sendLostParcelAlerts() {
     const ss = SpreadsheetApp.getActiveSpreadheet();
     const sheet = ss.getSheetByName('Your Orders Sheet Name'); // Change this to your sheet name
     const data = sheet.getDataRange().getValues();
     
     let alerts = [];
     // Assuming logistics_status is in column G (index 6)
     // Assuming order_id is in column A (index 0)
     
     for (let i = 1; i < data.length; i++) { // Skip header row
       const logisticsStatus = data[i][6]; 
       const orderId = data[i][0];
       const trackingNumber = data[i][8]; // Assuming tracking_number is in column I (index 8)
       const gmtRefund = data[i][5]; // Assuming gmt_refund is in column F (index 5)
   
       if ((logisticsStatus === 'DELIVERY_FAILED' || logisticsStatus === 'EXPIRED') && gmtRefund === '') {
         alerts.push(`Order ID: ${orderId}, Tracking: ${trackingNumber}, Status: ${logisticsStatus}`);
       }
     }
   
     if (alerts.length > 0) {
       const subject = `Fetch Alert: ${alerts.length} Potential Lost Parcels`;
       const body = `The following orders require your attention:
   
   ${alerts.join('\n')}
   
   Review your Fetch Order Tracking sheet for details.`;
       MailApp.sendEmail('your_email@example.com', subject, body); // Change to your email address
     }
   }
   

3. Save the script.
4. Set up a trigger: Click the clock icon on the left (Triggers), then 'Add Trigger'.
   • Choose which function to run: sendLostParcelAlerts
   • Choose deployment where function is executed: Head
   • Select event source: Time-driven
   • Select type of time-based trigger: Day timer (or Week timer)
   • Select time of day: Choose a convenient time.

This script will email you a list of orders with 'DELIVERY_FAILED' or 'EXPIRED' statuses that haven't had a refund initiated yet.

Proactive detection of 'DELIVERY_FAILED' and 'EXPIRED' logistics statuses is critical. Waiting for the buyer to complain means you've lost valuable time and potentially missed refund windows.

Step 4: Implement Your Recovery Protocol

Once an alert is triggered, follow these steps:

1. Check Tracking Details: Open the AliExpress order and review the detailed tracking information. Sometimes 'DELIVERY_FAILED' means the package is at a local post office for pickup.
2. Contact AliExpress Seller: If the status is 'EXPIRED' or 'DELIVERY_FAILED' without a clear resolution, contact the AliExpress seller immediately. Provide the order ID and tracking number. Ask for clarification and a solution.
3. Open a Dispute (if necessary): If the seller is unhelpful or unresponsive, or if the package is confirmed lost, initiate an AliExpress dispute.
4. Select 'Goods not received' as the dispute reason.
5. Provide evidence: Screenshot the Fetch data showing logistics_status as 'DELIVERY_FAILED' or 'EXPIRED', and any communication with the seller.
6. Request a full refund.
7. Communicate with eBay Buyer: Decide on your communication strategy. Some dropshippers prefer to wait until they have a resolution from AliExpress before contacting the buyer. Others proactively inform the buyer of a delay and offer a reshipment or refund. This depends on your customer service policy.
8. Update eBay Order: Once the AliExpress refund is processed, update the corresponding eBay order. If you're refunding the buyer, process the refund on eBay. If you're reshipping, update the new tracking number.

Step 5: Monitor Refund Status

Your Fetch sheet also tracks the gmt_refund field. Once you initiate a dispute on AliExpress, this field will populate. This allows you to track which 'lost' parcels have an active refund process. You can refine your conditional formatting or email alerts to exclude orders where gmt_refund is already populated, focusing only on new issues.

For example, modify the conditional formatting formula:

=AND(OR(G1="DELIVERY_FAILED",G1="EXPIRED"),ISBLANK(F1)) (Assuming gmt_refund is in Column F)

Beyond Lost Parcels: Other Alert Opportunities

This same alert framework can be extended to detect other critical events:

• Buyer Refused Goods: Monitor logistics_status for specific carrier codes indicating refusal.
• Order Closed Without Delivery: Watch for order_status changing to 'FINISH' with an end_reason like 'BUYER_REJECT_GOODS' or 'CLOSE_ORDER_BY_PLATFORM_NOT_PAY' (though the latter is less common for dropshipping).
• Early Refund Requests: Track gmt_refund for orders where the logistics_status is still 'IN_TRANSIT', indicating a buyer-initiated refund request before delivery.

By leveraging Fetch Order Tracking's real-time data and Google Sheets' automation capabilities, you can transform your reactive problem-solving into a proactive, profit-protecting workflow.

Ready to streamline your refund detection and recovery? Learn more about Fetch Order Tracking.

---