Sistema automatizado de reactivación de usuarios basado en Supabase, Google Sheets y Gmail

• •Cuenta de Google y credenciales de API de Gmail
• •Pueden requerirse credenciales de autenticación para la API de destino
• •Credenciales de API de Google Sheets

{
  "meta": {
    "instanceId": "b6731f6e46273cffeac6b5188a5b3bcb0840c00108e73b48ae2b799ac6e8e081",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5646092f-6ced-4e85-afb5-550b8e3d24ff",
      "name": "Disparador Programado",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1260,
        -560
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 0 * * *"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "36ad95fb-2057-4f63-a712-1a9e1e2e5dce",
      "name": "Solicitud HTTP",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -780,
        -460
      ],
      "parameters": {
        "url": "=https://vuxcvfezojknoxlxgvhf.supabase.co/rest/v1/stale_users_view?last_sign_in_at=lte.{{ $json.thirtyDaysAgo }}\n",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "supabaseApi"
      },
      "credentials": {
        "supabaseApi": {
          "id": "YrlSoXTufsJxWo88",
          "name": "Supabase account"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "cd1445a8-e6e5-400c-aac9-1f081b7cdbbb",
      "name": "Editar Campos",
      "type": "n8n-nodes-base.set",
      "position": [
        -1000,
        -460
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "90462ba4-317a-4a80-83a9-68d5424cdb8b",
              "name": "thirtyDaysAgo",
              "type": "string",
              "value": "={{ new Date(Date.now() - 30*24*60*6*1000).toISOString().slice(0,10) }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "502c2e3e-c297-4788-b614-906017a79b56",
      "name": "Al hacer clic en 'Ejecutar flujo de trabajo'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        460,
        -580
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "7e42b798-8dc2-4460-8d85-aa4f0107b174",
      "name": "Eliminar Duplicados",
      "type": "n8n-nodes-base.removeDuplicates",
      "position": [
        -560,
        -460
      ],
      "parameters": {
        "compare": "selectedFields",
        "options": {},
        "fieldsToCompare": "email"
      },
      "typeVersion": 2
    },
    {
      "id": "6d344b97-86c4-409e-a9ec-5de067d6f440",
      "name": "Limpiar Hoja",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1040,
        -620
      ],
      "parameters": {
        "operation": "clear",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HcTc5dD05NfCbLjOPH3UBERhrrGX41EpGmW-2HvadtE/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1HcTc5dD05NfCbLjOPH3UBERhrrGX41EpGmW-2HvadtE",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HcTc5dD05NfCbLjOPH3UBERhrrGX41EpGmW-2HvadtE/edit?usp=drivesdk",
          "cachedResultName": "Supabase Stale Leads"
        },
        "keepFirstRow": true,
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "bK1tErzcCo9nt89s",
          "name": "Google Service Account account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "5891472b-e095-4761-b754-39dd84918fcf",
      "name": "Enviar Mensaje",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -320,
        -160
      ],
      "webhookId": "10f8897e-01cb-4f7e-aa04-5018bff6193a",
      "parameters": {
        "sendTo": "={{ $json.Email }}",
        "message": "=<!DOCTYPE html>\\n<html>\\n<head>\\n<meta charset=\"utf-8\">\\n<meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\\n<title>We miss you, {{ $json.Name }}!</title>\\n<style type=\"text/css\">\\n  body {\\n    font-family: Arial, sans-serif;\\n    line-height: 1.6;\\n    color: #333333;\\n    background-color: #f4f4f4;\\n    margin: 0;\\n    padding: 20px;\\n  }\\n  .email-container {\\n    max-width: 600px;\\n    margin: 0 auto;\\n    background-color: #ffffff;\\n    padding: 30px;\\n    border-radius: 8px;\\n    box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);\\n  }\\n  p {\\n    margin-bottom: 15px;\\n  }\\n  a {\\n    color: #007bff;\\n    text-decoration: none;\\n  }\\n  a:hover {\\n    text-decoration: underline;\\n  }\\n  table {\\n    width: 100%;\\n    border-collapse: collapse;\\n    margin-top: 20px;\\n    margin-bottom: 20px;\\n  }\\n  th, td {\\n    padding: 10px;\\n    border: 1px solid #dddddd;\\n    text-align: left;\\n  }\\n  th {\\n    background-color: #f8f8f8;\\n    font-weight: bold;\\n  }\\n  .footer {\\n    margin-top: 30px;\\n    padding-top: 20px;\\n    border-top: 1px solid #eeeeee;\\n    font-size: 14px;\\n    color: #777777;\\n    text-align: center;\\n  }\\n</style>\\n</head>\\n<body>\\n  <div class=\"email-container\">\\n    <p>Hi {{ $json.Name }},</p>\\n    <p>It's been a while since we last saw you on Thalathikka! We noticed you haven't logged in recently and wanted to check in. Our AI resume generator is always evolving to help you create the perfect resume effortlessly.</p>\\n\\n    <p>Here's a quick look at your account details:</p>\\n    <table>\\n      <thead>\\n        <tr>\\n          <th>Name</th>\\n          <th>Email</th>\\n          <th>Last Signed In @</th>\\n        </tr>\\n      </thead>\\n      <tbody>\\n        <tr>\\n          <td>{{ $json.Name }}</td>\\n          <td>{{ $json.Email }}</td>\\n          <td>{{ $json['Last Signed In @'] }}</td>\\n        </tr>\\n      </tbody>\\n    </table>\\n\\n    <p>We'd love to have you back! If you're still looking to land your dream job, Thalathikka is here to help you craft an outstanding resume that gets noticed.</p>\\n    <p>Click here to pick up where you left off: <a href=\"https://thalathikka.com\">https://thalathikka.com</a></p>\\n    <p>If you have any questions or need assistance, feel free to reply to this email or contact us at <a href=\"mailto:support@thalathikka.com\">support@thalathikka.com</a>.</p>\\n\\n    <p>Best regards,</p>\\n    <p>The Thalathikka Team</p>\\n\\n    <div class=\"footer\">\\n      <p>&copy; 2023 Thalathikka. All rights reserved.</p>\\n      <p>Thalathikka is an AI resume generator SaaS.</p>\\n    </div>\\n  </div>\\n</body>\\n</html>",
        "options": {
          "replyTo": "venkibvb5192@gmail.com",
          "appendAttribution": false
        },
        "subject": "=We miss you, {{ $json.Name }}!",
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "bK1tErzcCo9nt89s",
          "name": "Google Service Account account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "22781c7b-258b-4caa-8643-f8d67c3e5a70",
      "name": "Modelo de Chat Google Vertex",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleVertex",
      "position": [
        780,
        -360
      ],
      "parameters": {
        "options": {},
        "modelName": "gemini-2.5-flash",
        "projectId": {
          "__rl": true,
          "mode": "list",
          "value": "slidingpuzzlepro1",
          "cachedResultName": "Slidingpuzzlepro"
        }
      },
      "credentials": {
        "googleApi": {
          "id": "bK1tErzcCo9nt89s",
          "name": "Google Service Account account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "2a0bdef4-0526-42e5-aa0e-4978312a2f92",
      "name": "Actualizar Hoja Google con Leads",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -340,
        -460
      ],
      "parameters": {
        "columns": {
          "value": {
            "Name": "={{ $json.email.split('@')[0].charAt(0).toUpperCase() + $json.email.split('@')[0].slice(1) }}",
            "Email": "={{ $json.email }}",
            "Last Signed In @": "={{ $json.last_sign_in_at }}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Last Signed In @",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Last Signed In @",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Last Signed In @"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HcTc5dD05NfCbLjOPH3UBERhrrGX41EpGmW-2HvadtE/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1HcTc5dD05NfCbLjOPH3UBERhrrGX41EpGmW-2HvadtE",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1HcTc5dD05NfCbLjOPH3UBERhrrGX41EpGmW-2HvadtE/edit?usp=drivesdk",
          "cachedResultName": "Supabase Stale Leads"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "bK1tErzcCo9nt89s",
          "name": "Google Service Account account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "c2e589ec-8bcb-4e0c-a58d-2fa7e98cd7de",
      "name": "Agente de IA",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        780,
        -580
      ],
      "parameters": {
        "text": "Generate ONE friendly HTML email template to re‑engage stale leads from my website.\n\nThe data is aggregated into arrays:\n- {{ $json.Name }}\n- {{ $json.Email }}\n- {{ $json['Last Signed In @'] }}\n\nInclude:\n- An intro paragraph\n- A table listing Name, Email, Last Signed In date using these placeholders\n- A closing message\n\nBrand info:\n- website: https://thalathikka.com\n- supportEmail: support@thalathikka.com\n- brand name: Thalathikka\n- description: Thalathikka is an AI resume generator SaaS\n\n⚠ Do NOT hardcode sample data. Use only placeholders:\n- {{ $json.Name }}\n- {{ $json.Email }}\n- {{ $json['Last Signed In @'] }}\n\nOutput only JSON:\n{\n  \"subject\": \"We miss you, {{ $json.Name }}!\",\n  \"message\": \"<html>...</html> with placeholders\"\n}\n\nDo NOT wrap in triple backticks.  \nDo NOT explain.\n",
        "options": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "executeOnce": true,
      "retryOnFail": false,
      "typeVersion": 2
    },
    {
      "id": "10e1a702-0b83-48f0-ba20-024596856af1",
      "name": "Código",
      "type": "n8n-nodes-base.code",
      "position": [
        1100,
        -580
      ],
      "parameters": {
        "jsCode": "// items is an array; loop & map over them\nreturn items.map(item => {\n  let clean = item.json.output.trim();\n\n  if (clean.startsWith('```json')) {\n    clean = clean.slice(7).trim();\n  } else if (clean.startsWith('```')) {\n    clean = clean.slice(3).trim();\n  }\n\n  if (clean.endsWith('```')) {\n    clean = clean.slice(0, -3).trim();\n  }\n\n  const parsed = JSON.parse(clean);\n\n  // Add parsed fields to item\n  item.json.subject = parsed.subject;\n  item.json.message = parsed.message;\n\n  return item;\n});\n"
      },
      "typeVersion": 2
    },
    {
      "id": "ecea920d-2618-4e02-a861-a437fe7321a3",
      "name": "Analizador de Salida Estructurada",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        960,
        -340
      ],
      "parameters": {
        "schemaType": "manual",
        "inputSchema": "{\n  \"subject\": \"\",\n  \"message\": \"\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "87beb742-874f-4bbc-973f-f43257fd8147",
      "name": "Nota Adhesiva",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        -740
      ],
      "parameters": {
        "width": 1200,
        "height": 800,
        "content": "## 📨  Generate Email Description for Stale Leads\n\nThis workflow automatically creates a friendly **HTML email template** to re‑engage stale leads.\n\n**What it does:**\n\n* Uses AI to generate:\n\n  * Email subject line\n  * HTML email body with:\n\n    * Intro paragraph\n    * Dynamic table listing:\n\n      * `{{ $json.Name }}`\n      * `{{ $json.Email }}`\n      * `{{ $json['Last Signed In @'] }}`\n    * Closing message\n* Cleans AI output & parses JSON fields (`subject` & `message`)\n* Final email content is ready to send dynamically to each lead\n\n⚠ Placeholders are kept dynamic — no hardcoded data.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "aecd7af3-bec2-4ea4-b2ff-9f6ca3d3ba76",
      "name": "Nota Adhesiva1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1900,
        -740
      ],
      "parameters": {
        "color": 4,
        "width": 1900,
        "height": 800,
        "content": "## 🛠 Automated Stale Users Re‑engagement Workflow\n\nThis workflow runs **daily** to fetch and re‑engage users who haven’t logged in for over 30 days.\n\n### ✅ Steps:\n\n* Fetch stale users from **PostgreSQL** (via Supabase view)\n* Remove duplicate emails to keep data clean\n* Clear old data in **Google Sheets**\n* Append fresh lead data to the Google Sheet\n* Send **personalized HTML emails** to each user using Gmail\n\n### 📌 Data included per user:\n\n* Name (parsed from email)\n* Email\n* Last Signed In date\n\n### ✏ Email content:\n\nFriendly branded HTML email reminding the user about:\n\n* Thalathikka's AI resume generator\n* Quick account details table\n* Call‑to‑action link back to the site\n\n⚙ Runs automatically every night at midnight.\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {
    "When clicking ‘Execute workflow’": [
      {}
    ]
  },
  "connections": {
    "10e1a702-0b83-48f0-ba20-024596856af1": {
      "main": [
        []
      ]
    },
    "c2e589ec-8bcb-4e0c-a58d-2fa7e98cd7de": {
      "main": [
        [
          {
            "node": "10e1a702-0b83-48f0-ba20-024596856af1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6d344b97-86c4-409e-a9ec-5de067d6f440": {
      "main": [
        []
      ]
    },
    "cd1445a8-e6e5-400c-aac9-1f081b7cdbbb": {
      "main": [
        [
          {
            "node": "36ad95fb-2057-4f63-a712-1a9e1e2e5dce",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "36ad95fb-2057-4f63-a712-1a9e1e2e5dce": {
      "main": [
        [
          {
            "node": "7e42b798-8dc2-4460-8d85-aa4f0107b174",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5646092f-6ced-4e85-afb5-550b8e3d24ff": {
      "main": [
        [
          {
            "node": "6d344b97-86c4-409e-a9ec-5de067d6f440",
            "type": "main",
            "index": 0
          },
          {
            "node": "cd1445a8-e6e5-400c-aac9-1f081b7cdbbb",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7e42b798-8dc2-4460-8d85-aa4f0107b174": {
      "main": [
        [
          {
            "node": "2a0bdef4-0526-42e5-aa0e-4978312a2f92",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "22781c7b-258b-4caa-8643-f8d67c3e5a70": {
      "ai_languageModel": [
        [
          {
            "node": "c2e589ec-8bcb-4e0c-a58d-2fa7e98cd7de",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "ecea920d-2618-4e02-a861-a437fe7321a3": {
      "ai_outputParser": [
        [
          {
            "node": "c2e589ec-8bcb-4e0c-a58d-2fa7e98cd7de",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "2a0bdef4-0526-42e5-aa0e-4978312a2f92": {
      "main": [
        [
          {
            "node": "5891472b-e095-4761-b754-39dd84918fcf",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "502c2e3e-c297-4788-b614-906017a79b56": {
      "main": [
        [
          {
            "node": "c2e589ec-8bcb-4e0c-a58d-2fa7e98cd7de",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}