Automatisiertes Reaktivierungssystem für Benutzer basierend auf Supabase, Google Tabellen und Gmail

• •Google-Konto + Gmail API-Anmeldedaten
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten

{
  "meta": {
    "instanceId": "b6731f6e46273cffeac6b5188a5b3bcb0840c00108e73b48ae2b799ac6e8e081",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5646092f-6ced-4e85-afb5-550b8e3d24ff",
      "name": "Zeitplan-Trigger",
      "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": "HTTP Request",
      "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": "Felder bearbeiten",
      "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": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        460,
        -580
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "7e42b798-8dc2-4460-8d85-aa4f0107b174",
      "name": "Duplikate entfernen",
      "type": "n8n-nodes-base.removeDuplicates",
      "position": [
        -560,
        -460
      ],
      "parameters": {
        "compare": "selectedFields",
        "options": {},
        "fieldsToCompare": "email"
      },
      "typeVersion": 2
    },
    {
      "id": "6d344b97-86c4-409e-a9ec-5de067d6f440",
      "name": "Tabelle leeren",
      "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": "Nachricht senden",
      "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": "Google Vertex Chat Model",
      "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": "Google Sheet mit Leads aktualisieren",
      "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": "KI-Agent",
      "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": "Code",
      "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": "Structured Output Parser",
      "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": "Kurznotiz",
      "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": "Kurznotiz1",
      "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
          }
        ]
      ]
    }
  }
}