스마트 공급업체 계약 갱신 및 알림 워크플로우 (GPT 4.1 mini 사용)

• •Slack Bot Token 또는 Webhook URL
• •Google Sheets API 인증 정보
• •OpenAI API Key

{
  "id": "DYAmOE9mHdq9X8eT",
  "meta": {
    "instanceId": "4a2e6764ba7a6bc9890d9225f4b21d570ce88fc9bd57549c89057fcee58fed0f",
    "templateId": "5453",
    "templateCredsSetupCompleted": true
  },
  "name": "Smart Vendor Contract Renewal & Reminder Workflow With GPT 4.1 mini",
  "tags": [
    {
      "id": "5HqPDYxcmr92h5gG",
      "name": "Finance Workflow",
      "createdAt": "2025-08-02T13:47:30.790Z",
      "updatedAt": "2025-08-02T13:47:30.790Z"
    }
  ],
  "nodes": [
    {
      "id": "c14ab271-f79e-4b40-99f4-4a67cef3988a",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1472,
        1456
      ],
      "parameters": {
        "width": 540,
        "height": 528,
        "content": "## 1. Workflow trigger on daily basis every 6AM\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7608b8d6-1acb-43f6-ad33-0cef8401e633",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2544,
        1120
      ],
      "parameters": {
        "width": 1024,
        "height": 1696,
        "content": "# 📝 Smart Vendor Contract Renewal & Reminder Workflow With GPT 4.1 mini\nNever miss a vendor renewal again!\nThis smart workflow automatically tracks expiring contracts, reminds your finance team via Slack, and helps initiate renewal with vendors through email — all with built-in approval and logging. Perfect for managing both auto-renew and manual contracts.\n## 📌 Who’s it for\nThis workflow is designed for **Finance and Procurement teams** responsible for managing **vendor/service contracts**. It ensures timely notifications for expiring contracts and automates the initiation of renewal conversations with vendors.\n\n## ⚙️ How it works / What it does\n\n1. **⏰ Daily Trigger**\n   - Runs every day at 6:00 AM using a scheduler.\n\n2. **📄 Retrieve Contract List**\n   - Reads vendor contract data from a **Google Sheet** (or any data source).\n   - Filters for contracts nearing their **end date**, using a **Notice Period (days)** field.\n\n3. **🔀 Branch Based on Renewal Type**\n   - **Auto-Renew Contracts**:\n     - Compose a Slack message summarizing the auto-renewal.\n     - Notify the finance contact via Slack.\n\n   - **Manual Renewal Contracts**:\n     - Use an OpenAI-powered agent to generate a meaningful Slack message.\n     - Send message and **wait for approval** from the finance contact (e.g., within 8 hours).\n     - Upon approval, generate a formal **HTML email** to the vendor.\n     - Send the email to **initiate the contract extension process**.\n\n4. **📊 (Optional) Logging**\n   - Can be extended to **log all actions** (Slack messages, emails, approvals) to **Google Sheets** or other databases.\n\n## 🛠️ How to set up\n\n1. **Prepare your Google Sheet**\n   - Include the following fields:\n     - `Vendor Name`, `Vendor Email`, `Service Type`, `Contract Start Date`, `Contract End Date`, `Notice Period (days)`, `Renewal Type`, `Finance Contact`, `Contact Email`, `Slack ID`, `Contract Value`, `Notes`.\n     - Sample: https://docs.google.com/spreadsheets/d/1zdDgKyL0sY54By57Yz4dNokQC_oIbVxcCKeWJ6PADBM/edit?usp=sharing\n\n2. **Configure Integrations**\n   - 🟢 **Google Sheets API**: To read contract data.\n   - 🔵 **Slack API**: To notify and wait for approval.\n   - 🧠 **OpenAI API (GPT-4)**: To generate personalized reminders.\n   - ✉️ **Email (SMTP/Gmail)**: To send emails to vendors.\n\n3. **Set the Daily Scheduler**\n   - Use a Cron node to trigger the workflow at `6:00 AM` daily.\n\n## ✅ Requirements\n\n| Component                        | Required |\n|----------------------------------|----------|\n| Google Sheets API                | ✅       |\n| Slack API                        | ✅       |\n| OpenAI API (GPT-4)               | ✅       |\n| Email (SMTP/Gmail)               | ✅       |\n| n8n (Self-hosted or Cloud)       | ✅       |\n| Contract Sheet with proper schema| ✅       |\n\n## 🧩 How to customize the workflow\n- **Adjust Reminder Period**: Modify the logic in the `Find Expiring Vendors` node (based on `Contract End Date` and `Notice Period`).\n- **Change Message Tone or Format**: Customize the OpenAI agent's prompt or switch from plain text to branded HTML email.\n- **Add Logging or Tracking**: Add a node to append logs to a **Google Sheet**, **Notion**, or **database**.\n- **Replace Data Source**: Swap out Google Sheets for **Airtable**, **PostgreSQL**, or **other CRM/database systems**.\n- **Adjust Wait/Approval Duration**: Modify the `sendAndWait` Slack node timeout (e.g., from 8 hours to 2 hours).\n\n## 📦 Optional Extensions\n- 🧾 Add PDF contract preview via Drive link\n- 🧠 Use GPT to summarize renewal terms\n- 🛠 Auto-create Jira task for contract review\n"
      },
      "typeVersion": 1
    },
    {
      "id": "a7c8a69b-d5bb-4b7d-abef-8be18c0d2f83",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        2256
      ],
      "parameters": {
        "width": 1020,
        "height": 596,
        "content": "![Alt text](https://wisestackai.s3.ap-southeast-1.amazonaws.com/Screenshot+2025-08-03+at+7.59.45%E2%80%AFPM.png \"Optional title text\")\n"
      },
      "typeVersion": 1
    },
    {
      "id": "e0a78a50-7d76-4ce5-b240-6bb38a04f5ea",
      "name": "만료 예정 공급업체 찾기",
      "type": "n8n-nodes-base.code",
      "position": [
        -544,
        1648
      ],
      "parameters": {
        "jsCode": "const results = [];\n\nfor (const item of items) {\n  const data = item.json;\n\n  const endDate = new Date(data[\"Contract End Date\"]);\n  const noticePeriod = parseInt(data[\"Notice Period (days)\"], 10);\n\n  // Calculate the date when the reminder should be sent\n  const reminderDate = new Date(endDate);\n  reminderDate.setDate(reminderDate.getDate() - noticePeriod);\n\n  // If today's date >= reminder date, it's time to remind\n  if ($today >= reminderDate && $today <= endDate) {\n    results.push({\n      json: {\n        ...data,\n        remind: true,\n        reminderDate: reminderDate.toISOString().split(\"T\")[0],\n        daysUntilExpiry: Math.floor((endDate - $today) / (1000 * 60 * 60 * 24))\n      }\n    });\n  }\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "8517796c-9c66-4c74-9c0e-b0050e495870",
      "name": "OpenAI 채팅 모델",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -48,
        2080
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "PPSwAKeLQYgAPobT",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "c7a565b9-97ca-4685-913b-80f01f9f9c09",
      "name": "구조화된 출력 파서",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        80,
        2080
      ],
      "parameters": {
        "jsonSchemaExample": "{\n\t\"subject\": \"California\",\n\t\"body\": \"\",\n  \"original_json\":\"\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "909772f9-a143-4c8d-b7b9-482eb450d428",
      "name": "재무 담당자 알림 및 승인 대기",
      "type": "n8n-nodes-base.slack",
      "position": [
        320,
        1872
      ],
      "webhookId": "bf20ed1a-2179-4efb-bfa2-b1a5fa585d6d",
      "parameters": {
        "select": "channel",
        "message": "={{ $json.output.subject }}\n{{ $json.output.body }}",
        "options": {
          "limitWaitTime": {
            "values": {
              "resumeAmount": 8
            }
          }
        },
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "C0989EJ7Z6K",
          "cachedResultName": "automation"
        },
        "operation": "sendAndWait",
        "authentication": "oAuth2"
      },
      "credentials": {
        "slackOAuth2Api": {
          "id": "fDHHxMgQm69z0h5T",
          "name": "Slack account"
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "f07fa613-050d-4416-ab36-24050854bf0d",
      "name": "이메일 템플릿 작성",
      "type": "n8n-nodes-base.code",
      "position": [
        752,
        1872
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const record = JSON.parse($('Vendor reminder agent').item.json.output.original_json)\n\nconst vendorName = record[\"Vendor Name\"];\nconst vendorEmail = record[\"Vendor Email\"];\nconst serviceType = record[\"Service Type\"];\nconst contractEndDate = new Date(record[\"Contract End Date\"]).toDateString();\nconst financeContact = record[\"Finance Contact\"];\nconst financeEmail = record[\"Contact Email\"];\nconst renewalType = record[\"Renewal Type\"];\nconst contractValue = record[\"Contract Value (USD)\"];\n\nconst subject = `Contract Extension: ${vendorName} - ${serviceType}`;\n\nconst htmlBody = `\n  <div style=\"font-family: Arial, sans-serif; color: #333; line-height: 1.6;\">\n    <p>Dear ${vendorName} Team,</p>\n\n    <p>We are reaching out to initiate the contract extension process for your <strong>${serviceType}</strong> service. According to our records, the current contract is set to end on <strong>${contractEndDate}</strong>.</p>\n\n    <p>Key contract details:</p>\n    <ul>\n      <li><strong>Vendor:</strong> ${vendorName}</li>\n      <li><strong>Service Type:</strong> ${serviceType}</li>\n      <li><strong>Renewal Type:</strong> ${renewalType}</li>\n      <li><strong>Contract Value:</strong> $${contractValue}</li>\n    </ul>\n\n    <p>If any changes are required for the upcoming term or if you need us to provide any documentation, kindly let us know by replying to this email.</p>\n\n    <p>Best regards,<br/>\n    ${financeContact}<br/>\n    <a href=\"mailto:${financeEmail}\">${financeEmail}</a><br/>\n    Finance Team</p>\n  </div>\n`;\n\nreturn {\n  to: vendorEmail,\n  subject,\n  html: htmlBody\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "92b106a8-712e-4be8-a95d-a71e8032f0f2",
      "name": "공급업체 알림 에이전트",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        -80,
        1872
      ],
      "parameters": {
        "text": "=Here is the contract info:\n{{ $json.toJsonString()}}",
        "batching": {},
        "messages": {
          "messageValues": [
            {
              "message": "You are a helpful assistant that drafts professional email reminders to finance contacts about upcoming or overdue vendor contract renewals.\\n\\n## Instructions:\\n- Use a clear, polite, and informative tone.\\n- The email should include:\\n  - Vendor name and service type\\n  - Contract end date\\n  - Renewal type (Manual or Auto-Renew)\\n  - Days left until expiration (or if overdue)\\n  - Contract value (if available)\\n  - Notes field (if provided)\\n- Address the finance contact by name.\\n- Mention next steps if renewal is manual.\\n- Keep the email concise but informative.\\n\\n## Output format:\\n- `subject`: A short subject line for the email\\n- `body`: The full email body (plain text or simple HTML)\\n\\nNow write the subject and body."
            }
          ]
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 1.7
    },
    {
      "id": "43d8ff59-2359-4dbe-bc5b-4fa341f6266d",
      "name": "공급업체 계약 목록 가져오기",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -768,
        1648
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1zdDgKyL0sY54By57Yz4dNokQC_oIbVxcCKeWJ6PADBM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1zdDgKyL0sY54By57Yz4dNokQC_oIbVxcCKeWJ6PADBM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1zdDgKyL0sY54By57Yz4dNokQC_oIbVxcCKeWJ6PADBM/edit?usp=drivesdk",
          "cachedResultName": "Vendors"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "L670Nly6gZGo71br",
          "name": "Google Sheets account 2"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "e1ef73e4-980d-4b47-a715-4dc071d6a35c",
      "name": "자동 갱신 계약인가요?",
      "type": "n8n-nodes-base.if",
      "position": [
        -320,
        1648
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "31b61f50-4cc8-4c0c-a761-a1b16d1a9078",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json[\"Renewal Type\"] }}",
              "rightValue": "=Auto-Renew"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "e6316dbe-ef76-4240-bfdd-6dc1ca0d52db",
      "name": "수동 계약 연장 이메일 전송",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        976,
        1872
      ],
      "webhookId": "2cfc6b7b-220e-441b-a89f-0b14f7feb07c",
      "parameters": {
        "html": "={{ $json.html }}",
        "options": {},
        "subject": "={{ $json.subject }}",
        "toEmail": "={{ $json.to }}"
      },
      "credentials": {
        "smtp": {
          "id": "Cqc9p9Z5PHj2MHzn",
          "name": "SMTP account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "d6947406-9340-4d1d-b274-1b180019c67e",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        896
      ],
      "parameters": {
        "width": 1036,
        "height": 436,
        "content": "![Alt text](https://wisestackai.s3.ap-southeast-1.amazonaws.com/Screenshot+2025-08-03+at+7.56.19%E2%80%AFPM.png \"Optional title text\")\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b6b47449-df79-495b-9d79-f0003b6144d2",
      "name": "Slack 메시지 작성",
      "type": "n8n-nodes-base.code",
      "position": [
        -16,
        1488
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const item = $json;\n\nconst vendorName = item[\"Vendor Name\"];\nconst serviceType = item[\"Service Type\"];\nconst contractEndDate = new Date(item[\"Contract End Date\"]).toDateString();\nconst renewalType = item[\"Renewal Type\"];\nconst contractValue = item[\"Contract Value (USD)\"];\nconst financeContact = item[\"Finance Contact\"];\nconst slackId = item[\"Slack ID\"];\nconst vendorEmail = item[\"Vendor Email\"];\nconst notes = item[\"Notes\"];\nconst daysUntil = item[\"daysUntilExpiry\"];\n\nconst message = `\n:bell: *Upcoming Auto-Renewal Notice*\n\nHi ${financeContact},\n\nThe contract with *${vendorName}* for *${serviceType}* is set to *auto-renew* in *${daysUntil} days* — on *${contractEndDate}*.\n\n> 💰 Contract Value: $${contractValue.toLocaleString()}\n> 📝 Renewal Type: ${renewalType}\n> 🗒️ Notes: ${notes}\n\nIf you wish to change this behavior or prevent auto-renewal, please send an email to the vendor at: *${vendorEmail}* before the renewal date.\n\nLet us know if you need help drafting the message or reviewing the terms.\n\nThanks,  \nFinance Automation Bot\n`;\n\nreturn {\n      slack_message: message,\n      slack_target: slackId\n    }"
      },
      "typeVersion": 2
    },
    {
      "id": "a7d66e99-4447-4c79-8b63-660bc77c1c5c",
      "name": "재무팀에 자동 갱신 계약 알림",
      "type": "n8n-nodes-base.slack",
      "position": [
        304,
        1488
      ],
      "webhookId": "bf20ed1a-2179-4efb-bfa2-b1a5fa585d6d",
      "parameters": {
        "text": "={{ $json.slack_message }}",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "C0989EJ7Z6K",
          "cachedResultName": "automation"
        },
        "otherOptions": {},
        "authentication": "oAuth2"
      },
      "credentials": {
        "slackOAuth2Api": {
          "id": "fDHHxMgQm69z0h5T",
          "name": "Slack account"
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "374f879f-6f9b-46fd-a510-ce60e2c29b51",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1264,
        1616
      ],
      "parameters": {
        "width": 1484,
        "height": 676,
        "content": "![Alt text](https://wisestackai.s3.ap-southeast-1.amazonaws.com/Screenshot+2025-08-03+at+7.55.29%E2%80%AFPM.png \"Optional title text\")\n"
      },
      "typeVersion": 1
    },
    {
      "id": "9291730f-742b-4fb3-9468-94f81bc8c987",
      "name": "일일 스케줄러",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1232,
        1648
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 6
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "1574e09b-04a5-49b0-b1f6-d82d69ed40a5",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -896,
        1456
      ],
      "parameters": {
        "width": 524,
        "height": 528,
        "content": "## 2. Get vendor contract list & find expiring contract\n- Get vendor contract list from database (Google Sheet)\n- Filter out expiring contract based on end date & notice date\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b6a74f00-3bad-484f-8b39-ab95f0601d03",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -128,
        1360
      ],
      "parameters": {
        "width": 620,
        "height": 320,
        "content": "## 3.1 Notify finance team about auto-renew contract\n- Send message via Slack to finance team so that they're aware of upcoming auto-renew contract\n"
      },
      "typeVersion": 1
    },
    {
      "id": "e22dffff-56ce-4c92-80a1-4ef987c24e41",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -128,
        1712
      ],
      "parameters": {
        "width": 620,
        "height": 496,
        "content": "## 3.2 Send manual contract extension notice to finance team\n- Vendor reminder agent compose meaningful message to finance team about upcoming manual renewal\n- Message send over Slack and waiting for approval (max 8hrs, configurable)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "a218ee9b-80bc-4a50-bbac-8690026f3fca",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        576,
        1712
      ],
      "parameters": {
        "width": 620,
        "height": 496,
        "content": "## 4. Send email to vendor to initiate the contract extend process\n- Code node compose HTML message \n- Mail node send email to vendor to init contract extension process\n"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "8176c31e-1552-4674-bd0a-ec6f4aab7ea5",
  "connections": {
    "9291730f-742b-4fb3-9468-94f81bc8c987": {
      "main": [
        [
          {
            "node": "43d8ff59-2359-4dbe-bc5b-4fa341f6266d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8517796c-9c66-4c74-9c0e-b0050e495870": {
      "ai_languageModel": [
        [
          {
            "node": "92b106a8-712e-4be8-a95d-a71e8032f0f2",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "b6b47449-df79-495b-9d79-f0003b6144d2": {
      "main": [
        [
          {
            "node": "a7d66e99-4447-4c79-8b63-660bc77c1c5c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "92b106a8-712e-4be8-a95d-a71e8032f0f2": {
      "main": [
        [
          {
            "node": "909772f9-a143-4c8d-b7b9-482eb450d428",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f07fa613-050d-4416-ab36-24050854bf0d": {
      "main": [
        [
          {
            "node": "e6316dbe-ef76-4240-bfdd-6dc1ca0d52db",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e0a78a50-7d76-4ce5-b240-6bb38a04f5ea": {
      "main": [
        [
          {
            "node": "e1ef73e4-980d-4b47-a715-4dc071d6a35c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e1ef73e4-980d-4b47-a715-4dc071d6a35c": {
      "main": [
        [
          {
            "node": "b6b47449-df79-495b-9d79-f0003b6144d2",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "92b106a8-712e-4be8-a95d-a71e8032f0f2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "43d8ff59-2359-4dbe-bc5b-4fa341f6266d": {
      "main": [
        [
          {
            "node": "e0a78a50-7d76-4ce5-b240-6bb38a04f5ea",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c7a565b9-97ca-4685-913b-80f01f9f9c09": {
      "ai_outputParser": [
        [
          {
            "node": "92b106a8-712e-4be8-a95d-a71e8032f0f2",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "e6316dbe-ef76-4240-bfdd-6dc1ca0d52db": {
      "main": [
        []
      ]
    },
    "909772f9-a143-4c8d-b7b9-482eb450d428": {
      "main": [
        [
          {
            "node": "f07fa613-050d-4416-ab36-24050854bf0d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}