Detector de pérdida de en el cultivo de leads

• •Cuenta de Google y credenciales de API de Gmail
• •Bot Token de Slack o URL de Webhook
• •Credenciales de API de Google Sheets

{
  "id": "HQsEQI9dgnQXbgkF",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Lead Nurture Drop-off Detector",
  "tags": [],
  "nodes": [
    {
      "id": "5f8b2f26-9f5d-40be-baad-51f65e9eb357",
      "name": "Al hacer clic en 'Ejecutar flujo de trabajo'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -832,
        -16
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "c014b22a-fc48-4f32-94e2-72e7ce3cf3f9",
      "name": "Nota adhesiva",
      "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": "Nota adhesiva1",
      "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": "Nota adhesiva2",
      "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": "Nota adhesiva3",
      "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": "Nota adhesiva4",
      "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": "Nota adhesiva5",
      "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": "Nota adhesiva6",
      "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": "Nota adhesiva7",
      "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": "Nota adhesiva8",
      "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": "Nota adhesiva9",
      "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": "Nota adhesiva10",
      "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": "Obtener contactos de HighLevel",
      "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": "Validar éxito en la obtención del acuerdo",
      "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": "Registrar errores en Google Sheets",
      "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": "Filtrar contactos sin respuesta",
      "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": "Obtener contacto más reciente",
      "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": "Enviar correo de seguimiento al contacto",
      "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": "Esperar 24 horas antes de la siguiente acción",
      "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": "Recuperar hilo de correo para respuesta",
      "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": "Verificar si el contacto respondió con 'Sí'",
      "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": "Notificar al equipo de ventas en Slack si no hay respuesta",
      "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": "Notificar al equipo de ventas en Slack si se recibió respuesta",
      "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
          }
        ]
      ]
    }
  }
}