AI-Powered CRM Data Integration

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "active": false,
  "activeVersion": null,
  "activeVersionId": null,
  "connections": {
    "Google Gemini Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "AI Agent": {
      "main": [
        [
          {
            "node": "Execute a SQL query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Execute a SQL query": {
      "main": [
        [
          {
            "node": "If Alert Level Is Not Low",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch CRM Data": {
      "main": [
        [
          {
            "node": "If",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Fetch CRM Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If": {
      "main": [
        [
          {
            "node": "Format Data for AI",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "Generate HTML Report": {
      "main": [
        [
          {
            "node": "Send email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Data for AI": {
      "main": [
        [
          {
            "node": "AI Agent",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Structured Output Parser": {
      "ai_outputParser": [
        [
          {
            "node": "AI Agent",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "If Alert Level Is Not Low": {
      "main": [
        [
          {
            "node": "Generate HTML Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "createdAt": "2025-11-27T20:55:09.390Z",
  "id": "U7TOVBPrWYVgks6Z",
  "isArchived": false,
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "CRM Data Integration Subworkflow",
  "nodes": [
    {
      "parameters": {
        "options": {}
      },
      "id": "b97348da-99a4-4b85-9d6e-3d0e4bcdba20",
      "name": "Google Gemini Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "typeVersion": 1,
      "position": [
        -464,
        1008
      ],
      "credentials": {
        "googlePalmApi": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "agent": "conversationalAgent",
        "promptType": "define",
        "text": "=**Rol asignado al modelo:**\n*Eres un analista de datos experto en CRM y gesti\u00f3n de reclamos, especializado en detectar patrones, anomal\u00edas y riesgos operativos. Tu objetivo es producir un an\u00e1lisis claro, conciso y profesional basado en los datos proporcionados.*\n\n**Instrucciones:**\n\nAnaliza los siguientes datos de reclamos del CRM. Los datos ya han sido procesados y contienen informaci\u00f3n como `cliente_id`, `tipo_reclamo`, `fecha_reclamo`, `ciudad`, `prioridad`, entre otros:\n\n{{ JSON.stringify($json) }}\n\nTu an\u00e1lisis debe identificar y describir en texto:\n\n1. Reclamos duplicados o muy similares\n2. Patrones geogr\u00e1ficos o temporales de reclamos\n3. Incrementos inusuales en ciertos tipos de reclamo\n4. Clientes con m\u00faltiples reclamos recurrentes\n\n**Responde \u00fanicamente en JSON**, siguiendo exactamente este formato, como **un array de objetos**, donde cada objeto contiene un campo `ai_analysis` con la informaci\u00f3n solicitada:\n\n```json\n[\n  {\n    \"ai_analysis\": {\n      \"anomalias_detectadas\": \"Describe en una frase o p\u00e1rrafo las anomal\u00edas detectadas de manera profesional\",\n      \"patrones_identificados\": \"Describe en texto los patrones observados y su posible relevancia\",\n      \"recomendaciones\": \"Proporciona recomendaciones accionables basadas en los hallazgos\",\n      \"nivel_alerta\": \"bajo | medio | alto\"\n    }\n  }\n]\n```\n\n**Notas importantes:**\n\n* No agregues campos adicionales, comentarios ni texto fuera del JSON.\n* S\u00e9 preciso, objetivo y profesional en tu redacci\u00f3n.\n* Si la informaci\u00f3n disponible es insuficiente para detectar anomal\u00edas o patrones, ind\u00edcalo de forma clara en los campos correspondientes, manteniendo el formato JSON.\n* Aseg\u00farate de que la salida sea un **array de objetos**, no un string dentro de un campo como `unified`.",
        "hasOutputParser": true,
        "options": {}
      },
      "id": "aba4c8e0-cb9e-434d-b8e6-61b31df8850e",
      "name": "AI Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "typeVersion": 1.7,
      "position": [
        -464,
        800
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "INSERT INTO crm_analisis_resultados \n(anomalias, patrones, recomendaciones, nivel_alerta, processed_at)\nVALUES \n(\n  '{{ $json.output[0].ai_analysis.anomalias_detectadas.replace(/'/g, \"''\") }}',\n  '{{ $json.output[0].ai_analysis.patrones_identificados.replace(/'/g, \"''\") }}',\n  '{{ $json.output[0].ai_analysis.recomendaciones.replace(/'/g, \"''\") }}',\n  '{{ $json.output[0].ai_analysis.nivel_alerta.replace(/'/g, \"''\") }}',\n  '{{ $now }}'\n)\nRETURNING *;\n",
        "options": {}
      },
      "id": "fe328a9d-337f-459d-9a10-e9e5000cb7b8",
      "name": "Execute a SQL query",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [
        -96,
        800
      ],
      "credentials": {
        "postgres": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT \n  id,\n  cliente_id,\n  nombre_cliente,\n  tipo_reclamo,\n  descripcion,\n  fecha_reclamo,\n  estado,\n  ciudad,\n  coordenadas_lat,\n  coordenadas_lon,\n  consumo_m3,\n  created_at\nFROM crm_reclamos\nWHERE fecha_reclamo >= CURRENT_DATE - INTERVAL '1 day'\n  AND estado IN ('pendiente', 'en_proceso')\nORDER BY fecha_reclamo DESC\nLIMIT 1000;",
        "options": {}
      },
      "id": "97e4d5d6-bc9c-45ee-a63d-59d4e1914b79",
      "name": "Fetch CRM Data",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [
        -1136,
        816
      ],
      "alwaysOutputData": true,
      "credentials": {
        "postgres": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours",
              "hoursInterval": 24
            }
          ]
        }
      },
      "id": "7d68b74f-374f-43de-92f7-a02171eb6c15",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1,
      "position": [
        -1360,
        816
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "strict",
            "version": 1
          },
          "conditions": [
            {
              "id": "has-cliente-id",
              "leftValue": "={{ $('Fetch CRM Data').item.json }}",
              "rightValue": true,
              "operator": {
                "type": "object",
                "operation": "notEmpty",
                "singleValue": true
              }
            }
          ],
          "combinator": "and"
        },
        "options": {}
      },
      "id": "9d472e24-c5c0-48f6-b382-3b57a61a8b5d",
      "name": "If",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2,
      "position": [
        -928,
        816
      ]
    },
    {
      "parameters": {
        "html": "<!DOCTYPE html>\n<html>\n  <body style=\"font-family: Arial, Helvetica, sans-serif; background-color:#f4f8fb; padding: 20px; color:#003049;\">\n\n    <div style=\"max-width: 800px; margin: auto; background: #ffffff; padding: 25px; border-radius: 8px; border: 1px solid #dce6ee;\">\n\n      <h2 style=\"color:#005f99; margin-bottom: 10px; font-weight: 600;\">\n        Reporte de Reclamos - CRM\n      </h2>\n\n      <p style=\"font-size: 15px; color:#003049; line-height: 1.5;\">\n        Se han procesado los siguientes reclamos reportados por clientes.  \n        Este informe ha sido generado autom\u00e1ticamente por el sistema CRM con an\u00e1lisis de IA.\n      </p>\n\n      <div style=\"margin-top: 25px;\">\n        {{\n          $input.all().map(i => i.json).map(item => `\n            <div style=\"\n              border-left: 4px solid ${\n                item.prioridad === 'alta' ? '#dc3545' :\n                item.prioridad === 'media' ? '#fd7e14' : '#4bb3fd'\n              };\n              padding: 15px;\n              margin-bottom: 20px;\n              background: #f0f7fc;\n              border-radius: 6px;\n            \">\n\n              <strong style=\"font-size: 16px; color:#003049;\">\n                Reclamo ID: ${item.id}\n              </strong>\n\n              <p style=\"margin: 8px 0; font-size: 14px; color:#003049;\">\n                <strong>Nivel de Alerta:</strong> ${item.nivel_alerta.toUpperCase()}\n              </p>\n\n              <div style=\"margin-top: 12px;\">\n                <strong style=\"font-size: 14px; color:#003049;\">Anomal\u00edas Detectadas:</strong>\n                <p style=\"margin: 5px 0; font-size: 13px;\">${item.anomalias}</p>\n              </div>\n\n              <div style=\"margin-top: 12px;\">\n                <strong style=\"font-size: 14px; color:#003049;\">Patrones Identificados:</strong>\n                <p style=\"margin: 5px 0; font-size: 13px;\">${item.patrones}</p>\n              </div>\n\n              <div style=\"margin-top: 12px;\">\n                <strong style=\"font-size: 14px; color:#005f99;\">Recomendaciones:</strong>\n                <p style=\"margin: 5px 0; font-size: 13px; color:#005f99;\">${item.recomendaciones}</p>\n              </div>\n\n              <p style=\"margin-top: 12px; font-size: 12px; color:#6c7a86;\">\n                Procesado el: ${new Date(item.processed_at).toLocaleString()}\n              </p>\n\n            </div>\n          `).join(\"\")\n        }}\n      </div>\n\n      <p style=\"margin-top: 30px; font-size: 13px; color:#6c7a86; text-align: center;\">\n        Este es un reporte autom\u00e1tico generado por el sistema CRM con an\u00e1lisis de IA.\n      </p>\n\n    </div>\n  </body>\n</html>\n"
      },
      "id": "48541223-8b90-403a-8461-ca4167cddf90",
      "name": "Generate HTML Report",
      "type": "n8n-nodes-base.html",
      "typeVersion": 1,
      "position": [
        384,
        784
      ]
    },
    {
      "parameters": {
        "jsCode": "const items = $input.all();\nconst processedItems = [];\n\nfunction simpleHash(str) {\n  let hash = 0;\n  for (let i = 0; i < str.length; i++) {\n    const char = str.charCodeAt(i);\n    hash = ((hash << 5) - hash) + char;\n    hash = hash & hash;\n  }\n  return Math.abs(hash).toString(16);\n}\n\nfor (const item of items) {\n  const data = item.json;\n\n  const hasRequiredFields = data.cliente_id &&\n                            data.tipo_reclamo &&\n                            data.fecha_reclamo;\n\n  if (!hasRequiredFields) {\n    processedItems.push({\n      ...data,\n      validation_status: 'error',\n      validation_message: 'Campos requeridos faltantes',\n      processed_at: new Date().toISOString()\n    });\n    continue;\n  }\n\n  let prioridad = 'media';\n  const tipoLower = data.tipo_reclamo.toLowerCase();\n\n  if (tipoLower.includes('fuga') || tipoLower.includes('corte')) {\n    prioridad = 'alta';\n  } else if (tipoLower.includes('calidad') || tipoLower.includes('presi\u00f3n')) {\n    prioridad = 'alta';\n  } else if (tipoLower.includes('factura')) {\n    prioridad = 'media';\n  } else {\n    prioridad = 'baja';\n  }\n\n  processedItems.push({\n    ...data,\n    prioridad,\n    validation_status: 'success',\n    processed_at: new Date().toISOString(),\n    data_hash: simpleHash(JSON.stringify({\n      cliente_id: data.cliente_id,\n      fecha_reclamo: data.fecha_reclamo,\n      tipo_reclamo: data.tipo_reclamo\n    }))\n  });\n}\n\nfunction formatJSON(arr) {\n  return JSON.stringify(arr, null, 2);\n}\n\nconst unified = formatJSON(processedItems);\n\nreturn [\n  { json: { unified } }\n];\n"
      },
      "id": "9b5eaf40-88e0-4944-9719-ba2db718bf36",
      "name": "Format Data for AI",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        -688,
        800
      ]
    },
    {
      "parameters": {
        "fromEmail": "=santiago.amaya-z@mail.escuelaing.edu.co",
        "toEmail": "=laura.perilla-q@mail.escuelaing.edu.co, ricardo.ayala-g@mail.escuelaing.edu.co, santiago.botero-g@mail.escuelaing.edu.co, andres.calderon-r@mail.escuelaing.edu.co, santiago.amaya-z@mail.escuelaing.edu.co",
        "subject": "=Reporte Diario CRM - {{ $json.report_timestamp }}",
        "html": "={{ $json.html }}",
        "options": {}
      },
      "id": "f44f3821-429f-4426-b0ca-199bf4e0ea05",
      "name": "Send email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 2.1,
      "position": [
        608,
        784
      ],
      "credentials": {
        "smtp": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "schemaType": "manual",
        "inputSchema": "{\n  \"$schema\": \"https://json-schema.org/draft/2020-12/schema\",\n  \"title\": \"An\u00e1lisis de Reclamos CRM\",\n  \"description\": \"Esquema para el resultado del an\u00e1lisis de reclamos, incluyendo anomal\u00edas, patrones y recomendaciones.\",\n  \"type\": \"array\",\n  \"items\": {\n    \"type\": \"object\",\n    \"properties\": {\n      \"ai_analysis\": {\n        \"type\": \"object\",\n        \"description\": \"Resultado del an\u00e1lisis realizado por el modelo sobre los reclamos CRM\",\n        \"properties\": {\n          \"anomalias_detectadas\": {\n            \"type\": \"string\",\n            \"description\": \"Descripci\u00f3n de las anomal\u00edas detectadas en los reclamos\"\n          },\n          \"patrones_identificados\": {\n            \"type\": \"string\",\n            \"description\": \"Patrones observados en los datos de reclamos, geogr\u00e1ficos, temporales o por tipo\"\n          },\n          \"recomendaciones\": {\n            \"type\": \"string\",\n            \"description\": \"Recomendaciones accionables basadas en los hallazgos\"\n          },\n          \"nivel_alerta\": {\n            \"type\": \"string\",\n            \"description\": \"Nivel de alerta asignado seg\u00fan la criticidad de los hallazgos\",\n            \"enum\": [\"bajo\", \"medio\", \"alto\"]\n          }\n        },\n        \"required\": [\n          \"anomalias_detectadas\",\n          \"patrones_identificados\",\n          \"recomendaciones\",\n          \"nivel_alerta\"\n        ],\n        \"additionalProperties\": false\n      }\n    },\n    \"required\": [\"ai_analysis\"],\n    \"additionalProperties\": false\n  }\n}"
      },
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "typeVersion": 1.3,
      "position": [
        -320,
        1008
      ],
      "id": "61b3e240-b7ad-4fcc-bd12-1310ae09f4b1",
      "name": "Structured Output Parser"
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "strict",
            "version": 2
          },
          "conditions": [
            {
              "id": "21037a47-b766-4cb9-85de-ac62b934c579",
              "leftValue": "={{ $json.nivel_alerta }}",
              "rightValue": "bajo",
              "operator": {
                "type": "string",
                "operation": "notEquals"
              }
            }
          ],
          "combinator": "and"
        },
        "options": {}
      },
      "type": "n8n-nodes-base.if",
      "typeVersion": 2.2,
      "position": [
        112,
        800
      ],
      "id": "36cbbb94-3a54-457d-a201-dbd44cb7f622",
      "name": "If Alert Level Is Not Low"
    }
  ],
  "settings": {
    "executionOrder": "v1"
  },
  "shared": [
    {
      "updatedAt": "2025-11-27T20:55:09.400Z",
      "createdAt": "2025-11-27T20:55:09.400Z",
      "role": "workflow:owner",
      "workflowId": "U7TOVBPrWYVgks6Z",
      "projectId": "xno20arzPonor2IK"
    }
  ],
  "staticData": null,
  "tags": [
    {
      "updatedAt": "2025-11-27T20:55:21.471Z",
      "createdAt": "2025-11-27T20:55:21.471Z",
      "id": "xJr6SPZKss4pCHM4",
      "name": "CRM"
    }
  ],
  "triggerCount": 0,
  "updatedAt": "2025-12-06T23:01:08.000Z",
  "versionId": "07385577-a3ec-417f-8059-0bd3483fd7b3"
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• googlePalmApi
• postgres
• smtp

About this workflow

CRM Data Integration Subworkflow. Uses lmChatGoogleGemini, agent, postgres, emailSend. Scheduled trigger; 11 nodes.

Source: https://github.com/LePeanutButter/n8n-workflows-aquasense/blob/main/workflows/U7TOVBPrWYVgks6Z.json — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.