BeyondPresence 動画エージェントかけおきを使った分析・AI と Google Sheets

• •HTTP Webhookエンドポイント（n8nが自動生成）
• •Google Sheets API認証情報
• •OpenAI API Key

{
  "id": "3lIutQSsxmpnS38v",
  "meta": {
    "instanceId": "eb3d53320c110bef9b66cf21b0da1ce60c7b6876e22315eca1be511a26fd726c",
    "templateCredsSetupCompleted": true
  },
  "name": "BeyondPresence Video Agent Call Analytics with AI & Google Sheets",
  "tags": [],
  "nodes": [
    {
      "id": "e31ac319-8e92-492a-bfe7-80244176a830",
      "name": "ワークフロー概要",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        0
      ],
      "parameters": {
        "color": 6,
        "width": 640,
        "height": 580,
        "content": "## 🎥 BeyondPresence Webhook Trigger\n\nThis workflow is triggered when a BeyondPresence video agent call ends. The webhook receives comprehensive call data including:\n\n- Call metadata (participant, duration, timestamps)\n- Complete conversation transcript\n- Initial sentiment analysis\n- Call evaluation metrics\n\nMake sure to configure the webhook URL in your BeyondPresence dashboard under Settings → Webhooks"
      },
      "typeVersion": 1
    },
    {
      "id": "b0fbdc1e-0248-4af5-9476-329581ec3e61",
      "name": "処理ステップ",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        680,
        0
      ],
      "parameters": {
        "color": 4,
        "width": 680,
        "height": 580,
        "content": "## 🔍 Data Validation & AI Analysis\n\n1. **Validate & Enrich Data**: Ensures webhook data integrity and adds calculated fields\n2. **AI Call Analysis**: Uses OpenAI/OpenRouter to generate comprehensive call summary\n3. **Parse AI Response**: Extracts structured JSON from AI response with error handling\n\nThe AI analyzes:\n- Main discussion points\n- Action items & follow-ups\n- Sentiment analysis\n- Key decisions made\n- Questions raised"
      },
      "typeVersion": 1
    },
    {
      "id": "0384fb96-fe5f-4584-a6fc-17dd5b8c1f50",
      "name": "データストレージ",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1400,
        0
      ],
      "parameters": {
        "color": 3,
        "width": 360,
        "height": 580,
        "content": "## 📊 Google Sheets Setup\n\n**Quick Start**:\n1. Copy our template sheet:  https://docs.google.com/spreadsheets/d/1TO6-jkCtoSFNLJObtN0UyklgdUd3ZxEnUaNvUaBjpvo/copy\n2. After copying, get your new sheet's ID from the URL\n3. Update the Google Sheets node with your ID\nThe template includes all required columns and sample data!"
      },
      "typeVersion": 1
    },
    {
      "id": "efb2be58-f618-4412-82d1-b5c87a6bb906",
      "name": "BeyondPresence Webhook",
      "type": "n8n-nodes-base.webhook",
      "position": [
        60,
        400
      ],
      "webhookId": "beyondpresence-call-webhook",
      "parameters": {
        "path": "beyondpresence-call-webhook",
        "options": {
          "rawBody": false
        },
        "httpMethod": "POST",
        "responseMode": "responseNode"
      },
      "typeVersion": 2
    },
    {
      "id": "00370bff-2f39-43b2-8ada-b99c7e60a63e",
      "name": "Acknowledge Webhook",
      "type": "n8n-nodes-base.respondToWebhook",
      "position": [
        260,
        400
      ],
      "parameters": {
        "options": {
          "responseCode": 200
        },
        "respondWith": "json",
        "responseBody": "{\n  \"status\": \"success\",\n  \"message\": \"Webhook received and processing\",\n  \"timestamp\": \"{{ $now.toISO() }}\"\n}"
      },
      "typeVersion": 1.2
    },
    {
      "id": "1ef688fd-23cb-4bf5-8ece-26a426a6bd53",
      "name": "通話イベントのフィルタリング",
      "type": "n8n-nodes-base.switch",
      "position": [
        440,
        400
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "outputKey": "call_ended",
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "6dbb8a0e-3cd1-481b-8553-d861c7c5e6e7",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.body.event_type }}",
                    "rightValue": "call_ended"
                  }
                ]
              },
              "renameOutput": true
            }
          ]
        },
        "options": {
          "fallbackOutput": "extra"
        }
      },
      "typeVersion": 3.2,
      "continueOnFail": true
    },
    {
      "id": "f46e8a2a-2c7c-4cd5-bbb6-703711b62804",
      "name": "データの検証とエンリッチメント",
      "type": "n8n-nodes-base.code",
      "position": [
        740,
        380
      ],
      "parameters": {
        "jsCode": "// Validate incoming webhook data\nconst data = $input.first().json.body;\nconst errors = [];\n\n// Check required fields\nif (!data.event_type) errors.push(\"Missing event_type\");\nif (!data.call_id) errors.push(\"Missing call_id\");\nif (!data.call_data) errors.push(\"Missing call_data\");\nif (!data.messages || !Array.isArray(data.messages)) errors.push(\"Missing or invalid messages array\");\n\n// Validate call_data structure\nif (data.call_data) {\n  if (!data.call_data.userName) errors.push(\"Missing userName in call_data\");\n  if (!data.call_data.startedAt) errors.push(\"Missing startedAt timestamp\");\n  if (!data.call_data.endedAt) errors.push(\"Missing endedAt timestamp\");\n}\n\n// If there are errors, throw with details\nif (errors.length > 0) {\n  throw new Error(\"Validation failed: \" + errors.join(\", \"));\n}\n\n// Add calculated fields\nconst startTime = new Date(data.call_data.startedAt);\nconst endTime = new Date(data.call_data.endedAt);\nconst durationMs = endTime - startTime;\nconst durationMinutes = durationMs / 60000;\n\n// Return enriched data\nreturn [{\n  json: {\n    body: data,\n    validation: {\n      isValid: true,\n      timestamp: new Date().toISOString()\n    },\n    calculated: {\n      durationMinutes: durationMinutes.toFixed(2),\n      durationFormatted: formatDuration(durationMs),\n      dayOfWeek: startTime.toLocaleDateString('en-US', { weekday: 'long' }),\n      timeOfDay: getTimeOfDay(startTime),\n      messageCount: data.messages.length,\n      hasActionItems: checkForActionItems(data.messages)\n    }\n  }\n}];\n\n// Helper functions\nfunction formatDuration(ms) {\n  const seconds = Math.floor(ms / 1000);\n  const minutes = Math.floor(seconds / 60);\n  const remainingSeconds = seconds % 60;\n  return `${minutes}m ${remainingSeconds}s`;\n}\n\nfunction getTimeOfDay(date) {\n  const hour = date.getHours();\n  if (hour < 6) return \"Early Morning\";\n  if (hour < 12) return \"Morning\";\n  if (hour < 17) return \"Afternoon\";\n  if (hour < 21) return \"Evening\";\n  return \"Night\";\n}\n\nfunction checkForActionItems(messages) {\n  const actionKeywords = ['will do', 'action item', 'follow up', 'next step', 'todo', 'task'];\n  return messages.some(msg => \n    actionKeywords.some(keyword => \n      msg.message.toLowerCase().includes(keyword)\n    )\n  );\n}"
      },
      "typeVersion": 2,
      "continueOnFail": true
    },
    {
      "id": "3f649c76-f8ac-4be6-9421-7b3b432fa775",
      "name": "AIによる通話分析",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        880,
        380
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "id",
          "value": "gpt-4o-mini"
        },
        "options": {
          "topP": 0.9,
          "temperature": 0.3
        },
        "messages": {
          "values": [
            {
              "role": "system",
              "content": "=You are an expert conversation analyst. Analyze the following call data and provide a comprehensive summary.\n\nCONTEXT:\n- Current Date/Time: {{ $now }}\n- Participant: {{ $json.body.call_data.userName }}\n- Call Duration: {{ $json.calculated.durationMinutes }} minutes\n- Time of Day: {{ $json.calculated.timeOfDay }}\n- Day of Week: {{ $json.calculated.dayOfWeek }}\n- Message Count: {{ $json.calculated.messageCount }}\n\nCONVERSATION DATA:\n{{ $json.body.messages.map(m => `[${m.sent_at}] ${m.sender.toUpperCase()}: ${m.message}`).join('\\n') }}\n\nANALYSIS REQUIREMENTS:\n1. Extract factual information only from the provided data\n2. If a section has no relevant content, use \"Not applicable\"\n3. Identify specific commitments, dates, and actions mentioned\n4. Analyze emotional tone and engagement level\n5. Note any technical issues or interruptions\n\nOUTPUT FORMAT (strict JSON):\n```json\n{\n  \"title\": \"Brief, descriptive title (max 80 chars)\",\n  \"metadata\": {\n    \"participant\": \"exact name from data\",\n    \"date\": \"ISO format date\",\n    \"duration_minutes\": \"number as string\",\n    \"topic\": \"main discussion topic\",\n    \"user_sentiment\": \"satisfied/neutral/dissatisfied\",\n    \"engagement_level\": \"high/medium/low\"\n  },\n  \"summary\": \"2-3 sentence executive summary of the conversation\",\n  \"main_points\": [\"up to 5 key discussion points\"],\n  \"action_items\": [\n    {\"task\": \"specific action\", \"owner\": \"person responsible\", \"due_date\": \"ISO date or TBD\"}\n  ],\n  \"follow_ups\": [\"specific next steps mentioned\"],\n  \"key_decisions\": [\"decisions made during call\"],\n  \"questions_raised\": [\"unresolved questions\"],\n  \"stories\": [\"any anecdotes or examples shared\"],\n  \"references\": [\"external resources or documents mentioned\"],\n  \"arguments\": [\n    {\"point\": \"argument made\", \"counterpoint\": \"opposing view if any\"}\n  ],\n  \"related_topics\": [\"topics that came up in discussion\"],\n  \"sentiment_analysis\": {\n    \"overall\": \"positive/neutral/negative\",\n    \"confidence\": \"high/medium/low\",\n    \"turning_points\": [\"moments where sentiment changed\"],\n    \"key_emotional_indicators\": [\"specific phrases indicating emotion\"]\n  },\n  \"recommendations\": [\"suggested actions based on analysis\"]\n}\n```\n\nRules:\n1. Use ONLY data from the provided conversation\n2. For empty sections, use empty arrays [] or \"Not applicable\"\n3. All dates should be in ISO 8601 format\n4. Be specific and actionable in recommendations"
            },
            {
              "content": "=Analyze this BeyondPresence video agent call:\n\nCall Data: {{ JSON.stringify($json.body.call_data) }}\nEvaluation: {{ JSON.stringify($json.body.evaluation) }}\nMessages: {{ JSON.stringify($json.body.messages) }}\n\nProvide the analysis in the exact JSON format specified."
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "f8f01179-b8ee-4251-92e3-cf0e781365ff",
      "name": "AI応答の解析",
      "type": "n8n-nodes-base.code",
      "position": [
        1200,
        380
      ],
      "parameters": {
        "jsCode": "// Get the content string from the OpenAI response\nconst content = $input.first().json.message.content;\n\n// Use a regular expression to extract the JSON inside the code block\n// This matches content between ```json and ``` or just between ``` and ```\nconst match = content.match(/```(?:json)?\\s*([\\s\\S]*?)```/);\n\nif (!match || !match[1]) {\n  // If no match with code block, try to extract JSON directly from the content\n  // Sometimes the response might have JSON without code blocks\n  const directJsonMatch = content.match(/\\{[\\s\\S]*\\}/);\n  if (directJsonMatch) {\n    try {\n      return [{ json: JSON.parse(directJsonMatch[0]) }];\n    } catch (e) {\n      throw new Error('Found potential JSON but failed to parse it: ' + e.message);\n    }\n  }\n  throw new Error('No JSON code block found in content.');\n}\n\n// Clean up the JSON string\nlet jsonString = match[1]\n  .replace(/\\/\\/.*$/gm, '')     // Remove single-line comments\n  .replace(/\\/\\*[\\s\\S]*?\\*\\//g, '') // Remove multi-line comments\n  .replace(/,(\\s*[}\\]])/g, '$1') // Remove trailing commas\n  .trim();                       // Trim whitespace\n\nlet parsed;\ntry {\n  parsed = JSON.parse(jsonString);\n} catch (e) {\n  // If initial parse fails, try a more aggressive cleaning approach\n  try {\n    // Try to fix common JSON errors\n    jsonString = jsonString\n      .replace(/(['\"])?([a-zA-Z0-9_]+)(['\"])?\\s*:/g, '\"$2\":') // Ensure property names are quoted\n      .replace(/:\\s*'([^']*)'/g, ':\"$1\"');                   // Replace single quotes with double quotes\n    \n    parsed = JSON.parse(jsonString);\n  } catch (fallbackError) {\n    throw new Error('Failed to parse JSON: ' + e.message + \n      '\\nExtracted string:\\n' + jsonString);\n  }\n}\n\nreturn [{ json: parsed }];"
      },
      "typeVersion": 2,
      "continueOnFail": true
    },
    {
      "id": "a81dde9e-a951-4c43-93ae-818a3032e84d",
      "name": "Google スプレッドシートに保存",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1520,
        380
      ],
      "parameters": {
        "columns": {
          "value": {
            "Date": "={{ $json.metadata.date }}",
            "Title": "={{ $json.title }}",
            "Topic": "={{ $json.metadata.topic }}",
            "Summary": "={{ $json.summary }}",
            "Follow Up 1": "={{ $json.follow_ups[0] || '' }}",
            "Follow Up 2": "={{ $json.follow_ups[1] || '' }}",
            "Participant": "={{ $json.metadata.participant }}",
            "Reference 1": "={{ $json.references[0] || '' }}",
            "Reference 2": "={{ $json.references[1] || '' }}",
            "Action Due 1": "={{ $json.action_items[0]?.due_date || '' }}",
            "Action Due 2": "={{ $json.action_items[1]?.due_date || '' }}",
            "Main Point 1": "={{ $json.main_points[0] || '' }}",
            "Main Point 2": "={{ $json.main_points[1] || '' }}",
            "Main Point 3": "={{ $json.main_points[2] || '' }}",
            "Processed At": "={{ $now.toISO() }}",
            "Action Item 1": "={{ $json.action_items[0]?.task || '' }}",
            "Action Item 2": "={{ $json.action_items[1]?.task || '' }}",
            "Story/Example": "={{ $json.stories[0] || '' }}",
            "Action Owner 1": "={{ $json.action_items[0]?.owner || '' }}",
            "Action Owner 2": "={{ $json.action_items[1]?.owner || '' }}",
            "Key Decision 1": "={{ $json.key_decisions[0] || '' }}",
            "Key Decision 2": "={{ $json.key_decisions[1] || '' }}",
            "User Sentiment": "={{ $json.metadata.user_sentiment }}",
            "Related Topic 1": "={{ $json.related_topics[0] || '' }}",
            "Related Topic 2": "={{ $json.related_topics[1] || '' }}",
            "Related Topic 3": "={{ $json.related_topics[2] || '' }}",
            "Engagement Level": "={{ $json.metadata.engagement_level || 'Not analyzed' }}",
            "Recommendation 1": "={{ $json.recommendations[0] || '' }}",
            "Recommendation 2": "={{ $json.recommendations[1] || '' }}",
            "Question Raised 1": "={{ $json.questions_raised[0] || '' }}",
            "Question Raised 2": "={{ $json.questions_raised[1] || '' }}",
            "Sentiment Overall": "={{ $json.sentiment_analysis.overall }}",
            "Duration (minutes)": "={{ $json.metadata.duration_minutes }}",
            "Workflow Execution": "={{ $execution.id }}",
            "Sentiment Confidence": "={{ $json.sentiment_analysis.confidence }}",
            "Emotional Indicator 1": "={{ $json.sentiment_analysis.key_emotional_indicators[0] || '' }}",
            "Emotional Indicator 2": "={{ $json.sentiment_analysis.key_emotional_indicators[1] || '' }}"
          },
          "schema": [],
          "mappingMode": "defineBelow",
          "matchingColumns": []
        },
        "options": {
          "cellFormat": "USER_ENTERED"
        },
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "",
          "cachedResultUrl": "",
          "cachedResultName": "Select your copied sheet"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "68d270b9-eebd-409c-8ed4-862300ab0c02",
      "name": "エラーハンドリングに関する注意事項",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        680
      ],
      "parameters": {
        "width": 380,
        "height": 340,
        "content": "## 🚨 Error Handling (Optional)\n\nAdd this error trigger to handle failures gracefully:\n- Log errors to a separate sheet\n- Send notifications to admins\n- Retry failed operations\n\nConnect to any node that might fail"
      },
      "typeVersion": 1
    },
    {
      "id": "41bcf838-b8e6-451f-bae7-a2bb46d376d9",
      "name": "拡張オプション",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1780,
        0
      ],
      "parameters": {
        "color": 5,
        "width": 320,
        "height": 580,
        "content": "## 🔔 Optional Extensions\n\n**Slack Notification**:\n- Send summary to team channel\n- Alert on negative sentiment\n- Notify about action items\n\n**Database Backup**:\n- Store in PostgreSQL/MySQL\n- Enable advanced analytics\n- Create dashboards\n\n**CRM Integration**:\n- Update contact records\n- Create follow-up tasks\n- Log call activities"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "fc64278d-af54-4843-9f36-8eb5e9176c92",
  "connections": {
    "3f649c76-f8ac-4be6-9421-7b3b432fa775": {
      "main": [
        [
          {
            "node": "f8f01179-b8ee-4251-92e3-cf0e781365ff",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f8f01179-b8ee-4251-92e3-cf0e781365ff": {
      "main": [
        [
          {
            "node": "a81dde9e-a951-4c43-93ae-818a3032e84d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1ef688fd-23cb-4bf5-8ece-26a426a6bd53": {
      "main": [
        [
          {
            "node": "f46e8a2a-2c7c-4cd5-bbb6-703711b62804",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "00370bff-2f39-43b2-8ada-b99c7e60a63e": {
      "main": [
        [
          {
            "node": "1ef688fd-23cb-4bf5-8ece-26a426a6bd53",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "efb2be58-f618-4412-82d1-b5c87a6bb906": {
      "main": [
        [
          {
            "node": "00370bff-2f39-43b2-8ada-b99c7e60a63e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f46e8a2a-2c7c-4cd5-bbb6-703711b62804": {
      "main": [
        [
          {
            "node": "3f649c76-f8ac-4be6-9421-7b3b432fa775",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}