Système d'automatisation marketing (eCRM) - NocoDB avec Brevo

• •Aucun prérequis spécial, prêt à l'emploi après importation

{
  "id": "ZwLBpKMfyctFZtlY",
  "meta": {
    "instanceId": "c24388df44432e8ff2c4acecd7ab0dd2faec628bd83c70beb384cea105f7a50a",
    "templateCredsSetupCompleted": true
  },
  "name": "Marketing Automation System (eCRM) with NocoDB & Brevo",
  "tags": [
    {
      "id": "KQB0C2uaojMEQFs4",
      "name": "BASE",
      "createdAt": "2025-05-17T11:59:50.083Z",
      "updatedAt": "2025-05-17T11:59:50.083Z"
    },
    {
      "id": "Me7M2CkKWNG68tiO",
      "name": "MA-FLOW",
      "createdAt": "2025-05-17T12:01:12.861Z",
      "updatedAt": "2025-05-17T12:01:12.861Z"
    }
  ],
  "nodes": [
    {
      "id": "56476cf7-b1d2-43d7-9b68-80dcc9d580e0",
      "name": "Déclencheur Planifié",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -2240,
        620
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 10
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "06ba0ef4-7b4a-4251-b370-320e71d02037",
      "name": "Configurer le Flux",
      "type": "n8n-nodes-base.set",
      "position": [
        -2000,
        620
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "d309aa70-6566-4caf-b195-343a439005a8",
              "name": "flow_id",
              "type": "number",
              "value": 1
            }
          ]
        }
      },
      "retryOnFail": false,
      "typeVersion": 3.4
    },
    {
      "id": "fb8730fc-f2c0-4426-9781-d865ec0c773b",
      "name": "Mapper les Données",
      "type": "n8n-nodes-base.set",
      "position": [
        -2180,
        920
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "a859ab5d-72db-499b-916f-c86056d2e8e5",
              "name": "user_id",
              "type": "string",
              "value": "={{ $json.user_id }}"
            },
            {
              "id": "a08c6a8f-d449-419c-8137-97ff7da25eb9",
              "name": "question_id",
              "type": "string",
              "value": "={{ $('Filter Template').item.json.question_id }}"
            },
            {
              "id": "68feb703-6295-4181-a9d4-f30891a5ea1d",
              "name": "type",
              "type": "string",
              "value": "={{ $('Filter Template').item.json.type }}"
            },
            {
              "id": "82eaab08-abd6-45a2-a1f0-a1f4388064c1",
              "name": "flow_name",
              "type": "string",
              "value": "={{ $('Filter Template').item.json.flow_name }}"
            },
            {
              "id": "c598ef15-d75d-48d6-aa95-9ca9d951293c",
              "name": "type_template_id",
              "type": "number",
              "value": "={{ $('Filter Template').item.json.type_template_id }}"
            },
            {
              "id": "82a53708-84f8-4ef1-a97a-421ca0926f38",
              "name": "flow_id",
              "type": "string",
              "value": "={{ $('Filter Template').item.json.Id }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "5515a0dc-6ef7-4325-a48c-067663d9d531",
      "name": "Filtrer le Modèle",
      "type": "n8n-nodes-base.filter",
      "position": [
        -1600,
        620
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "0ec67f9e-14e1-4581-be9f-1cccf1e07c89",
              "operator": {
                "type": "number",
                "operation": "equals"
              },
              "leftValue": "={{ $('Setup Flow').item.json.flow_id }}",
              "rightValue": "={{ $json.Id }}"
            }
          ]
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "d79c4618-ea24-4140-b797-88e81ee810dc",
      "name": "SI Paramètres du Modèle OK",
      "type": "n8n-nodes-base.if",
      "position": [
        -1380,
        620
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "cb609e58-4b53-45ce-b8b7-878bb66ec21c",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.flow_name }}",
              "rightValue": ""
            },
            {
              "id": "bb59523e-94fb-4db1-9218-5cd1bd95f644",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.question_id }}",
              "rightValue": ""
            },
            {
              "id": "d7d05f35-503a-4211-a343-021462f8a798",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.type }}",
              "rightValue": ""
            },
            {
              "id": "71520d2f-c23f-424b-a6fd-7d84a492d499",
              "operator": {
                "type": "number",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.type_template_id }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "06ee76ce-47aa-4cdc-9d24-6440d7da2508",
      "name": "SI user_id n'est pas vide",
      "type": "n8n-nodes-base.if",
      "position": [
        -940,
        700
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "3807b703-6ebf-46a7-877c-bf7b10c23da8",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.user_id }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "28db049c-fb73-4379-bc14-05660e3902dc",
      "name": "Changer le Statut en Envoi",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -1140,
        1120
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldName": "id",
              "fieldValue": "={{ $json.Id }}"
            },
            {
              "fieldName": "status",
              "fieldValue": "1-sending"
            }
          ]
        },
        "operation": "update",
        "projectId": "pxe4z67y9xyv2k9",
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 3,
      "waitBetweenTries": 5000
    },
    {
      "id": "345014cf-1bfa-43aa-8649-6a7068926183",
      "name": "Obtenir tous les modèles de flux depuis NocoDB",
      "type": "n8n-nodes-base.nocoDb",
      "position": [
        -1800,
        620
      ],
      "parameters": {
        "table": "m2518a9tfazlnje",
        "options": {},
        "operation": "getAll",
        "projectId": "pxe4z67y9xyv2k9",
        "returnAll": true,
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "758693db-b596-4592-9e28-61dd85c823a1",
      "name": "Attente",
      "type": "n8n-nodes-base.wait",
      "position": [
        -1780,
        1020
      ],
      "webhookId": "5704f00a-d80d-42d4-a812-4e40eeac8a1e",
      "parameters": {
        "amount": 1
      },
      "typeVersion": 1.1
    },
    {
      "id": "d88b03fc-58c6-4b75-9b98-5ee5dae89b22",
      "name": "Supprimer les Doublons",
      "type": "n8n-nodes-base.removeDuplicates",
      "position": [
        -1360,
        1120
      ],
      "parameters": {
        "compare": "selectedFields",
        "options": {},
        "fieldsToCompare": "user_id"
      },
      "typeVersion": 2
    },
    {
      "id": "4d661409-9944-4df1-9cb3-9f49e1fa063f",
      "name": "Ajouter des enregistrements Par Statut Traitement",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -1980,
        920
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldName": "user_id",
              "fieldValue": "={{ $json.user_id }}"
            },
            {
              "fieldName": "status",
              "fieldValue": "0-processing"
            },
            {
              "fieldName": "question_id",
              "fieldValue": "={{ $json.question_id }}"
            },
            {
              "fieldName": "type",
              "fieldValue": "={{ $json.type }}"
            },
            {
              "fieldName": "flow_id",
              "fieldValue": "={{ $json.flow_id }}"
            },
            {
              "fieldName": "type_template_id",
              "fieldValue": "={{ $json.type_template_id }}"
            },
            {
              "fieldName": "flow_name",
              "fieldValue": "={{ $json.flow_name }}"
            },
            {
              "fieldName": "journey_name",
              "fieldValue": "={{ $('IF Template Parameters OK').item.json.journey_name }}"
            },
            {
              "fieldName": "discount_code",
              "fieldValue": "={{ $('IF Template Parameters OK').item.json.discount_code }}"
            },
            {
              "fieldName": "first_name",
              "fieldValue": "={{ $('Get user_id from dcp').item.json.first_name }}"
            },
            {
              "fieldName": "email",
              "fieldValue": "={{ $('Get user_id from dcp').item.json.email }}"
            }
          ]
        },
        "operation": "create",
        "projectId": "pxe4z67y9xyv2k9",
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "executeOnce": false,
      "retryOnFail": true,
      "typeVersion": 3,
      "waitBetweenTries": 5000
    },
    {
      "id": "8bca3194-be35-462e-a25f-45b01a1f69bd",
      "name": "Insérer des Données Par Statut Traitement",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -1600,
        1020
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "options": {
          "viewId": "vwmpsk71to2jk63t"
        },
        "operation": "getAll",
        "projectId": "pxe4z67y9xyv2k9",
        "returnAll": true,
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 3,
      "alwaysOutputData": false,
      "waitBetweenTries": 5000
    },
    {
      "id": "71e1bc5d-c96a-441f-b71d-d875b4665737",
      "name": "SI Type Email",
      "type": "n8n-nodes-base.if",
      "position": [
        -1860,
        1660
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "da42108f-7501-4028-8cfe-a91bf73e081d",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.type }}",
              "rightValue": "email"
            },
            {
              "id": "d267dd9d-7695-4093-aea6-8bd93f58c460",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "{{ $json.user_id}}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "429a0c25-6687-486e-9355-c950a98f29a6",
      "name": "Brevo Envoyer Email",
      "type": "n8n-nodes-base.sendInBlue",
      "maxTries": 5,
      "position": [
        -1120,
        1400
      ],
      "parameters": {
        "operation": "sendTemplate",
        "templateId": "={{ $json.type_template_id }}",
        "receipients": "={{ $json.email }}",
        "requestOptions": {},
        "additionalFields": {
          "emailTags": {
            "tags": {
              "tag": "={{ $json.flow_name }}"
            }
          },
          "templateParameters": {
            "parameterValues": {
              "parameters": "=first_name={{ $json.first_name }},discount_code={{ $json.discount_code }}"
            }
          }
        }
      },
      "credentials": {
        "sendInBlueApi": {
          "id": "YQK9pEnWrCzPOkw8",
          "name": "Brevo account - U-test"
        }
      },
      "retryOnFail": false,
      "typeVersion": 1,
      "waitBetweenTries": 5000
    },
    {
      "id": "ca39dc6c-0cdf-450b-95cd-6d28f1b21490",
      "name": "Mettre à Jour les Données",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -920,
        1400
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldName": "=id",
              "fieldValue": "={{ $('Disposal Check').item.json.Id }}"
            },
            {
              "fieldName": "sent_result",
              "fieldValue": "={{ $json.messageId }}"
            },
            {
              "fieldName": "sent_at",
              "fieldValue": "={{ $('Schedule Trigger1').item.json.timestamp }}"
            },
            {
              "fieldName": "status",
              "fieldValue": "2-sent"
            }
          ]
        },
        "operation": "update",
        "projectId": "pxe4z67y9xyv2k9",
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 3,
      "waitBetweenTries": 5000
    },
    {
      "id": "9b9d7339-622f-4f35-9482-ef672b99bcb2",
      "name": "Statut-pas-d'email",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -1400,
        1720
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldName": "=id",
              "fieldValue": "={{ $json.Id }}"
            },
            {
              "fieldName": "status",
              "fieldValue": "3-no-email"
            }
          ]
        },
        "operation": "update",
        "projectId": "pxe4z67y9xyv2k9",
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 3,
      "waitBetweenTries": 5000
    },
    {
      "id": "6e107d84-c06d-496d-a67f-089c942d96a6",
      "name": "Déclencheur Planifié1",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -2260,
        1660
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "2fc8d6dd-29f0-47ef-81ac-f3c7c8c49a56",
      "name": "Insérer des Données Par Statut Envoi",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -2060,
        1660
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "options": {
          "viewId": "vw5phhy8u8c12yx9"
        },
        "operation": "getAll",
        "projectId": "pxe4z67y9xyv2k9",
        "returnAll": true,
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 3,
      "alwaysOutputData": false,
      "waitBetweenTries": 5000
    },
    {
      "id": "799746be-1a81-4ff9-93a0-7db87ae22215",
      "name": "Statut-email-rebut",
      "type": "n8n-nodes-base.nocoDb",
      "maxTries": 5,
      "position": [
        -1120,
        1620
      ],
      "parameters": {
        "table": "m2ncsl0i9xks23s",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldName": "=id",
              "fieldValue": "={{ $json.Id }}"
            },
            {
              "fieldName": "status",
              "fieldValue": "4-disposal-email"
            }
          ]
        },
        "operation": "update",
        "projectId": "pxe4z67y9xyv2k9",
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "retryOnFail": true,
      "typeVersion": 3,
      "waitBetweenTries": 5000
    },
    {
      "id": "07d0a337-00fb-41c6-a4a3-23e593a744d9",
      "name": "Note Adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3800,
        380
      ],
      "parameters": {
        "color": 5,
        "width": 720,
        "height": 1000,
        "content": "# Description\n\n## ✅ Workflow Overview Summary (Based on Your Description)\n\n### 🔁 **Flow 1: \"Insert user_id\"**\n\n- Triggered manually or on schedule.\n    \n- You **choose a flow** via `Setup Flow` node by setting a `flow_id`.\n    \n- For each `user_id`, you **insert** a record into NocoDB `n8n-transaction` table:\n    \n    - Fields: `user_id`, `phone_number`, `email`, `first_name`, `flow_name`, etc.\n        \n    - Status: `0-processing`\n        \n- It checks for **duplicate `user_id`**.\n    \n    - If no duplicate → **update status to `1-sending`**\n\n\n### 📩 **Flow 2: \"Sending Email\"**\n\n- Triggered **every 30 minutes** via schedule.\n    \n- Selects records from NocoDB where:\n    \n    - Table = `n8n-transaction`\n        \n    - `status = 1-sending`\n        \n- Checks:\n    \n    - ❌ If no email → `status = 3-no-email`\n        \n    - 🧪 If email is disposable → `status = 4-disposal-email`\n        \n    - ✅ If valid → Send email via **Brevo**\n        \n        - Params: `email`, `first_name`, `flow_name`, `discount_code`\n            \n        - Update:\n            \n            - `sent_result`\n                \n            - `status = 2-sent`\n\n\n### ➕ **To Add Another Flow**\n\n- Duplicate the **\"insert\"** flow.\n    \n- Change only the `Setup Flow` node to a new `flow_id`.\n    \n- That `flow_id` corresponds to a record in `n8n-templates` (a table in NocoDB) with all necessary metadata (e.g., template ID, discount code, etc.)."
      },
      "typeVersion": 1
    },
    {
      "id": "76c26fff-c938-49c4-81ea-983fa1951f77",
      "name": "Note Adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -660,
        540
      ],
      "parameters": {
        "color": 4,
        "width": 620,
        "height": 460,
        "content": "## n8n-transaction-ecrm\n[\n  {\n    \"id\": 1,\n    \"user_id\": \"u123\",\n    \"phone_number\": \"09120000000\",\n    \"email\": \"john@example.com\",\n    \"first_name\": \"John\",\n    \"flow_id\": \"1\",\n    \"flow_name\": \"register2verify-1day-ago\",\n    \"type\": \"email\",\n    \"type_template_id\": 201,\n    \"discount_code\": \"DISCOUNT50\",\n    \"journey_name\": \"register2verify\",\n    \"question_id\": \"7002\",\n    \"status\": \"0-processing\",\n    \"sent_result\": null,\n    \"sent_at\": null,\n    \"created_at\": \"2025-08-07T10:30:00Z\",\n    \"updated_at\": null\n  }\n]"
      },
      "typeVersion": 1
    },
    {
      "id": "3e77a4a6-b4a8-4087-b57c-1346c1281513",
      "name": "Note Adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -660,
        1040
      ],
      "parameters": {
        "color": 4,
        "width": 620,
        "height": 280,
        "content": "## n8n-templates-ecrm\n[\n  {\n    \"Id\": 1,\n    \"flow_name\": \"register2verify-1day-ago\",\n    \"journey_name\": \"register2verify\",\n    \"question_id\": \"7002\",\n    \"type\": \"email\",\n    \"type_template_id\": 201,\n    \"discount_code\": \"DISCOUNT50\",\n    \"gift_code\": null\n  }"
      },
      "typeVersion": 1
    },
    {
      "id": "fc561bb6-bdc7-4ad3-9bda-f548eb8aa29f",
      "name": "Note Adhésive3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -660,
        1360
      ],
      "parameters": {
        "color": 4,
        "width": 620,
        "height": 320,
        "content": "## n8n-templates-ecrm\n[\n  {\n    \"user_id\": \"u123\",\n    \"phone_number\": \"09120000000\",\n    \"email\": \"john@example.com\",\n    \"first_name\": \"John\"\n  },\n  {\n    \"user_id\": \"u124\",\n    \"phone_number\": \"09120000001\",\n    \"email\": \"sara@example.com\",\n    \"first_name\": \"Sara\"\n  }\n]"
      },
      "typeVersion": 1
    },
    {
      "id": "a40743bd-6fb0-4fdb-8381-226c9f9518d5",
      "name": "Note Adhésive4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -660,
        440
      ],
      "parameters": {
        "color": 4,
        "width": 460,
        "height": 80,
        "content": "# Example of Databases📋\n"
      },
      "typeVersion": 1
    },
    {
      "id": "4d24e31c-196a-437e-bb9a-d3dc0b842439",
      "name": "Note Adhésive5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3060,
        380
      ],
      "parameters": {
        "color": 5,
        "width": 720,
        "height": 1600,
        "content": "# Every Node Description\n\n### Setup Flow:\nThis node selects which flow (campaign/journey) to run by setting the flow_id.\nYou can change the flow_id here to control which flow from the n8n-templates table will be used for sending.\n\n### Get all flow templates from NocoDB\nThis node fetches all available flows from the n8n-templates-ecrm table in NocoDB. These templates contain metadata for each flow, such as flow_name, type, type_template_id, discount_code, etc.\n\n### Filter Template\nThis node filters the flow templates retrieved from n8n-templates-ecrm to find the one that matches the selected flow_id from the Setup Flow node.\n\n### Get user_id from dcp\nThis node retrieves user records from the cdp-ecrm table. Each record contains user details such as user_id, email, first_name, and phone_number.\n\n### Add records By Status Processing\nCreate a new record in the n8n-transaction-ecrm table for each user, combining user data and selected flow template.\nSets initial status to 0-processing.\n\n### Insert Data By Status Processing\nFetch all records from the n8n-transaction-ecrm table where status is 0-processing.\nThese are newly inserted user records that are ready for deduplication before sending.\n\n### Remove Duplicates\nRemove duplicate records based on the user_id field to ensure each user receives only one email during the current flow execution.\nOnly the first occurrence of each user_id is kept.\n\n### Change Status to Sending\nUpdate the status field in the n8n-transaction-ecrm table from 0-processing to 1-sending for each unique user.\nMarks the records as ready for the email sending process.\n\n### Schedule Trigger1\nTriggers every 30 minutes.\n\n### Insert Data By Status Sending\nFetch all records from the n8n-transaction-ecrm table where status is 1-sending.\nThese are the users queued for email delivery in the next step.\n\n### IF email is not empty\nCheck if the email field exists and is not empty.\nIf the email is missing, the record will be updated with status: 3-no-email and excluded from sending.\n\n### Disposal Check\nCheck if the user's email is from a disposable or temporary email provider using a regex match.\nIf the email is disposable, update the record with status: 4-disposal-email and skip sending.\nIf it's a valid email, continue to the sending step.\n\n### Brevo Send Email\nSend a transactional email to the user using Brevo (Sendinblue) based on the selected flow template.\nIncludes dynamic parameters such as first_name, email, flow_name, and discount_code.\n- Create a template in Brevo.\n- Write id of that template in type_template_id in n8n-template-ecrm for that flow.\n\n### Update Data\nAfter the email is sent, update the corresponding record in the n8n-transaction-ecrm table:\n\n- Set sent_result with the message ID returned by Brevo\n- Set sent_at with the current timestamp\n- Change status to 2-sent\n\n\n\n\n\n\n\n\n\nAsk ChatGPT\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2997ce01-33dc-4f1f-a4a7-fe23e42dcbd2",
      "name": "Obtenir user_id depuis dcp",
      "type": "n8n-nodes-base.nocoDb",
      "position": [
        -1140,
        680
      ],
      "parameters": {
        "table": "ml3d8rnyud0g1bd",
        "options": {
          "viewId": "={{ $json.question_id }}"
        },
        "operation": "getAll",
        "projectId": "pxe4z67y9xyv2k9",
        "returnAll": true,
        "authentication": "nocoDbApiToken"
      },
      "credentials": {
        "nocoDbApiToken": {
          "id": "OmiCzu1TOrJhZRIa",
          "name": "NocoDB Token account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "187c2a84-04f6-4c70-9126-2208c253d4f4",
      "name": "Si email n'est pas vide",
      "type": "n8n-nodes-base.if",
      "position": [
        -1600,
        1580
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "3df05c9a-ebe6-4126-9143-2b24066fde58",
              "operator": {
                "type": "string",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.email }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "3e84e316-51a3-4b33-a98d-7783ab27c4f0",
      "name": "Note Adhésive6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2220,
        400
      ],
      "parameters": {
        "color": 3,
        "width": 340,
        "height": 80,
        "content": "# 📝 Insert user_id"
      },
      "typeVersion": 1
    },
    {
      "id": "e5235b08-960c-464b-8357-34b16db89604",
      "name": "Note Adhésive7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2240,
        1260
      ],
      "parameters": {
        "color": 3,
        "width": 340,
        "height": 80,
        "content": "# ✉️ Sending Email"
      },
      "typeVersion": 1
    },
    {
      "id": "18f7658f-2f59-498d-9434-5d795d256d7d",
      "name": "Vérification Rebut",
      "type": "n8n-nodes-base.if",
      "position": [
        -1380,
        1500
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "or",
          "conditions": [
            {
              "id": "e5fd1b3e-9575-4bfa-ae7a-fe65943fbebc",
              "operator": {
                "type": "string",
                "operation": "notRegex"
              },
              "leftValue": "{{ $json.email }}",
              "rightValue": ".*(temp|abc|1234|yopmail|protonmail|mailinator|\\.cc|bigbester|fake|spam|gdf|sdf|mr123|passinbox|landininbox|@inbox|random|anony|mymail|mail\\.ru|\\.buzz|asdasd|asf|simplelogin|simplelogin\\.com|silomails\\.com|slmails\\.com|simplelogin\\.fr|aleeas\\.com|slmail\\.me|8shield\\.net|dralias\\.com|passinbox\\.com|passfwd\\.com|passmail\\.com|passmail\\.net|simplelogin\\.co|simplelogin\\.io|duck\\.com|mozmail\\.com|anonaddy\\.com|anonaddy\\.me|trash|guerrilla|getnada|owly|mvrht|sharklasers|anonbox).*"
            }
          ]
        }
      },
      "typeVersion": 2.1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "69821e9f-ff7d-49d0-947f-4004eb715861",
  "connections": {
    "758693db-b596-4592-9e28-61dd85c823a1": {
      "main": [
        [
          {
            "node": "8bca3194-be35-462e-a25f-45b01a1f69bd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "fb8730fc-f2c0-4426-9781-d865ec0c773b": {
      "main": [
        [
          {
            "node": "4d661409-9944-4df1-9cb3-9f49e1fa063f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "06ba0ef4-7b4a-4251-b370-320e71d02037": {
      "main": [
        [
          {
            "node": "345014cf-1bfa-43aa-8649-6a7068926183",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "71e1bc5d-c96a-441f-b71d-d875b4665737": {
      "main": [
        [
          {
            "node": "187c2a84-04f6-4c70-9126-2208c253d4f4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "18f7658f-2f59-498d-9434-5d795d256d7d": {
      "main": [
        [
          {
            "node": "429a0c25-6687-486e-9355-c950a98f29a6",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "799746be-1a81-4ff9-93a0-7db87ae22215",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5515a0dc-6ef7-4325-a48c-067663d9d531": {
      "main": [
        [
          {
            "node": "d79c4618-ea24-4140-b797-88e81ee810dc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "429a0c25-6687-486e-9355-c950a98f29a6": {
      "main": [
        [
          {
            "node": "ca39dc6c-0cdf-450b-95cd-6d28f1b21490",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "56476cf7-b1d2-43d7-9b68-80dcc9d580e0": {
      "main": [
        [
          {
            "node": "06ba0ef4-7b4a-4251-b370-320e71d02037",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d88b03fc-58c6-4b75-9b98-5ee5dae89b22": {
      "main": [
        [
          {
            "node": "28db049c-fb73-4379-bc14-05660e3902dc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6e107d84-c06d-496d-a67f-089c942d96a6": {
      "main": [
        [
          {
            "node": "2fc8d6dd-29f0-47ef-81ac-f3c7c8c49a56",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2997ce01-33dc-4f1f-a4a7-fe23e42dcbd2": {
      "main": [
        [
          {
            "node": "06ee76ce-47aa-4cdc-9d24-6440d7da2508",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "187c2a84-04f6-4c70-9126-2208c253d4f4": {
      "main": [
        [
          {
            "node": "18f7658f-2f59-498d-9434-5d795d256d7d",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "9b9d7339-622f-4f35-9482-ef672b99bcb2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "06ee76ce-47aa-4cdc-9d24-6440d7da2508": {
      "main": [
        [
          {
            "node": "fb8730fc-f2c0-4426-9781-d865ec0c773b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d79c4618-ea24-4140-b797-88e81ee810dc": {
      "main": [
        [
          {
            "node": "2997ce01-33dc-4f1f-a4a7-fe23e42dcbd2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2fc8d6dd-29f0-47ef-81ac-f3c7c8c49a56": {
      "main": [
        [
          {
            "node": "71e1bc5d-c96a-441f-b71d-d875b4665737",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4d661409-9944-4df1-9cb3-9f49e1fa063f": {
      "main": [
        [
          {
            "node": "758693db-b596-4592-9e28-61dd85c823a1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8bca3194-be35-462e-a25f-45b01a1f69bd": {
      "main": [
        [
          {
            "node": "d88b03fc-58c6-4b75-9b98-5ee5dae89b22",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "345014cf-1bfa-43aa-8649-6a7068926183": {
      "main": [
        [
          {
            "node": "5515a0dc-6ef7-4325-a48c-067663d9d531",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}