Lead-Pflege-Churn-Detektor

• •Google-Konto + Gmail API-Anmeldedaten
• •Slack Bot Token oder Webhook URL
• •Google Sheets API-Anmeldedaten

{
  "id": "HQsEQI9dgnQXbgkF",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Lead Nurture Drop-off Detector",
  "tags": [],
  "nodes": [
    {
      "id": "5f8b2f26-9f5d-40be-baad-51f65e9eb357",
      "name": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -832,
        -16
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "c014b22a-fc48-4f32-94e2-72e7ce3cf3f9",
      "name": "Notiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -656,
        -400
      ],
      "parameters": {
        "height": 352,
        "content": "## Node Name: Fetch Contacts from HighLevel\n**Action**: Retrieves all contacts from the HighLevel CRM.\n**Description**:\nThis node connects to the HighLevel API using OAuth credentials and fetches all contacts. It pulls data such as contact names, email addresses, and other related metadata to be processed in subsequent steps.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2fa119c2-8252-480e-b7b6-964e04100266",
      "name": "Notiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -384,
        176
      ],
      "parameters": {
        "height": 272,
        "content": "## Node Name: Validate Deal Fetch Success\n**Action**: Validates if the deal fetch was successful or not.\n**Description**:\nThis node checks whether the fetched data (deal or contact) exists. If the data is invalid or missing, it logs an error or redirects to error handling.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7c00102b-0b79-498a-b8ec-e1b9f2488392",
      "name": "Notiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -48,
        608
      ],
      "parameters": {
        "height": 336,
        "content": "## Node Name: Log Errors in Google Sheets\n**Action**: Logs any errors encountered during data fetching or validation.\n**Description**:\nThis node logs any errors or issues that occur during the fetching or validation steps into a Google Sheet for auditing and future reference. It ensures that all issues are recorded for troubleshooting.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "65e796c2-8e01-4009-b900-c523a669657a",
      "name": "Notiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -48,
        -400
      ],
      "parameters": {
        "height": 352,
        "content": "## Node Name: Filter Contacts with No Response\n**Action**: Filters contacts who have not responded in the last 24 hours.\n**Description**:\nThis node processes the input contacts and filters out those who have not replied to recent communications within the last 24 hours. It calculates the time difference and selects only contacts who have been inactive for a specified period.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "d1f3ba19-cfb4-4845-b628-cff0f4238c65",
      "name": "Notiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        224,
        144
      ],
      "parameters": {
        "height": 336,
        "content": "## Node Name: Get Most Recent Contact\n**Action**: Retrieves the most recent contact based on the last updated date.\n**Description**:\nThis node sorts the contacts by their most recent update and selects the one with the latest activity. It also calculates the number of hours since the last update and formats it for easy communication.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "723f4c58-76e0-4cf2-ae04-88c3820e7d15",
      "name": "Notiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        464,
        -352
      ],
      "parameters": {
        "height": 320,
        "content": "## Node Name: Send Follow-Up Email to Contact\n**Action**: Sends a follow-up email to a contact.\n**Description**:\nThis node sends a personalized follow-up email to the selected contact. The email asks for confirmation and offers help in case the contact has any further questions or concerns.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "8ee4227f-f4b3-4bcb-a154-cf316050baef",
      "name": "Notiz6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        720,
        160
      ],
      "parameters": {
        "height": 304,
        "content": "## Node Name: Wait for 24 Hours Before Next Action\n**Action**: Pauses the workflow for 24 hours before continuing.\n**Description**:\nThis node introduces a 24-hour delay in the workflow. It is typically used when waiting for a response or allowing some time before the next step in the automation process is triggered.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "f1211abc-1883-4c50-b75c-0a835a549ae5",
      "name": "Notiz7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        912,
        -432
      ],
      "parameters": {
        "height": 368,
        "content": "## Node Name: Retrieve Email Thread for Response\n**Action**: Fetches the email thread based on the provided thread ID.\n**Description**:\nThis node retrieves the most recent email thread from Gmail using the thread ID. It's used for checking if the contact has responded and fetching the necessary email data for processing.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "796c98f8-270a-46e7-8f95-4b35ebce2a4d",
      "name": "Notiz8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1152,
        144
      ],
      "parameters": {
        "height": 384,
        "content": "## Node Name: Check If Contact Responded with 'Yes'\n**Action**: Evaluates if the email response contains the word \"yes\".\n**Description**:\nThis node checks the email response content for the keyword \"yes\". If found, it signals that the contact has replied positively, triggering the appropriate follow-up action.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "01d082d9-9575-4287-af57-ec6f7b85e5e0",
      "name": "Notiz9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1456,
        304
      ],
      "parameters": {
        "height": 400,
        "content": "## Node Name: Notify Sales Team in Slack if No Response\n**Action**: Sends a notification to Slack if no response has been received from the contact.\n**Description**:\nIf no response has been received from the contact, this node notifies the sales team via Slack. It includes relevant details such as the contact's email and follow-up status, so the team can take the next steps.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b7874a2b-ce74-4492-8623-37d0689dfbf8",
      "name": "Notiz10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1440,
        -640
      ],
      "parameters": {
        "height": 384,
        "content": "## Node Name: Notify Sales Team in Slack if Response Received\n**Action**: Sends a notification to Slack if a response is received from the contact.\n**Description**:\nIf the contact responds positively (i.e., the email contains \"yes\"), this node notifies the sales team in Slack, highlighting the response and providing further details for the next action.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "9e35ac7e-8c3c-4821-bbba-a5b5fbf3f8c8",
      "name": "Kontakte von HighLevel abrufen",
      "type": "n8n-nodes-base.highLevel",
      "position": [
        -576,
        -16
      ],
      "parameters": {
        "filters": {},
        "options": {},
        "operation": "getAll",
        "requestOptions": {}
      },
      "credentials": {
        "highLevelOAuth2Api": {
          "id": "5QWHSi134dLIBEsC",
          "name": "HighLevel account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "7fdab81f-d78c-4ed3-887b-d28092afde3a",
      "name": "Deal-Abruferfolg validieren",
      "type": "n8n-nodes-base.if",
      "position": [
        -304,
        -16
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "e2adb005-2b3c-4d1e-8445-442df1fe925a",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.id }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "8ef5a95b-518d-42e9-9cf1-d0f84232f8e0",
      "name": "Fehler in Google Tabellen protokollieren",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        16,
        416
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "error_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "error_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "error",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "error",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "error_id"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1338537721,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit#gid=1338537721",
          "cachedResultName": "error log sheet"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1Uldk_4BxWbdZTDZxFUeohIfeBmGHHqVEl9Ogb0l6R8Y/edit?usp=drivesdk",
          "cachedResultName": "Interviewer Brief Pack "
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "kpPEOLCGn963qpoh",
          "name": "automations@techdome.ai"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "6285af42-d462-460d-90dc-853899d8486d",
      "name": "Kontakte ohne Antwort filtern",
      "type": "n8n-nodes-base.code",
      "position": [
        32,
        -16
      ],
      "parameters": {
        "jsCode": "// Get all contacts from the input\nconst contacts = $input.all().map(item => item.json);\n\n// Get the current date and time\nconst now = new Date();\n\n// Filter contacts with no response in the last 24 hours\nconst contactsWithNoResponse = contacts.filter(contact => {\n  const lastResponseTime = new Date(contact.dateUpdated);\n\n  // Calculate the time difference in hours\n  const timeDiff = now - lastResponseTime; \n  const timeDiffInHours = timeDiff / (1000 * 60 * 60);\n\n  // Check if no response in the last 24 hours\n  return timeDiffInHours >= 24; \n});\n\n// Return the filtered contacts as separate items\nreturn contactsWithNoResponse.map(contact => ({\n  json: contact\n}));"
      },
      "typeVersion": 2
    },
    {
      "id": "971be9a4-415a-4fca-8a70-ed7d528f4d72",
      "name": "Letzten Kontakt ermitteln",
      "type": "n8n-nodes-base.code",
      "position": [
        288,
        -16
      ],
      "parameters": {
        "jsCode": "// Get all contacts from the input\nconst contacts = $input.all().map(item => item.json);\n\n// Sort contacts by dateUpdated (most recent first)\nconst sortedContacts = contacts.sort((a, b) => {\n  return new Date(b.dateUpdated) - new Date(a.dateUpdated);\n});\n\n// Get the most recent contact\nconst mostRecentContact = sortedContacts[0];\n\n// Calculate hours ago\nconst now = new Date();\nconst lastUpdated = new Date(mostRecentContact.dateUpdated);\nconst timeDiff = now - lastUpdated;\nconst hoursAgo = Math.floor(timeDiff / (1000 * 60 * 60));\n\n// Format the output\nconst formattedOutput = `${mostRecentContact.contactName} (${mostRecentContact.email || 'no email'}) - \\`dateUpdated: \"${mostRecentContact.dateUpdated}\"\\`\n* That's ~${hoursAgo} hours ago ✅`;\n\n// Return the formatted output\nreturn [\n  {\n    json: {\n      mostRecentContact: mostRecentContact,\n      formattedOutput: formattedOutput,\n      hoursAgo: hoursAgo,\n      contactName: mostRecentContact.contactName,\n      email: mostRecentContact.email,\n      dateUpdated: mostRecentContact.dateUpdated\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "e70e4622-1756-4e65-a5a2-13ba657be75f",
      "name": "Follow-up-E-Mail an Kontakt senden",
      "type": "n8n-nodes-base.gmail",
      "position": [
        544,
        -16
      ],
      "webhookId": "0c82c299-6938-42ed-bda6-5007d79af34f",
      "parameters": {
        "sendTo": "=newscctv22@gmail.com",
        "message": "=<p>Hi {{ $json.firstName || $json.contactName }},</p>\n\n<p>I hope this email finds you well!</p>\n\n<p>I wanted to follow up with you as I noticed we haven't heard back from you in the last {{ $json.hoursAgo }} hours (since {{ $json.dateUpdated }}).</p>\n\n<p>Just checking in to see if you had any questions or if there's anything I can help you with?</p>\n\n<p>Looking forward to hearing from you! Replay to \" yes \" to confirm your Availability</p>\n\n<p>Best regards,<br>\nYour Team</p>",
        "options": {},
        "subject": "=Just Checking In - {{ $json.contactName }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "a41f8e94-b84f-45e4-a968-f3f865b0ec09",
      "name": "24 Stunden bis zur nächsten Aktion warten",
      "type": "n8n-nodes-base.wait",
      "position": [
        784,
        -16
      ],
      "webhookId": "7475fece-4562-46b3-a702-7a5c5fe88266",
      "parameters": {
        "amount": 42
      },
      "typeVersion": 1
    },
    {
      "id": "cfe934cb-4747-4162-a4c0-e130d013e172",
      "name": "E-Mail-Thread für Antwort abrufen",
      "type": "n8n-nodes-base.gmail",
      "position": [
        992,
        -16
      ],
      "webhookId": "ffb3a844-8c8b-4f17-a6e9-98e0371f4214",
      "parameters": {
        "options": {},
        "resource": "thread",
        "threadId": "={{ $json.threadId }}",
        "operation": "get"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "RchiXdmY8WaQhOSJ",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "f9c94301-54c7-40e8-b5c0-75146fa7dab1",
      "name": "Prüfen, ob Kontakt mit 'Ja' geantwortet hat",
      "type": "n8n-nodes-base.if",
      "position": [
        1200,
        -16
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "4a6d1310-29be-4559-8bea-afe8fe0f0f30",
              "operator": {
                "type": "string",
                "operation": "contains"
              },
              "leftValue": "={{ $json.messages[1].snippet }}",
              "rightValue": "yes"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "d254c041-3f7e-4c12-9df5-7a996c97f938",
      "name": "Vertriebsteam in Slack bei fehlender Antwort benachrichtigen",
      "type": "n8n-nodes-base.slack",
      "position": [
        1520,
        128
      ],
      "webhookId": "ddaa7632-9e35-4bd3-82d6-572d5cae84cc",
      "parameters": {
        "text": "=⚠️ *No Response Yet - Follow-up Sent*\n\n*Contact:* {{ $json.messages[0].Subject.replace('Just Checking In - ', '') }}\n*Email:* {{ $json.messages[0].To }}\n\n*Follow-up Sent:* {{ new Date($json.messages[0].internalDate).toLocaleString() }}\n*Message:* {{ $json.messages[0].snippet }}\n\n⏳ Waiting for response... Consider next follow-up action.",
        "user": {
          "__rl": true,
          "mode": "list",
          "value": "U09HMPVD466",
          "cachedResultName": "newscctv22"
        },
        "select": "user",
        "otherOptions": {}
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account vivek"
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "a1659ee2-859a-4540-9470-3d0b5a464dbe",
      "name": "Vertriebsteam in Slack bei erhaltene Antwort benachrichtigen",
      "type": "n8n-nodes-base.slack",
      "position": [
        1520,
        -224
      ],
      "webhookId": "ddaa7632-9e35-4bd3-82d6-572d5cae84cc",
      "parameters": {
        "text": "=🎉 *Great News! We Got a Response!*\n\n*Contact:* {{ $json.messages[0].Subject.replace('Just Checking In - ', '') }}\n*Email:* {{ $json.messages[0].To }}\n\n*Their Response:*\n> {{ $json.messages[1].snippet }}\n✅ Follow-up was successful! Time to take next action.",
        "user": {
          "__rl": true,
          "mode": "list",
          "value": "U09HMPVD466",
          "cachedResultName": "newscctv22"
        },
        "select": "user",
        "otherOptions": {}
      },
      "credentials": {
        "slackApi": {
          "id": "rNqvWj9TfChPVRYY",
          "name": "Slack account vivek"
        }
      },
      "typeVersion": 2.3
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "6b99bb1c-a693-4c09-abd4-9bd98bb6cb3e",
  "connections": {
    "971be9a4-415a-4fca-8a70-ed7d528f4d72": {
      "main": [
        [
          {
            "node": "e70e4622-1756-4e65-a5a2-13ba657be75f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7fdab81f-d78c-4ed3-887b-d28092afde3a": {
      "main": [
        [
          {
            "node": "6285af42-d462-460d-90dc-853899d8486d",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "8ef5a95b-518d-42e9-9cf1-d0f84232f8e0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9e35ac7e-8c3c-4821-bbba-a5b5fbf3f8c8": {
      "main": [
        [
          {
            "node": "7fdab81f-d78c-4ed3-887b-d28092afde3a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e70e4622-1756-4e65-a5a2-13ba657be75f": {
      "main": [
        [
          {
            "node": "a41f8e94-b84f-45e4-a968-f3f865b0ec09",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6285af42-d462-460d-90dc-853899d8486d": {
      "main": [
        [
          {
            "node": "971be9a4-415a-4fca-8a70-ed7d528f4d72",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cfe934cb-4747-4162-a4c0-e130d013e172": {
      "main": [
        [
          {
            "node": "f9c94301-54c7-40e8-b5c0-75146fa7dab1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a41f8e94-b84f-45e4-a968-f3f865b0ec09": {
      "main": [
        [
          {
            "node": "cfe934cb-4747-4162-a4c0-e130d013e172",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5f8b2f26-9f5d-40be-baad-51f65e9eb357": {
      "main": [
        [
          {
            "node": "9e35ac7e-8c3c-4821-bbba-a5b5fbf3f8c8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f9c94301-54c7-40e8-b5c0-75146fa7dab1": {
      "main": [
        [
          {
            "node": "a1659ee2-859a-4540-9470-3d0b5a464dbe",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "d254c041-3f7e-4c12-9df5-7a996c97f938",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}