Generador de resúmenes de encuestas de clientes

• •Bot Token de Slack o URL de Webhook
• •Credenciales de API de Google Sheets

{
  "id": "hMRDyYoD0nvR4E5y",
  "meta": {
    "instanceId": "15d6057a37b8367f33882dd60593ee5f6cc0c59310ff1dc66b626d726083b48d",
    "templateCredsSetupCompleted": false
  },
  "name": "Customer Survey Summary Generator",
  "tags": [
    "surveys",
    "ai-analysis",
    "slack",
    "sheets"
  ],
  "nodes": [
    {
      "id": "note1",
      "name": "Descripción General del Flujo de Trabajo",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -144,
        224
      ],
      "parameters": {
        "color": 5,
        "width": 350,
        "height": 280,
        "content": "## Customer Survey Summary Generator (Template)\n\nThis workflow groups customer survey responses by sentiment (positive / neutral / negative), then uses an AI agent to extract themes, insights, and actionable recommendations, and finally saves or shares a consolidated report.\n\n**What you get**\n- Automated daily run via Schedule Trigger\n- Clean grouping & batching for accurate AI analysis\n- Consolidated summary (themes / insights / recommendations)\n- Output to Google Sheets and Slack\n\n**No secrets included** — add your own credentials after import."
      },
      "typeVersion": 1
    },
    {
      "id": "trigger1",
      "name": "Activador de Programación Diaria",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        208,
        816
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 9
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "note2",
      "name": "Nota de Fuente de Datos",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        336,
        224
      ],
      "parameters": {
        "color": 3,
        "width": 300,
        "height": 260,
        "content": "## Setup: Data Source\n\nConnect your survey data source:\n- Google Sheets (responses table)\n- Database (SQL query)\n- CSV/other sources\n\n**How to configure (example: Google Sheets)**\n1. Add Google Sheets credentials (OAuth2) in n8n Credentials.\n2. Replace `YOUR_SHEET_ID` and `YOUR_SHEET_NAME` on both Sheets nodes.\n3. Make sure columns exist (e.g., `満足度 (Rating)`, `自由記述コメント (Comment)`, `回答日時 (Timestamp)`)."
      },
      "typeVersion": 1
    },
    {
      "id": "sheets1",
      "name": "Obtener Respuestas de la Encuesta",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        432,
        816
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "YOUR_SHEET_NAME"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_SHEET_ID"
        }
      },
      "credentials": {},
      "typeVersion": 4.5
    },
    {
      "id": "code1",
      "name": "Agrupar y Preparar Datos",
      "type": "n8n-nodes-base.code",
      "position": [
        656,
        816
      ],
      "parameters": {
        "jsCode": "const items = $input.all();\n\n// Group responses by rating\nconst groupedByRating = {\n  positive: [],\n  neutral: [],\n  negative: []\n};\n\nfor (const item of items) {\n  // Google Sheetの日本語列名を正しく参照するように修正\n  const rating = parseInt(item.json[\"満足度 (Rating)\"] || 0);\n  const feedback = item.json[\"自由記述コメント (Comment)\"] || '';\n  const date = item.json[\"回答日時 (Timestamp)\"] || new Date().toISOString();\n  \n  const response = {\n    rating: rating,\n    feedback: feedback,\n    date: date\n  };\n  \n  // --- ここから修正 ---\n  // 5段階評価に合わせて感情を分類\n  if (rating >= 4) {\n    // 4点以上なら \"positive\"\n    groupedByRating.positive.push(response);\n  } else if (rating === 3) {\n    // 3点なら \"neutral\"\n    groupedByRating.neutral.push(response);\n  } else {\n    // 3点未満なら \"negative\"\n    groupedByRating.negative.push(response);\n  }\n  // --- ここまで修正 ---\n}\n\n// Prepare batches for AI processing\nconst batches = [];\n\nfor (const [sentiment, responses] of Object.entries(groupedByRating)) {\n  if (responses.length > 0) {\n    batches.push({\n      sentiment: sentiment,\n      count: responses.length,\n      responses: responses.slice(0, 50) // 1バッチあたりの回答数を50に制限\n    });\n  }\n}\n\nreturn batches;"
      },
      "typeVersion": 2
    },
    {
      "id": "note3",
      "name": "Nota de Procesamiento con IA",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        736,
        224
      ],
      "parameters": {
        "color": 4,
        "width": 300,
        "height": 260,
        "content": "## Setup: AI Processing\n\nThe AI Agent analyzes each batch to:\n1. Identify recurring themes\n2. Extract key insights\n3. Propose actionable recommendations\n\n**Configure**\n- Add your preferred model credentials (e.g., OpenAI or OpenRouter) in n8n.\n- Keep the JSON-only output requirement for reliable downstream parsing."
      },
      "typeVersion": 1
    },
    {
      "id": "agent1",
      "name": "Analizar Lote de Encuestas",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1104,
        432
      ],
      "parameters": {
        "text": "=Analyze these {{ $json.sentiment }} customer survey responses and provide insights.\nNumber of responses: {{ $json.count }}\n\nResponses:\n{{ $json.responses.map(r => `Rating: ${r.rating}/5 - Feedback: \"${r.feedback}\"`).join('\\n\\n') }}\n\nPlease provide:\n1. Top 3-5 recurring themes\n2. Key insights and patterns\n3. Actionable recommendations for improvement\n\nIMPORTANT: Your entire response must be ONLY the JSON object, starting with { and ending with }. Do not include any other text, explanations, or markdown formatting.",
        "options": {
          "systemMessage": "# ROLE\nYou are an exceptional expert in customer feedback analysis.\n\n# CORE TASK\nYour mission is tao thoroughly analyze the provided batch of customer survey responses and extract strategic insights that drive business growth, not just mere summaries.\n\n# ANALYSIS PRINCIPLES\n1.  **Deep Dive:** Go beyond surface-level keywords. Delve deep into the underlying causes and customer emotions behind the responses.\n2.  **Thematization:** Identify recurring patterns and themes that appear across multiple responses. Support each theme with specific quotes or examples from the feedback.\n3.  **Actionability:** Ensure your analysis leads to specific, measurable, and actionable business improvements. Avoid vague recommendations.\n\n# CRITICAL: OUTPUT FORMAT\nYour entire response MUST strictly adhere to the provided JSON schema. Do NOT include any explanatory text, conversational pleasantries, or markdown formatting. Your entire output must be a single, valid JSON object and nothing else."
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2
    },
    {
      "id": "parser1",
      "name": "Estructurar Salida",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        1248,
        640
      ],
      "parameters": {
        "schemaType": "manual",
        "inputSchema": "{\n  \"type\": \"object\",\n  \"properties\": {\n    \"themes\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"theme\": { \"type\": \"string\" },\n          \"frequency\": { \"type\": \"string\" },\n          \"examples\": { \"type\": \"array\", \"items\": { \"type\": \"string\" } }\n        }\n      },\n      \"description\": \"Top recurring themes from the feedback\"\n    },\n    \"insights\": {\n      \"type\": \"array\",\n      \"items\": { \"type\": \"string\" },\n      \"description\": \"Key insights and patterns identified\"\n    },\n    \"recommendations\": {\n      \"type\": \"array\",\n      \"items\": { \"type\": \"string\" },\n      \"description\": \"Actionable recommendations for improvement\"\n    },\n    \"sentiment_summary\": {\n      \"type\": \"string\",\n      \"description\": \"Overall sentiment summary for this batch\"\n    }\n  },\n  \"required\": [\"themes\", \"insights\", \"recommendations\", \"sentiment_summary\"]\n}"
      },
      "typeVersion": 1.2
    },
    {
      "id": "code2",
      "name": "Resultados Agregados",
      "type": "n8n-nodes-base.code",
      "position": [
        1200,
        800
      ],
      "parameters": {
        "jsCode": "// Aggregate all AI analysis results\nconst allAnalyses = $input.all();\nconst timestamp = new Date().toISOString();\nconst totalResponses = allAnalyses.reduce((sum, item) => sum + (item.json.originalCount || 0), 0);\n\n// Combine all themes\nconst allThemes = [];\nconst themeMap = new Map();\n\nfor (const analysis of allAnalyses) {\n  // --- ここから修正 ---\n  // \"output\" キーの中にある themes を参照するように変更\n  if (analysis.json.output && analysis.json.output.themes) {\n    for (const theme of analysis.json.output.themes) {\n  // --- ここまで修正 ---\n      const key = theme.theme.toLowerCase();\n      if (themeMap.has(key)) {\n        const existing = themeMap.get(key);\n        existing.examples = [...existing.examples, ...theme.examples];\n        existing.frequency = 'Multiple mentions';\n      } else {\n        themeMap.set(key, { ...theme });\n      }\n    }\n  }\n}\n\n// Convert map to array and sort by frequency\nconst consolidatedThemes = Array.from(themeMap.values())\n  .sort((a, b) => b.examples.length - a.examples.length)\n  .slice(0, 10);\n\n// Combine all insights and recommendations\nconst allInsights = [];\nconst allRecommendations = [];\nconst sentimentBreakdown = {};\n\nfor (const analysis of allAnalyses) {\n  const sentiment = analysis.json.originalSentiment || 'unknown';\n  sentimentBreakdown[sentiment] = analysis.json.originalCount || 0;\n  \n  // --- ここから修正 ---\n  // \"output\" キーの中にある insights と recommendations を参照\n  if (analysis.json.output && analysis.json.output.insights) {\n    allInsights.push(...analysis.json.output.insights);\n  }\n  if (analysis.json.output && analysis.json.output.recommendations) {\n    allRecommendations.push(...analysis.json.output.recommendations);\n  }\n  // --- ここまで修正 ---\n}\n\n// Create final summary\nconst summary = {\n  reportDate: timestamp,\n  totalResponsesAnalyzed: totalResponses,\n  sentimentBreakdown: sentimentBreakdown,\n  topThemes: consolidatedThemes,\n  keyInsights: [...new Set(allInsights)].slice(0, 10),\n  priorityRecommendations: [...new Set(allRecommendations)].slice(0, 7),\n  executiveSummary: `Analysis of ${totalResponses} customer survey responses revealed ${consolidatedThemes.length} key themes. Overall sentiment distribution: Positive (${sentimentBreakdown.positive || 0}), Neutral (${sentimentBreakdown.neutral || 0}), Negative (${sentimentBreakdown.negative || 0}).`\n};\n\nreturn [summary];"
      },
      "typeVersion": 2
    },
    {
      "id": "note4",
      "name": "Nota de Opciones de Salida",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1136,
        160
      ],
      "parameters": {
        "color": 6,
        "width": 300,
        "height": 260,
        "content": "## Output Options\n\nChoose one or more:\n1. Save the consolidated summary to Google Sheets\n2. Send a formatted report to Slack\n3. (Optional) Add CSV export or Database insert\n\n**Reminder**\n- Replace all placeholders (e.g., Sheet ID/Name, Slack Channel).\n- Add your own credentials in n8n after importing."
      },
      "typeVersion": 1
    },
    {
      "id": "sheets2",
      "name": "Guardar en Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1456,
        992
      ],
      "parameters": {
        "columns": {
          "value": {
            "報告日": "={{ $json.reportDate }}",
            "感情の内訳": "={{ $json.sentimentBreakdown }}",
            "トップテーマ": "={{ $json.topThemes }}",
            "キーインサイト": "={{ $json.keyInsights }}",
            "優先度推奨事項": "={{ $json.priorityRecommendations }}",
            "分析された合計応答数": "={{ $json.totalResponsesAnalyzed }}",
            "エグゼクティブサマリー```": "={{ $json.executiveSummary }}"
          },
          "schema": [
            {
              "id": "報告日",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "報告日",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "分析された合計応答数",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "分析された合計応答数",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "感情の内訳",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "感情の内訳",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "トップテーマ",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "トップテーマ",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "キーインサイト",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "キーインサイト",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "優先度推奨事項",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "優先度推奨事項",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "エグゼクティブサマリー```",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "エグゼクティブサマリー```",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "YOUR_SHEET_NAME"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_SHEET_ID"
        }
      },
      "credentials": {},
      "typeVersion": 4.5
    },
    {
      "id": "note5",
      "name": "Nota de Procesamiento",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -80,
        720
      ],
      "parameters": {
        "color": 2,
        "width": 250,
        "height": 204,
        "content": "## Processing Logic\n\n- `Group & Prepare Data` splits responses into three buckets by rating (>=4 Positive, =3 Neutral, <3 Negative).\n- `Loop Over Batches` ensures the AI analyzes each bucket separately for better accuracy.\n- Results are aggregated into a single executive summary for sharing."
      },
      "typeVersion": 1
    },
    {
      "id": "loop1",
      "name": "Bucle sobre Lotes",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        864,
        816
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "code3",
      "name": "Agregar Metadatos",
      "type": "n8n-nodes-base.code",
      "position": [
        1456,
        560
      ],
      "parameters": {
        "jsCode": "// Add metadata to each analysis result\nconst item = $input.first();\nconst loopData = $('Loop Over Batches').first();\n\nreturn {\n  ...item.json,\n  originalSentiment: loopData.json.sentiment,\n  originalCount: loopData.json.count\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "9a64a877-3946-4c33-82c2-e802c98c30f8",
      "name": "Enviar un Mensaje1",
      "type": "n8n-nodes-base.slack",
      "position": [
        1456,
        1184
      ],
      "parameters": {
        "text": "=*顧客アンケート分析レポート*\n\n*報告日時:* {{ DateTime.fromISO($json.reportDate).toFormat('yyyy年MM月dd日 HH:mm') }}\n*分析対象の回答数:* {{ $json.totalResponsesAnalyzed }} 件\n---------------------------------\n\n*エグゼクティブサマリー*\n{{ $json.executiveSummary }}\n\n*感情の内訳*\n• ポジティブ: {{ $json.sentimentBreakdown.positive || 0 }} 件\n• ネガティブ: {{ $json.sentimentBreakdown.negative || 0 }} 件\n• 中立: {{ $json.sentimentBreakdown.neutral || 0 }} 件\n\n*トップテーマ*\n{{ $json.topThemes.length > 0 ? '\\n> ' + $json.topThemes.map(item => item.theme).join('\\n> ') : '該当なし' }}\n\n*キーインサイト*\n{{ $json.keyInsights.length > 0 ? '\\n> ' + $json.keyInsights.join('\\n> ') : '該当なし' }}\n\n*優先度推奨事項*\n{{ $json.priorityRecommendations.length > 0 ? '\\n> ' + $json.priorityRecommendations.join('\\n> ') : '該当なし' }}\n\n---------------------------------\n_本メッセージはn8nワークフローによって自動送信されました。_",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_CHANNEL_ID",
          "cachedResultName": "YOUR_CHANNEL_NAME"
        },
        "otherOptions": {},
        "authentication": "oAuth2"
      },
      "credentials": {},
      "typeVersion": 2.3
    },
    {
      "id": "0c3ebcf9-2daf-4014-94c8-ae8a9c2a4244",
      "name": "Modelo de Chat OpenRouter",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenRouter",
      "position": [
        1104,
        640
      ],
      "parameters": {
        "options": {
          "responseFormat": "json_object"
        }
      },
      "credentials": {},
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "timezone": "Asia/Tokyo",
    "errorWorkflow": "",
    "executionOrder": "v1",
    "saveManualExecutions": true,
    "saveExecutionProgress": true,
    "saveDataErrorExecution": "all",
    "saveDataSuccessExecution": "all"
  },
  "versionId": "2657a174-a4b8-49e9-a6f8-d0979141c6be",
  "connections": {
    "code3": {
      "main": [
        [
          {
            "node": "loop1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "parser1": {
      "ai_outputParser": [
        [
          {
            "node": "agent1",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "code2": {
      "main": [
        [
          {
            "node": "sheets2",
            "type": "main",
            "index": 0
          },
          {
            "node": "9a64a877-3946-4c33-82c2-e802c98c30f8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "loop1": {
      "main": [
        [
          {
            "node": "code2",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "agent1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "agent1": {
      "main": [
        [
          {
            "node": "code3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "sheets1": {
      "main": [
        [
          {
            "node": "code1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "code1": {
      "main": [
        [
          {
            "node": "loop1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0c3ebcf9-2daf-4014-94c8-ae8a9c2a4244": {
      "ai_languageModel": [
        [
          {
            "node": "agent1",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "trigger1": {
      "main": [
        [
          {
            "node": "sheets1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}