Système automatisé de réactivation des utilisateurs basé sur Supabase, Google Sheets et Gmail

• •Compte Google et informations d'identification Gmail API
• •Peut nécessiter les informations d'identification d'authentification de l'API cible
• •Informations d'identification Google Sheets API

{
  "meta": {
    "instanceId": "b6731f6e46273cffeac6b5188a5b3bcb0840c00108e73b48ae2b799ac6e8e081",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "5646092f-6ced-4e85-afb5-550b8e3d24ff",
      "name": "Déclencheur programmé",
      "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": "Requête 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": "Modifier les champs",
      "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": "Lors du clic sur 'Exécuter le workflow'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        460,
        -580
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "7e42b798-8dc2-4460-8d85-aa4f0107b174",
      "name": "Supprimer les doublons",
      "type": "n8n-nodes-base.removeDuplicates",
      "position": [
        -560,
        -460
      ],
      "parameters": {
        "compare": "selectedFields",
        "options": {},
        "fieldsToCompare": "email"
      },
      "typeVersion": 2
    },
    {
      "id": "6d344b97-86c4-409e-a9ec-5de067d6f440",
      "name": "Vider la feuille",
      "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": "Envoyer un message",
      "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": "Modèle 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": "Mettre à jour la feuille Google avec les prospects",
      "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": "Agent 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": "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": "Analyseur de sortie structurée",
      "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": "Note autocollante",
      "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": "Note autocollante 1",
      "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
          }
        ]
      ]
    }
  }
}