---
# System prepended metadata

title: 農純鄉會員分群

---

## 農純鄉會員分群

https://drive.google.com/drive/folders/1carj59KIRIT5Zej5FyfmiPQqlW-3k6WJ

資料準備：
1. 下載農純鄉會員流動名單
```
nongchunxiang-ga4.prod_mart.mart-napl_report
```

2.至 [[農純鄉] 自動化_回購預測 + 分群](https://docs.google.com/spreadsheets/d/1ib1o12Tg9WlZFBMvWC0yZZ7sZ75_LX2QCaaU2HTz4KM/edit?gid=382761811#gid=382761811)

3.打開APP SCRIPT
![截圖 2025-01-20 14.29.06](https://hackmd.io/_uploads/HJNV1vjw1l.png)


4.執行第一步- 設定日期（無需修改程式碼）
![截圖 2025-01-20 14.30.35](https://hackmd.io/_uploads/BJ9F1wow1e.png)


5.執行firststep
![截圖 2025-01-20 14.29.57](https://hackmd.io/_uploads/S1mDJwjP1l.png)


6.取得新報表連接
    a. 可在**Execution log**中取得（直接用這個ID 導向新報表）
    ![image](https://hackmd.io/_uploads/HJJsgwiPJx.png)
    b.
    至[[農純鄉] 自動化_回購預測 + 分群](https://docs.google.com/spreadsheets/d/1ib1o12Tg9WlZFBMvWC0yZZ7sZ75_LX2QCaaU2HTz4KM/edit?gid=382761811#gid=382761811)取得新報表ID
    ![image](https://hackmd.io/_uploads/B1Kebwowyx.png)
    

7.確認新報表有建立好
**補截圖**

8. 點選**整體**，並在這個工作表中匯入 ```nongchunxiang-ga4.prod_mart.mart-napl_report```名單，（因為名單大小操過BQ API上限所以只能用手動匯入）
![截圖 2025-01-20 14.47.38](https://hackmd.io/_uploads/By5KXDivyl.png)
![截圖 2025-01-20 14.54.06](https://hackmd.io/_uploads/HJ-zSPoPJg.png)


9.調整Q, R欄轉成數值 (```lifetime_cnt```,```	lifetime_total_amount```)

![截圖 2025-01-22 11.34.16](https://hackmd.io/_uploads/HJ8NYATPye.png)


10. 執行step 2,3,4,5,6 每一步確認執行成功資料無誤即可執行下一步

![截圖 2025-01-22 11.35.38](https://hackmd.io/_uploads/BJYtFA6v1g.png)
![截圖 2025-01-22 11.35.56](https://hackmd.io/_uploads/HyysYRTPyg.png)
![截圖 2025-01-22 11.36.23](https://hackmd.io/_uploads/HyShK0pvJg.png)
![截圖 2025-01-22 11.36.32](https://hackmd.io/_uploads/ryCnYApvJx.png)
![截圖 2025-01-22 11.36.43](https://hackmd.io/_uploads/SyjatC6wke.png)


11. 推薦商品，因目前無自動化，且推薦品類變動不大，故複製前個月即可，在整體、寶寶、非寶寶工作表中新增推薦品類欄位，並使用vlookup公式 
```=VLOOKUP(H2, '用戶分群名單'!A:J, 10, FALSE)```

![截圖 2025-01-22 11.37.58](https://hackmd.io/_uploads/BkBMqCpDyg.png)
![截圖 2025-01-22 11.39.07](https://hackmd.io/_uploads/SkcI5RTwke.png)


12. 確認分群流動數字無異常（小於1%可視正常 ）

![image](https://hackmd.io/_uploads/ryZh90avyg.png)

14. 近一年平均需手動計算（不含上個月的平均數）

![image](https://hackmd.io/_uploads/SJsU2CTP1e.png)









以下為APPSRIPT程式碼
```

// 設定本期時間並返回台灣年份和月份
function title() {
  // Create a date object for the current date in Taiwan's time zone
  let currentDate = new Date(new Date().toLocaleString("en-US", {timeZone: "Asia/Taipei"}));
  
  // Calculate Taiwan year (Republic Era)
  let tw_year = currentDate.getFullYear() - 1911;
  
  // Get the current month (0-indexed)
  let currentMonth = currentDate.getMonth();
  
  // Calculate the previous month
  let month;
  if (currentMonth === 0) {  // If it's January
    month = 12;
    tw_year--;  // Decrease the year
  } else {
    month = currentMonth;  // Current month is already the previous month (0-indexed)
  }

  
  console.log("Current date :", currentDate);
  console.log("Taiwan year:", tw_year, "Previous Month:", month);
  
  return [tw_year, month];
}
// 設定完時間，執行第一步：初始化新的報表
// 建立新的試算表並清除特定工作表的內容
function firstStep(){
  let year_month = title();
  let year = year_month[0];
  let month = year_month[1];
  // 建立 當月試算表
  let reportId = nextMonthReport(year, month);
  
  // 紀錄 試算表 ID
  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SpreadSheetId");
  ss.insertRowsBefore(1, 1);
  ss.getRange(1,1).setValue(year+'/'+month+'_回購預測與分群');
  ss.getRange(1,2).setValue(reportId);
}

// 建立新月份的報表
function nextMonthReport(year, month) {
  // 農純鄉雲端資料夾 - 每月會員分群_NEW 
  let folder = DriveApp.getFolderById("1carj59KIRIT5Zej5FyfmiPQqlW-3k6WJ")
  console.log(folder);
  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SpreadSheetId')
  console.log(ss);
  let lastMonthId = ss.getRange(1,2).getValue()
  console.log(lastMonthId);

  // 建立試算表 回購預測與分群
  let folderName1 = "農純鄉" + year + "年" + month + "月_回購預測與分群";
  let ss1 = DriveApp.getFileById(lastMonthId).makeCopy(folderName1, folder); 
   // 清除特定工作表的內容
  let ts = SpreadsheetApp.openById(ss1.getId())
  ts.getSheetByName('用戶分群名單').getRange("B2:B6").clear();
  ts.getSheetByName('用戶分群名單').getRange("D2:L6").clear();
  ts.getSheetByName('整體').clear();
  ts.getSheetByName('寶寶').clear();
  ts.getSheetByName('非寶寶').clear();

  return ss1.getId();
}

// 整體 從 Big query table 下載匯入後，將lifetime_cnt，lifetime_total_amount欄位轉為INT，執行第二步
// 第二步：處理客戶分群變動
// 在整體工作表中新增客群變動標記和建立樞紐分析表
function secondStep(){
  let clusterID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SpreadSheetId').getRange(1,2).getValue()
  console.log(clusterID);
  
  // 分析客群變動
  isChange(clusterID);
  // 建立樞紐分析表
  setPivotTable(clusterID);
  // 建立儀表板
  dashBoard(clusterID);
}

 // 資料欄位常數定義
 // 定義試算表中各欄位的索引位置

const COLUMNS = {
  CUSTOMER_ID: 1,
  CUSTOMER_NAME: 2,
  CUSTOMER_PHONE: 3,
  EMAIL: 4,
  RECENCY: 5,
  AVG_REPURCHASE_DAYS: 6,
  PRED_MONEY: 7,
  NAPL_SEGMENT: 8,
  LAST_MONTH_NAPL_SEGMENT: 9,
  NAPL_SEGMENT_FOR_BABY_PORIDGE: 10,
  IS_BUY_ALL: 11,
  IS_BUY_BABY: 12,
  IS_BUY_NONBABY: 13,
  CUSTOMER_SOURCE_LATEST: 14,
  CUSTOMER_TAG: 15,
  IS_SUBSCRIBED_MARKETING_EMAIL: 16,
  LIFETIME_CNT: 17,
  LIFETIME_TOTAL_AMOUNT: 18,
  SMS_CONVERSION30_PROBABILITY: 19,
  NEWSLETTER_OPEN_PROBABILITY: 20
};

// 將數字轉換為Excel欄位代號 (A, B, C, ...)
function columnToLetter(column) {
  let temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}


function isChange(clusterID) {
  let ss = SpreadsheetApp.openById(clusterID).getSheetByName('整體');
  
  // 比较本月和上月的NAPL分段
  const isChangeColumn = ss.getLastColumn() + 1;
  ss.getRange(1, isChangeColumn).setValue("isChange");
  
  ss.getRange(2, isChangeColumn).setFormula(
    `=IF(${columnToLetter(COLUMNS.NAPL_SEGMENT)}2=${columnToLetter(COLUMNS.LAST_MONTH_NAPL_SEGMENT)}2, 0, 1)`
  );

  let lastRow = ss.getLastRow();
  let fillDownRange = ss.getRange(2, isChangeColumn, lastRow - 1);
  ss.getRange(2, isChangeColumn).copyTo(fillDownRange);
}

// 建立和設定樞紐分析表
function setPivotTable(clusterID) {
  let spreadsheet = SpreadsheetApp.openById(clusterID);
  let dataSource = spreadsheet.getSheetByName("整體");
  
 // 取得資料範圍
  let lr = dataSource.getLastRow();
  let lc = dataSource.getLastColumn();
  let data = dataSource.getRange(1, 1, lr, lc);

 // 建立或獲取樞紐分析表工作表
  let anchorSheet = spreadsheet.getSheetByName("pivot");
  if (!anchorSheet) {
    spreadsheet.insertSheet('pivot', 1);
    anchorSheet = spreadsheet.getSheetByName("pivot");
  }
  
  let anchorCell = anchorSheet.getRange('A1');
  let pivotTable = anchorCell.createPivotTable(data);
  
 // 設定樞紐分析表的行、列和值 pivotTable.addRowGroup(COLUMNS.LAST_MONTH_NAPL_SEGMENT);
  pivotTable.addColumnGroup(COLUMNS.NAPL_SEGMENT);
  pivotTable.addPivotValue(COLUMNS.CUSTOMER_ID, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
}

function findValue(columnName, rowName, ss) {
  // Get the range of values
  var data = ss.getDataRange().getValues();
  var columnNames = data[0]; // Get the first row as column names
  var rowIndex = -1;
  var columnIndex = -1;

  // Find the index of the column with the given name
  for (var i = 0; i < columnNames.length; i++) {
    if (columnNames[i] === columnName) {
      columnIndex = i;
      break;
    }
  }

  // Find the index of the row with the given name
  for (var j = 1; j < data.length; j++) {
    if (data[j][0] === rowName) {
      rowIndex = j;
      break;
    }
  }

  // If both row and column indices are found, return the value
  if (rowIndex !== -1 && columnIndex !== -1) {
    return data[rowIndex][columnIndex] || 0; // Return 0 if the cell is empty
  } else if (rowName === '' && columnIndex !== -1) {
    // Special case for '本月新增' (empty row name)
    return data[1][columnIndex] || 0; // Return the value from the second row
  } else {
    return 0; // Return 0 instead of "Value not found"
  }
}

// Create 客群分佈和變動分析的儀表板
function dashBoard(clusterID) {
  let s = SpreadsheetApp.openById(clusterID);
  let ss = s.getSheetByName('pivot');
  let ts = s.getSheetByName('Dashboard');

// 建立或清除儀表板工作表
  if (!ts) {
    s.insertSheet('Dashboard', 2);
    ts = s.getSheetByName('Dashboard');
  }
  ts.clear();

 // 定義客群類型
  let customerTypes = ['新客', '忠誠客', '活躍客', '沉睡客', '流失客', 'Grand Total'];

// 設定表頭
  ts.getRange("B1:G1").setValues([customerTypes]);
  ts.getRange("A2:A8").setValues([['本月新增'], ['新客'], ['忠誠客'], ['活躍客'], ['沉睡客'], ['流失客'], ['總和']]);

  let pivotData = ss.getDataRange().getValues();
  console.log("Pivot data:", pivotData);

  // Get index
  let columnIndices = {};
  pivotData[1].forEach((col, index) => {
    if (customerTypes.includes(col)) {
      columnIndices[col] = index;
    }
  });

  // 填補數據
  for (let i = 0; i < customerTypes.length; i++) {
    let columnName = customerTypes[i];
    let columnIndex = columnIndices[columnName];
    
    if (columnIndex !== undefined) {
      for (let j = 0; j < customerTypes.length + 1; j++) {
        let rowName = j === 0 ? '' : customerTypes[j-1];
        let rowIndex = pivotData.findIndex(row => row[0] === rowName);
        
        let val = 0;
        if (rowIndex !== -1) {
          val = pivotData[rowIndex][columnIndex] || 0;
        } else if (j === 0) {
          // 特殊处理 '本月新增' 行
          val = pivotData[2][columnIndex] || 0;
        }
        
        ts.getRange(j + 2, i + 2).setValue(val);
        console.log(`Setting value ${val} at row ${j+2}, column ${i+2}`);
      }
    }
  }

  // 計算總和
  for (let i = 2; i <= 8; i++) {
    let rowSum = 0;
    for (let j = 2; j <= 6; j++) {
      rowSum += ts.getRange(i, j).getValue() || 0;
    }
    ts.getRange(i, 7).setValue(rowSum);
    console.log(`Setting row sum ${rowSum} at row ${i}, column 7`);
  }

  console.log('Count');

  // === 計算比例 ===
  ts.getRange("B11:G11").setValues([['新客', '忠誠客', '活躍客', '沉睡客', '流失客', '總和']]);
  ts.getRange("A12:A18").setValues([['本月新增'], ['新客'], ['忠誠客'], ['活躍客'], ['沉睡客'], ['流失客'], ['總和']]);

 // 設定各客群佔比公式
  let cell = ts.getRange(12,2).setFormula('=IF(B2<>"", B2/B$8, "")').setNumberFormat("0.00%");
	let fillDownRange = ts.getRange(12, 2, 6, 6);
	ts.getRange(12,2).copyTo(fillDownRange);

// 設定總計列的佔比公式
  cell = ts.getRange(18,2).setFormula('=B8/$G$8').setNumberFormat("0.00%");
	fillDownRange = ts.getRange(18, 2, 1, 6);
	ts.getRange(18,2).copyTo(fillDownRange);
  console.log('Ratio')

  // === 計算總人數變化 ===
  ts.getRange("J1:M1").setValues([['上個月總人數', '本月總人數', '本月新增','客群變動']]).setBackground('#efefef');

  let change = ts.getRange(1,20).setFormula('=AVERAGE(整體!U:U)').getValue(); // 變動比例
  ts.getRange("M2").setValue(change).setNumberFormat("0.00%");

  // 本月新增
  let newComer = ts.getRange("G2").getValue();
  ts.getRange("L2").setValue(newComer);

  // 本月總人數
  let total = ts.getRange("G8").getValue(); 
  ts.getRange("K2").setValue(total);

  //上月總人數
  let last = total - newComer; 
  ts.getRange("J2").setValue(last);
  console.log('Total & Change')

  // ==== 變動率 ===
  ts.getRange("J11:N11").setValues([['新客', '忠誠客', '活躍客','沉睡客','流失客']]).setBackground('#efefef');
  ts.getRange("I12:I14").setValues([['本月人數'],['變動人數'],['變動率']]).setBackground('#efefef');
  
  // 本月人數
  ts.getRange("J12:N12").setValues(ts.getRange("B8:F8").getValues()); 

   // 變動 新客
  let newC = ts.getRange("G3").getValue() - ts.getRange("B3").getValue(); 
  ts.getRange("J13").setValue(newC);

  // 變動 忠誠客
  let loyalC = ts.getRange("G4").getValue() - ts.getRange("C4").getValue();
  ts.getRange("K13").setValue(loyalC); 

  // 變動 活躍客
  let actvieC = ts.getRange("G5").getValue() - ts.getRange("D5").getValue();
  ts.getRange("L13").setValue(actvieC); 

  // 變動 沈睡客
  let sleepC = ts.getRange("G6").getValue() - ts.getRange("E6").getValue();
  ts.getRange("M13").setValue(sleepC); 

  // 變動 流失客
  let lossC = ts.getRange("G7").getValue() - ts.getRange("F7").getValue();
  ts.getRange("N13").setValue(lossC); 

  // 變動率
  cell = ts.getRange("J14").setFormula('=J13/J12').setNumberFormat("0.00%");
	fillDownRange = ts.getRange(14, 10, 1, 5);
	ts.getRange("J14").copyTo(fillDownRange); 
  console.log('Class & Change')
}


// 第三步：處理寶寶與非寶寶預測
function thirdStep() {
  let year_month = title();
  let year = year_month[0];
  let month = year_month[1];
  let reportID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SpreadSheetId').getRange(1,2).getValue();
  console.log(reportID);

  // // 進階分析-忠誠客流動
  // advLoyal(reportID, year, month);

// 執行寶寶/非寶寶分析
  babyNonBaby(reportID);
  
  // // 用戶分群名單處理
  // processUserGrouping(reportID);

  console.log("Third step completed successfully");
}

// 處理寶寶與非寶寶客群分析
// 建立並處理寶寶和非寶寶相關的工作表
function babyNonBaby(reportID){
  try {
    let ss = SpreadsheetApp.openById(reportID);
    let sheet = ss.getSheetByName('整體');

    if (!sheet) {
      throw new Error("'整體' sheet not found");
    }

    // 删除現有的 '寶寶' 和 '非寶寶' 工作表 
    ['寶寶', '非寶寶'].forEach(sheetName => {
      let existingSheet = ss.getSheetByName(sheetName);
      if (existingSheet) {
        ss.deleteSheet(existingSheet);
      }
    });
    console.log('Existing sheets cleared');

    // 處理 '寶寶' 工作表
    let baby = sheet.copyTo(ss).setName('寶寶');
    console.log('寶寶 sheet before: ' + baby.getLastColumn() + ' columns');
    deleteColumnsByLetter(baby, ['M', 'K']);
    addRecommendationColumn(baby);
    renameColumns(baby, getBabyColumnNames());
    console.log('寶寶 sheet after: ' + baby.getLastColumn() + ' columns');

    // 處理 '非寶寶' 工作表
    let nonbaby = sheet.copyTo(ss).setName('非寶寶');
    console.log('非寶寶 sheet before: ' + nonbaby.getLastColumn() + ' columns');
    deleteColumnsByLetter(nonbaby, ['L', 'K']);
    addRecommendationColumn(nonbaby);
    renameColumns(nonbaby, getNonBabyColumnNames());
    console.log('非寶寶 sheet after: ' + nonbaby.getLastColumn() + ' columns');

    // 處理 '整體' 工作表
    console.log('整體 sheet before: ' + sheet.getLastColumn() + ' columns');
    deleteColumnsByLetter(sheet, ['M', 'L']);
    addRecommendationColumn(sheet);
    renameColumns(sheet, getWholeColumnNames());
    console.log('整體 sheet after: ' + sheet.getLastColumn() + ' columns');

    console.log('BabyNonBaby function completed successfully');
  } catch (error) {
    console.error('Error in babyNonBaby function: ' + error.message);
  }
}


function addRecommendationColumn(sheet) {
  sheet.insertColumnAfter(10);  // 在第10列后插入新列
  sheet.getRange(1, 11).setValue("推薦品類");  // 设置新列的标题
  console.log('增加第10列欄位');

}

function renameColumns(sheet, newNames) {
  sheet.getRange(1, 1, 1, newNames.length).setValues([newNames]);
}

function getBabyColumnNames() {
  return ["會員ID", "會員姓名", "會員手機", "會員EMAIL", "最近一次購買天數", "平均回購天數","預期消費金額", "本月分群", "上個月分群", "寶寶粥分群", "推薦品類", "回購機率", "會員來源", "會員標籤", "Email推播", "購買次數", "消費金額", "簡訊開啟率", "Email開啟率"];
}

function getNonBabyColumnNames() {
  return ["會員ID", "會員姓名", "會員手機", "會員EMAIL", "最近一次購買天數", "平均回購天數", "預期消費金額", "本月分群", "上個月分群", "寶寶粥分群", "推薦品類", "回購機率", "會員來源", "會員標籤", "Email推播", "購買次數", "消費金額", "簡訊開啟率", "Email開啟率"];
}

function getWholeColumnNames() {
  return ["會員ID", "會員姓名", "會員手機", "會員EMAIL", "最近一次購買天數", "平均回購天數", "", "本月分群", "上個月分群", "寶寶粥分群", "推薦品類", "回購機率", "會員來源", "會員標籤", "Email推播", "購買次數", "消費金額", "簡訊開啟率", "Email開啟率"];
}


// 辅助函数：按字母删除多个列
function deleteColumnsByLetter(sheet, columnLetters) {
  let columnNumbers = columnLetters.map(letter => letterToColumn(letter));
  columnNumbers.sort((a, b) => b - a).forEach(columnNumber => {
    try {
      if (columnNumber <= sheet.getLastColumn()) {
        sheet.deleteColumn(columnNumber);
        console.log('Deleted column ' + columnToLetter(columnNumber));
      } else {
        console.log('Column ' + columnToLetter(columnNumber) + ' does not exist, skipping');
      }
    } catch (error) {
      console.error('Error deleting column ' + columnToLetter(columnNumber) + ': ' + error.message);
    }
  });
}
function columnToLetter(column) {
  let temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}
// 辅助函数：将列字母转换为数字
function letterToColumn(letter) {
  let column = 0;
  const length = letter.length;
  for (let i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}


// 第四步：處理用戶分群資料
// 更新一般和寶寶用戶的分群資訊
function fourthStep() {
  console.log("Starting fourth step: Processing user grouping");
  let reportID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SpreadSheetId').getRange(1,2).getValue();
  let ss = SpreadsheetApp.openById(reportID);
  let wholeSheet = ss.getSheetByName('整體');
  let babySheet = ss.getSheetByName('寶寶');
  let groupSheet = ss.getSheetByName('用戶分群名單');
  let babyGroupSheet = ss.getSheetByName('用戶分群名單(寶寶)');

  if (!wholeSheet || !babySheet || !groupSheet || !babyGroupSheet) {
    console.error("Required sheets not found");
    return;
  }

  // Process general user grouping sheet
  processSheet(wholeSheet, groupSheet, false, '整體');

  // Process baby user grouping sheet
  processSheet(babySheet, babyGroupSheet, true, '寶寶');

  console.log("User grouping sheets processed successfully");
}


function processSheet(sourceSheet, targetSheet, isBabySheet, sourceSheetName) {
  // 清除现有数据
  targetSheet.getRange("B2:L7").clear();

  // 设置顾客分群
  let customerTypes = ['新客', '忠誠客', '活躍客', '沉睡客', '流失客'];
  if (isBabySheet) {
    customerTypes.push('非寶寶消費者');
  }
  targetSheet.getRange(2, 1, customerTypes.length, 1).setValues(customerTypes.map(type => [type]));

  // 处理每个顾客分群
  for (let i = 0; i < customerTypes.length; i++) {
    let customerType = customerTypes[i];
    let row = i + 2; // 从第2行开始

    let typeColumn = 'H'; // 使用H列作为客户类型列
    let babyColumn = 'J'; // 使用J列作为寶寶/非寶寶标识列
    let orderColumn = 'P';
    let revenueColumn = 'Q';

    // 顾客数
    let customerCountFormula;
    if (customerType === '非寶寶消費者' && isBabySheet) {
      customerCountFormula = `=COUNTIFS(${sourceSheetName}!${babyColumn}:${babyColumn}, "非寶寶消費者")`;
    } else if (isBabySheet) {
      customerCountFormula = `=COUNTIFS(${sourceSheetName}!${typeColumn}:${typeColumn}, "${customerType}")`;
    } else {
      customerCountFormula = `=COUNTIFS(${sourceSheetName}!${typeColumn}:${typeColumn}, "${customerType}")`;
    }
    targetSheet.getRange(row, 2).setFormula(customerCountFormula);

    // 占比
    let percentageFormula = `=IF(SUM($B$2:$B$${customerTypes.length + 1})=0, 0, B${row}/SUM($B$2:$B$${customerTypes.length + 1}))`;
    targetSheet.getRange(row, 3).setFormula(percentageFormula).setNumberFormat("0.00%");

    // 总订单量
    let totalOrdersFormula;
    if (customerType === '非寶寶消費者' && isBabySheet) {
      totalOrdersFormula = `=SUMIFS(${sourceSheetName}!${orderColumn}:${orderColumn}, ${sourceSheetName}!${babyColumn}:${babyColumn}, "非寶寶消費者")`;
    } else if (isBabySheet) {
      totalOrdersFormula = `=SUMIFS(${sourceSheetName}!${orderColumn}:${orderColumn}, ${sourceSheetName}!${typeColumn}:${typeColumn}, "${customerType}")`;
    } else {
      totalOrdersFormula = `=SUMIFS(${sourceSheetName}!${orderColumn}:${orderColumn}, ${sourceSheetName}!${typeColumn}:${typeColumn}, "${customerType}")`;
    }
    targetSheet.getRange(row, 5).setFormula(totalOrdersFormula);

    // 总营业额
    let totalRevenueFormula;
    if (customerType === '非寶寶消費者' && isBabySheet) {
      totalRevenueFormula = `=SUMIFS(${sourceSheetName}!${revenueColumn}:${revenueColumn}, ${sourceSheetName}!${babyColumn}:${babyColumn}, "非寶寶消費者")`;
    } else if (isBabySheet) {
      totalRevenueFormula = `=SUMIFS(${sourceSheetName}!${revenueColumn}:${revenueColumn}, ${sourceSheetName}!${typeColumn}:${typeColumn}, "${customerType}")`;
    } else {
      totalRevenueFormula = `=SUMIFS(${sourceSheetName}!${revenueColumn}:${revenueColumn}, ${sourceSheetName}!${typeColumn}:${typeColumn}, "${customerType}")`;
    }
    targetSheet.getRange(row, 6).setFormula(totalRevenueFormula).setNumberFormat("#,##0.00");

    // 平均订单价格
    let avgOrderPriceFormula = `=IF(E${row}=0, 0, F${row}/E${row})`;
    targetSheet.getRange(row, 4).setFormula(avgOrderPriceFormula).setNumberFormat("#,##0.00");
  }

  // 设置总计行
  let totalRow = customerTypes.length + 2;
  targetSheet.getRange(totalRow, 1).setValue("總計");
  targetSheet.getRange(totalRow, 2).setFormula(`=SUM(B2:B${totalRow-1})`);
  targetSheet.getRange(totalRow, 3).setFormula(`=SUM(C2:C${totalRow-1})`).setNumberFormat("0.00%");
  targetSheet.getRange(totalRow, 4).setFormula(`=IF(E${totalRow}=0, 0, F${totalRow}/E${totalRow})`).setNumberFormat("#,##0.00");
  targetSheet.getRange(totalRow, 5).setFormula(`=SUM(E2:E${totalRow-1})`);
  targetSheet.getRange(totalRow, 6).setFormula(`=SUM(F2:F${totalRow-1})`).setNumberFormat("#,##0.00");
}

// 第五步：進行忠誠客戶分析
function fifthStep() {
  console.log("Starting fifth step: Advanced loyal customer analysis");
  let reportID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SpreadSheetId').getRange(1,2).getValue();
  let year_month = title();
  let year = year_month[0];
  let month = year_month[1];

  advLoyal(reportID, year, month);
}

function advLoyal(reportID, year, month){
  let ss = SpreadsheetApp.openById(reportID).getSheetByName('Dashboard');
  let ts = SpreadsheetApp.openById(reportID).getSheetByName('進階分析-忠誠客流動');
  ts.insertRowsBefore(1, 10);
  ts.getRange(1,1).setValue(year+'-'+month)
  ts.getRange("B2:H2").setValues([['本月分群','本月新客', '上個月新客','上個月忠誠客', '上個月活躍客','上個月沉睡客','上個月流失客']])
  ts.getRange("A3:A7").setValues([['新客'], ['忠誠客'], ['活躍客'],['沉睡客'],['流失客']])

  ts.getRange(3,2,6).setValues(ss.getRange(8,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));
  ts.getRange(3,3,6).setValues(ss.getRange(2,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));
  ts.getRange(3,4,6).setValues(ss.getRange(3,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));
  ts.getRange(3,5,6).setValues(ss.getRange(4,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));
  ts.getRange(3,6,6).setValues(ss.getRange(5,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));
  ts.getRange(3,7,6).setValues(ss.getRange(6,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));
  ts.getRange(3,8,6).setValues(ss.getRange(7,2,1,6).getValues().flatMap(subArray => subArray.map(num => [num])));

  ts.getRange(2,1).setValue('上個月分群/這個月分群');
  ts.getRange(4,2,1,7).setBackground('#ffff00');

  ts.getRange("K2:Q2").setValues([['本月分群','本月新客', '上個月新客','上個月忠誠客', '上個月活躍客','上個月沉睡客','上個月流失客']])
  ts.getRange("J3:J7").setValues([['新客'], ['忠誠客'], ['活躍客'],['沉睡客'],['流失客']])

  ts.getRange(3,11).setFormula("=sum(L3:Q3)");
	ts.getRange(3,11).copyTo(ts.getRange(3, 11, 5));
  ts.getRange(3,12,5).setValues(ss.getRange(12,2,1,5).getValues().flatMap(subArray => subArray.map(num => [num]))).setNumberFormat("0.00%");
  ts.getRange(3,13,5).setValues(ss.getRange(13,2,1,5).getValues().flatMap(subArray => subArray.map(num => [num]))).setNumberFormat("0.00%");
  ts.getRange(3,14,5).setValues(ss.getRange(14,2,1,5).getValues().flatMap(subArray => subArray.map(num => [num]))).setNumberFormat("0.00%");
  ts.getRange(3,15,5).setValues(ss.getRange(15,2,1,5).getValues().flatMap(subArray => subArray.map(num => [num]))).setNumberFormat("0.00%");
  ts.getRange(3,16,5).setValues(ss.getRange(16,2,1,5).getValues().flatMap(subArray => subArray.map(num => [num]))).setNumberFormat("0.00%");
  ts.getRange(3,17,5).setValues(ss.getRange(17,2,1,5).getValues().flatMap(subArray => subArray.map(num => [num]))).setNumberFormat("0.00%");

  ts.getRange(2,10).setValue('上個月分群/這個月分群');
  ts.getRange(4,12,1,6).setBackground('#ffff00');

  ts.getRange(3,20,6,1).setValues([['本月新客'], ['上個月新客'],['上個月忠誠客'], ['上個月活躍客'],['上個月沉睡客'],['上個月流失客']]);
  ts.getRange(2,21,7,1).setValues(ss.getRange(11,3,7,1).getValues());
  ts.getRange(2,22,7,1).setValues(ss.getRange(11,2,7,1).getValues());

  console.log("Advanced loyal customer analysis completed");
}

// 第六步：進行整體分析
function sixthStep() {
  console.log("Starting fifth step: Advanced loyal customer analysis");
  let reportID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SpreadSheetId').getRange(1,2).getValue();
  let year_month = title();
  let year = year_month[0];
  let month = year_month[1];

  advWhole(reportID, year, month);
}
function advWhole(reportID, year, month){
  let ss = SpreadsheetApp.openById(reportID).getSheetByName('用戶分群名單');
  let ss2 = SpreadsheetApp.openById(reportID).getSheetByName('進階分析-忠誠客流動')
  let ts = SpreadsheetApp.openById(reportID).getSheetByName('進階分析-整體');
  let lc = ts.getLastColumn();

  // 訂單數
  ts.getRange(2,lc).setValue(year+'-'+month).setFontWeight("bold");
  let orders = ss.getRange("E2:E6").getValues();
  ts.getRange(3,lc,5).setValues(orders);
  ts.getRange(2,lc,6).setBackground('#ffff00');
  ts.getRange(2,lc-1,6).setBackground('#d9ead3');
  // 人數
  ts.getRange(9,lc).setValue(year+'-'+month).setFontWeight("bold");
  let nums = ss.getRange("B2:B6").getValues();
  ts.getRange(10,lc,5).setValues(nums);
  // 人數總和
  var sum = 0;
  for (var j = 10; j <= 14; j++) {
    sum += ts.getRange(j, lc).getValue();
  }
  ts.getRange(15, lc).setValue(sum);
  ts.getRange(9,lc,7).setBackground('#ffff00');
  ts.getRange(9,lc-1,7).setBackground('#d9ead3');
  // 人數佔比
  ts.getRange(18,lc).setValue(year+'-'+month).setFontWeight("bold");
  let pcts = ss.getRange("C2:C6").getValues();
  ts.getRange(19,lc,5).setValues(pcts);
  ts.getRange(18,lc,6).setBackground('#ffff00');
  ts.getRange(18,lc-1,6).setBackground('#d9ead3');
  // 營業額
  ts.getRange(26,lc).setValue(year+'-'+month).setFontWeight("bold");
  let sales = ss.getRange("F2:F6").getValues();
  ts.getRange(27,lc,5).setValues(sales).setNumberFormat('[$$-en-US]#,##0.00');
  ts.getRange(26,lc,6).setBackground('#ffff00');
  ts.getRange(26,lc-1,6).setBackground('#d9ead3');
  // 營業額/人數
  ts.getRange(34,lc).setValue(year+'-'+month).setFontWeight("bold");
  for (var i = 0; i <= 4; i++) {
    var sum = 0;
    sum = ts.getRange(27+i, lc).getValue() / ts.getRange(10+i, lc).getValue();
    ts.getRange(35+i, lc).setValue(sum).setNumberFormat('[$$-en-US]#,##0.00');
  }
  ts.getRange(34,lc,6).setBackground('#ffff00');
  ts.getRange(34,lc-1,6).setBackground('#d9ead3');
  // 營業額/訂單數
  ts.getRange(42,lc).setValue(year+'-'+month).setFontWeight("bold");
  for (var i = 0; i <= 4; i++) {
    var sum = 0;
    sum = ts.getRange(27+i, lc).getValue() / ts.getRange(3+i, lc).getValue();
    ts.getRange(43+i, lc).setValue(sum).setNumberFormat('[$$-en-US]#,##0.00');
  }
  ts.getRange(42,lc,6).setBackground('#ffff00');
  ts.getRange(42,lc-1,6).setBackground('#d9ead3');
  // 人數變化
  let addNew = ts.getRange(15,lc).getValue() - ts.getRange(15,lc-1).getValue(); //新增人數
  ts.getRange(52, lc).setValue(addNew);
  let lostNew = ts.getRange(14,lc).getValue() - ts.getRange(14, lc-1).getValue(); //流失人數
  ts.getRange(53,lc).setValue(lostNew);
  ts.getRange(54,lc).setValue(addNew-lostNew).setFontColor('#ff0000'); //異動人數
  ts.getRange(52,lc,3).setBackground('#ffff00');
  ts.getRange(52,lc-1,3).setBackground('#ffffff');
  // 人數變化 - 近一年平均
  ts.getRange(51,lc).clear();
  ts.getRange(51,lc+1).setValue('近一年平均');
  let newSum = ts.getRange(52,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0);
  ts.getRange(52,lc+1).setValue(newSum/12);
  newSum = ts.getRange(53,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0);
  ts.getRange(53,lc+1).setValue(newSum/12);
  newSum = ts.getRange(54,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0);
  ts.getRange(54,lc+1).setValue(newSum/12);
  // 忠誠客
  let newLoyal = ts.getRange(11,lc).getValue();
  let newActive = ts.getRange(12,lc).getValue();
  let newSleep = ts.getRange(13,lc).getValue();
  ts.getRange(57,lc).setValue(newLoyal/(newLoyal+newActive+newSleep)).setNumberFormat('0.00%');
  ts.getRange(58,lc).setValue((newActive+newSleep)/(newLoyal+newActive+newSleep)).setNumberFormat('0.00%');
  ts.getRange(57,lc,2).setBackground('#ffff00');
  ts.getRange(57,lc-1,2).setBackground('#ffffff');
  // 忠誠客 - 近一年平均
  ts.getRange(56,lc).clear();
  ts.getRange(56,lc+1).setValue('近一年平均');
  newSum = ts.getRange(57,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0); // Sum 12 months
  ts.getRange(57,lc+1).setValue(newSum/12);
  newSum = ts.getRange(58,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0); // Sum 12 months
  ts.getRange(58,lc+1).setValue(newSum/12);
  // 經營會員指標
  let d90 = ts.getRange(10,lc).getValue() + ts.getRange(11,lc).getValue();
  let d180 = d90 + ts.getRange(12,lc).getValue() + ts.getRange(13,lc).getValue();
  ts.getRange(62,lc).setValue(d90).setFontColor('#ff0000');
  ts.getRange(63,lc).setValue(d180).setFontColor('#ff0000');
  ts.getRange(62,lc,2).setBackground('#ffff00');
  ts.getRange(62,lc-1,2).setBackground('#ffffff');
  // 經營會員指標 - 近一年平均
  ts.getRange(61,lc).clear();
  ts.getRange(61,lc+1).setValue('近一年平均');
  newSum = ts.getRange(62,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0); // Sum 12 months
  ts.getRange(62,lc+1).setValue(newSum/12);
  newSum = ts.getRange(63,lc-11,1,12).getValues().flat().reduce((acc, curr) => acc + curr, 0); // Sum 12 months
  ts.getRange(63,lc+1).setValue(newSum/12);

  ts.getRange(1,lc+1,63).setBackground('#ffffff');
  ts.getRange(1,lc-1,63).setFontColor('#000000');
}
```