${\n esc(title)\n }

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_250":251,"_252":253},"routes/_app.x.$handle.$name",{"_97":96,"_32":45,"_106":87,"_27":107,"_13":14,"_108":-5,"_11":109,"_110":111,"_112":113,"_114":115,"_116":117,"_20":-5,"_118":-5,"_119":-5,"_120":25,"_121":122,"_123":124,"_125":-5,"_126":-5,"_127":128},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":-5,"_53":-5,"_18":54,"_55":-5,"_56":25,"_57":-5,"_58":-5,"_59":60,"_61":62,"_63":64,"_65":66,"_67":68,"_69":-5,"_70":71,"_72":71,"_73":74,"_75":74,"_76":74,"_77":71,"_78":71,"_79":80,"_81":82,"_83":84,"_85":25},"version",116,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","esmAnalyticsSync.ts","latestPath","fileId","a880a5d0-6b98-11f1-8e90-1607ee4eb77e","name","fileType","interval","content","/**\n * esmAnalyticsSync.ts v3 (2026-06-19) — 데이터 + 규칙엔진 + Claude 심층분석(Anthropic API)\n * Cron: 10 5 * * * (UTC = 14:10 KST). blob: esm_analytics_latest + esm_ai_insight_latest\n * 필요: env ANTHROPIC_API_KEY (없으면 분석만 건너뛰고 데이터/표는 정상).\n */\nimport * as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst OUT_BLOB = \"esm_analytics_latest\";\nconst AI_BLOB = \"esm_ai_insight_latest\";\nconst RULES_BLOB = \"esm_action_rules\";\nconst BEP = 1000;\nconst AI_MODEL = \"claude-sonnet-4-6\";\n\nconst TH = {\n auctionBidUpRoas: 400,\n auctionRankLow: 6,\n auctionRankHigh: 13,\n kwBidUpRoas: 900,\n kwBidUpMinCost: 20000,\n kwMinSales: 150000,\n kwDisplayMinCost: 30000,\n gmktCutRoas: 350,\n gmktCutMinCost: 150000,\n offMinImp: 15000,\n offMinCost: 20000,\n};\nconst DEFAULT_RULES = {\n neverOff: [\"2394407876\"],\n noIncrease: [\"C557639742\", \"C557632127\"],\n selfBrandKw: [\n \"투데이굿타임\",\n \"풍년보감\",\n \"올골\",\n \"올굿\",\n \"에브리데이\",\n \"밸런스타임\",\n \"투굿\",\n \"쫀드기\",\n \"쫀디기\",\n \"쫀득이\",\n \"텅스\",\n ],\n};\n\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v) {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction normDate(v) {\n if (v === null || v === undefined || v === \"\") return null;\n let s;\n if (v instanceof Date) {\n s = v.toLocaleDateString(\"sv-SE\", { timeZone: \"Asia/Seoul\" });\n } else {\n s = String(v).trim();\n const m = s.match(/^(\\d{4})(\\d{2})(\\d{2})$/);\n if (m) s = `${m[1]}-${m[2]}-${m[3]}`;\n }\n return /^\\d{4}-\\d{2}-\\d{2}$/.test(s) ? s : null;\n}\nconst WD = [\"일\", \"월\", \"화\", \"수\", \"목\", \"금\", \"토\"];\nfunction weekday(d) {\n return WD[new Date(d + \"T00:00:00+09:00\").getDay()];\n}\nfunction roasOf(rev, ad) {\n return ad > 0 ? Math.round(rev / ad * 100) : 0;\n}\n\nasync function token() {\n const r = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\"),\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\"),\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\"),\n grant_type: \"refresh_token\",\n }),\n });\n if (!r.ok) throw new Error(`token ${r.status}`);\n return (await r.json()).access_token;\n}\nasync function list(parentId, tk) {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const r = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n { headers: { Authorization: `Bearer ${tk}` } },\n );\n if (!r.ok) throw new Error(`list ${r.status}`);\n return (await r.json()).files || [];\n}\nasync function download(id, tk) {\n const r = await fetch(\n `https://www.googleapis.com/drive/v3/files/${id}?alt=media`,\n { headers: { Authorization: `Bearer ${tk}` } },\n );\n if (!r.ok) throw new Error(`download ${r.status}`);\n return r.arrayBuffer();\n}\nfunction parse(buf, mustCol) {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sh = wb.Sheets[wb.SheetNames[0]];\n const aoa = XLSX.utils.sheet_to_json(sh, {\n header: 1,\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n let h = -1;\n for (let i = 0; i < Math.min(aoa.length, 15); i++) {\n if ((aoa[i] || []).some((c) => String(c).trim() === mustCol)) {\n h = i;\n break;\n }\n }\n if (h === -1) return [];\n const hd = (aoa[h] || []).map((c) => String(c).trim());\n const out = [];\n for (let i = h + 1; i < aoa.length; i++) {\n const o = {};\n (aoa[i] || []).forEach((v, j) => {\n if (hd[j]) o[hd[j]] = v;\n });\n out.push(o);\n }\n return out;\n}\nasync function pickFolder(tk) {\n const months = (await list(ESM_DRIVE_FOLDER_ID, tk)).filter((f) =>\n f.mimeType.includes(\"folder\")\n ).sort((a, b) => (a.name < b.name ? 1 : -1));\n for (const m of months) {\n const days = (await list(m.id, tk)).filter((f) =>\n f.mimeType.includes(\"folder\")\n ).sort((a, b) => (a.name < b.name ? 1 : -1));\n for (const d of days) {\n const files = await list(d.id, tk);\n if (files.some((f) => f.name.startsWith(\"일간 판매실적 현황\"))) {\n return { id: d.id, name: d.name, _files: files };\n }\n }\n }\n throw new Error(\"매출 파일 포함 폴더 없음\");\n}\n\nexport default async function () {\n const tk = await token();\n const folder = await pickFolder(tk);\n const files = folder._files || await list(folder.id, tk);\n const get = (pred) => files.filter((f) => pred(f.name));\n const fSalesA = get((n) =>\n n.startsWith(\"일간 판매실적 현황\") && n.includes(\"옥션\")\n );\n const fSalesG = get((n) =>\n n.startsWith(\"일간 판매실적 현황\") && n.includes(\"G마켓\")\n );\n const fPcA = get((n) =>\n n.startsWith(\"woojooware_report_daily_\") && n.includes(\"옥션\")\n );\n const fPcG = get((n) =>\n n.startsWith(\"woojooware_report_daily_\") && n.includes(\"G마켓\")\n );\n const fAiDaily = get((n) => n.startsWith(\"AI매출업_일별리포트\"));\n const fGoods = get((n) => n.startsWith(\"woojooware_report_goods\"));\n const fKw = get((n) => n.startsWith(\"woojooware_report_keyword\"));\n const fGroup = get((n) => n.startsWith(\"woojooware_report_group\"));\n const fAiProd = get((n) => n.startsWith(\"AI매출업_상품별리포트\"));\n\n const dl = async (arr) => Promise.all(arr.map((f) => download(f.id, tk)));\n const [bSalesA, bSalesG, bPcA, bPcG, bAiDaily, bGoods, bKw, bGroup, bAiProd] =\n await Promise.all([\n dl(fSalesA),\n dl(fSalesG),\n dl(fPcA),\n dl(fPcG),\n dl(fAiDaily),\n dl(fGoods),\n dl(fKw),\n dl(fGroup),\n dl(fAiProd),\n ]);\n\n const dayMap = new Map();\n const D = (d) => {\n let x = dayMap.get(d);\n if (!x) {\n x = { rev: 0, orders: 0, ad: 0, rA: 0, rG: 0, aA: 0, aG: 0 };\n dayMap.set(d, x);\n }\n return x;\n };\n const addSales = (bufs, ch) =>\n bufs.forEach((b) =>\n parse(b, \"기준일\").forEach((r) => {\n const d = normDate(r[\"기준일\"]);\n if (!d) return;\n const x = D(d);\n const v = num(r[\"판매금액\"]);\n x.rev += v;\n x.orders += num(r[\"판매건수\"]);\n if (ch === \"A\") x.rA += v;\n else x.rG += v;\n })\n );\n addSales(bSalesA, \"A\");\n addSales(bSalesG, \"G\");\n const addPc = (bufs, ch) =>\n bufs.forEach((b) =>\n parse(b, \"날짜\").forEach((r) => {\n const d = normDate(r[\"날짜\"]);\n if (!d) return;\n const x = D(d);\n const c = num(r[\"총비용\"]);\n x.ad += c;\n if (ch === \"A\") x.aA += c;\n else x.aG += c;\n })\n );\n addPc(bPcA, \"A\");\n addPc(bPcG, \"G\");\n bAiDaily.forEach((b) =>\n parse(b, \"날짜별\").forEach((r) => {\n const d = normDate(r[\"날짜별\"]);\n if (!d) return;\n D(d).ad += num(r[\"총비용\"]);\n })\n );\n\n const daily = [...dayMap.entries()].filter(([d, x]) => x.rev > 0 || x.ad > 0)\n .map(([d, x]) => ({\n date: d,\n wd: weekday(d),\n rev: x.rev,\n orders: x.orders,\n ad: x.ad,\n roas: roasOf(x.rev, x.ad),\n rA: x.rA,\n rG: x.rG,\n aA: x.aA,\n aG: x.aG,\n }))\n .sort((a, b) => a.date.localeCompare(b.date));\n\n let aiAdA = 0, aiAdG = 0, aiRevA = 0, aiRevG = 0;\n bAiProd.forEach((b) =>\n parse(b, \"상품번호\").forEach((r) => {\n const g = String(r[\"그룹명\"] || \"\");\n const cost = num(r[\"총비용\"]);\n const rev = num(r[\"판매자기준-구매금액\"]);\n if (\n g.includes(\"옥션\") ||\n String(r[\"판매자 ID\"] || \"\").trim().startsWith(\"A \")\n ) {\n aiAdA += cost;\n aiRevA += rev;\n } else {\n aiAdG += cost;\n aiRevG += rev;\n }\n })\n );\n\n const sum = (bufs, col, dcol) => {\n let s = 0;\n bufs.forEach((b) => parse(b, dcol).forEach((r) => s += num(r[col])));\n return s;\n };\n const saAdA = sum(bPcA, \"총비용\", \"날짜\"),\n saAdG = sum(bPcG, \"총비용\", \"날짜\");\n let revA = 0, revG = 0;\n dayMap.forEach((x) => {\n revA += x.rA;\n revG += x.rG;\n });\n const aiTotal = sum(bAiDaily, \"총비용\", \"날짜별\");\n const aiRaw = aiAdA + aiAdG;\n const ratioA = aiRaw > 0 ? aiAdA / aiRaw : 0;\n const aiCostA = Math.round(aiTotal * ratioA), aiCostG = aiTotal - aiCostA;\n\n const channel = {\n auction: {\n sa: saAdA,\n ai: aiCostA,\n ad: saAdA + aiCostA,\n rev: revA,\n roas: roasOf(revA, saAdA + aiCostA),\n aiRev: aiRevA,\n aiRoas: roasOf(aiRevA, aiCostA),\n },\n gmarket: {\n sa: saAdG,\n ai: aiCostG,\n ad: saAdG + aiCostG,\n rev: revG,\n roas: roasOf(revG, saAdG + aiCostG),\n aiRev: aiRevG,\n aiRoas: roasOf(aiRevG, aiCostG),\n },\n };\n const totAd = channel.auction.ad + channel.gmarket.ad,\n totRev = channel.auction.rev + channel.gmarket.rev;\n const totals = {\n ad: totAd,\n rev: totRev,\n roas: roasOf(totRev, totAd),\n auctionCostShare: totAd\n ? +(channel.auction.ad / totAd * 100).toFixed(1)\n : 0,\n auctionRevShare: totRev\n ? +(channel.auction.rev / totRev * 100).toFixed(1)\n : 0,\n aiTotal,\n aiRev: aiRevA + aiRevG,\n aiRoas: roasOf(aiRevA + aiRevG, aiTotal),\n };\n\n const reallocate = (p) => {\n const move = channel.gmarket.sa * p;\n const lostG = move * (channel.gmarket.roas / 100) * 0.6;\n const gainA = move * (channel.auction.roas / 100) * 0.7;\n const newRev = totRev - lostG + gainA;\n return {\n pct: Math.round(p * 100),\n roas: +(newRev / totAd * 100).toFixed(1),\n rev: Math.round(newRev),\n };\n };\n const realloc = [reallocate(0.10), reallocate(0.15), reallocate(0.20)];\n\n const wdAgg = {};\n daily.filter((d) => d.date >= dateKST(-1).slice(0, 7) + \"-01\").forEach(\n (d) => {\n wdAgg[d.wd] ||= { rev: 0, ad: 0 };\n wdAgg[d.wd].rev += d.rev;\n wdAgg[d.wd].ad += d.ad;\n },\n );\n const weekdays = WD.map((w) => ({\n wd: w,\n roas: wdAgg[w] ? roasOf(wdAgg[w].rev, wdAgg[w].ad) : 0,\n })).filter((x) => x.roas > 0);\n\n const prodMap = new Map();\n bGoods.forEach((b) =>\n parse(b, \"광고상품번호\").forEach((r) => {\n const id = String(r[\"광고상품번호\"] || \"\").trim();\n if (!id) return;\n const cost = num(r[\"총비용\"]), rev = num(r[\"구매금액\"]);\n let p = prodMap.get(id);\n if (!p) {\n p = {\n id,\n site: String(r[\"사이트\"] || \"\").trim(),\n cost: 0,\n rev: 0,\n rank: num(r[\"평균노출순위\"]),\n };\n prodMap.set(id, p);\n }\n p.cost += cost;\n p.rev += rev;\n if (num(r[\"평균노출순위\"]) > 0) p.rank = num(r[\"평균노출순위\"]);\n })\n );\n const products = [...prodMap.values()].map((p) => ({\n ...p,\n roas: roasOf(p.rev, p.cost),\n })).filter((p) => p.rev > 0).sort((a, b) => b.rev - a.rev).slice(0, 12);\n\n const rules = {\n ...DEFAULT_RULES,\n ...((await blob.getJSON(RULES_BLOB).catch(() => null)) || {}),\n };\n const keywords = (() => {\n const arr = [];\n bKw.forEach((b) =>\n parse(b, \"키워드\").forEach((r) => {\n const kw = String(r[\"키워드\"] || \"\").trim();\n const cost = num(r[\"총비용\"]);\n const rev = num(r[\"구매금액\"]);\n if (\n kw && rev > 0 && cost >= TH.kwDisplayMinCost &&\n !rules.selfBrandKw.some((s) => kw.includes(s))\n ) arr.push({ kw, cost, rev, roas: roasOf(rev, cost) });\n })\n );\n return arr.sort((a, b) => b.roas - a.roas).slice(0, 12);\n })();\n const groups = (() => {\n const arr = [];\n bGroup.forEach((b) =>\n parse(b, \"그룹명\").forEach((r) => {\n const g = String(r[\"그룹명\"] || \"\").trim();\n const cost = num(r[\"총비용\"]);\n const rev = num(r[\"구매금액\"]);\n if (g && rev > 0) {\n arr.push({\n group: g,\n cost,\n rev,\n roas: roasOf(rev, cost),\n ctr: num(r[\"클릭률\"]),\n });\n }\n })\n );\n return arr.sort((a, b) => b.rev - a.rev).slice(0, 8);\n })();\n\n const monthStart =\n (daily.length\n ? daily[daily.length - 1].date.slice(0, 7)\n : dateKST(-1).slice(0, 7)) + \"-01\";\n const mtd = daily.filter((d) => d.date >= monthStart);\n const mtdAd = mtd.reduce((s, d) => s + d.ad, 0),\n mtdRev = mtd.reduce((s, d) => s + d.rev, 0);\n const lastDate = daily.length ? daily[daily.length - 1].date : dateKST(-1);\n const daysInMonth = new Date(+lastDate.slice(0, 4), +lastDate.slice(5, 7), 0)\n .getDate();\n const remain = daysInMonth - +lastDate.slice(8, 10);\n const avgAd = mtd.length ? Math.round(mtdAd / mtd.length) : 0;\n const neededRoas = (avgAd * remain) > 0\n ? Math.round(\n ((mtdAd + avgAd * remain) * (BEP / 100) - mtdRev) / (avgAd * remain) *\n 100,\n )\n : 0;\n const bep = {\n mtdAd,\n mtdRev,\n mtdRoas: roasOf(mtdRev, mtdAd),\n remain,\n avgAd,\n neededRoas,\n bep: BEP,\n };\n\n const actionA = products.filter((p) =>\n p.site === \"A\" && p.roas >= TH.auctionBidUpRoas &&\n p.rank >= TH.auctionRankLow && p.rank <= TH.auctionRankHigh &&\n !rules.noIncrease.includes(p.id)\n ).map((p) => ({ ...p, move: \"CPC +15%\" }));\n const actionB = keywords.filter((k) =>\n k.roas >= TH.kwBidUpRoas && k.cost >= TH.kwBidUpMinCost &&\n k.rev >= TH.kwMinSales\n ).slice(0, 5).map((k) => ({ ...k, move: \"입찰 +15%\" }));\n const actionD = products.filter((p) =>\n p.site === \"G\" && p.roas < TH.gmktCutRoas && p.cost >= TH.gmktCutMinCost\n ).map((p) => ({ ...p, move: \"CPC -15% (OFF 금지)\" }));\n const actionE = (() => {\n const arr = [];\n bKw.forEach((b) =>\n parse(b, \"키워드\").forEach((r) => {\n const kw = String(r[\"키워드\"] || \"\").trim();\n const rev = num(r[\"구매금액\"]);\n const imp = num(r[\"노출수\"]);\n const cost = num(r[\"총비용\"]);\n if (\n kw && rev === 0 && imp >= TH.offMinImp && cost >= TH.offMinCost &&\n !rules.selfBrandKw.some((s) => kw.includes(s))\n ) arr.push({ kw, imp, cost, move: \"OFF 후보(매출0·고노출)\" });\n })\n );\n return arr.sort((a, b) => b.cost - a.cost).slice(0, 5);\n })();\n\n const payload = {\n generatedAt: new Date().toISOString(),\n folder: folder.name,\n window: { start: daily[0]?.date, end: lastDate },\n totals,\n channel,\n realloc,\n daily,\n weekdays,\n products,\n keywords,\n groups,\n bep,\n actions: {\n A: actionA,\n B: actionB,\n C: { note: \"옥션 SA 일예산 +20~30% 점진\" },\n D: actionD,\n E: actionE,\n },\n rules,\n };\n await blob.setJSON(OUT_BLOB, payload);\n console.log(\n `[esmAnalyticsSync v3] ${folder.name} | 통합ROAS ${totals.roas}% | 옥션 ${channel.auction.roas}% / G마켓 ${channel.gmarket.roas}% | A:${actionA.length} B:${actionB.length} D:${actionD.length} E:${actionE.length}`,\n );\n\n try {\n const key = Deno.env.get(\"ANTHROPIC_API_KEY\");\n if (!key) throw new Error(\"ANTHROPIC_API_KEY 미설정\");\n const ctx = {\n 기간: payload.window,\n 통합: totals,\n 채널: channel,\n 재배분: realloc,\n 요일: weekdays,\n BEP: bep,\n 상위상품: products,\n 키워드: keywords.slice(0, 10),\n 그룹: groups,\n 일별: daily.slice(-12),\n 자동액션: payload.actions,\n };\n const prompt =\n `너는 WOOJOO BIO ESM(옥션·G마켓) 광고 심층 분석가다. 전략 기조 \"옥션 강화 / 지마켓 효율화\", 신BEP ROAS ${BEP}%. 순매출=판매금액, 광고비=파워클릭(SA)+AI매출업, 키워드는 채널분리 불가(통합ROAS).\n아래 실데이터로 \"수치 나열이 아니라 원인·시사점·조치까지 해석하는\" 분석을 한국어로 작성하라. 숫자 근거 필수, 과장 금지.\n절대룰: 매출1위(rules.neverOff) OFF금지(감액만), 고순위·기증액분(rules.noIncrease) 추가증액 금지(학습 리셋), 자사 브랜드 키워드/AI매출업 강화 자제, 단일일·주말·특정요일 부진만으로 OFF금지.\n반드시 아래 JSON만 출력(코드블록·설명 금지):\n{\"headline\":\"한 줄 결론(전략 검증 + 이번 주 핵심, 80자내)\",\"channel\":\"채널 전략 해석 2~3문장(옥션 우위·재배분 win-win 여부)\",\"drop\":\"최근7일 추세/급락 진단 2~3문장(원인 분해: 기저효과/채널/요일)\",\"products\":\"상품·그룹 판단 2~3문장(저효율 대형 드래그·숨은 고효율 기회 지목)\",\"keywords\":\"키워드 판단 2~3문장(최대지출 저효율 등)\",\"insights\":[\"핵심 인사이트 6개(숫자 근거)\"],\"actions\":[\"오늘 우선순위 액션 6개: '대상 — 조치 — 근거' 형식\"],\"memos\":[\"의사결정 메모 5개\"],\"principles\":[\"BEP 5대 원칙\"]}\n데이터: ${JSON.stringify(ctx)}`;\n const ar = await fetch(\"https://api.anthropic.com/v1/messages\", {\n method: \"POST\",\n headers: {\n \"x-api-key\": key,\n \"anthropic-version\": \"2023-06-01\",\n \"content-type\": \"application/json\",\n },\n body: JSON.stringify({\n model: AI_MODEL,\n max_tokens: 2600,\n messages: [{ role: \"user\", content: prompt }],\n }),\n });\n if (!ar.ok) {\n throw new Error(\n \"anthropic \" + ar.status + \" \" + (await ar.text()).slice(0, 200),\n );\n }\n const aj = await ar.json();\n let txt = (aj.content?.[0]?.text || \"\").trim();\n const s = txt.indexOf(\"{\"), e = txt.lastIndexOf(\"}\");\n if (s >= 0 && e > s) txt = txt.slice(s, e + 1);\n const ai = JSON.parse(txt);\n await blob.setJSON(AI_BLOB, {\n ...ai,\n model: AI_MODEL,\n generatedAt: new Date().toISOString(),\n window: payload.window,\n });\n console.log(\n `[esmAnalyticsSync v3] Claude 분석 완료 (insights ${\n (ai.insights || []).length\n }, actions ${(ai.actions || []).length})`,\n );\n } catch (e) {\n console.log(\"[esmAnalyticsSync v3] Claude 분석 건너뜀: \" + e.message);\n }\n\n return { ok: true, folder: folder.name, totals, channel };\n}","/**\n * esmAnalyticsSync.ts v3 (2026-06-19) — 데이터 + 규칙엔진 + Claude 심층분석(Anthropic API)\n * Cron: 10 5 * * * (UTC = 14:10 KST). blob: esm_analytics_latest + esm_ai_insight_latest\n * 필요: env ANTHROPIC_API_KEY (없으면 분석만 건너뛰고 데이터/표는 정상).\n */\nimport * as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst OUT_BLOB = \"esm_analytics_latest\";\nconst AI_BLOB = \"esm_ai_insight_latest\";\nconst RULES_BLOB = \"esm_action_rules\";\nconst BEP = 1000;\nconst AI_MODEL = \"claude-sonnet-4-6\";\n\nconst TH = {\n auctionBidUpRoas: 400,\n auctionRankLow: 6,\n auctionRankHigh: 13,\n kwBidUpRoas: 900,\n kwBidUpMinCost: 20000,\n kwMinSales: 150000,\n kwDisplayMinCost: 30000,\n gmktCutRoas: 350,\n gmktCutMinCost: 150000,\n offMinImp: 15000,\n offMinCost: 20000,\n};\nconst DEFAULT_RULES = {\n neverOff: [\"2394407876\"],\n noIncrease: [\"C557639742\", \"C557632127\"],\n selfBrandKw: [\n \"투데이굿타임\",\n \"풍년보감\",\n \"올골\",\n \"올굿\",\n \"에브리데이\",\n \"밸런스타임\",\n \"투굿\",\n \"쫀드기\",\n \"쫀디기\",\n \"쫀득이\",\n \"텅스\",\n ],\n};\n\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v) {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction normDate(v) {\n if (v === null || v === undefined || v === \"\") return null;\n let s;\n if (v instanceof Date) {\n s = v.toLocaleDateString(\"sv-SE\", { timeZone: \"Asia/Seoul\" });\n } else {\n s = String(v).trim();\n const m = s.match(/^(\\d{4})(\\d{2})(\\d{2})$/);\n if (m) s = `${m[1]}-${m[2]}-${m[3]}`;\n }\n return /^\\d{4}-\\d{2}-\\d{2}$/.test(s) ? s : null;\n}\nconst WD = [\"일\", \"월\", \"화\", \"수\", \"목\", \"금\", \"토\"];\nfunction weekday(d) {\n return WD[new Date(d + \"T00:00:00+09:00\").getDay()];\n}\nfunction roasOf(rev, ad) {\n return ad > 0 ? Math.round(rev / ad * 100) : 0;\n}\n\nasync function token() {\n const r = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\"),\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\"),\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\"),\n grant_type: \"refresh_token\",\n }),\n });\n if (!r.ok) throw new Error(`token ${r.status}`);\n return (await r.json()).access_token;\n}\nasync function list(parentId, tk) {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const r = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n { headers: { Authorization: `Bearer ${tk}` } },\n );\n if (!r.ok) throw new Error(`list ${r.status}`);\n return (await r.json()).files || [];\n}\nasync function download(id, tk) {\n const r = await fetch(\n `https://www.googleapis.com/drive/v3/files/${id}?alt=media`,\n { headers: { Authorization: `Bearer ${tk}` } },\n );\n if (!r.ok) throw new Error(`download ${r.status}`);\n return r.arrayBuffer();\n}\nfunction parse(buf, mustCol) {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sh = wb.Sheets[wb.SheetNames[0]];\n const aoa = XLSX.utils.sheet_to_json(sh, {\n header: 1,\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n let h = -1;\n for (let i = 0; i < Math.min(aoa.length, 15); i++) {\n if ((aoa[i] || []).some((c) => String(c).trim() === mustCol)) {\n h = i;\n break;\n }\n }\n if (h === -1) return [];\n const hd = (aoa[h] || []).map((c) => String(c).trim());\n const out = [];\n for (let i = h + 1; i < aoa.length; i++) {\n const o = {};\n (aoa[i] || []).forEach((v, j) => {\n if (hd[j]) o[hd[j]] = v;\n });\n out.push(o);\n }\n return out;\n}\nasync function pickFolder(tk) {\n const months = (await list(ESM_DRIVE_FOLDER_ID, tk)).filter((f) =>\n f.mimeType.includes(\"folder\")\n ).sort((a, b) => (a.name < b.name ? 1 : -1));\n for (const m of months) {\n const days = (await list(m.id, tk)).filter((f) =>\n f.mimeType.includes(\"folder\")\n ).sort((a, b) => (a.name < b.name ? 1 : -1));\n for (const d of days) {\n const files = await list(d.id, tk);\n if (files.some((f) => f.name.startsWith(\"일간 판매실적 현황\"))) {\n return { id: d.id, name: d.name, _files: files };\n }\n }\n }\n throw new Error(\"매출 파일 포함 폴더 없음\");\n}\n\nexport default async function () {\n const tk = await token();\n const folder = await pickFolder(tk);\n const files = folder._files || await list(folder.id, tk);\n const get = (pred) => files.filter((f) => pred(f.name));\n const fSalesA = get((n) =>\n n.startsWith(\"일간 판매실적 현황\") && n.includes(\"옥션\")\n );\n const fSalesG = get((n) =>\n n.startsWith(\"일간 판매실적 현황\") && n.includes(\"G마켓\")\n );\n const fPcA = get((n) =>\n n.startsWith(\"woojooware_report_daily_\") && n.includes(\"옥션\")\n );\n const fPcG = get((n) =>\n n.startsWith(\"woojooware_report_daily_\") && n.includes(\"G마켓\")\n );\n const fAiDaily = get((n) => n.startsWith(\"AI매출업_일별리포트\"));\n const fGoods = get((n) => n.startsWith(\"woojooware_report_goods\"));\n const fKw = get((n) => n.startsWith(\"woojooware_report_keyword\"));\n const fGroup = get((n) => n.startsWith(\"woojooware_report_group\"));\n const fAiProd = get((n) => n.startsWith(\"AI매출업_상품별리포트\"));\n\n const dl = async (arr) => Promise.all(arr.map((f) => download(f.id, tk)));\n const [bSalesA, bSalesG, bPcA, bPcG, bAiDaily, bGoods, bKw, bGroup, bAiProd] =\n await Promise.all([\n dl(fSalesA),\n dl(fSalesG),\n dl(fPcA),\n dl(fPcG),\n dl(fAiDaily),\n dl(fGoods),\n dl(fKw),\n dl(fGroup),\n dl(fAiProd),\n ]);\n\n const dayMap = new Map();\n const D = (d) => {\n let x = dayMap.get(d);\n if (!x) {\n x = { rev: 0, orders: 0, ad: 0, rA: 0, rG: 0, aA: 0, aG: 0 };\n dayMap.set(d, x);\n }\n return x;\n };\n const addSales = (bufs, ch) =>\n bufs.forEach((b) =>\n parse(b, \"기준일\").forEach((r) => {\n const d = normDate(r[\"기준일\"]);\n if (!d) return;\n const x = D(d);\n const v = num(r[\"판매금액\"]);\n x.rev += v;\n x.orders += num(r[\"판매건수\"]);\n if (ch === \"A\") x.rA += v;\n else x.rG += v;\n })\n );\n addSales(bSalesA, \"A\");\n addSales(bSalesG, \"G\");\n const addPc = (bufs, ch) =>\n bufs.forEach((b) =>\n parse(b, \"날짜\").forEach((r) => {\n const d = normDate(r[\"날짜\"]);\n if (!d) return;\n const x = D(d);\n const c = num(r[\"총비용\"]);\n x.ad += c;\n if (ch === \"A\") x.aA += c;\n else x.aG += c;\n })\n );\n addPc(bPcA, \"A\");\n addPc(bPcG, \"G\");\n bAiDaily.forEach((b) =>\n parse(b, \"날짜별\").forEach((r) => {\n const d = normDate(r[\"날짜별\"]);\n if (!d) return;\n D(d).ad += num(r[\"총비용\"]);\n })\n );\n\n const daily = [...dayMap.entries()].filter(([d, x]) => x.rev > 0 || x.ad > 0)\n .map(([d, x]) => ({\n date: d,\n wd: weekday(d),\n rev: x.rev,\n orders: x.orders,\n ad: x.ad,\n roas: roasOf(x.rev, x.ad),\n rA: x.rA,\n rG: x.rG,\n aA: x.aA,\n aG: x.aG,\n }))\n .sort((a, b) => a.date.localeCompare(b.date));\n\n let aiAdA = 0, aiAdG = 0, aiRevA = 0, aiRevG = 0;\n bAiProd.forEach((b) =>\n parse(b, \"상품번호\").forEach((r) => {\n const g = String(r[\"그룹명\"] || \"\");\n const cost = num(r[\"총비용\"]);\n const rev = num(r[\"판매자기준-구매금액\"]);\n if (\n g.includes(\"옥션\") ||\n String(r[\"판매자 ID\"] || \"\").trim().startsWith(\"A \")\n ) {\n aiAdA += cost;\n aiRevA += rev;\n } else {\n aiAdG += cost;\n aiRevG += rev;\n }\n })\n );\n\n const sum = (bufs, col, dcol) => {\n let s = 0;\n bufs.forEach((b) => parse(b, dcol).forEach((r) => s += num(r[col])));\n return s;\n };\n const saAdA = sum(bPcA, \"총비용\", \"날짜\"),\n saAdG = sum(bPcG, \"총비용\", \"날짜\");\n let revA = 0, revG = 0;\n dayMap.forEach((x) => {\n revA += x.rA;\n revG += x.rG;\n });\n const aiTotal = sum(bAiDaily, \"총비용\", \"날짜별\");\n const aiRaw = aiAdA + aiAdG;\n const ratioA = aiRaw > 0 ? aiAdA / aiRaw : 0;\n const aiCostA = Math.round(aiTotal * ratioA), aiCostG = aiTotal - aiCostA;\n\n const channel = {\n auction: {\n sa: saAdA,\n ai: aiCostA,\n ad: saAdA + aiCostA,\n rev: revA,\n roas: roasOf(revA, saAdA + aiCostA),\n aiRev: aiRevA,\n aiRoas: roasOf(aiRevA, aiCostA),\n },\n gmarket: {\n sa: saAdG,\n ai: aiCostG,\n ad: saAdG + aiCostG,\n rev: revG,\n roas: roasOf(revG, saAdG + aiCostG),\n aiRev: aiRevG,\n aiRoas: roasOf(aiRevG, aiCostG),\n },\n };\n const totAd = channel.auction.ad + channel.gmarket.ad,\n totRev = channel.auction.rev + channel.gmarket.rev;\n const totals = {\n ad: totAd,\n rev: totRev,\n roas: roasOf(totRev, totAd),\n auctionCostShare: totAd\n ? +(channel.auction.ad / totAd * 100).toFixed(1)\n : 0,\n auctionRevShare: totRev\n ? +(channel.auction.rev / totRev * 100).toFixed(1)\n : 0,\n aiTotal,\n aiRev: aiRevA + aiRevG,\n aiRoas: roasOf(aiRevA + aiRevG, aiTotal),\n };\n\n const reallocate = (p) => {\n const move = channel.gmarket.sa * p;\n const lostG = move * (channel.gmarket.roas / 100) * 0.6;\n const gainA = move * (channel.auction.roas / 100) * 0.7;\n const newRev = totRev - lostG + gainA;\n return {\n pct: Math.round(p * 100),\n roas: +(newRev / totAd * 100).toFixed(1),\n rev: Math.round(newRev),\n };\n };\n const realloc = [reallocate(0.10), reallocate(0.15), reallocate(0.20)];\n\n const wdAgg = {};\n daily.filter((d) => d.date >= dateKST(-1).slice(0, 7) + \"-01\").forEach(\n (d) => {\n wdAgg[d.wd] ||= { rev: 0, ad: 0 };\n wdAgg[d.wd].rev += d.rev;\n wdAgg[d.wd].ad += d.ad;\n },\n );\n const weekdays = WD.map((w) => ({\n wd: w,\n roas: wdAgg[w] ? roasOf(wdAgg[w].rev, wdAgg[w].ad) : 0,\n })).filter((x) => x.roas > 0);\n\n const prodMap = new Map();\n bGoods.forEach((b) =>\n parse(b, \"광고상품번호\").forEach((r) => {\n const id = String(r[\"광고상품번호\"] || \"\").trim();\n if (!id) return;\n const cost = num(r[\"총비용\"]), rev = num(r[\"구매금액\"]);\n let p = prodMap.get(id);\n if (!p) {\n p = {\n id,\n site: String(r[\"사이트\"] || \"\").trim(),\n cost: 0,\n rev: 0,\n rank: num(r[\"평균노출순위\"]),\n };\n prodMap.set(id, p);\n }\n p.cost += cost;\n p.rev += rev;\n if (num(r[\"평균노출순위\"]) > 0) p.rank = num(r[\"평균노출순위\"]);\n })\n );\n const products = [...prodMap.values()].map((p) => ({\n ...p,\n roas: roasOf(p.rev, p.cost),\n })).filter((p) => p.rev > 0).sort((a, b) => b.rev - a.rev).slice(0, 12);\n\n const rules = {\n ...DEFAULT_RULES,\n ...((await blob.getJSON(RULES_BLOB).catch(() => null)) || {}),\n };\n const keywords = (() => {\n const arr = [];\n bKw.forEach((b) =>\n parse(b, \"키워드\").forEach((r) => {\n const kw = String(r[\"키워드\"] || \"\").trim();\n const cost = num(r[\"총비용\"]);\n const rev = num(r[\"구매금액\"]);\n if (\n kw && rev > 0 && cost >= TH.kwDisplayMinCost &&\n !rules.selfBrandKw.some((s) => kw.includes(s))\n ) arr.push({ kw, cost, rev, roas: roasOf(rev, cost) });\n })\n );\n return arr.sort((a, b) => b.roas - a.roas).slice(0, 12);\n })();\n const groups = (() => {\n const arr = [];\n bGroup.forEach((b) =>\n parse(b, \"그룹명\").forEach((r) => {\n const g = String(r[\"그룹명\"] || \"\").trim();\n const cost = num(r[\"총비용\"]);\n const rev = num(r[\"구매금액\"]);\n if (g && rev > 0) {\n arr.push({\n group: g,\n cost,\n rev,\n roas: roasOf(rev, cost),\n ctr: num(r[\"클릭률\"]),\n });\n }\n })\n );\n return arr.sort((a, b) => b.rev - a.rev).slice(0, 8);\n })();\n\n const monthStart =\n (daily.length\n ? daily[daily.length - 1].date.slice(0, 7)\n : dateKST(-1).slice(0, 7)) + \"-01\";\n const mtd = daily.filter((d) => d.date >= monthStart);\n const mtdAd = mtd.reduce((s, d) => s + d.ad, 0),\n mtdRev = mtd.reduce((s, d) => s + d.rev, 0);\n const lastDate = daily.length ? daily[daily.length - 1].date : dateKST(-1);\n const daysInMonth = new Date(+lastDate.slice(0, 4), +lastDate.slice(5, 7), 0)\n .getDate();\n const remain = daysInMonth - +lastDate.slice(8, 10);\n const avgAd = mtd.length ? Math.round(mtdAd / mtd.length) : 0;\n const neededRoas = (avgAd * remain) > 0\n ? Math.round(\n ((mtdAd + avgAd * remain) * (BEP / 100) - mtdRev) / (avgAd * remain) *\n 100,\n )\n : 0;\n const bep = {\n mtdAd,\n mtdRev,\n mtdRoas: roasOf(mtdRev, mtdAd),\n remain,\n avgAd,\n neededRoas,\n bep: BEP,\n };\n\n const actionA = products.filter((p) =>\n p.site === \"A\" && p.roas >= TH.auctionBidUpRoas &&\n p.rank >= TH.auctionRankLow && p.rank <= TH.auctionRankHigh &&\n !rules.noIncrease.includes(p.id)\n ).map((p) => ({ ...p, move: \"CPC +15%\" }));\n const actionB = keywords.filter((k) =>\n k.roas >= TH.kwBidUpRoas && k.cost >= TH.kwBidUpMinCost &&\n k.rev >= TH.kwMinSales\n ).slice(0, 5).map((k) => ({ ...k, move: \"입찰 +15%\" }));\n const actionD = products.filter((p) =>\n p.site === \"G\" && p.roas < TH.gmktCutRoas && p.cost >= TH.gmktCutMinCost\n ).map((p) => ({ ...p, move: \"CPC -15% (OFF 금지)\" }));\n const actionE = (() => {\n const arr = [];\n bKw.forEach((b) =>\n parse(b, \"키워드\").forEach((r) => {\n const kw = String(r[\"키워드\"] || \"\").trim();\n const rev = num(r[\"구매금액\"]);\n const imp = num(r[\"노출수\"]);\n const cost = num(r[\"총비용\"]);\n if (\n kw && rev === 0 && imp >= TH.offMinImp && cost >= TH.offMinCost &&\n !rules.selfBrandKw.some((s) => kw.includes(s))\n ) arr.push({ kw, imp, cost, move: \"OFF 후보(매출0·고노출)\" });\n })\n );\n return arr.sort((a, b) => b.cost - a.cost).slice(0, 5);\n })();\n\n const payload = {\n generatedAt: new Date().toISOString(),\n folder: folder.name,\n window: { start: daily[0]?.date, end: lastDate },\n totals,\n channel,\n realloc,\n daily,\n weekdays,\n products,\n keywords,\n groups,\n bep,\n actions: {\n A: actionA,\n B: actionB,\n C: { note: \"옥션 SA 일예산 +20~30% 점진\" },\n D: actionD,\n E: actionE,\n },\n rules,\n };\n await blob.setJSON(OUT_BLOB, payload);\n console.log(\n `[esmAnalyticsSync v3] ${folder.name} | 통합ROAS ${totals.roas}% | 옥션 ${channel.auction.roas}% / G마켓 ${channel.gmarket.roas}% | A:${actionA.length} B:${actionB.length} D:${actionD.length} E:${actionE.length}`,\n );\n\n try {\n const key = Deno.env.get(\"ANTHROPIC_API_KEY\");\n if (!key) throw new Error(\"ANTHROPIC_API_KEY 미설정\");\n const ctx = {\n 기간: payload.window,\n 통합: totals,\n 채널: channel,\n 재배분: realloc,\n 요일: weekdays,\n BEP: bep,\n 상위상품: products,\n 키워드: keywords.slice(0, 10),\n 그룹: groups,\n 일별: daily.slice(-12),\n 자동액션: payload.actions,\n };\n const prompt =\n `너는 WOOJOO BIO ESM(옥션·G마켓) 광고 심층 분석가다. 전략 기조 \"옥션 강화 / 지마켓 효율화\", 신BEP ROAS ${BEP}%. 순매출=판매금액, 광고비=파워클릭(SA)+AI매출업, 키워드는 채널분리 불가(통합ROAS).\n아래 실데이터로 \"수치 나열이 아니라 원인·시사점·조치까지 해석하는\" 분석을 한국어로 작성하라. 숫자 근거 필수, 과장 금지.\n절대룰: 매출1위(rules.neverOff) OFF금지(감액만), 고순위·기증액분(rules.noIncrease) 추가증액 금지(학습 리셋), 자사 브랜드 키워드/AI매출업 강화 자제, 단일일·주말·특정요일 부진만으로 OFF금지.\n반드시 아래 JSON만 출력(코드블록·설명 금지):\n{\"headline\":\"한 줄 결론(전략 검증 + 이번 주 핵심, 80자내)\",\"channel\":\"채널 전략 해석 2~3문장(옥션 우위·재배분 win-win 여부)\",\"drop\":\"최근7일 추세/급락 진단 2~3문장(원인 분해: 기저효과/채널/요일)\",\"products\":\"상품·그룹 판단 2~3문장(저효율 대형 드래그·숨은 고효율 기회 지목)\",\"keywords\":\"키워드 판단 2~3문장(최대지출 저효율 등)\",\"insights\":[\"핵심 인사이트 6개(숫자 근거)\"],\"actions\":[\"오늘 우선순위 액션 6개: '대상 — 조치 — 근거' 형식\"],\"memos\":[\"의사결정 메모 5개\"],\"principles\":[\"BEP 5대 원칙\"]}\n데이터: ${JSON.stringify(ctx)}`;\n const ar = await fetch(\"https://api.anthropic.com/v1/messages\", {\n method: \"POST\",\n headers: {\n \"x-api-key\": key,\n \"anthropic-version\": \"2023-06-01\",\n \"content-type\": \"application/json\",\n },\n body: JSON.stringify({\n model: AI_MODEL,\n max_tokens: 4096,\n messages: [{ role: \"user\", content: prompt }],\n }),\n });\n if (!ar.ok) {\n throw new Error(\n \"anthropic \" + ar.status + \" \" + (await ar.text()).slice(0, 200),\n );\n }\n const aj = await ar.json();\n let txt = (aj.content?.[0]?.text || \"\").trim();\n const s = txt.indexOf(\"{\"), e = txt.lastIndexOf(\"}\");\n if (s >= 0 && e > s) txt = txt.slice(s, e + 1);\n const ai = JSON.parse(txt);\n await blob.setJSON(AI_BLOB, {\n ...ai,\n model: AI_MODEL,\n generatedAt: new Date().toISOString(),\n window: payload.window,\n });\n console.log(\n `[esmAnalyticsSync v3] Claude 분석 완료 (insights ${\n (ai.insights || []).length\n }, actions ${(ai.actions || []).length})`,\n );\n } catch (e) {\n console.log(\"[esmAnalyticsSync v3] Claude 분석 건너뜀: \" + e.message);\n }\n\n return { ok: true, folder: folder.name, totals, channel };\n}","createdAt",["D",1782104737568],"branchVersion","branchId","ef84c82c-4cde-11f1-bca8-ee650bb23af1","id","ef82bd7a-4cde-11f1-bca8-ee650bb23af1","Marketingauto",["D",1778465580996],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","imageUrl","93449a43-3797-404d-b4ae-d751bcdd306b","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019e14ce-2bdb-768c-a9d4-d59a30b7544b","tursoName","ef82bd7a4cde11f1bca8ee650bb23af1","tursoHostname","ef82bd7a4cde11f1bca8ee650bb23af1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789491713377],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_97":96,"_105":101},"org",{"_97":96,"_98":99,"_100":101,"_18":54,"_102":103,"_104":25},"branch",{"_13":86,"_32":87,"_43":42,"_88":44,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":45,"_95":96},"isOwned",134,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","joowoo","handle","displayName","joo woo","avatar","https://lh3.googleusercontent.com/a/ACg8ocJHI4ZtyHAcRli0zHzjn03GuJOb_BpuVst0ZtYzcbbGbLzMGA=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_32":45,"_38":245,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":-5,"_53":-5,"_18":54,"_55":-5,"_56":25,"_57":-5,"_58":-5,"_59":60,"_61":62,"_63":64,"_65":66,"_67":246,"_69":-5,"_70":71,"_72":71,"_73":74,"_75":74,"_76":74,"_77":71,"_78":71,"_79":247,"_81":248,"_83":249,"_85":25},"tree",[212,213,214,215,216,217,218,219],"files",[212,213,214,215,216,217,218,219],"counts",{"_199":200,"_201":202,"_203":60,"_204":205,"_206":207},"contents",[129,130,131,132,133,134,135,136],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_43":192,"_32":193,"_30":194,"_140":-5,"_41":42,"_40":195,"_142":124,"_38":196,"_13":170,"_33":34,"_35":197,"_24":25,"_146":-5,"_27":198},{"_43":184,"_32":185,"_30":186,"_140":-5,"_41":42,"_40":187,"_142":124,"_38":188,"_13":189,"_33":34,"_35":190,"_24":25,"_146":-5,"_27":191},{"_43":177,"_32":178,"_30":179,"_140":-5,"_41":42,"_40":180,"_142":124,"_38":181,"_13":170,"_33":34,"_35":182,"_24":25,"_146":-5,"_27":183},{"_43":173,"_32":28,"_30":31,"_140":-5,"_41":42,"_40":14,"_142":25,"_38":174,"_13":175,"_33":34,"_35":37,"_24":25,"_146":-5,"_27":176},{"_43":165,"_32":166,"_30":167,"_140":-5,"_41":42,"_40":168,"_142":25,"_38":169,"_13":170,"_33":162,"_35":171,"_24":25,"_146":-5,"_27":172},{"_43":156,"_32":157,"_30":158,"_140":-5,"_41":42,"_40":159,"_142":124,"_38":160,"_13":161,"_33":162,"_35":163,"_24":25,"_146":-5,"_27":164},{"_43":148,"_32":149,"_30":150,"_140":-5,"_41":42,"_40":151,"_142":124,"_38":152,"_13":153,"_33":34,"_35":154,"_24":25,"_146":-5,"_27":155},{"_43":137,"_32":138,"_30":139,"_140":-5,"_41":42,"_40":141,"_142":124,"_38":143,"_13":144,"_33":34,"_35":145,"_24":25,"_146":-5,"_27":147},"66dbd250-d3f5-412c-aec5-961e637df67b","weeklyBrandRatioSync.ts","2b7f02ba-6a31-11f1-b377-1607ee4eb77e","parentId",51,"latest",["D",1781744680319],10,"/**\n * weeklyBrandRatioSync.ts v4 (2026-06-17 패치) — 주 1회 일요일 02:00 KST\n *\n * v3 대비 변경:\n * - Slack 알림 완전 제거 (불필요)\n * - 결과는 console.log 및 return 값으로만 확인. 필요 시 val.town Logs 에서 직접 확인.\n * - 실패해도 Slack 안 보내고 그냥 throw\n *\n * ⚠️ Cron 설정 주의:\n * val.town 에서 스케줄을 반드시 \"0 17 * * 6\" (UTC 토요일 17:00 = KST 일요일 02:00) 으로 설정.\n * 현재 \"0 * * * *\" (매시간) 으로 잘못 등록되어 있으면 매시간 비율 재계산이 발생함.\n *\n * Blob key: esm_brand_ratio_latest\n */\nimport * as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst BLOB_RATIO_KEY = \"esm_brand_ratio_latest\";\nconst TONGSPACE_KEYWORDS = [\n \"텅스\",\n \"곤약\",\n \"쫀드기\",\n \"쫀디기\",\n \"쫀뜨기\",\n \"스파이크샷\",\n];\n\nfunction dateKST(o: number): string {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v: any): number {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction classifyBrand(name: string): \"풍년보감\" | \"텅스페이스\" {\n for (const kw of TONGSPACE_KEYWORDS) {\n if (name.includes(kw)) return \"텅스페이스\";\n }\n return \"풍년보감\";\n}\n\nasync function getDriveAccessToken(): Promise {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\")!,\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\")!,\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\")!,\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId: string, token: string): Promise {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownload(\n fileId: string,\n token: string,\n): Promise {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.arrayBuffer();\n}\nasync function findLatestDailyFolder(\n rootId: string,\n targetDate: string,\n token: string,\n) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n ).sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\n\nfunction parseXlsx(buf: ArrayBuffer): Record[] {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sheet = wb.Sheets[wb.SheetNames[0]];\n return XLSX.utils.sheet_to_json(sheet, {\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n}\n\nexport default async function () {\n const targetDate = dateKST(-1);\n const accessToken = await getDriveAccessToken();\n\n const folder = await findLatestDailyFolder(\n ESM_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"ESM 어제 폴더 없음\");\n const files = await driveList(folder.id, accessToken);\n const productReportFile = files.find((f) =>\n f.name.startsWith(\"AI매출업_상품별리포트_\")\n );\n if (!productReportFile) throw new Error(\"AI매출업_상품별리포트 파일 없음\");\n\n const esmRoot = await driveList(ESM_DRIVE_FOLDER_ID, accessToken);\n const productListFile = esmRoot.find((f) =>\n f.name.startsWith(\"goods-list-result_\")\n );\n if (!productListFile) {\n throw new Error(\"goods-list-result_상품리스트 파일 없음\");\n }\n\n const [productReportBuf, productListBuf] = await Promise.all([\n driveDownload(productReportFile.id, accessToken),\n driveDownload(productListFile.id, accessToken),\n ]);\n\n const productList = parseXlsx(productListBuf);\n const skuToName = new Map();\n for (const r of productList) {\n const id = String(r[\"상품번호\"] || \"\").trim();\n const name = String(r[\"상품명\"] || \"\").trim();\n if (id && name) skuToName.set(id, name);\n }\n\n const productReport = parseXlsx(productReportBuf);\n let pn30 = 0, ts30 = 0, matchedCount = 0, unmatchedCount = 0;\n for (const r of productReport) {\n const sku = String(r[\"상품번호\"] || \"\").trim();\n const nameFromMaster = skuToName.get(sku);\n const name = nameFromMaster || String(r[\"그룹명\"] || \"\");\n const brand = classifyBrand(name);\n const rev = num(r[\"판매자기준-구매금액\"]);\n if (rev <= 0) continue;\n if (brand === \"텅스페이스\") ts30 += rev;\n else pn30 += rev;\n if (nameFromMaster) matchedCount++;\n else unmatchedCount++;\n }\n\n const sum30 = pn30 + ts30;\n if (sum30 === 0) throw new Error(\"30일 누적 매출이 0 — 데이터 점검 필요\");\n const pnRatio = pn30 / sum30;\n const tsRatio = 1 - pnRatio;\n\n const payload = {\n pnRatio,\n tsRatio,\n pn30,\n ts30,\n calculatedAt: new Date().toISOString(),\n targetDate,\n productCount: productReport.length,\n masterMatchedCount: matchedCount,\n masterUnmatchedCount: unmatchedCount,\n };\n await blob.setJSON(BLOB_RATIO_KEY, payload);\n\n console.log(\n `[weeklyBrandRatioSync v4] ${targetDate} | pn ${\n (pnRatio * 100).toFixed(1)\n }% / ts ${\n (tsRatio * 100).toFixed(1)\n }% | 매핑 ${matchedCount}/${unmatchedCount}`,\n );\n return { ok: true, ...payload };\n}","label",[138],"a1dcaa25-ccba-4bb1-8083-6e6ce92d530a","slackDailyCron.ts","0dd272b4-4f66-11f1-be74-ee650bb23af1",113,["D",1781860048011],15,"/**\n * slackDailyCron.ts v4 (2026-06-19) — 매일 08:00 KST Slack 발송\n * v4: ESM 매출 버튼 → ESM 심층 보고서(esmFullReport) 연결. 발송 11:00 KST. Schedule: 0 2 * * * (UTC=11:00 KST)\n */\nimport { blob } from \"https://esm.town/v/std/blob\";\nconst API_BASE = \"https://api.searchad.naver.com\";\nconst BLOB_AI_KEY = \"ai_analysis_latest\";\nconst TONGSPACE_KEYWORDS = [\n \"텅스\",\n \"곤약\",\n \"쫀드기\",\n \"쫀디기\",\n \"쫀뜨기\",\n \"스파이크샷\",\n];\nconst NOTION_DB_DEFAULT = \"5828d1bf-aa95-4662-bdf2-fae160d0654f\";\nconst ESM_NOTION_DB_DEFAULT = \"5ed8388163b1468fa24b0e966291fd35\";\nconst REPORT_URL =\n \"https://joowoo--f970e0684cf811f1801eee650bb23af1.web.val.run\";\nconst TOSS_REPORT_URL = Deno.env.get(\"TOSS_REPORT_URL\") ||\n \"https://joowoo--8a0b13c04eb911f1b7d6ee650bb23af1.web.val.run\";\nconst ESM_REPORT_URL = Deno.env.get(\"ESM_REPORT_URL\") ||\n \"https://joowoo--73f23f386ba511f193d01607ee4eb77e.web.val.run\"; // ESM 심층 보고서(esmFullReport)\nconst SLACK_CHANNEL_ID = Deno.env.get(\"SLACK_CHANNEL_ID\") || \"C09B0J6QXN1\";\nconst MARKETING_CELL_SUBTEAM = Deno.env.get(\"MARKETING_CELL_SUBTEAM\") ||\n \"S09PHRXUR0W\";\nasync function fetchTossKPI(targetDate) {\n try {\n const resp = await fetch(`${TOSS_REPORT_URL}?kpi=1&date=${targetDate}`);\n if (!resp.ok) return null;\n return await resp.json();\n } catch (e) {\n return null;\n }\n}\nfunction classifyBrand(name) {\n for (const kw of TONGSPACE_KEYWORDS) {\n if (name.includes(kw)) return \"텅스페이스\";\n }\n return \"풍년보감\";\n}\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction stripTags(s) {\n return s.replace(/<[^>]+>/g, \"\").replace(/ /g, \" \").replace(\n /&/g,\n \"&\",\n ).replace(/</g, \"<\").replace(/>/g, \">\").replace(/"/g, '\"').trim();\n}\nfunction fmt(n) {\n return n.toLocaleString(\"ko-KR\") + \"원\";\n}\nasync function makeSignature(secretKey, ts, method, uri) {\n const enc = new TextEncoder();\n const key = await crypto.subtle.importKey(\n \"raw\",\n enc.encode(secretKey),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\"],\n );\n const sig = await crypto.subtle.sign(\n \"HMAC\",\n key,\n enc.encode(`${ts}.${method}.${uri}`),\n );\n return btoa(String.fromCharCode(...new Uint8Array(sig)));\n}\nasync function callNaver(uri, qs = {}) {\n const apiKey = Deno.env.get(\"NAVER_SA_API_KEY\");\n const secretKey = Deno.env.get(\"NAVER_SA_SECRET_KEY\");\n const customerId = Deno.env.get(\"NAVER_SA_CUSTOMER_ID\");\n const ts = String(Date.now());\n const sig = await makeSignature(secretKey, ts, \"GET\", uri);\n const url = new URL(`${API_BASE}${uri}`);\n for (const [k, v] of Object.entries(qs)) url.searchParams.set(k, v);\n const res = await fetch(url.toString(), {\n headers: {\n \"X-Timestamp\": ts,\n \"X-API-KEY\": apiKey,\n \"X-Customer\": customerId,\n \"X-Signature\": sig,\n },\n });\n if (!res.ok) throw new Error(`${uri} ${res.status}`);\n return res.json();\n}\nasync function fetchYesterdayKPI(targetDate) {\n const campaigns = await callNaver(\"/ncc/campaigns\");\n const cmap = new Map(\n campaigns.map((\n c,\n ) => [c.nccCampaignId, { ...c, brand: classifyBrand(c.name) }]),\n );\n const ids = campaigns.map((c) => c.nccCampaignId).join(\",\");\n const stats = await callNaver(\"/stats\", {\n ids,\n fields: '[\"impCnt\",\"clkCnt\",\"salesAmt\",\"ccnt\",\"ctr\",\"cpc\",\"convAmt\"]',\n timeRange: JSON.stringify({ since: targetDate, until: targetDate }),\n });\n const kpi = {\n \"풍년보감\": { spend: 0, rev: 0, conv: 0 },\n \"텅스페이스\": { spend: 0, rev: 0, conv: 0 },\n };\n for (const s of (stats?.data || [])) {\n const c = cmap.get(s.id);\n if (!c) continue;\n kpi[c.brand].spend += s.salesAmt || 0;\n kpi[c.brand].rev += s.convAmt || 0;\n kpi[c.brand].conv += s.ccnt || 0;\n }\n const pn = kpi[\"풍년보감\"], ts = kpi[\"텅스페이스\"];\n const total = {\n spend: pn.spend + ts.spend,\n rev: pn.rev + ts.rev,\n conv: pn.conv + ts.conv,\n };\n return {\n pn,\n ts,\n total,\n pnRoas: pn.spend > 0 ? Math.round(pn.rev / pn.spend * 100) : 0,\n tsRoas: ts.spend > 0 ? Math.round(ts.rev / ts.spend * 100) : 0,\n totalRoas: total.spend > 0 ? Math.round(total.rev / total.spend * 100) : 0,\n };\n}\nfunction extractSectionItems(html, sectionTitle, max = 3) {\n const re = new RegExp(`

