CRM取引フェーズの更新

• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "id": "kxb2ryCoMzkhmn1Q",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "CRM Deal Stage Update",
  "tags": [],
  "nodes": [
    {
      "id": "e3e73b68-7446-46bc-a841-f57e5c21021d",
      "name": "⏰ 日次トリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        96,
        896
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "6d75184f-e2b1-43d1-9212-def76a8f144c",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -352,
        784
      ],
      "parameters": {
        "width": 398,
        "height": 464,
        "content": "## 🚀 Automation Start\n\n**Purpose:** This automation runs on a schedule to sync paid Stripe invoices with your CRM system.\n\n**What it does:**\n1. Fetches all paid invoices from Stripe\n2. Looks up customer details in Google Sheets\n3. Updates deal status to \"Closed\" for paid customers\n4. Syncs back to your CRM tracking sheet\n\n**💡 Tip:** Adjust the schedule interval based on how often you want to sync data."
      },
      "typeVersion": 1
    },
    {
      "id": "48817707-e147-4d4c-ad97-4f1a1c4a3234",
      "name": "🔍 CRMシートで顧客を検索",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        816,
        896
      ],
      "parameters": {
        "range": "A:C",
        "options": {},
        "sheetId": "1i0_xNab1dLKkIJ72DLfa1PXvR9VH4d8IssbsphhAAQk",
        "operation": "lookup",
        "lookupValue": "={{ $json.data.customer_email }}",
        "lookupColumn": "Stripe Email",
        "authentication": "oAuth2"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "a82686ae-13cd-41bc-a862-de5644646cc2",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        640,
        512
      ],
      "parameters": {
        "width": 358,
        "height": 364,
        "content": "## 🔍 Customer Lookup\n\n**What happens here:**\n- Takes each paid invoice email\n- Searches for matching customer in Google Sheets\n- Returns CRM details like HubSpot/Pipedrive Deal IDs\n\n**Sheet Structure Expected:**\n- Column A: Stripe Email\n- Column B: HubSpot Deal ID  \n- Column C: Pipedrive Deal ID\n\n**⚠️ Important:** Make sure your sheet has these exact column headers!"
      },
      "typeVersion": 1
    },
    {
      "id": "d66985e5-229b-4a76-a60e-1a2098d0a5da",
      "name": "💳 Stripeから支払済み請求書を取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        336,
        896
      ],
      "parameters": {
        "url": "https://api.stripe.com/v1/invoices",
        "options": {},
        "sendQuery": true,
        "authentication": "predefinedCredentialType",
        "queryParameters": {
          "parameters": [
            {
              "name": "status",
              "value": "paid"
            }
          ]
        },
        "nodeCredentialType": "stripeApi"
      },
      "credentials": {
        "stripeApi": {
          "id": "DV4tPpxjbOUkGfAx",
          "name": "Stripe account"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "7ffbdab4-3601-40b9-8db2-93d5f802b0ae",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        176,
        544
      ],
      "parameters": {
        "width": 318,
        "height": 320,
        "content": "## 💳 Stripe API Call\n\n**Purpose:** Fetches all invoices with \"paid\" status from Stripe\n\n**API Endpoint:** /v1/invoices?status=paid\n\n**Returns:** List of paid invoices with customer details\n\n**🔧 Setup Required:**\n- Add your Stripe API credentials\n- Ensure API key has read permissions for invoices"
      },
      "typeVersion": 1
    },
    {
      "id": "5f726010-2a52-4034-9aaf-8c2a788feb93",
      "name": "📋 請求書リストを分割",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        576,
        896
      ],
      "parameters": {
        "include": "allOtherFields",
        "options": {},
        "fieldToSplitOut": "data"
      },
      "typeVersion": 1
    },
    {
      "id": "6a606041-a880-46aa-89b9-a739c5ec052e",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        480,
        1088
      ],
      "parameters": {
        "width": 288,
        "height": 296,
        "content": "## 📋 Split Array Data\n\n**Purpose:** Converts the array of invoices into individual items\n\n**Why needed:** Stripe returns invoices as an array in the 'data' field. This node separates each invoice so we can process them individually.\n\n**Result:** Each paid invoice becomes a separate workflow item that can be processed in the next steps."
      },
      "typeVersion": 1
    },
    {
      "id": "d7b8f723-5935-40a9-9e66-ca6b274ddff8",
      "name": "🧹 データをクリーンアップしてクローズ済みとしてマーク",
      "type": "n8n-nodes-base.code",
      "position": [
        1024,
        896
      ],
      "parameters": {
        "jsCode": "return items\n  .filter((item, index, self) => {\n    return (\n      Object.keys(item.json).length > 0 &&\n      index === self.findIndex(t => t.json[\"Stripe Email\"] === item.json[\"Stripe Email\"])\n    );\n  })\n  .map(item => {\n    return {\n      json: {\n        ...item.json,\n        Deal: \"Closed\"\n      }\n    };\n  });\n"
      },
      "typeVersion": 2
    },
    {
      "id": "ef040dd8-67d2-4bfb-82aa-7d30289e3682",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        1104
      ],
      "parameters": {
        "width": 358,
        "height": 424,
        "content": "## 🧹 Data Processing Logic\n\n**What this code does:**\n1. **Removes duplicates** - If same email appears multiple times, keeps only one\n2. **Filters empty records** - Removes any items without data\n3. **Sets deal status** - Adds 'Deal: Closed' to each record\n\n**Why needed:** \n- Prevents duplicate updates in your CRM\n- Ensures only valid records are processed\n- Automatically marks deals as won/closed since payment was received\n\n**💡 Customization:** Change 'Closed' to match your CRM's deal stage naming (e.g., 'Won', 'Closed-Won', etc.)"
      },
      "typeVersion": 1
    },
    {
      "id": "c7bd8e45-0fd1-4d66-9ce6-25a9a858b267",
      "name": "✅ クローズ済み取引でCRMシートを更新",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1232,
        896
      ],
      "parameters": {
        "columns": {
          "value": {
            "Deal": "={{ $json.Deal }}",
            "Stripe Email": "={{ $json[\"Stripe Email\"] }}",
            "HubSpot Deal ID": "={{ $json[\"HubSpot Deal ID\"] }}",
            "Pipedrive Deal ID": "={{ $json[\"Pipedrive Deal ID\"] }}"
          },
          "schema": [
            {
              "id": "Stripe Email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Stripe Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "HubSpot Deal ID",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "HubSpot Deal ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Pipedrive Deal ID",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Pipedrive Deal ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Deal",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Deal",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Stripe Email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1552459854,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1i0_xNab1dLKkIJ72DLfa1PXvR9VH4d8IssbsphhAAQk/edit#gid=1552459854",
          "cachedResultName": "CRM"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1i0_xNab1dLKkIJ72DLfa1PXvR9VH4d8IssbsphhAAQk",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1i0_xNab1dLKkIJ72DLfa1PXvR9VH4d8IssbsphhAAQk/edit?usp=drivesdk",
          "cachedResultName": "Stripe Invoice store"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "59518fac-7bf3-4cad-9dc4-75dfe277b3e4",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1104,
        448
      ],
      "parameters": {
        "width": 378,
        "height": 424,
        "content": "## ✅ Final Update Step\n\n**Action:** Updates the Google Sheet with closed deal status\n\n**How it works:**\n- **Matches by:** Stripe Email (finds existing rows)\n- **Updates:** Deal status column to 'Closed'\n- **Preserves:** All existing CRM IDs and data\n\n**Column Mapping:**\n- Stripe Email → Stripe Email\n- HubSpot Deal ID → HubSpot Deal ID  \n- Pipedrive Deal ID → Pipedrive Deal ID\n- Deal Status → Deal (set to 'Closed')\n\n**🎯 Result:** Your CRM tracking sheet now shows which deals are closed based on Stripe payments!"
      },
      "typeVersion": 1
    },
    {
      "id": "8d610b5a-0db9-41e9-8380-2719f72e4572",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1552,
        768
      ],
      "parameters": {
        "width": 398,
        "height": 440,
        "content": "## 🎯 Automation Complete!\n\n**What was accomplished:**\n✅ Fetched all paid Stripe invoices\n✅ Found matching customers in your CRM sheet\n✅ Removed duplicates and cleaned data\n✅ Updated deal status to 'Closed'\n✅ Synced everything back to Google Sheets\n\n**Next Steps:**\n1. Set up your preferred schedule (daily, hourly, etc.)\n2. Test with a few sample records first\n3. Monitor the execution logs for any errors\n4. Customize deal status naming if needed\n\n**🔧 Troubleshooting:**\n- Check Stripe API credentials\n- Verify Google Sheets permissions\n- Ensure sheet column headers match exactly"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "72d572b1-bc10-442e-a0df-09a43c88cb7e",
  "connections": {
    "e3e73b68-7446-46bc-a841-f57e5c21021d": {
      "main": [
        [
          {
            "node": "d66985e5-229b-4a76-a60e-1a2098d0a5da",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5f726010-2a52-4034-9aaf-8c2a788feb93": {
      "main": [
        [
          {
            "node": "48817707-e147-4d4c-ad97-4f1a1c4a3234",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "48817707-e147-4d4c-ad97-4f1a1c4a3234": {
      "main": [
        [
          {
            "node": "d7b8f723-5935-40a9-9e66-ca6b274ddff8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d7b8f723-5935-40a9-9e66-ca6b274ddff8": {
      "main": [
        [
          {
            "node": "c7bd8e45-0fd1-4d66-9ce6-25a9a858b267",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d66985e5-229b-4a76-a60e-1a2098d0a5da": {
      "main": [
        [
          {
            "node": "5f726010-2a52-4034-9aaf-8c2a788feb93",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}