## 農純鄉會員分群
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
4.執行第一步- 設定日期（無需修改程式碼）
5.執行firststep
6.取得新報表連接
a. 可在**Execution log**中取得（直接用這個ID 導向新報表）
b.
至[[農純鄉] 自動化_回購預測 + 分群](https://docs.google.com/spreadsheets/d/1ib1o12Tg9WlZFBMvWC0yZZ7sZ75_LX2QCaaU2HTz4KM/edit?gid=382761811#gid=382761811)取得新報表ID
7.確認新報表有建立好
**補截圖**
8. 點選**整體**，並在這個工作表中匯入 ```nongchunxiang-ga4.prod_mart.mart-napl_report```名單，（因為名單大小操過BQ API上限所以只能用手動匯入）
9.調整Q, R欄轉成數值 (```lifetime_cnt```,``` lifetime_total_amount```)
10. 執行step 2,3,4,5,6 每一步確認執行成功資料無誤即可執行下一步
11. 推薦商品，因目前無自動化，且推薦品類變動不大，故複製前個月即可，在整體、寶寶、非寶寶工作表中新增推薦品類欄位，並使用vlookup公式
```=VLOOKUP(H2, '用戶分群名單'!A:J, 10, FALSE)```
12. 確認分群流動數字無異常（小於1%可視正常 ）
14. 近一年平均需手動計算（不含上個月的平均數）
以下為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');
}
```
Sign in with Wallet
Connect another wallet