${sectionTitle}

([\\\\s\\\\S]*?)(?=

|$)`, \"\");\n const m = html.match(re);\n if (!m) return [];\n const content = m[1];\n const items = [];\n const liRe = /
([\\s\\S]?)<\\/li>/g;\n let lm;\n while ((lm = liRe.exec(content)) !== null) {\n const t = stripTags(lm[1]);\n if (t) items.push(t);\n if (items.length >= max) return items;\n }\n if (items.length > 0) return items;\n const rowRe = /([\\s\\S]?)<\\/tr>/g;\n let rm;\n let isFirst = true;\n while ((rm = rowRe.exec(content)) !== null) {\n if (isFirst) {\n isFirst = false;\n continue;\n }\n const cellRe = /]>([\\s\\S]?)<\\/t[hd]>/g;\n const cells = [];\n let cm;\n while ((cm = cellRe.exec(rm[1])) !== null) cells.push(stripTags(cm[1]));\n if (cells.length) items.push(cells.join(\" · \"));\n if (items.length >= max) return items;\n }\n return items;\n}\nfunction extractInsight(html) {\n const m = html.match(/
핵심 인사이트<\\/h3>\\s*
([\\s\\S]?)<\\/p>/);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html) {\n const m = html.match(\n /
어제 권장 액션 후속 추적<\\/h3>([\\s\\S]?)(?=
|$)/,\n );\n if (!m) return \"전일 분석 데이터 없음\";\n const text = stripTags(m[1]).slice(0, 400);\n return text || \"전일 분석 데이터 없음\";\n}\nasync function findYesterdayNotionPage(targetDate) {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const dbId = Deno.env.get(\"NOTION_DB_ID\") || NOTION_DB_DEFAULT;\n if (!token) return null;\n try {\n const resp = await fetch(\n `https://api.notion.com/v1/databases/${dbId}/query`,\n {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n },\n );\n if (!resp.ok) return null;\n const data = await resp.json();\n return data.results?.[0]?.url || null;\n } catch (e) {\n return null;\n }\n}\nasync function slackPost(method, body, token) {\n const resp = await fetch(`https://slack.com/api/${method}`, {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Content-Type\": \"application/json; charset=utf-8\",\n },\n body: JSON.stringify(body),\n });\n const data = await resp.json();\n if (!data.ok) throw new Error(`Slack ${method} failed: ${data.error}`);\n return data;\n}\nexport default async function () {\n const yesterday = dateKST(-1);\n const slackToken = Deno.env.get(\"SLACK_BOT_TOKEN\");\n if (!slackToken) throw new Error(\"SLACK_BOT_TOKEN not set\");\n let aiAnalysis = null;\n try {\n aiAnalysis = await blob.getJSON(BLOB_AI_KEY);\n } catch (e) {}\n const aiHtml = aiAnalysis?.html || \"\";\n const [k, tossKpi, notionUrl] = await Promise.all([\n fetchYesterdayKPI(yesterday),\n fetchTossKPI(yesterday),\n findYesterdayNotionPage(yesterday).then((u) =>\n u ||\n \"https://www.notion.so/\" +\n (Deno.env.get(\"NOTION_DB_ID\") || NOTION_DB_DEFAULT).replace(/-/g, \"\")\n ),\n ]);\n const insight = extractInsight(aiHtml);\n const signals = extractSectionItems(aiHtml, \"핵심 인사이트\", 0).length === 0\n ? extractSectionItems(aiHtml, \"캠페인 단위 데일리 액션\", 3)\n : [];\n const signalsFinal = signals.length > 0\n ? signals\n : (insight\n ? [insight]\n : [\"AI 분석 데이터 없음 — 오늘 07:30 정기 실행 후 적재 예정\"]);\n const top3 = extractSectionItems(aiHtml, \"오늘 즉시 실행 우선순위\", 3);\n const top3Final = top3.length > 0\n ? top3\n : [\"AI 분석 미작성 — 데이터 확인 후 수동 액션 검토 필요\"];\n const followup = extractFollowup(aiHtml);\n const mainBlocks = [\n {\n type: \"section\",\n text: {\n type: \"mrkdwn\",\n text:\n ` 좋은 아침! 어제 (${yesterday}) 마케팅 리포트가 준비됐어요.\\n\\nPNBG 풍년보감 + 텅스페이스의 검색광고 성과, 토스쇼핑 매출, ESM(G마켓+옥션) 매출을 한눈에 확인하세요.`,\n },\n },\n {\n type: \"actions\",\n elements: [\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"네이버 검색광고 열기\" },\n url: REPORT_URL,\n style: \"primary\",\n },\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"토스 매출 열기\" },\n url: TOSS_REPORT_URL,\n },\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"ESM 매출 열기\" },\n url: ESM_REPORT_URL,\n },\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"노션 페이지\" },\n url: notionUrl,\n },\n ],\n },\n ];\n const mainResp = await slackPost(\"chat.postMessage\", {\n channel: SLACK_CHANNEL_ID,\n text: `${yesterday} 마케팅 리포트 준비됨`,\n blocks: mainBlocks,\n unfurl_links: false,\n unfurl_media: false,\n }, slackToken);\n const threadTs = mainResp.ts;\n const cell = (text, bold = false) => ({\n type: \"rich_text\",\n elements: [{\n type: \"rich_text_section\",\n elements: [{\n type: \"text\",\n text,\n ...(bold ? { style: { bold: true } } : {}),\n }],\n }],\n });\n const signalsBlock = `핵심 시그널 ${signalsFinal.length}가지\\n` +\n signalsFinal.slice(0, 3).map((s, i) => `${i + 1}. ${s.slice(0, 250)}`).join(\n \"\\n\",\n );\n const top3Block = `오늘 실행 TOP 3\\n` +\n top3Final.slice(0, 3).map((s, i) => `${i + 1}. ${s.slice(0, 250)}`).join(\n \"\\n\",\n );\n const followupBlock = `어제 집행률\\n${followup.slice(0, 300)}`;\n const blocks = [\n {\n type: \"section\",\n text: { type: \"mrkdwn\", text: `네이버 검색광고 KPI (${yesterday})` },\n },\n {\n type: \"table\",\n rows: [\n [\n cell(\"구분\", true),\n cell(\"광고비\", true),\n cell(\"매출\", true),\n cell(\"ROAS\", true),\n ],\n [\n cell(\"통합\"),\n cell(fmt(k.total.spend)),\n cell(fmt(k.total.rev)),\n cell(`${k.totalRoas.toLocaleString()}%`),\n ],\n [\n cell(\"풍년보감\"),\n cell(fmt(k.pn.spend)),\n cell(fmt(k.pn.rev)),\n cell(`${k.pnRoas.toLocaleString()}%`),\n ],\n [\n cell(\"텅스페이스\"),\n cell(fmt(k.ts.spend)),\n cell(fmt(k.ts.rev)),\n cell(`${k.tsRoas.toLocaleString()}%`),\n ],\n ],\n },\n ];\n if (tossKpi && !tossKpi.error) {\n blocks.push({ type: \"divider\" });\n blocks.push({\n type: \"section\",\n text: { type: \"mrkdwn\", text: `토스쇼핑 매출 KPI (${yesterday})` },\n });\n blocks.push({\n type: \"table\",\n rows: [\n [cell(\"구분\", true), cell(\"매출\", true), cell(\"주문수\", true)],\n [\n cell(\"통합\"),\n cell(fmt(tossKpi.totalRev || 0)),\n cell(`${(tossKpi.totalOrders || 0).toLocaleString()}건`),\n ],\n [\n cell(\"풍년보감\"),\n cell(fmt(tossKpi.pn?.rev || 0)),\n cell(`${(tossKpi.pn?.orders || 0).toLocaleString()}건`),\n ],\n [\n cell(\"텅스페이스\"),\n cell(fmt(tossKpi.ts?.rev || 0)),\n cell(`${(tossKpi.ts?.orders || 0).toLocaleString()}건`),\n ],\n ],\n });\n }\n blocks.push({ type: \"divider\" });\n blocks.push({\n type: \"section\",\n text: { type: \"mrkdwn\", text: signalsBlock },\n });\n blocks.push({ type: \"section\", text: { type: \"mrkdwn\", text: top3Block } });\n blocks.push({\n type: \"section\",\n text: { type: \"mrkdwn\", text: followupBlock },\n });\n blocks.push({\n type: \"context\",\n elements: [{\n type: \"mrkdwn\",\n text:\n \"_액션플랜을 수정하시려면 이 스레드에 ✏️수정 액션플랜: 으로 시작하는 댓글을 작성하세요. 5분 내 보고서와 노션에 자동 반영됩니다. ESM 매출은 오후 14:10 갱신 후 'ESM 매출 열기' 버튼에서 확인하세요._\",\n }],\n });\n await slackPost(\"chat.postMessage\", {\n channel: SLACK_CHANNEL_ID,\n text: `요약 KPI ${yesterday} — ${k.totalRoas}% ROAS`,\n blocks,\n thread_ts: threadTs,\n unfurl_links: false,\n unfurl_media: false,\n }, slackToken);\n console.log(\n `[OK] Slack 발송 완료 — ${yesterday} | main_ts=${threadTs} | esm=${ESM_REPORT_URL}`,\n );\n return {\n ok: true,\n date: yesterday,\n mainTs: threadTs,\n esmReportUrl: ESM_REPORT_URL,\n };\n}",[149],"7baac7b2-b886-4172-9b49-6acdff97e727","removeSlack.ts","0d1d2c9a-6acf-11f1-9a5f-1607ee4eb77e",72,["D",1781760512003],12,"http","/**\n * cleanupPNBGBot.ts — \"PNBG 마케팅 인사이트\" 봇 메시지 일괄 삭제\n \n 핵심:\n * - bot_id = \"B0B3FM7G2CW\" (PNBG 마케팅 인사이트) 메시지만 대상\n * - 다른 봇 (\"항목 업데이트 알림\" 등) 은 건드리지 않음 — 안전\n * - 스레드 답글까지 모두 삭제\n \n 사용:\n * 1. val.town → 기존 코드 자리에 이 코드 교체 → Save\n * 2. [▶ Run] → 응답 확인\n * 3. found = 0 나올 때까지 반복 Run\n * 4. 작업 끝나면 이 Val 자체 삭제\n \n 환경변수: SLACK_BOT_TOKEN\n /\n\nconst SLACK_CHANNEL = \"C09B0J6QXN1\";\n\n// 삭제 대상 봇 ID (PNBG 마케팅 인사이트만)\nconst TARGET_BOT_IDS = [\"B0B3FM7G2CW\"];\n\n// 추가 안전장치: app_id 도 매칭\nconst TARGET_APP_IDS = [\"A0B328T36PR\"];\n\nfunction isTargetBot(msg: any): boolean {\n if (msg.bot_id && TARGET_BOT_IDS.includes(msg.bot_id)) return true;\n if (msg.app_id && TARGET_APP_IDS.includes(msg.app_id)) return true;\n // username 으로도 한 번 더 매칭 (혹시 bot_id 가 다를 때 대비)\n const name = msg.username || msg.bot_profile?.name || \"\";\n if (\n name.includes(\"PNBG 마케팅 인사이트\") || name.includes(\"마케팅 인사이트 봇\")\n ) return true;\n return false;\n}\n\nasync function slackApi(\n method: string,\n params: Record,\n token: string,\n): Promise {\n const url = `https://slack.com/api/${method}`;\n const isGet = method === \"conversations.history\" ||\n method === \"conversations.replies\";\n if (isGet) {\n const qs = new URLSearchParams(params as any).toString();\n const r = await fetch(`${url}?${qs}`, {\n headers: { \"Authorization\": `Bearer ${token}` },\n });\n return r.json();\n } else {\n const r = await fetch(url, {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Content-Type\": \"application/json; charset=utf-8\",\n },\n body: JSON.stringify(params),\n });\n return r.json();\n }\n}\n\nexport default async function (): Promise {\n const token = Deno.env.get(\"SLACK_BOT_TOKEN\");\n if (!token) {\n return Response.json({ error: \"SLACK_BOT_TOKEN not set\" }, { status: 500 });\n }\n\n const hist = await slackApi(\n \"conversations.history\",\n { channel: SLACK_CHANNEL, limit: 200 },\n token,\n );\n if (!hist.ok) {\n return Response.json(\n { error: \"history fetch failed\", details: hist.error },\n { status: 500 },\n );\n }\n\n const allTargets: Array<\n {\n ts: string;\n text: string;\n time: string;\n isReply: boolean;\n username: string;\n }\n > = [];\n\n for (const parent of (hist.messages || [])) {\n if (isTargetBot(parent)) {\n allTargets.push({\n ts: parent.ts,\n text: (parent.text || \"\").slice(0, 100),\n time: new Date(parseFloat(parent.ts) 1000).toLocaleString(\"ko-KR\", {\n timeZone: \"Asia/Seoul\",\n }),\n isReply: false,\n username: parent.username || parent.bot_profile?.name || \"unknown\",\n });\n }\n\n if (parent.reply_count && parent.reply_count > 0) {\n const replies = await slackApi(\n \"conversations.replies\",\n { channel: SLACK_CHANNEL, ts: parent.ts, limit: 100 },\n token,\n );\n if (replies.ok) {\n for (const r of (replies.messages || []).slice(1)) {\n if (isTargetBot(r)) {\n allTargets.push({\n ts: r.ts,\n text: (r.text || \"\").slice(0, 100),\n time: new Date(parseFloat(r.ts) * 1000).toLocaleString(\"ko-KR\", {\n timeZone: \"Asia/Seoul\",\n }),\n isReply: true,\n username: r.username || r.bot_profile?.name || \"unknown\",\n });\n }\n }\n }\n await new Promise((res) => setTimeout(res, 100));\n }\n }\n\n if (allTargets.length === 0) {\n return Response.json({\n ok: true,\n channel: SLACK_CHANNEL,\n deleted: 0,\n message: \"삭제 대상 없음 — 정리 완료. 이 Val 자체를 삭제하세요.\",\n });\n }\n\n const results: any[] = [];\n for (const t of allTargets) {\n const del = await slackApi(\n \"chat.delete\",\n { channel: SLACK_CHANNEL, ts: t.ts },\n token,\n );\n results.push({\n time: t.time,\n isReply: t.isReply,\n username: t.username,\n preview: t.text,\n ok: del.ok,\n error: del.error || null,\n });\n await new Promise((res) => setTimeout(res, 200));\n }\n\n const okCount = results.filter((r) => r.ok).length;\n const failCount = results.length - okCount;\n\n return Response.json({\n ok: true,\n channel: SLACK_CHANNEL,\n targetBotIds: TARGET_BOT_IDS,\n found: allTargets.length,\n deleted: okCount,\n failed: failCount,\n results,\n note: failCount > 0\n ? \"일부 실패 — 1분 후 다시 Run.\"\n : \"모두 삭제 완료. found=0 나올 때까지 한 번 더 Run 후 이 Val 자체 삭제.\",\n });\n}",[157],"9e328225-7783-41e8-afda-2b2378a122aa","esmFullReport.ts","73f23f38-6ba5-11f1-93d0-1607ee4eb77e",115,["D",1782104505572],8,"/**\n * esmFullReport.ts v3 (2026-06-19) — 보고서 + 액션플랜 + Claude 심층 해석 렌더 (HTTP)\n * blob esm_analytics_latest + esm_ai_insight_latest 읽어 렌더.\n /\nimport { blob } from \"https://esm.town/v/std/blob\";\nconst OUT_BLOB = \"esm_analytics_latest\";\nconst AI_BLOB = \"esm_ai_insight_latest\";\n\nconst won = (n) => \"₩\" + (n || 0).toLocaleString(\"ko-KR\");\nconst pct = (n) => (n || 0).toLocaleString(\"ko-KR\") + \"%\";\nconst esc = (s) =>\n String(s ?? \"\").replace(/&/g, \"&\").replace(//g,\n \">\",\n );\nconst cls = (r) => r >= 1000 ? \"good\" : r < 400 ? \"bad\" : r < 600 ? \"warn\" : \"\";\n\nexport default async function (req) {\n const d = await blob.getJSON(OUT_BLOB).catch(() => null);\n const ai = await blob.getJSON(AI_BLOB).catch(() => null);\n if (new URL(req.url).searchParams.get(\"json\") === \"1\") {\n return Response.json({ data: d, ai });\n }\n if (!d) {\n return new Response(\n page(\n \"
분석 데이터 없음. esmAnalyticsSync 를 먼저 실행하세요.
\",\n \"ESM 통합 보고서\",\n ),\n { headers: { \"Content-Type\": \"text/html; charset=utf-8\" } },\n );\n }\n\n const c = d.channel, t = d.totals;\n const roas = (r, a) => a > 0 ? Math.round(r / a 100) : 0;\n const last7 = d.daily.slice(-7), prev7 = d.daily.slice(-14, -7);\n const sum7 = (arr, k) => arr.reduce((s, x) => s + (x[k] || 0), 0);\n const s7ad = sum7(last7, \"ad\"),\n s7rev = sum7(last7, \"rev\"),\n p7ad = sum7(prev7, \"ad\"),\n p7rev = sum7(prev7, \"rev\");\n const a7A = sum7(last7, \"aA\"),\n r7A = sum7(last7, \"rA\"),\n a7G = sum7(last7, \"aG\"),\n r7G = sum7(last7, \"rG\");\n\n const trH = (cells) =>\n \"\" + cells.map((x, i) =>\n `${x}`\n ).join(\"\") + \"\";\n const trD = (cells, cl = []) =>\n \"\" + cells.map((x, i) =>\n `${x}`\n ).join(\"\") + \"\";\n const ulOf = (arr) =>\n arr && arr.length\n ? \"
\" + arr.map((x) => `
${esc(x)}
`).join(\"\") + \"
\"\n : \"\";\n const read = (label, txt) =>\n txt ? `
${label} — ${esc(txt)}
` : \"\";\n\n const ch0 = `${\n trH([\n \"채널\",\n \"SA 비용\",\n \"AI 비용\",\n \"통합 비용\",\n \"순매출\",\n \"ROAS\",\n \"비용점유\",\n \"매출점유\",\n ])\n }\n${\n trD([\n \"옥션\",\n c.auction.sa.toLocaleString(),\n c.auction.ai.toLocaleString(),\n c.auction.ad.toLocaleString(),\n won(c.auction.rev),\n pct(c.auction.roas),\n t.auctionCostShare + \"%\",\n t.auctionRevShare + \"%\",\n ], [\"\", \"\", \"\", \"\", \"\", cls(c.auction.roas)])\n }\n${\n trD([\n \"지마켓\",\n c.gmarket.sa.toLocaleString(),\n c.gmarket.ai.toLocaleString(),\n c.gmarket.ad.toLocaleString(),\n won(c.gmarket.rev),\n pct(c.gmarket.roas),\n (100 - t.auctionCostShare).toFixed(1) + \"%\",\n (100 - t.auctionRevShare).toFixed(1) + \"%\",\n ], [\"\", \"\", \"\", \"\", \"\", cls(c.gmarket.roas)])\n }\n${\n trD([\n \"통합\",\n (c.auction.sa + c.gmarket.sa).toLocaleString(),\n (c.auction.ai + c.gmarket.ai).toLocaleString(),\n t.ad.toLocaleString(),\n won(t.rev),\n pct(t.roas),\n \"100%\",\n \"100%\",\n ])\n }
`;\n const realloc = `${\n trH([\"재배분(지마켓 SA→옥션)\", \"통합 ROAS\", \"순매출\"])\n }\n${trD([\"현재\", pct(t.roas), won(t.rev)])}\n${\n d.realloc.map((r) =>\n trD([\n `−${r.pct}%`,\n `${pct(r.roas)}`,\n won(r.rev),\n ])\n ).join(\"\")\n }
`;\n const grpTbl = `${trH([\"광고그룹\", \"비용\", \"매출\", \"ROAS\", \"CTR\"])}${\n d.groups.map((g) =>\n trD([\n esc(g.group),\n g.cost.toLocaleString(),\n won(g.rev),\n pct(g.roas),\n g.ctr + \"%\",\n ], [\"\", \"\", \"\", cls(g.roas), \"\"])\n ).join(\"\")\n }
`;\n const prodTbl = `${\n trH([\"사이트\", \"상품번호\", \"비용\", \"매출\", \"ROAS\", \"노출순위\"])\n }${\n d.products.map((p) =>\n trD([\n p.site,\n p.id,\n p.cost.toLocaleString(),\n won(p.rev),\n pct(p.roas),\n String(p.rank || \"-\"),\n ], [\"\", \"\", \"\", \"\", cls(p.roas), \"\"])\n ).join(\"\")\n }
`;\n const kwTbl = `${trH([\"키워드\", \"비용\", \"매출\", \"ROAS\"])}${\n d.keywords.slice(0, 10).map((k) =>\n trD([esc(k.kw), k.cost.toLocaleString(), won(k.rev), pct(k.roas)], [\n \"\",\n \"\",\n \"\",\n cls(k.roas),\n ])\n ).join(\"\")\n }
`;\n const dailyTbl = `${\n trH([\"날짜\", \"요일\", \"통합비\", \"순매출\", \"ROAS\"])\n }${\n d.daily.slice(-8).map((x) =>\n trD([\n x.date.slice(5),\n x.wd,\n x.ad.toLocaleString(),\n won(x.rev),\n pct(x.roas),\n ], [\"\", \"\", \"\", \"\", cls(x.roas)])\n ).join(\"\")\n }
`;\n const wdTbl = `${trH([\"요일\", \"ROAS\"])}${\n d.weekdays.map((w) => trD([w.wd, pct(w.roas)], [\"\", cls(w.roas)])).join(\"\")\n }
`;\n const aiTbl = `${trH([\"채널\", \"AI 비용\", \"AI 실판매\", \"AI ROAS\"])}\n${\n trD([\n \"옥션 AI\",\n c.auction.ai.toLocaleString(),\n won(c.auction.aiRev),\n pct(c.auction.aiRoas),\n ], [\"\", \"\", \"\", cls(c.auction.aiRoas)])\n }\n${\n trD([\n \"지마켓 AI\",\n c.gmarket.ai.toLocaleString(),\n won(c.gmarket.aiRev),\n pct(c.gmarket.aiRoas),\n ], [\"\", \"\", \"\", cls(c.gmarket.aiRoas)])\n }\n${\n trD([\n \"AI 합계\",\n t.aiTotal.toLocaleString(),\n won(t.aiRev),\n pct(t.aiRoas),\n ])\n }
`;\n\n const aA = d.actions.A.length\n ? `${\n trH([\"상품(옥션)\", \"ROAS\", \"비용\", \"매출\", \"노출순위\", \"조치\"])\n }${\n d.actions.A.map((p) =>\n trD([\n p.id,\n pct(p.roas),\n p.cost.toLocaleString(),\n won(p.rev),\n String(p.rank),\n \"\" + p.move + \"\",\n ], [\"\", cls(p.roas), \"\", \"\", \"\", \"\"])\n ).join(\"\")\n }
`\n : \"
해당 조건 상품 없음
\";\n const aB = d.actions.B.length\n ? `${trH([\"키워드\", \"ROAS\", \"비용\", \"매출\", \"조치\"])}${\n d.actions.B.map((k) =>\n trD([\n esc(k.kw),\n pct(k.roas),\n k.cost.toLocaleString(),\n won(k.rev),\n \"\" + k.move + \"\",\n ], [\"\", cls(k.roas), \"\", \"\", \"\"])\n ).join(\"\")\n }
`\n : \"
해당 조건 키워드 없음
\";\n const aD = d.actions.D.length\n ? `${trH([\"상품(지마켓)\", \"ROAS\", \"비용\", \"매출\", \"조치\"])}${\n d.actions.D.map((p) =>\n trD([\n p.id,\n pct(p.roas),\n p.cost.toLocaleString(),\n won(p.rev),\n \"\" + p.move + \"\",\n ], [\"\", cls(p.roas), \"\", \"\", \"\"])\n ).join(\"\")\n }
`\n : \"
해당 조건 상품 없음
\";\n const aE = d.actions.E.length\n ? `${trH([\"키워드\", \"노출\", \"비용\", \"조치\"])}${\n d.actions.E.map((k) =>\n trD([\n esc(k.kw),\n k.imp.toLocaleString(),\n k.cost.toLocaleString(),\n \"\" + k.move + \"\",\n ])\n ).join(\"\")\n }
`\n : \"
OFF 후보 없음
\";\n\n const aiHead = ai && ai.headline\n ? `
🎯 ${esc(ai.headline)}
`\n : \"\";\n const aiInsights = ai && ai.insights\n ? `
📌 핵심 인사이트 (Claude)
${ulOf(ai.insights)}`\n : \"\";\n const aiActions = ai && ai.actions\n ? `
⭐ 오늘 우선순위 액션 (Claude)
${ulOf(ai.actions)}`\n : \"\";\n const aiMemos = ai && ai.memos\n ? `
📝 의사결정 메모 (Claude)
${ulOf(ai.memos)}`\n : \"\";\n const aiPrinc = ai && ai.principles\n ? `
🎯 BEP ${d.bep.bep}% 5대 원칙 (Claude)
${ulOf(ai.principles)}`\n : \"\";\n const aiNote = ai\n ? `
Claude 심층분석 (${ai.model || \"claude\"}) · ${\n new Date(ai.generatedAt).toLocaleString(\"ko-KR\", {\n timeZone: \"Asia/Seoul\",\n })\n }
`\n : `
⚠️ Claude 분석 미생성 — val.town env에 ANTHROPIC_API_KEY 등록 후 esmAnalyticsSync 재실행. (데이터·표는 정상)
`;\n\n const body = `\n
🛒 ESM(옥션·G마켓) 통합 보고서 — 분석 + 액션플랜
\n
분석기간 ${d.window.start} ~ ${d.window.end} · 생성 ${\n new Date(d.generatedAt).toLocaleString(\"ko-KR\", { timeZone: \"Asia/Seoul\" })\n } (KST) · 폴더 ${esc(d.folder)}
\nESM=G마켓+옥션 · 순매출=판매금액 · 광고비=파워클릭+AI매출업 · 키워드는 통합 ROAS
\n
🎯 전략: 옥션 강화 / 지마켓 효율화 — 옥션 ROAS ${\n pct(c.auction.roas)\n } vs 지마켓 ${pct(c.gmarket.roas)} (효율 ${\n c.gmarket.roas ? (c.auction.roas / c.gmarket.roas).toFixed(2) : \"-\"\n }배). 옥션 비용 ${t.auctionCostShare}%로 매출 ${t.auctionRevShare}% 생산.
\n${aiHead}\n
📑 1부 분석 🚀 2부 액션
\n${aiInsights}\n\n
📑 1부 · 분석 보고서
\n
0부. 채널 전략 (옥션 vs 지마켓)
${ch0}\n
예산 재배분 시뮬
${realloc}\n${read(\"Claude 해석\", ai && ai.channel)}\n
1부. 30일 누적
\n${trH([\"구분\", \"통합 광고비\", \"순매출\", \"ROAS\"])}\n${\n trD([\n \"통합\",\n \"\" + t.ad.toLocaleString() + \"\",\n \"\" + won(t.rev) + \"\",\n \"\" + pct(t.roas) + \"\",\n ])\n }\n${\n trD([\n \"├ 옥션\",\n c.auction.ad.toLocaleString(),\n won(c.auction.rev),\n pct(c.auction.roas),\n ], [\"\", \"\", \"\", cls(c.auction.roas)])\n }\n${\n trD([\n \"└ 지마켓\",\n c.gmarket.ad.toLocaleString(),\n won(c.gmarket.rev),\n pct(c.gmarket.roas),\n ], [\"\", \"\", \"\", cls(c.gmarket.roas)])\n }
\n
매출 상위 광고그룹
${grpTbl}\n
매출 상위 광고상품 (채널)
${prodTbl}\n${read(\"Claude 해석\", ai && ai.products)}\n
통합 키워드 ROAS TOP
${kwTbl}\n${read(\"Claude 해석\", ai && ai.keywords)}\n
일별 ROAS 추이
${dailyTbl}\n
🤖 AI매출업 KPI (채널별)
${aiTbl}\n\n
2부. 7일 단기 + 요일
\n${trH([\"구분\", \"통합비\", \"순매출\", \"ROAS\"])}\n${\n trD(\n [\"최근 7일\", s7ad.toLocaleString(), won(s7rev), pct(roas(s7rev, s7ad))],\n [\"\", \"\", \"\", cls(roas(s7rev, s7ad))],\n )\n }\n${trD([\"직전 7일\", p7ad.toLocaleString(), won(p7rev), pct(roas(p7rev, p7ad))])}\n${\n trD(\n [\"└ 옥션 7일(SA)\", a7A.toLocaleString(), won(r7A), pct(roas(r7A, a7A))],\n [\"\", \"\", \"\", cls(roas(r7A, a7A))],\n )\n }\n${\n trD([\n \"└ 지마켓 7일(SA)\",\n a7G.toLocaleString(),\n won(r7G),\n pct(roas(r7G, a7G)),\n ], [\"\", \"\", \"\", cls(roas(r7G, a7G))])\n }
\n
요일 패턴 (당월)
${wdTbl}\n${read(\"Claude 해석\", ai && ai.drop)}\n\n
3부. BEP ${d.bep.bep}% 시뮬 (당월)
\n${trH([\"항목\", \"값\"])}\n${trD([\"당월 누적 통합비\", won(d.bep.mtdAd)])}\n${trD([\"당월 누적 순매출\", won(d.bep.mtdRev)])}\n${trD([\"당월 누적 ROAS\", pct(d.bep.mtdRoas)])}\n${trD([\"잔여일수\", d.bep.remain + \"일\"])}\n${trD([\"잔여 필요 ROAS(BEP)\", \"\" + pct(d.bep.neededRoas) + \"\"])}
\n\n
\n
🚀 2부 · 액션플랜
\n${aiActions}\n
자동 규칙 액션 (참고)
\n
A. 옥션 상품 증액 (ROAS≥400%·노출순위 여력)
${aA}\n
B. 키워드 증액 (ROAS≥900%·자사 제외)
${aB}\n
C. 옥션 SA 일예산 +20~30% 점진
\n
D. 지마켓 저효율 대규모 CPC −15% (OFF 금지)
${aD}\n
E. OFF 후보 (매출0·고노출)
${aE}\n
정량효과: 지마켓 SA −15% → 옥션 재배분 시 통합 ROAS ${\n d.realloc[1] ? pct(d.realloc[1].roas) : \"-\"\n }·순매출 ${d.realloc[1] ? won(d.realloc[1].rev) : \"-\"}(현재 ${\n pct(t.roas)\n }/${won(t.rev)}).
\n${aiMemos}\n${aiPrinc}\n
⛔ 금지: ${\n d.rules.neverOff.join(\", \")\n } OFF 금지(감액만) · ${\n d.rules.noIncrease.join(\", \")\n } 추가증액 금지 · 자사 브랜드 키워드/AI매출업 강화 자제 · 단일일·주말·요일 부진만으로 OFF 금지.
\n${aiNote}\n`;\n return new Response(page(body, `ESM 통합 보고서 ${d.window.end}`), {\n headers: {\n \"Content-Type\": \"text/html; charset=utf-8\",\n \"Cache-Control\": \"public, max-age=900\",\n },\n });\n}\n\nfunction page(body, title) {\n return `${\n esc(title)\n }\n
${body}
`;\n}",[166],"4b5fb91d-7a21-4388-8dda-ee187c331c70",["D",1782104737568],13,[28],"3d3656fb-7d28-42ed-91e2-c13aed41c6ac","dailyDriveSync_esm.ts","1a40308c-6a31-11f1-8870-1607ee4eb77e",75,["D",1781833269129],"/**\n * dailyDriveSync_esm.ts v5 (2026-06-18 패치) — 매일 14:00 KST\n \n v5 변경 (실제 파일 헤더 검증 후):\n * - [버그] 매출 파일 매칭 구분자 수정: \"일간 판매실적 현황-\"(하이픈) -> \"일간 판매실적 현황\"(언더스코어 포함 모두 매칭).\n * - [버그] 매출 xlsx는 1행이 제목, 2행이 헤더. parseXlsxFind 로 헤더행 자동 탐색.\n * - [갭] 광고비에 AI매출업_일별리포트(컬럼 날짜별/총비용) 합산 추가.\n * - ESM = 옥션 + G마켓 통합.\n \n Schedule: 0 5 * * * (UTC = 14:00 KST)\n /\nimport as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst ESM_NOTION_DB_ID = \"5ed8388163b1468fa24b0e966291fd35\";\nconst BLOB_RATIO_KEY = \"esm_brand_ratio_latest\";\n\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v) {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction matchDate(v, targetDate) {\n if (v === null || v === undefined || v === \"\") return false;\n let s;\n if (v instanceof Date) {\n s = v.toLocaleDateString(\"sv-SE\", { timeZone: \"Asia/Seoul\" });\n } else {\n s = String(v).trim();\n }\n const compact = targetDate.replace(/-/g, \"\");\n return s === targetDate || s === compact || s.startsWith(targetDate) ||\n s.includes(targetDate) || s.includes(compact);\n}\n\nasync function getDriveAccessToken() {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\"),\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\"),\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\"),\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId, token) {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownload(fileId, token) {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.arrayBuffer();\n}\nasync function findLatestDailyFolder(rootId, targetDate, token) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n ).sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\n\n// 헤더행 자동 탐색 파서 (매출 파일은 1행 제목/2행 헤더)\nfunction parseXlsxFind(buf, mustHaveCol) {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sheet = wb.Sheets[wb.SheetNames[0]];\n const aoa = XLSX.utils.sheet_to_json(sheet, {\n header: 1,\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n let hdr = -1;\n for (let i = 0; i < Math.min(aoa.length, 15); i++) {\n if ((aoa[i] || []).some((c) => String(c).trim() === mustHaveCol)) {\n hdr = i;\n break;\n }\n }\n if (hdr === -1) return [];\n const headers = (aoa[hdr] || []).map((c) => String(c).trim());\n const out = [];\n for (let i = hdr + 1; i < aoa.length; i++) {\n const row = {};\n (aoa[i] || []).forEach((v, j) => {\n if (headers[j]) row[headers[j]] = v;\n });\n out.push(row);\n }\n return out;\n}\n\nasync function notionUpsert(\n dbId,\n targetDate,\n label,\n properties,\n status,\n insight,\n) {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const headers = {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n };\n const q = await fetch(`https://api.notion.com/v1/databases/${dbId}/query`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n });\n const existing = (await q.json()).results?.[0];\n const fullProps = {\n ...properties,\n \"날짜\": {\n title: [{ text: { content: `${targetDate} ${label} 일일 리포트` } }],\n },\n \"분석일\": { date: { start: targetDate } },\n \"상태\": { select: { name: status } },\n \"핵심 인사이트\": {\n rich_text: [{ text: { content: insight.slice(0, 2000) } }],\n },\n };\n if (existing) {\n const r = await fetch(`https://api.notion.com/v1/pages/${existing.id}`, {\n method: \"PATCH\",\n headers,\n body: JSON.stringify({ properties: fullProps }),\n });\n if (!r.ok) throw new Error(`Notion update ${r.status}`);\n return (await r.json()).url;\n } else {\n const r = await fetch(`https://api.notion.com/v1/pages`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n parent: { database_id: dbId },\n properties: fullProps,\n }),\n });\n if (!r.ok) {\n throw new Error(\n `Notion create ${r.status}: ${(await r.text()).slice(0, 300)}`,\n );\n }\n return (await r.json()).url;\n }\n}\n\nexport default async function () {\n const targetDate = dateKST(-1);\n const accessToken = await getDriveAccessToken();\n\n const folder = await findLatestDailyFolder(\n ESM_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"ESM 어제 폴더 없음\");\n const files = await driveList(folder.id, accessToken);\n\n // ESM = 옥션 + G마켓 통합\n const salesFiles = files.filter((f) =>\n f.name.startsWith(\"일간 판매실적 현황\")\n );\n const pcAdFiles = files.filter((f) =>\n f.name.startsWith(\"woojooware_report_daily_\")\n );\n const aiAdFiles = files.filter((f) =>\n f.name.startsWith(\"AI매출업_일별리포트\")\n );\n\n const dl = async (f) => ({\n name: f.name,\n buf: await driveDownload(f.id, accessToken),\n });\n const [salesB, pcB, aiB] = await Promise.all([\n Promise.all(salesFiles.map(dl)),\n Promise.all(pcAdFiles.map(dl)),\n Promise.all(aiAdFiles.map(dl)),\n ]);\n\n let totalRev = 0, totalOrders = 0, totalAdSpend = 0;\n\n for (const x of salesB) {\n for (const r of parseXlsxFind(x.buf, \"기준일\")) {\n if (!matchDate(r[\"기준일\"], targetDate)) continue;\n totalRev += num(r[\"판매금액\"]);\n totalOrders += num(r[\"판매건수\"]);\n }\n }\n for (const x of pcB) {\n for (const r of parseXlsxFind(x.buf, \"날짜\")) {\n if (!matchDate(r[\"날짜\"], targetDate)) continue;\n totalAdSpend += num(r[\"총비용\"]);\n }\n }\n for (const x of aiB) {\n for (const r of parseXlsxFind(x.buf, \"날짜별\")) {\n if (!matchDate(r[\"날짜별\"], targetDate)) continue;\n totalAdSpend += num(r[\"총비용\"]);\n }\n }\n\n let pnRatio = 1, tsRatio = 0, ratioSource = \"기본값(매핑 없음)\";\n try {\n const ratio = await blob.getJSON(BLOB_RATIO_KEY);\n if (ratio && typeof ratio.pnRatio === \"number\") {\n pnRatio = ratio.pnRatio;\n tsRatio = 1 - pnRatio;\n ratioSource = `30일 누적 (계산일 ${\n ratio.calculatedAt?.slice(0, 10) || \"?\"\n })`;\n }\n } catch (e) { /* 기본값 / }\n\n const pnRev = Math.round(totalRev pnRatio);\n const tsRev = totalRev - pnRev;\n const pnOrders = Math.round(totalOrders * pnRatio);\n const tsOrders = totalOrders - pnOrders;\n const roas = totalAdSpend > 0 ? Math.round(totalRev / totalAdSpend * 100) : 0;\n\n let status = \"정상\";\n if (totalRev === 0) status = \"데이터 부족\";\n else if (totalAdSpend > 0 && roas < 200) status = \"경고\";\n else if (totalAdSpend > 0 && roas < 500) status = \"검토 필요\";\n\n const insight = `통합 매출 ₩${totalRev.toLocaleString()} (풍년 ${\n (pnRatio * 100).toFixed(1)\n }%, 텅스 ${\n (tsRatio * 100).toFixed(1)\n }%, 비율 출처: ${ratioSource}), 주문 ${totalOrders}건. 광고비 ₩${totalAdSpend.toLocaleString()}${\n totalAdSpend > 0 ? `, ROAS ${roas}%` : \"\"\n }. (광고비 = 파워클릭 + AI매출업, 옥션+G마켓 통합)`;\n const props = {\n \"풍년보감 매출\": { number: pnRev },\n \"풍년보감 주문수\": { number: pnOrders },\n \"텅스페이스 매출\": { number: tsRev },\n \"텅스페이스 주문수\": { number: tsOrders },\n \"통합 매출\": { number: totalRev },\n \"통합 주문수\": { number: totalOrders },\n \"광고비\": { number: totalAdSpend },\n \"ROAS (%)\": { number: roas },\n };\n const url = await notionUpsert(\n ESM_NOTION_DB_ID,\n targetDate,\n \"ESM 매출\",\n props,\n status,\n insight,\n );\n\n console.log(\n `[dailyDriveSync_esm v5] ${targetDate} | rev ₩${totalRev.toLocaleString()} | ad ₩${totalAdSpend.toLocaleString()} | roas ${roas}% | sales:${salesB.length} pc:${pcB.length} ai:${aiB.length} | ${url}`,\n );\n return { ok: true, date: targetDate, totalRev, totalAdSpend, roas, url };\n}",[178],"5c7be5a5-3540-4c60-941f-1ba83f7eeaea","dailyDriveSyncSA.ts","3bf49eda-6a2b-11f1-b922-1607ee4eb77e",49,["D",1781690233842],6,"/**\n * dailyDriveSync_sa.ts — 매일 11:00 KST\n \n 네이버 폴더의 일별_raw_js,934268_.csv 1개만 처리 → \"네이버 SA 캠페인유형별 일일 리포트\" DB\n 단일 파일이라 빠름 (~10초 안에 끝남)\n \n Schedule: 0 2 * * * (UTC = 11:00 KST)\n /\n\nconst NAVER_DRIVE_FOLDER_ID = Deno.env.get(\"NAVER_DRIVE_FOLDER_ID\") ||\n \"1WOPgcs8YD80s7beD4Ev0IVT7VhTwXie6\";\nconst SA_DETAIL_NOTION_DB_ID = \"2829fbc585c74004b3ae33771f106d2a\";\nconst SLACK_CHANNEL = Deno.env.get(\"SLACK_CHANNEL_ID\") || \"C09B0J6QXN1\";\n\nfunction dateKST(o: number): string {\n return new Date(Date.now() + o 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v: any): number {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction matchDate(v: string, targetDate: string): boolean {\n if (!v) return false;\n const compact = targetDate.replace(/-/g, \"\");\n const dotted = targetDate.replace(/-/g, \".\") + \".\";\n const x = v.trim();\n return x === targetDate || x === compact || x === dotted ||\n x.includes(targetDate) || x.includes(compact);\n}\n\nasync function getDriveAccessToken(): Promise {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\")!,\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\")!,\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\")!,\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId: string, token: string): Promise {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownloadText(\n fileId: string,\n token: string,\n): Promise {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.text();\n}\nasync function findLatestDailyFolder(\n rootId: string,\n targetDate: string,\n token: string,\n) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n )\n .sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\n\nfunction parseCSV(text: string): Record[] {\n const clean = text.replace(/^/, \"\").trim();\n const lines = clean.split(/\\r?\\n/).filter((l) => l.trim());\n if (lines.length < 2) return [];\n let headerLineIdx = 0;\n if (!lines[0].includes(\",\") || lines[0].split(\",\").length < 3) {\n headerLineIdx = 1;\n }\n const headers = lines[headerLineIdx].split(\",\").map((h) =>\n h.replace(/^\"|\"$/g, \"\").trim()\n );\n return lines.slice(headerLineIdx + 1).map((line) => {\n const cells = line.split(\",\").map((c) => c.replace(/^\"|\"$/g, \"\").trim());\n const row: Record = {};\n headers.forEach((h, i) => row[h] = cells[i] || \"\");\n return row;\n });\n}\n\nasync function notionUpsert(\n dbId: string,\n targetDate: string,\n label: string,\n properties: any,\n status: string,\n insight: string,\n): Promise {\n const token = Deno.env.get(\"NOTION_TOKEN\")!;\n const headers = {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n };\n const q = await fetch(`https://api.notion.com/v1/databases/${dbId}/query`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n });\n const existing = (await q.json()).results?.[0];\n const fullProps = {\n ...properties,\n \"날짜\": {\n title: [{ text: { content: `${targetDate} ${label} 일일 리포트` } }],\n },\n \"분석일\": { date: { start: targetDate } },\n \"상태\": { select: { name: status } },\n \"핵심 인사이트\": {\n rich_text: [{ text: { content: insight.slice(0, 2000) } }],\n },\n };\n if (existing) {\n const r = await fetch(`https://api.notion.com/v1/pages/${existing.id}`, {\n method: \"PATCH\",\n headers,\n body: JSON.stringify({ properties: fullProps }),\n });\n if (!r.ok) throw new Error(`Notion update ${r.status}`);\n return (await r.json()).url;\n } else {\n const r = await fetch(`https://api.notion.com/v1/pages`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n parent: { database_id: dbId },\n properties: fullProps,\n }),\n });\n if (!r.ok) {\n throw new Error(\n `Notion create ${r.status}: ${(await r.text()).slice(0, 300)}`,\n );\n }\n return (await r.json()).url;\n }\n}\n\nasync function findTodayBotThreadTs(): Promise {\n const token = Deno.env.get(\"SLACK_BOT_TOKEN\")!;\n const resp = await fetch(\n `https://slack.com/api/conversations.history?channel=${SLACK_CHANNEL}&limit=20`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n const data = await resp.json();\n if (!data.ok) return null;\n const today = dateKST(0);\n for (const m of data.messages || []) {\n const d = new Date(parseFloat(m.ts) * 1000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n if (d === today && m.bot_id) return m.ts;\n }\n return null;\n}\nasync function slackPost(text: string, threadTs: string | null): Promise {\n const token = Deno.env.get(\"SLACK_BOT_TOKEN\")!;\n const body: any = {\n channel: SLACK_CHANNEL,\n text,\n unfurl_links: false,\n unfurl_media: false,\n };\n if (threadTs) body.thread_ts = threadTs;\n const r = await fetch(\"https://slack.com/api/chat.postMessage\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Content-Type\": \"application/json; charset=utf-8\",\n },\n body: JSON.stringify(body),\n });\n if (!(await r.json()).ok) throw new Error(\"Slack post failed\");\n}\n\nexport default async function () {\n const targetDate = dateKST(-1);\n const accessToken = await getDriveAccessToken();\n\n let msg = \"\";\n try {\n const folder = await findLatestDailyFolder(\n NAVER_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"네이버 어제 폴더 없음\");\n const files = await driveList(folder.id, accessToken);\n const csv = files.find((f) =>\n f.name.startsWith(\"일별_raw_js,934268_\") && f.name.endsWith(\".csv\")\n );\n if (!csv) throw new Error(\"일별 raw csv 파일 없음\");\n const text = await driveDownloadText(csv.id, accessToken);\n const rows = parseCSV(text);\n\n const byType: Record = {\n \"브랜드검색\": { spend: 0, rev: 0 },\n \"쇼핑검색\": { spend: 0, rev: 0 },\n \"파워링크\": { spend: 0, rev: 0 },\n \"파워컨텐츠\": { spend: 0, rev: 0 },\n };\n for (const r of rows) {\n if (!matchDate(r[\"일별\"] || r[\"날짜\"] || \"\", targetDate)) continue;\n const tp = String(r[\"캠페인유형\"] || \"\").trim();\n let k = \"\";\n if (tp.includes(\"브랜드검색\")) k = \"브랜드검색\";\n else if (tp.includes(\"쇼핑검색\")) k = \"쇼핑검색\";\n else if (tp.includes(\"파워링크\")) k = \"파워링크\";\n else if (tp.includes(\"파워컨텐츠\")) k = \"파워컨텐츠\";\n if (!k) continue;\n byType[k].spend += num(r[\"총비용\"]);\n byType[k].rev += num(\n r[\"구매완료 전환매출액(원)\"] || r[\"구매완료 전환매출액\"],\n );\n }\n const totalSpend = Object.values(byType).reduce((s, v) => s + v.spend, 0);\n const totalRev = Object.values(byType).reduce((s, v) => s + v.rev, 0);\n const totalRoas = totalSpend > 0\n ? Math.round(totalRev / totalSpend * 100)\n : 0;\n let status = \"정상\";\n if (totalSpend === 0 && totalRev === 0) status = \"데이터 부족\";\n else if (totalSpend > 0 && totalRoas < 300) status = \"경고\";\n else if (totalSpend > 0 && totalRoas < 700) status = \"검토 필요\";\n const roas = (b: typeof byType[string]) =>\n b.spend > 0 ? Math.round(b.rev / b.spend * 100) : 0;\n const insight = `브랜드검색 ROAS ${roas(byType[\"브랜드검색\"])}%, 쇼핑검색 ${\n roas(byType[\"쇼핑검색\"])\n }%, 파워링크 ${roas(byType[\"파워링크\"])}%, 파워컨텐츠 ${\n roas(byType[\"파워컨텐츠\"])\n }%. 통합 ROAS ${totalRoas}%.`;\n const props = {\n \"브랜드검색 비용\": { number: byType[\"브랜드검색\"].spend },\n \"브랜드검색 매출\": { number: byType[\"브랜드검색\"].rev },\n \"브랜드검색 ROAS (%)\": { number: roas(byType[\"브랜드검색\"]) },\n \"쇼핑검색 비용\": { number: byType[\"쇼핑검색\"].spend },\n \"쇼핑검색 매출\": { number: byType[\"쇼핑검색\"].rev },\n \"쇼핑검색 ROAS (%)\": { number: roas(byType[\"쇼핑검색\"]) },\n \"파워링크 비용\": { number: byType[\"파워링크\"].spend },\n \"파워링크 매출\": { number: byType[\"파워링크\"].rev },\n \"파워링크 ROAS (%)\": { number: roas(byType[\"파워링크\"]) },\n \"파워컨텐츠 비용\": { number: byType[\"파워컨텐츠\"].spend },\n \"파워컨텐츠 매출\": { number: byType[\"파워컨텐츠\"].rev },\n \"파워컨텐츠 ROAS (%)\": { number: roas(byType[\"파워컨텐츠\"]) },\n \"통합 비용\": { number: totalSpend },\n \"통합 매출\": { number: totalRev },\n \"통합 ROAS (%)\": { number: totalRoas },\n };\n const url = await notionUpsert(\n SA_DETAIL_NOTION_DB_ID,\n targetDate,\n \"SA 캠페인유형별\",\n props,\n status,\n insight,\n );\n const emoji = status === \"정상\"\n ? \"🟢\"\n : status === \"검토 필요\"\n ? \"🟡\"\n : status === \"경고\"\n ? \"🔴\"\n : \"⬜\";\n msg =\n `${emoji} [SA 캠페인유형별] ${targetDate} · 비용 ₩${totalSpend.toLocaleString()} · 매출 ₩${totalRev.toLocaleString()} · ROAS ${totalRoas}% · <${url}|상세>`;\n } catch (e: any) {\n msg = `🔴 [SA 캠페인유형별] ${targetDate} 처리 실패 — ${e.message}`;\n }\n\n try {\n const threadTs = await findTodayBotThreadTs();\n if (threadTs) await slackPost(msg, threadTs);\n else await slackPost(`📊 ${dateKST(0)} Drive 동기화 — SA\\n${msg}`, null);\n } catch (e: any) {\n console.error(\"Slack notify failed:\", e.message);\n }\n\n console.log(`[dailyDriveSync_sa] ${targetDate}`);\n return { ok: true, date: targetDate, msg };\n}",[185],"d5222c1b-05a0-4826-b551-2f577db19e87","actionPlanSync.ts","7dd53b42-53f2-11f1-8c1a-ee650bb23af1",24,["D",1781679945496],"// Learn more: https://docs.val.town/vals/cron/\nexport default async function (interval: Interval) {\n console.log(interval);\n}",[193],"pulls",1,"branches",2,"forks","envVars",{"_208":210,"_211":60},"connections",{"_208":60,"_209":60},"count","unfilledCount",23,"emptyCount",{"_43":192,"_32":193,"_30":194,"_140":-5,"_41":42,"_40":195,"_142":124,"_38":242,"_13":170,"_33":34,"_35":197,"_24":25,"_146":-5,"_221":243,"_27":244},{"_43":184,"_32":185,"_30":186,"_140":-5,"_41":42,"_40":187,"_142":124,"_38":239,"_13":189,"_33":34,"_35":190,"_24":25,"_146":-5,"_221":240,"_27":241},{"_43":177,"_32":178,"_30":179,"_140":-5,"_41":42,"_40":180,"_142":124,"_38":236,"_13":170,"_33":34,"_35":182,"_24":25,"_146":-5,"_221":237,"_27":238},{"_43":173,"_32":28,"_30":31,"_140":-5,"_41":42,"_40":14,"_142":25,"_38":233,"_13":175,"_33":34,"_35":37,"_24":25,"_146":-5,"_221":234,"_27":235},{"_43":165,"_32":166,"_30":167,"_140":-5,"_41":42,"_40":168,"_142":25,"_38":230,"_13":170,"_33":162,"_35":171,"_24":25,"_146":-5,"_221":231,"_27":232},{"_43":156,"_32":157,"_30":158,"_140":-5,"_41":42,"_40":159,"_142":124,"_38":227,"_13":161,"_33":162,"_35":163,"_24":25,"_146":-5,"_221":228,"_27":229},{"_43":148,"_32":149,"_30":150,"_140":-5,"_41":42,"_40":151,"_142":124,"_38":224,"_13":153,"_33":34,"_35":154,"_24":25,"_146":-5,"_221":225,"_27":226},{"_43":137,"_32":138,"_30":139,"_140":-5,"_41":42,"_40":141,"_142":124,"_38":220,"_13":144,"_33":34,"_35":145,"_24":25,"_146":-5,"_221":222,"_27":223},["D",1781744680319],"children",[],[138],["D",1781860048011],[],[149],["D",1781760512003],[],[157],["D",1782104505572],[],[166],["D",1782104737568],[],[28],["D",1781833269129],[],[178],["D",1781690233842],[],[185],["D",1781679945496],[],[193],["D",1778465580996],["D",1789491713377],{"_97":96,"_105":101},{"_97":96,"_98":99,"_100":101,"_18":54,"_102":103,"_104":25},{"_13":86,"_32":87,"_43":42,"_88":44,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":45,"_95":96},"ctx",{"_79":-5,"_126":283,"_284":285,"_286":287,"_288":25,"_289":25,"_290":25,"_291":124,"_292":-5,"_293":-7,"_294":-7,"_295":60,"_296":25,"_297":25,"_298":-5,"_299":25,"_300":25,"_301":25,"_302":-5},"ENV",{"_254":255,"_256":257,"_258":87,"_259":260,"_261":262,"_263":264,"_265":266,"_267":268,"_269":270,"_271":272,"_273":274,"_275":276,"_277":278,"_279":280,"_281":282},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

${sectionTitle}

핵심 인사이트<\\/h3>\\s*
([\\s\\S]*?)<\\/p>/);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html) {\n const m = html.match(\n /

어제 권장 액션 후속 추적<\\/h3>([\\s\\S]*?)(?=

📌 핵심 인사이트 (Claude)

⭐ 오늘 우선순위 액션 (Claude)

📝 의사결정 메모 (Claude)

🎯 BEP ${d.bep.bep}% 5대 원칙 (Claude)

🛒 ESM(옥션·G마켓) 통합 보고서 — 분석 + 액션플랜

0부. 채널 전략 (옥션 vs 지마켓)

예산 재배분 시뮬

1부. 30일 누적

매출 상위 광고그룹

매출 상위 광고상품 (채널)

통합 키워드 ROAS TOP

일별 ROAS 추이

🤖 AI매출업 KPI (채널별)

2부. 7일 단기 + 요일

요일 패턴 (당월)

3부. BEP ${d.bep.bep}% 시뮬 (당월)

자동 규칙 액션 (참고)

A. 옥션 상품 증액 (ROAS≥400%·노출순위 여력)

B. 키워드 증액 (ROAS≥900%·자사 제외)

C. 옥션 SA 일예산 +20~30% 점진

D. 지마켓 저효율 대규모 CPC −15% (OFF 금지)

E. OFF 후보 (매출0·고노출)

${sectionTitle}

핵심 인사이트<\\/h3>\\s*([\\s\\S]*?)<\\/p>/);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html) {\n const m = html.match(\n /

어제 권장 액션 후속 추적<\\/h3>([\\s\\S]*?)(?=

📌 핵심 인사이트 (Claude)

⭐ 오늘 우선순위 액션 (Claude)

📝 의사결정 메모 (Claude)

🎯 BEP ${d.bep.bep}% 5대 원칙 (Claude)

🛒 ESM(옥션·G마켓) 통합 보고서 — 분석 + 액션플랜

0부. 채널 전략 (옥션 vs 지마켓)

예산 재배분 시뮬

1부. 30일 누적

매출 상위 광고그룹

매출 상위 광고상품 (채널)

통합 키워드 ROAS TOP

일별 ROAS 추이

🤖 AI매출업 KPI (채널별)

2부. 7일 단기 + 요일

요일 패턴 (당월)

3부. BEP ${d.bep.bep}% 시뮬 (당월)

자동 규칙 액션 (참고)

A. 옥션 상품 증액 (ROAS≥400%·노출순위 여력)

B. 키워드 증액 (ROAS≥900%·자사 제외)

C. 옥션 SA 일예산 +20~30% 점진

D. 지마켓 저효율 대규모 CPC −15% (OFF 금지)

E. OFF 후보 (매출0·고노출)

핵심 인사이트<\\/h3>\\s*
([\\s\\S]*?)<\\/p>/);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html) {\n const m = html.match(\n /

Diff · v116