サプライヤーリスクスコアリングの自動化

• •Googleアカウント + Gmail API認証情報
• •PostgreSQLデータベース接続情報
• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "nodes": [
    {
      "id": "1",
      "name": "日次リスク評価",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        240,
        300
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "value": "0 6 * * *"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "2",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        140,
        180
      ],
      "parameters": {
        "width": 240,
        "height": 160,
        "content": "## Supply Chain Monitor\n\n⚙️ **Configure risk parameters:**\n- Risk thresholds by category\n- Supplier criticality levels\n- Alert escalation rules\n- Backup supplier priorities"
      },
      "typeVersion": 1
    },
    {
      "id": "3",
      "name": "リスク設定",
      "type": "n8n-nodes-base.set",
      "position": [
        440,
        300
      ],
      "parameters": {
        "values": {
          "number": [
            {
              "name": "mediumRiskThreshold",
              "value": 31
            },
            {
              "name": "highRiskThreshold",
              "value": 61
            },
            {
              "name": "criticalRiskThreshold",
              "value": 81
            }
          ],
          "string": [
            {
              "name": "procurementEmail",
              "value": "procurement@company.com"
            },
            {
              "name": "riskManagerEmail",
              "value": "risk@company.com"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "4",
      "name": "アクティブサプライヤーの取得",
      "type": "n8n-nodes-base.postgres",
      "position": [
        640,
        300
      ],
      "parameters": {
        "query": "SELECT supplier_id, supplier_name, category, criticality_level, last_delivery_date, quality_score, payment_terms, country FROM suppliers WHERE status = 'active'"
      },
      "typeVersion": 1
    },
    {
      "id": "5",
      "name": "財務健全性の取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        840,
        200
      ],
      "parameters": {
        "url": "https://api.dnb.com/v1/companies/{{ $json.supplier_id }}/creditAssessment",
        "method": "GET",
        "headers": {
          "Content-Type": "application/json",
          "Authorization": "Bearer {{ $credentials.dnb.apiKey }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "6",
      "name": "ニュース・イベントの監視",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        840,
        300
      ],
      "parameters": {
        "qs": {
          "q": "{{ $json.supplier_name }}",
          "from": "{{ new Date(Date.now() - 7*24*60*60*1000).toISOString().split('T')[0] }}",
          "sortBy": "publishedAt",
          "language": "en"
        },
        "url": "https://api.newsapi.org/v2/everything",
        "method": "GET",
        "headers": {
          "X-API-Key": "{{ $credentials.newsapi.apiKey }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "7",
      "name": "パフォーマンスデータの取得",
      "type": "n8n-nodes-base.postgres",
      "position": [
        840,
        400
      ],
      "parameters": {
        "query": "SELECT AVG(delivery_score) as avg_delivery, AVG(quality_score) as avg_quality, COUNT(late_deliveries) as late_count FROM supplier_performance WHERE supplier_id = '{{ $json.supplier_id }}' AND date >= NOW() - INTERVAL '30 days'"
      },
      "typeVersion": 1
    },
    {
      "id": "8",
      "name": "リスクスコアの計算",
      "type": "n8n-nodes-base.code",
      "position": [
        1040,
        300
      ],
      "parameters": {
        "jsCode": "// Comprehensive supplier risk assessment algorithm\nconst supplier = $json;\nconst riskSettings = $node['Risk Settings'].json;\n\n// Get financial health data\nconst financialData = $node['Get Financial Health'].json;\nconst newsData = $node['Monitor News & Events'].json;\nconst performanceData = $node['Get Performance Data'].json;\n\nlet totalRiskScore = 0;\nlet riskFactors = [];\n\n// Financial Risk Assessment (35% weight)\nlet financialRisk = 0;\nif (financialData.creditScore) {\n  if (financialData.creditScore < 500) {\n    financialRisk = 35;\n    riskFactors.push('Poor credit score');\n  } else if (financialData.creditScore < 700) {\n    financialRisk = 20;\n    riskFactors.push('Below average credit score');\n  } else if (financialData.creditScore < 800) {\n    financialRisk = 10;\n  } else {\n    financialRisk = 0;\n  }\n}\n\n// Payment behavior risk\nif (financialData.paymentDelays > 30) {\n  financialRisk += 10;\n  riskFactors.push('Payment delays detected');\n}\n\ntotalRiskScore += financialRisk;\n\n// Operational Risk Assessment (30% weight)\nlet operationalRisk = 0;\nif (performanceData.avg_delivery < 85) {\n  operationalRisk += 15;\n  riskFactors.push('Poor delivery performance');\n}\n\nif (performanceData.avg_quality < 90) {\n  operationalRisk += 10;\n  riskFactors.push('Quality issues detected');\n}\n\nif (performanceData.late_count > 5) {\n  operationalRisk += 5;\n  riskFactors.push('Frequent late deliveries');\n}\n\ntotalRiskScore += operationalRisk;\n\n// News Sentiment Risk (15% weight)\nlet newsRisk = 0;\nif (newsData.articles && newsData.articles.length > 0) {\n  const negativeKeywords = ['bankruptcy', 'lawsuit', 'recall', 'investigation', 'fraud', 'scandal'];\n  const negativeArticles = newsData.articles.filter(article => \n    negativeKeywords.some(keyword => \n      article.title.toLowerCase().includes(keyword) || \n      article.description.toLowerCase().includes(keyword)\n    )\n  );\n  \n  if (negativeArticles.length > 0) {\n    newsRisk = Math.min(negativeArticles.length * 5, 15);\n    riskFactors.push(`${negativeArticles.length} negative news articles`);\n  }\n}\n\ntotalRiskScore += newsRisk;\n\n// Geopolitical Risk Assessment (10% weight)\nlet geopoliticalRisk = 0;\nconst highRiskCountries = ['China', 'Russia', 'Iran', 'North Korea'];\nconst mediumRiskCountries = ['Turkey', 'Venezuela', 'Myanmar'];\n\nif (highRiskCountries.includes(supplier.country)) {\n  geopoliticalRisk = 10;\n  riskFactors.push('High geopolitical risk country');\n} else if (mediumRiskCountries.includes(supplier.country)) {\n  geopoliticalRisk = 5;\n  riskFactors.push('Medium geopolitical risk country');\n}\n\ntotalRiskScore += geopoliticalRisk;\n\n// Criticality Multiplier (10% weight)\nlet criticalityRisk = 0;\nif (supplier.criticality_level === 'critical') {\n  criticalityRisk = 10;\n} else if (supplier.criticality_level === 'high') {\n  criticalityRisk = 7;\n} else if (supplier.criticality_level === 'medium') {\n  criticalityRisk = 3;\n}\n\ntotalRiskScore += criticalityRisk;\n\n// Determine risk level and action\nlet riskLevel = 'low';\nlet riskColor = '🟢';\nlet recommendedAction = 'monitor';\n\nif (totalRiskScore >= riskSettings.criticalRiskThreshold) {\n  riskLevel = 'critical';\n  riskColor = '🔴';\n  recommendedAction = 'emergency_protocol';\n} else if (totalRiskScore >= riskSettings.highRiskThreshold) {\n  riskLevel = 'high';\n  riskColor = '🟠';\n  recommendedAction = 'immediate_contact';\n} else if (totalRiskScore >= riskSettings.mediumRiskThreshold) {\n  riskLevel = 'medium';\n  riskColor = '🟡';\n  recommendedAction = 'enhanced_monitoring';\n}\n\n// Calculate potential impact\nconst potentialImpact = {\n  financial: totalRiskScore * 1000, // Estimated cost impact\n  operational: riskLevel === 'critical' ? 'severe' : riskLevel === 'high' ? 'moderate' : 'minimal',\n  timeline: riskLevel === 'critical' ? 'immediate' : riskLevel === 'high' ? '1-3 days' : '1-2 weeks'\n};\n\nreturn {\n  supplier_id: supplier.supplier_id,\n  supplier_name: supplier.supplier_name,\n  category: supplier.category,\n  country: supplier.country,\n  risk_score: Math.round(totalRiskScore),\n  risk_level: riskLevel,\n  risk_color: riskColor,\n  recommended_action: recommendedAction,\n  risk_factors: riskFactors,\n  potential_impact: potentialImpact,\n  financial_risk: financialRisk,\n  operational_risk: operationalRisk,\n  news_risk: newsRisk,\n  geopolitical_risk: geopoliticalRisk,\n  criticality_risk: criticalityRisk,\n  assessed_at: new Date().toISOString()\n};"
      },
      "typeVersion": 1
    },
    {
      "id": "9",
      "name": "重大リスクのフィルタリング",
      "type": "n8n-nodes-base.if",
      "position": [
        1240,
        200
      ],
      "parameters": {
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.risk_level }}",
              "rightValue": "critical"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "10",
      "name": "重大アラートの送信",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1440,
        100
      ],
      "parameters": {
        "sendTo": "={{ $node['Risk Settings'].json.riskManagerEmail }}",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }\n    .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }\n    .critical-alert { background: linear-gradient(45deg, #dc3545, #c82333); color: white; padding: 20px; text-align: center; margin: -30px -30px 30px -30px; border-radius: 10px 10px 0 0; }\n    .supplier-info { background: #f8d7da; padding: 15px; margin: 15px 0; border-radius: 5px; border-left: 4px solid #dc3545; }\n    .risk-breakdown { background: #fff3cd; padding: 15px; margin: 15px 0; border-radius: 5px; }\n    .action-required { background: #d4edda; padding: 15px; margin: 15px 0; border-radius: 5px; border-left: 4px solid #28a745; }\n    .impact-analysis { background: #e2e3e5; padding: 15px; margin: 15px 0; border-radius: 5px; }\n    .cta { background: #007bff; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 20px 0; }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"critical-alert\">\n      <h2>🚨 CRITICAL SUPPLIER RISK</h2>\n      <p>Immediate action required - Risk Level: {{ $json.risk_score }}/100</p>\n    </div>\n    \n    <div class=\"supplier-info\">\n      <h3>🏢 Supplier Details</h3>\n      <p><strong>Name:</strong> {{ $json.supplier_name }}</p>\n      <p><strong>Category:</strong> {{ $json.category }}</p>\n      <p><strong>Country:</strong> {{ $json.country }}</p>\n      <p><strong>Risk Score:</strong> {{ $json.risk_color }} {{ $json.risk_score }}/100</p>\n    </div>\n    \n    <div class=\"risk-breakdown\">\n      <h3>⚠️ Risk Factors Identified</h3>\n      <ul>\n        {{#each $json.risk_factors}}\n        <li>{{ this }}</li>\n        {{/each}}\n      </ul>\n      <p><strong>Financial Risk:</strong> {{ $json.financial_risk }}/35</p>\n      <p><strong>Operational Risk:</strong> {{ $json.operational_risk }}/30</p>\n      <p><strong>News/Reputation Risk:</strong> {{ $json.news_risk }}/15</p>\n      <p><strong>Geopolitical Risk:</strong> {{ $json.geopolitical_risk }}/10</p>\n    </div>\n    \n    <div class=\"impact-analysis\">\n      <h3>📊 Potential Impact</h3>\n      <p><strong>Financial Impact:</strong> ${{ $json.potential_impact.financial.toLocaleString() }}</p>\n      <p><strong>Operational Impact:</strong> {{ $json.potential_impact.operational }}</p>\n      <p><strong>Response Timeline:</strong> {{ $json.potential_impact.timeline }}</p>\n    </div>\n    \n    <div class=\"action-required\">\n      <h3>🎯 Immediate Actions Required</h3>\n      <ul>\n        <li>Contact supplier immediately to assess situation</li>\n        <li>Activate backup suppliers for critical components</li>\n        <li>Review and update contingency plans</li>\n        <li>Notify stakeholders and affected departments</li>\n        <li>Document all mitigation actions taken</li>\n      </ul>\n    </div>\n    \n    <div style=\"text-align: center;\">\n      <a href=\"tel:+1234567890\" class=\"cta\">📞 Emergency Contact</a>\n      <a href=\"mailto:{{ $node['Risk Settings'].json.procurementEmail }}\" class=\"cta\">📧 Notify Procurement</a>\n    </div>\n    \n    <p style=\"color: #666; font-size: 14px; margin-top: 30px;\">\n      Alert generated: {{ $json.assessed_at }}<br>\n      Supplier ID: {{ $json.supplier_id }}\n    </p>\n  </div>\n</body>\n</html>",
        "options": {
          "contentType": "html"
        },
        "subject": "🚨 CRITICAL SUPPLIER RISK ALERT - {{ $json.supplier_name }}"
      },
      "typeVersion": 1
    },
    {
      "id": "11",
      "name": "高リスクのフィルタリング",
      "type": "n8n-nodes-base.if",
      "position": [
        1240,
        300
      ],
      "parameters": {
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.risk_level }}",
              "rightValue": "high"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "12",
      "name": "高リスクアラートの送信",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1440,
        300
      ],
      "parameters": {
        "sendTo": "={{ $node['Risk Settings'].json.procurementEmail }}",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }\n    .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }\n    .high-risk-alert { background: linear-gradient(45deg, #fd7e14, #e8590c); color: white; padding: 20px; text-align: center; margin: -30px -30px 30px -30px; border-radius: 10px 10px 0 0; }\n    .supplier-summary { background: #fff3cd; padding: 15px; margin: 15px 0; border-radius: 5px; border-left: 4px solid #ffc107; }\n    .recommendations { background: #e8f4f8; padding: 15px; margin: 15px 0; border-radius: 5px; }\n    .cta { background: #007bff; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 20px 0; }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"high-risk-alert\">\n      <h2>🟠 HIGH RISK SUPPLIER</h2>\n      <p>Enhanced monitoring required - Risk Score: {{ $json.risk_score }}/100</p>\n    </div>\n    \n    <p>A high-risk situation has been identified with one of our suppliers. Please review and take appropriate action.</p>\n    \n    <div class=\"supplier-summary\">\n      <h3>📋 Supplier Summary</h3>\n      <p><strong>Supplier:</strong> {{ $json.supplier_name }}</p>\n      <p><strong>Category:</strong> {{ $json.category }}</p>\n      <p><strong>Risk Level:</strong> {{ $json.risk_color }} {{ $json.risk_level }} ({{ $json.risk_score }}/100)</p>\n      <p><strong>Key Concerns:</strong></p>\n      <ul>\n        {{#each $json.risk_factors}}\n        <li>{{ this }}</li>\n        {{/each}}\n      </ul>\n    </div>\n    \n    <div class=\"recommendations\">\n      <h3>💡 Recommended Actions</h3>\n      <ul>\n        <li>Schedule immediate supplier review meeting</li>\n        <li>Request updated financial statements</li>\n        <li>Assess backup supplier readiness</li>\n        <li>Review contract terms and exit clauses</li>\n        <li>Increase monitoring frequency</li>\n      </ul>\n    </div>\n    \n    <div style=\"text-align: center;\">\n      <a href=\"mailto:{{ $json.supplier_name }}@supplier.com\" class=\"cta\">📧 Contact Supplier</a>\n      <a href=\"#\" class=\"cta\">📊 View Full Report</a>\n    </div>\n    \n    <p style=\"color: #666; font-size: 14px; margin-top: 30px;\">\n      This alert was generated automatically. Please document any actions taken.\n    </p>\n  </div>\n</body>\n</html>",
        "options": {
          "contentType": "html"
        },
        "subject": "🟠 HIGH RISK SUPPLIER ALERT - {{ $json.supplier_name }}"
      },
      "typeVersion": 1
    },
    {
      "id": "13",
      "name": "中リスクのフィルタリング",
      "type": "n8n-nodes-base.if",
      "position": [
        1240,
        400
      ],
      "parameters": {
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.risk_level }}",
              "rightValue": "medium"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "14",
      "name": "中リスクアラートの送信",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1440,
        400
      ],
      "parameters": {
        "sendTo": "={{ $node['Risk Settings'].json.procurementEmail }}",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }\n    .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }\n    .medium-risk { background: #ffc107; color: #212529; padding: 20px; text-align: center; margin: -30px -30px 30px -30px; border-radius: 10px 10px 0 0; }\n    .risk-summary { background: #fff3cd; padding: 15px; margin: 15px 0; border-radius: 5px; }\n    .monitoring-plan { background: #e8f4f8; padding: 15px; margin: 15px 0; border-radius: 5px; }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"medium-risk\">\n      <h2>🟡 MEDIUM RISK SUPPLIER</h2>\n      <p>Enhanced monitoring recommended</p>\n    </div>\n    \n    <p>Hello,</p>\n    \n    <p>Our routine supplier risk assessment has identified {{ $json.supplier_name }} as requiring enhanced monitoring due to elevated risk factors.</p>\n    \n    <div class=\"risk-summary\">\n      <h3>📊 Risk Summary</h3>\n      <p><strong>Supplier:</strong> {{ $json.supplier_name }}</p>\n      <p><strong>Current Risk Score:</strong> {{ $json.risk_score }}/100</p>\n      <p><strong>Identified Issues:</strong></p>\n      <ul>\n        {{#each $json.risk_factors}}\n        <li>{{ this }}</li>\n        {{/each}}\n      </ul>\n    </div>\n    \n    <div class=\"monitoring-plan\">\n      <h3>📋 Monitoring Plan</h3>\n      <ul>\n        <li>Increase communication frequency</li>\n        <li>Request monthly performance reports</li>\n        <li>Monitor financial indicators closely</li>\n        <li>Prepare contingency plans</li>\n      </ul>\n    </div>\n    \n    <p>No immediate action required, but please keep this supplier on your radar.</p>\n    \n    <p style=\"color: #666; font-size: 14px; margin-top: 30px;\">\n      Next assessment: {{ new Date(Date.now() + 7*24*60*60*1000).toLocaleDateString() }}\n    </p>\n  </div>\n</body>\n</html>",
        "options": {
          "contentType": "html"
        },
        "subject": "🟡 Medium Risk Supplier Update - {{ $json.supplier_name }}"
      },
      "typeVersion": 1
    },
    {
      "id": "15",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1340,
        60
      ],
      "parameters": {
        "width": 240,
        "height": 160,
        "content": "## Risk Management\n\n📊 **Automated responses:**\n- Critical risk: Emergency protocols\n- High risk: Immediate contact\n- Medium risk: Enhanced monitoring\n- Low risk: Routine surveillance"
      },
      "typeVersion": 1
    },
    {
      "id": "16",
      "name": "リスク評価の追跡",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1240,
        520
      ],
      "parameters": {
        "values": {
          "values": [
            "={{ $json.supplier_id }}",
            "={{ $json.supplier_name }}",
            "={{ $json.category }}",
            "={{ $json.country }}",
            "={{ $json.risk_score }}",
            "={{ $json.risk_level }}",
            "={{ $json.recommended_action }}",
            "={{ $json.assessed_at }}"
          ]
        },
        "resource": "sheet",
        "operation": "appendRow",
        "sheetName": "Supplier Risk Tracking",
        "documentId": "your-google-sheet-id"
      },
      "typeVersion": 1
    }
  ],
  "connections": {
    "1": {
      "main": [
        [
          {
            "node": "3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3": {
      "main": [
        [
          {
            "node": "4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4": {
      "main": [
        [
          {
            "node": "5",
            "type": "main",
            "index": 0
          },
          {
            "node": "6",
            "type": "main",
            "index": 0
          },
          {
            "node": "7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5": {
      "main": [
        [
          {
            "node": "8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6": {
      "main": [
        [
          {
            "node": "8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7": {
      "main": [
        [
          {
            "node": "8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8": {
      "main": [
        [
          {
            "node": "9",
            "type": "main",
            "index": 0
          },
          {
            "node": "11",
            "type": "main",
            "index": 0
          },
          {
            "node": "13",
            "type": "main",
            "index": 0
          },
          {
            "node": "16",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9": {
      "main": [
        [
          {
            "node": "10",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "11": {
      "main": [
        [
          {
            "node": "12",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "13": {
      "main": [
        [
          {
            "node": "14",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}