USFANS Spreadsheet: Building a Complete Order Management System
A Guide to Consolidating Product Links, QC Images, Receipts, and Refunds into One Master Sheet
For members of the USFANS community—agents, resellers, and personal shoppers managing orders from platforms like Weidian, Taobao, or 1688—staying organized is the key to efficiency and customer satisfaction. A well-structured master tracking spreadsheet
The Four Pillars of Your Master Sheet
An effective system integrates the following pillars into a single, searchable source of truth.
1. Product Links & Details
This is your foundation. For each item, capture:
- Original Product Link:
- Item Description:
- Price (¥):
- Seller/Store Name:
- Item Description:
Pro Tip: Use hyperlinks for product URLs and data validation lists for status (e.g., To Order, Ordered, Received in Warehouse).
2. QC (Quality Check) Images
Visual verification is non-negotiable. Integrate QC by:
- QC Image Links:
- QC Status & Notes:
- Timestamp:
- QC Status & Notes:
Pro Tip: Use the =IMAGE() function in Google Sheets to embed thumbnail previews of your QC photos directly in the spreadsheet for instant visual checks.
3. Receipts & Financial Logging
Track every yuan spent and earned with precision.
- Agent Invoice/Screenshot:
- Itemized Costs:
- Running Totals:
- Itemized Costs:
Pro Tip: Dedicate a separate tab within your master sheet for a comprehensive financial dashboard using SUMIFS formulas to track spending by month, agent, or category.
4. Returns & Refunds Tracker
Manage issues and recover costs systematically.
- Refund Request Date & Reason:
- Refund Status:
- Refund Amount & Date Received:
- RMA/Return Tracking Number:
- Refund Status:
Pro Tip: Use conditional formatting to highlight rows where refund status is "Denied" or "Processing" for over 7 days, requiring follow-up.
How to Structure Your Master Spreadsheet
Follow this blueprint to build your system in Google Sheets or Excel.
Suggested Column Structure (Your Main Tab)
| Column | Purpose | Data Type/Example |
|---|---|---|
| A: Order Date | Date the item was submitted to the agent. | 2024-11-05 |
| B: Item # / SKU | Unique identifier for the item. | USF-10524-01 |
| C: Product Link & Description | Hyperlink and brief description. | Weidian Link |
| D-E-F: Price, Seller, Status | Core purchase info. | 380¥, "XYZ Store", |
| G: QC Link & Thumbnail | Link to album and embedded image. | =IMAGE("imgur.com/xxx.jpg") |
| H: QC Approval | Status from your review. | |
| I-K: Costs (Product, Shipping, Total) | Financial breakdown. | Formulas to calculate totals. |
| L: Receipt Link | Link to agent's invoice. | Sugargoo Invoice #xxx |
| M-N: Refund Status & Amount | Track returns/refunds. | Completed, 380¥ |
| O: Notes | Any special instructions or issues. | "Exchange for Size 45 if OOS." |
Advanced Automation & Tips
- Dropdown Menus & Data Validation:
- Conditional Formatting:
- Linking Multiple Tabs:=QUERY()=FILTER()
- Use of Apps Script/Macros:
- Conditional Formatting:
Conclusion: From Chaos to Control
Building a USFANS-style master spreadsheet transforms order management from a scattered, stressful process into a streamlined, visual operation. By combining product links, QC images, receipts, and refunds
Next Step: