アイスのリエンゲージングメールジェネレーター：GPT-4o-mini、OutlookとSheets

• •Google Sheets API認証情報
• •OpenAI API Key

{
  "meta": {
    "instanceId": "ad0113c344ee237399e44e9f11798b05baeb83a6196d514a9ae9d2ad71c3b5c9",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "1a7a6e6d-94e9-4c5d-bdaf-bba303afba79",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        760,
        560
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "4l6TDfLZVFS24g3X",
          "name": "OpenAi account 4"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "ea89f057-8d27-4458-bf2c-9e9491c684c2",
      "name": "Structured Output Parser",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        880,
        560
      ],
      "parameters": {
        "jsonSchemaExample": "{\n\t\"summary\": \"summary\",\n\t\"how to re-engage\": \"how to re-engage\", \n   \"email subject\": \"email subject\", \n  \"email body\": \"Email Body\"\n}"
      },
      "typeVersion": 1.2
    },
    {
      "id": "099926bf-2203-440a-bdc2-24f0e37d9384",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -420,
        -40
      ],
      "parameters": {
        "color": 5,
        "width": 440,
        "height": 760,
        "content": "\n### 📥 Step 1 — Pull Email List from Google Sheets\n\n1. **Copy the sample spreadsheet**  \n   [Click to copy](https://docs.google.com/spreadsheets/d/1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM/copy)  \n   It must include at least one column named `Email`.\n\n2. **In n8n:**\n   - Add a **Google Sheets OAuth2** credential.\n   - In the **Google Sheets** node, select the copied sheet and set the sheet name (e.g., `Sheet1`).\n   - This will be your source list of leads to re-engage.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "5115756e-826f-46c9-ab01-94c67a18b914",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        40,
        -40
      ],
      "parameters": {
        "color": 4,
        "width": 600,
        "height": 760,
        "content": "### 📬 Step 2 — Search Outlook for Past Conversations\n\n1. **Create a Microsoft Outlook OAuth2 credential** in n8n.\n\n2. In the **Microsoft Outlook node**:\n   - Operation: `getAll`\n   - Filters:\n     - `receivedDateTime ge 2025-01-01T00:00:00Z`\n     - `sender = {{ $json.Email }}` (dynamic from Sheets)\n   - Return all emails sent by each lead since January 2025.\n\n---\n\n### 📊 Step 3 — Aggregate Email Content\n\n- Use an **Aggregate** node to combine the key fields:\n  - `subject`, `body`, `createdDateTime`\n\n- Add a **Code node** with the following JavaScript to convert all records into a single string:\n  ```js\n  return [{\n    json: {\n      text: items.map(item => JSON.stringify(item.json)).join('\\\\n'),\n    },\n  }];"
      },
      "typeVersion": 1
    },
    {
      "id": "75834341-74f5-4722-b106-f3b5ff80a1e3",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        660,
        -40
      ],
      "parameters": {
        "color": 6,
        "width": 380,
        "height": 760,
        "content": "### 🧠 Step 4 — Use OpenAI to Summarize & Suggest Re-Engagement Strategy\n\n1. **Add your OpenAI API key** in n8n using the `OpenAI Chat Model` node.  \n   - Recommended model: `gpt-4o-mini` or higher.\n\n2. **Attach a Code node** to pass the aggregated email content into a single text block:\n   ```js\n   return [{\n     json: {\n       text: items.map(item => JSON.stringify(item.json)).join('\\\\n'),\n     },\n   }];"
      },
      "typeVersion": 1
    },
    {
      "id": "2c7676d8-650d-46e9-8fe9-152f5afebc7b",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1060,
        -40
      ],
      "parameters": {
        "color": 3,
        "width": 380,
        "height": 760,
        "content": "### 📨 Step 6 — Create a Draft Email in Outlook\n\nAfter generating the re-engagement strategy and draft email with OpenAI, this step automatically creates a draft email in Microsoft Outlook.\n\n1. **Add a `Microsoft Outlook` node** to the workflow.\n2. Set the **Resource** to `Draft`.\n3. Fill out the following fields:\n   - **To Recipients:**  \n     `={{ $('Google Sheets').item.json.Email }}`\n   - **Subject:**  \n     `={{ $json.output['email subject'] }}`\n   - **Body Content:**  \n     `={{ $json.output['email body'] }}`\n\n4. Connect your **Microsoft Outlook account** using the `OAuth2` credential type.\n\n💡 This creates a fully drafted email based on AI analysis, ready for manual review and sending.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2355df82-9e1c-4777-bf90-4e7caa77cf9b",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -420,
        -200
      ],
      "parameters": {
        "width": 1860,
        "height": 140,
        "content": "# 🤖 AI-Powered Lead Re-Engagement via Google Sheets, Outlook & OpenAI  \n**Need more help? Feel free to reach out – robert@ynteractive.com | [LinkedIn](https://www.linkedin.com/in/robert-breen-29429625/)**\n"
      },
      "typeVersion": 1
    },
    {
      "id": "f56ec71b-15bd-4288-96cd-5726b79a36a2",
      "name": "ワークフロー開始",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -400,
        300
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "1f053b63-9915-4cfd-b70d-ac922a58f0d2",
      "name": "停滞リードの取得",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -200,
        480
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM/edit?usp=drivesdk",
          "cachedResultName": "Lead ReEngagement"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "2d3d468f-6d2e-4c13-a477-01c4f41bc3d1",
      "name": "リードからの全メール取得",
      "type": "n8n-nodes-base.microsoftOutlook",
      "position": [
        80,
        560
      ],
      "webhookId": "48751adc-6119-4d5e-9934-d84cecb1594c",
      "parameters": {
        "output": "raw",
        "options": {},
        "filtersUI": {
          "values": {
            "filters": {
              "custom": "receivedDateTime ge 2025-01-01T00:00:00Z",
              "sender": "={{ $json.Email }}"
            }
          }
        },
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "microsoftOutlookOAuth2Api": {
          "id": "v6aLYotT4QswHVsA",
          "name": "Microsoft Outlook account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "fb0efd84-b82b-44e9-bb74-59b4fd6e9e3a",
      "name": "単一フィールドに結合",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        280,
        560
      ],
      "parameters": {
        "include": "specifiedFields",
        "options": {},
        "aggregate": "aggregateAllItemData",
        "fieldsToInclude": "subject,  body, createdDateTime"
      },
      "typeVersion": 1
    },
    {
      "id": "306c8218-4f43-4a77-accf-19d3c64fd5cb",
      "name": "オブジェクトフィールドをテキストに変換",
      "type": "n8n-nodes-base.code",
      "position": [
        460,
        560
      ],
      "parameters": {
        "jsCode": "return [\n  {\n    json: {\n      text: items.map(item => JSON.stringify(item.json)).join('\\n'),\n    },\n  },\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "311c5ecd-fa2b-4259-a0ba-cb9f0e8d81f2",
      "name": "AIエージェント - リード再エンゲージ",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        720,
        360
      ],
      "parameters": {
        "text": "=Recent Emails from lead: {{ $json.text }}",
        "options": {
          "systemMessage": "You are a helpful assistant trained to analyze prior email conversations for lead re-engagement.\n\nGiven a list of recent emails from a contact, perform the following:\n\n1. Summarize what the person has communicated in the past. Focus on the main topics, interests, concerns, or any open threads.\n2. Suggest 2–3 strategic ideas for how I can re-engage this lead. Format your suggestions as bullet points.\n3. Write a professional, friendly re-engagement email I can send them. Make the tone approachable but business-relevant.\n\nReturn your output in the following JSON structure:\n\n{\n  \"summary\": \"Concise summary of their past emails\",\n  \"how to re-engage\": \"• First suggestion\\n• Second suggestion\\n• Third suggestion\",\n  \"email subject\": \"Suggested subject line\",\n  \"email body\": \"Complete draft email body\"\n}\n"
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2
    },
    {
      "id": "070dd56f-5d64-4132-a93c-1344185d985a",
      "name": "データをSheetsに保存",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1180,
        460
      ],
      "parameters": {
        "columns": {
          "value": {
            "Email": "={{ $('Get Stale Leads').item.json.Email }}",
            "Summary": "={{ $json.output.summary }}",
            "Email Body": "={{ $json.output['email body'] }}",
            "Email Subject": "={{ $json.output['email subject'] }}",
            "How Can I Re-Engage": "={{ $json.output['how to re-engage'] }}"
          },
          "schema": [
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Summary",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Summary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "How Can I Re-Engage",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "How Can I Re-Engage",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email Subject",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Email Subject",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email Body",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Email Body",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1rQD493GNtTWms6GF0Wracu9Yrm0AR0jxwaWdv8eJbUM/edit?usp=drivesdk",
          "cachedResultName": "Lead ReEngagement"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "fc7a0147-ac51-4500-aaf5-f233f1af8c7a",
      "name": "下書きメール作成",
      "type": "n8n-nodes-base.microsoftOutlook",
      "position": [
        1320,
        580
      ],
      "webhookId": "ad54610a-00fe-4f41-92a5-2c8031dddf5b",
      "parameters": {
        "subject": "={{ $json.output['email subject'] }}",
        "resource": "draft",
        "bodyContent": "={{ $json.output['email body'] }}",
        "additionalFields": {
          "toRecipients": "={{ $('Get Stale Leads').item.json.Email }}"
        }
      },
      "credentials": {
        "microsoftOutlookOAuth2Api": {
          "id": "v6aLYotT4QswHVsA",
          "name": "Microsoft Outlook account"
        }
      },
      "typeVersion": 2
    }
  ],
  "pinData": {},
  "connections": {
    "f56ec71b-15bd-4288-96cd-5726b79a36a2": {
      "main": [
        [
          {
            "node": "1f053b63-9915-4cfd-b70d-ac922a58f0d2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1f053b63-9915-4cfd-b70d-ac922a58f0d2": {
      "main": [
        [
          {
            "node": "2d3d468f-6d2e-4c13-a477-01c4f41bc3d1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1a7a6e6d-94e9-4c5d-bdaf-bba303afba79": {
      "ai_languageModel": [
        [
          {
            "node": "311c5ecd-fa2b-4259-a0ba-cb9f0e8d81f2",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "fb0efd84-b82b-44e9-bb74-59b4fd6e9e3a": {
      "main": [
        [
          {
            "node": "306c8218-4f43-4a77-accf-19d3c64fd5cb",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ea89f057-8d27-4458-bf2c-9e9491c684c2": {
      "ai_outputParser": [
        [
          {
            "node": "311c5ecd-fa2b-4259-a0ba-cb9f0e8d81f2",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "311c5ecd-fa2b-4259-a0ba-cb9f0e8d81f2": {
      "main": [
        [
          {
            "node": "fc7a0147-ac51-4500-aaf5-f233f1af8c7a",
            "type": "main",
            "index": 0
          },
          {
            "node": "070dd56f-5d64-4132-a93c-1344185d985a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2d3d468f-6d2e-4c13-a477-01c4f41bc3d1": {
      "main": [
        [
          {
            "node": "fb0efd84-b82b-44e9-bb74-59b4fd6e9e3a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "306c8218-4f43-4a77-accf-19d3c64fd5cb": {
      "main": [
        [
          {
            "node": "311c5ecd-fa2b-4259-a0ba-cb9f0e8d81f2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}