Sheet 6: CHI_PHI_VAN_CHUYEN — Phân Tích Chi Phí

// Chi phí vận chuyển theo kênh bán
=SUMIFS('DON_HANG'!L:L,'DON_HANG'!D:D,"Shopee",
        'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

// Chi phí vận chuyển trung bình mỗi đơn
=AVERAGEIFS('DON_HANG'!L:L,
            'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

// Chi phí ship theo kg (nếu ghi nhận trọng lượng)
=SUMIFS(L:L,C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) /
SUMIFS(Q:Q,C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))  // Q = Trọng lượng

// Chi phí hoàn hàng (ship đi + ship về)
=COUNTIFS(I:I,"Hoàn hàng",C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) * AVERAGE(L:L) * 2

Sheet 7: BAO_CAO_HOAN_HANG — Phân Tích Return Rate

// Tỷ lệ hoàn hàng theo ĐVVC
=COUNTIFS('DON_HANG'!G:G,"GHTK",'DON_HANG'!I:I,"Hoàn hàng",
          'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) /
COUNTIFS('DON_HANG'!G:G,"GHTK",
         'DON_HANG'!I:I,"<>Chờ xử lý",
         'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

// Tỷ lệ hoàn theo sản phẩm (nếu ghi nhận)
=COUNTIFS('DON_HANG'!R:R,R2,'DON_HANG'!I:I,"Hoàn hàng") /
COUNTIFS('DON_HANG'!R:R,R2,'DON_HANG'!I:I,"<>Chờ xử lý")

// Lý do hoàn hàng phổ biến
=QUERY('DON_HANG'!A:S,
  "SELECT S, COUNT(A) 
   WHERE I = 'Hoàn hàng'
   GROUP BY S ORDER BY COUNT(A) DESC")

Sheet 8: NHAT_KY_VAN_CHUYEN — Log Theo Dõi Trạng Thái

// Ghi log mỗi lần trạng thái đơn thay đổi (Apps Script)
function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'DON_HANG') return;
  
  const col = e.range.getColumn();
  const statusCol = 9; // Cột I = Trạng Thái
  
  if (col === statusCol) {
    const logSheet = SpreadsheetApp.getActiveSpreadsheet()
                                   .getSheetByName('NHAT_KY_VAN_CHUYEN');
    const row = e.range.getRow();
    const orderCode = sheet.getRange(row, 1).getValue();
    
    logSheet.appendRow([
      new Date(),
      orderCode,
      e.oldValue || 'Mới tạo',
      e.value,
      Session.getActiveUser().getEmail()
    ]);
  }
}

Formulas Quan Trọng cho Logistics

Tracking Trạng Thái Đơn Hàng

// Color coding tự động theo trạng thái (Conditional Formatting)
Trạng Thái "Đang giao" → Màu xanh dương
Trạng Thái "Đã giao" → Màu xanh lá
Trạng Thái "Hoàn hàng" → Màu đỏ
Trạng Thái "Giao trễ" → Màu cam
Trạng Thái "Chờ xử lý" → Màu vàng

// Formula kiểm tra giao trễ (cho Conditional Formatting)
=AND(I2<>"Đã giao",I2<>"Hoàn hàng",M2"Đã giao")*
        ('DON_HANG'!I:I<>"Hoàn hàng"))

Cost Per Order Analysis

// Chi phí vận chuyển % doanh thu
=SUMIFS('DON_HANG'!L:L,'DON_HANG'!I:I,"Đã giao",
        'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) /
SUMIFS('DON_HANG'!J:J,'DON_HANG'!I:I,"Đã giao",
       'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

// Lợi nhuận sau phí giao hàng
=SUMIFS('DON_HANG'!J:J,'DON_HANG'!I:I,"Đã giao",
        'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) -
SUMIFS('DON_HANG'!L:L,'DON_HANG'!I:I,"Đã giao",
       'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) -
COUNTIFS('DON_HANG'!I:I,"Hoàn hàng",
         'DON_HANG'!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)) * AVERAGE('DON_HANG'!L:L) * 2

Hướng Dẫn Tích Hợp Shopee/Lazada

Import Đơn Hàng Từ Shopee

1. Đăng nhập Shopee Seller Center
2. Vào "Đơn Hàng" → "Tất cả đơn hàng"
3. Lọc theo ngày → Xuất CSV
4. Mở file CSV trong Google Sheets
5. Copy cột cần thiết vào template

Mapping cột Shopee CSV → Template:

Shopee CSV Column → Template Column
"Order ID" → Cột B (Mã Đơn Kênh)
"Order Creation Date" → Cột C (Ngày Tạo)
"Buyer Username" → Cột E (Khách Hàng)
"Shipping Address" → Cột F (Địa Chỉ Giao)
"Tracking Number" → Cột H (Mã Vận Đơn)
"Order Total" → Cột J (Giá Trị Hàng)
"Shipping Fee Paid by Buyer" → Cột L (Phí Ship)
"Order Status" → Cột I (Trạng Thái - cần map lại)

Apps Script Tự Động Import

// Tự động import từ Google Drive (khi upload CSV vào Drive)
function importShopeeOrders() {
  const folderId = 'YOUR_FOLDER_ID'; // Folder chứa Shopee CSV
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.CSV);
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const orderSheet = ss.getSheetByName('DON_HANG');
  const processedSheet = ss.getSheetByName('PROCESSED_FILES');
  
  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    
    // Kiểm tra file đã xử lý chưa
    const processedData = processedSheet.getDataRange().getValues();
    const alreadyProcessed = processedData.some(row => row[0] === fileName);
    if (alreadyProcessed) continue;
    
    // Parse CSV và import vào DON_HANG
    const content = file.getBlob().getDataAsString('UTF-8');
    const rows = Utilities.parseCsv(content);
    
    for (let i = 1; i < rows.length; i++) {
      const row = rows[i];
      // Map Shopee columns to template columns
      orderSheet.appendRow([
        'ORD-' + new Date().getFullYear() + '-' + String(orderSheet.getLastRow()).padStart(5,'0'),
        row[0],  // Order ID
        new Date(row[1]),  // Order date
        'Shopee',
        row[5],  // Buyer name
        row[6],  // Address
        mapShipper(row[10]),  // Logistics provider
        row[11], // Tracking number
        mapStatus(row[3]),  // Status
        parseFloat(row[13]) || 0,  // Order total
        parseFloat(row[13]) || 0,  // COD amount
        parseFloat(row[14]) || 0,  // Shipping fee
      ]);
    }
    
    // Đánh dấu file đã xử lý
    processedSheet.appendRow([fileName, new Date(), rows.length - 1 + ' đơn']);
  }
}

function mapStatus(shopeeStatus) {
  const statusMap = {
    'PAID': 'Chờ xử lý',
    'IN_CANCEL': 'Chờ xử lý',
    'TO_SHIP': 'Chờ xử lý',
    'SHIPPED': 'Đang giao',
    'TO_CONFIRM_RECEIVE': 'Đang giao',
    'COMPLETED': 'Đã giao',
    'CANCELLED': 'Hoàn hàng'
  };
  return statusMap[shopeeStatus] || shopeeStatus;
}

Phân Tích COD Reconciliation Chi Tiết

Quy Trình Đối Soát COD Hàng Tuần

Bước 1: Mỗi thứ 6, tải bảng đối soát từ GHTK/GHN/J&T
Bước 2: Import vào sheet COD_BANK (sao kê bank)
Bước 3: Chạy công thức so khớp

// So khớp COD với sao kê ngân hàng
=IFERROR(
  VLOOKUP(H2,'COD_BANK'!B:D,3,FALSE),
  "⚠️ Không tìm thấy trong sao kê"
)

// Tổng hợp chênh lệch theo tuần
=SUMPRODUCT((WEEKNUM('DON_HANG'!C:C)=WEEKNUM(TODAY()))*
            ('DON_HANG'!I:I="Đã giao")*
            ('DON_HANG'!K:K)) -
SUMPRODUCT((WEEKNUM('COD_BANK'!A:A)=WEEKNUM(TODAY()))*'COD_BANK'!C:C)

Dashboard COD Real-time

// COD đang "trên đường" (đã giao nhưng chưa về tài khoản)
=SUMIFS('DON_HANG'!K:K,'DON_HANG'!I:I,"Đã giao",'DON_HANG'!O:O,"Chưa")

// COD quá hạn đối soát (đã giao > 7 ngày chưa về)
=SUMIFS('DON_HANG'!K:K,
        'DON_HANG'!I:I,"Đã giao",
        'DON_HANG'!O:O,"Chưa",
        'DON_HANG'!N:N,"<"&TODAY()-7)

// Số ngày trung bình từ giao hàng đến nhận COD (tính từ lịch sử)
=AVERAGEIFS(COD_BANK!A:A-'DON_HANG'!N:N,
            'DON_HANG'!O:O,"Đã đối soát")

Báo Cáo Tỷ Lệ Giao Thành Công / Thất Bại / Hoàn Hàng

Bảng Phân Tích Return Rate Theo Nhiều Chiều

// Return rate theo ĐVVC (cho thấy nên dùng đơn vị nào)
GHTK: =COUNTIFS(G:G,"GHTK",I:I,"Hoàn hàng") / COUNTIFS(G:G,"GHTK",I:I,"<>Chờ xử lý")
GHN:  =COUNTIFS(G:G,"GHN",I:I,"Hoàn hàng")  / COUNTIFS(G:G,"GHN",I:I,"<>Chờ xử lý")
J&T:  =COUNTIFS(G:G,"J&T Express",I:I,"Hoàn hàng") / COUNTIFS(G:G,"J&T Express",I:I,"<>Chờ xử lý")

// Return rate theo tỉnh/thành phố (để tối ưu chiến lược ship)
=COUNTIFS('DON_HANG'!F:F,"*Hà Nội*",'DON_HANG'!I:I,"Hoàn hàng") /
COUNTIFS('DON_HANG'!F:F,"*Hà Nội*",'DON_HANG'!I:I,"<>Chờ xử lý")

// Return rate theo kênh bán
=COUNTIFS('DON_HANG'!D:D,"Shopee",'DON_HANG'!I:I,"Hoàn hàng") /
COUNTIFS('DON_HANG'!D:D,"Shopee",'DON_HANG'!I:I,"<>Chờ xử lý")

// Xu hướng return rate 12 tháng (mảng dữ liệu cho chart)
=ARRAYFORMULA(
  COUNTIFS('DON_HANG'!C:C,">="&DATE(2026,ROW(INDIRECT("1:12")),1),
            'DON_HANG'!C:C,"<="&DATE(2026,ROW(INDIRECT("1:12"))+1,0),
            'DON_HANG'!I:I,"Hoàn hàng") /
  COUNTIFS('DON_HANG'!C:C,">="&DATE(2026,ROW(INDIRECT("1:12")),1),
            'DON_HANG'!C:C,"<="&DATE(2026,ROW(INDIRECT("1:12"))+1,0),
            'DON_HANG'!I:I,"<>Chờ xử lý")
)

Tối Ưu Hóa Chi Phí Vận Chuyển

So Sánh Giá Các ĐVVC

Sheet BANG_GIA_DVVC:
ĐVVC | Zone 1 (Nội thành) | Zone 2 (Ngoại thành) | Zone 3 (Tỉnh) | Zone 4 (Vùng sâu)
     | Giá 1-3kg | Giá 3-10kg | Giá >10kg
GHTK | 22,000 | 30,000 | 35,000 | 45,000
GHN  | 20,000 | 28,000 | 33,000 | 43,000
J&T  | 18,000 | 25,000 | 30,000 | 40,000

// Tự động gợi ý ĐVVC rẻ nhất theo địa chỉ và trọng lượng
=INDEX(BANG_GIA_DVVC!A:A,
  MATCH(MIN(VLOOKUP(getZone(F2),BANG_GIA_DVVC!B:E,getWeightCol(Q2),FALSE)),
        VLOOKUP(getZone(F2),BANG_GIA_DVVC!B:E,getWeightCol(Q2),FALSE),0))

KPI và Mục Tiêu

Benchmark industry Việt Nam 2027:
- Tỷ lệ giao thành công: ≥ 90% (mục tiêu tốt: ≥ 95%)
- Return rate: ≤ 10% (mục tiêu tốt: ≤ 5%)
- On-time delivery: ≥ 85%
- Chi phí ship/doanh thu: ≤ 8% (tốt: ≤ 5%)
- COD reconciliation: ≤ 3 ngày sau giao

// So sánh với benchmark
=IF(successRate>=0.95,"🟢 Xuất sắc",IF(successRate>=0.90,"🟡 Đạt yêu cầu","🔴 Cần cải thiện"))

Câu Hỏi Thường Gặp

Kết Luận

Template Google Sheets quản lý giao hàng vận chuyển là công cụ thiết yếu cho mọi shop online có từ 50 đơn/ngày trở lên. Với COD reconciliation chính xác, phân tích return rate đa chiều, và theo dõi shipper performance, bạn sẽ kiểm soát hoàn toàn hoạt động logistics — giảm chi phí và tăng tỷ lệ giao thành công.

Cần template đã xây dựng sẵn hoặc tư vấn tích hợp với GHTK/GHN API? Liên hệ SheetStore để được hỗ trợ.