Track Support Performance Metrics Across Zendesk & Freshdesk with Sheets,…

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 →

{
  "id": "QyegIMfwfC0kN3kN",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Unified Support Team Performance Dashboard (Zendesk & Freshdesk \u2192 Google Sheets + Slack + Gmail)",
  "tags": [],
  "nodes": [
    {
      "id": "af98b36c-8053-4882-b3ba-2b90fb5c7523",
      "name": "Send Slack Alert",
      "type": "n8n-nodes-base.slack",
      "position": [
        1936,
        -64
      ],
      "parameters": {
        "text": "={{ $json.text }}",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "C09FM9N8UEA",
          "cachedResultName": "zendesk-YOUR_OPENAI_KEY_HERE-alerts"
        },
        "otherOptions": {
          "mrkdwn": true
        }
      },
      "credentials": {
        "slackApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "df0ba9dc-3737-4030-a225-e58cd3c17a86",
      "name": "Send Weekly Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1936,
        128
      ],
      "parameters": {
        "toList": [
          "{Enter Your  Email}"
        ],
        "message": "=Support Dashboard Alert",
        "subject": "={{ $json.subject }}",
        "resource": "message",
        "htmlMessage": "={{ $json.html }}",
        "includeHtml": true,
        "additionalFields": {}
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "b96bf81b-8486-4545-b23e-518643ca9b89",
      "name": "Weekly Trigger",
      "type": "n8n-nodes-base.cron",
      "position": [
        0,
        32
      ],
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "hour": 20,
              "mode": "everyWeek"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "bb68236c-376b-4c00-80fd-6db781100bc2",
      "name": "Fetch Tickets From Zendesk",
      "type": "n8n-nodes-base.zendesk",
      "position": [
        368,
        -64
      ],
      "parameters": {
        "options": {},
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "zendeskApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "972d1914-47a0-48a3-8018-46519599bc2f",
      "name": "Log KPIs in Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1040,
        32
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "platform",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "platform",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ticket_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ticket_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ticket_url",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ticket_url",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "subject",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "subject",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "priority",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "priority",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "priority_level",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "priority_level",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "status_normalized",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "status_normalized",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "created_timestamp",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "created_timestamp",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "updated_timestamp",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "updated_timestamp",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "original_created_at",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "original_created_at",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "description_preview",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "description_preview",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "description",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "description",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "tags",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "tags",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "requester_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "requester_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "assignee_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "assignee_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "channel",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "channel",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ticket_age_hours",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ticket_age_hours",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "needs_attention",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "needs_attention",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ticket_type",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ticket_type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "due_by",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "due_by",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "is_escalated",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "is_escalated",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "sentiment_score",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "sentiment_score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "ticket_id"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1VLAw8-Om4ULgGFfG_xwYdgni1uggTAP0UA24Nfop3RQ/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1VLAw8-Om4ULgGFfG_xwYdgni1uggTAP0UA24Nfop3RQ",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1VLAw8-Om4ULgGFfG_xwYdgni1uggTAP0UA24Nfop3RQ/edit?usp=drivesdk",
          "cachedResultName": "Performance Report Support"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.4
    },
    {
      "id": "42d5b2ac-2c2b-44ad-bb68-654213112f67",
      "name": "Calculate Support KPIs",
      "type": "n8n-nodes-base.code",
      "position": [
        1264,
        32
      ],
      "parameters": {
        "jsCode": "// Unified KPI Calculation Code Node for n8n Support Team Performance\n// Handles both Zendesk and Freshdesk tickets\n// Get all input items from n8n\nconst inputItems = $input.all();\nlet tickets = [];\n\n// Extract tickets from all input items\ninputItems.forEach(item => {\n  if (item.json) {\n    if (Array.isArray(item.json)) {\n      // If the item contains an array of tickets\n      tickets = tickets.concat(item.json);\n    } else {\n      // If the item is a single ticket\n      tickets.push(item.json);\n    }\n  }\n});\n\nconsole.log('Total tickets processed:', tickets.length);\n\n// Configuration thresholds (you can adjust these or get from previous node)\nconst thresholds = {\n  response_time_hours: 4,     // 4 hours response time threshold\n  resolution_time_hours: 24,  // 24 hours resolution time threshold\n  csat_threshold: 4.0,        // Minimum CSAT score\n  high_priority_threshold: 48, // Hours for high priority escalation\n};\n\n// Initialize KPI tracking variables\nlet totalTickets = tickets.length;\nlet resolvedTickets = 0;\nlet openTickets = 0;\nlet urgentTickets = 0;\nlet highPriorityTickets = 0;\nlet slaBreachedTickets = 0;\nlet channelDistribution = {};\nlet priorityDistribution = { low: 0, medium: 0, normal: 0, high: 0, urgent: 0 };\nlet platformDistribution = { zendesk: 0, freshdesk: 0, unknown: 0 };\nlet totalAgeHours = 0;\nlet needsAttentionCount = 0;\nlet escalatedTickets = 0;\nlet overdueTickets = 0;\n\n// Enhanced status mapping for both platforms\nfunction isTicketResolved(ticket) {\n  const resolvedStatuses = {\n    zendesk: ['solved', 'closed'],\n    freshdesk: ['resolved', 'closed']\n  };\n  \n  const platform = ticket.platform || 'zendesk';\n  const normalizedStatus = ticket.status_normalized || ticket.status;\n  \n  return resolvedStatuses[platform]?.includes(normalizedStatus) || \n         resolvedStatuses[platform]?.includes(ticket.status) ||\n         ['solved', 'closed', 'resolved'].includes(normalizedStatus);\n}\n\n// Enhanced priority normalization\nfunction normalizePriority(ticket) {\n  const platform = ticket.platform || 'zendesk';\n  let priority = ticket.priority;\n  \n  // Handle Freshdesk numeric priorities that weren't converted\n  if (platform === 'freshdesk' && typeof priority === 'number') {\n    const freshdeskPriorityMap = {\n      1: 'low',\n      2: 'medium',\n      3: 'high',\n      4: 'urgent'\n    };\n    priority = freshdeskPriorityMap[priority] || 'normal';\n  }\n  \n  // Normalize medium to normal for consistency\n  if (priority === 'medium') {\n    priority = 'normal';\n  }\n  \n  return priority || 'normal';\n}\n\n// Check if ticket is overdue (for Freshdesk)\nfunction isTicketOverdue(ticket) {\n  if (ticket.platform === 'freshdesk' && ticket.due_by) {\n    const dueDate = new Date(ticket.due_by);\n    const now = new Date();\n    return dueDate < now;\n  }\n  return false;\n}\n\n// Process each ticket\ntickets.forEach(ticket => {\n  // Platform distribution\n  const platform = ticket.platform || 'unknown';\n  platformDistribution[platform] = (platformDistribution[platform] || 0) + 1;\n  \n  // Basic counts\n  if (isTicketResolved(ticket)) {\n    resolvedTickets++;\n  } else {\n    openTickets++;\n  }\n  \n  // Priority analysis with normalization\n  const priority = normalizePriority(ticket);\n  priorityDistribution[priority] = (priorityDistribution[priority] || 0) + 1;\n  \n  if (priority === 'urgent') {\n    urgentTickets++;\n  }\n  if (priority === 'high' || priority === 'urgent') {\n    highPriorityTickets++;\n  }\n  \n  // Channel distribution\n  const channel = ticket.channel || 'unknown';\n  channelDistribution[channel] = (channelDistribution[channel] || 0) + 1;\n  \n  // Age and SLA analysis\n  const ageHours = ticket.ticket_age_hours || 0;\n  totalAgeHours += ageHours;\n  \n  // Escalation tracking (Freshdesk specific)\n  if (ticket.platform === 'freshdesk' && ticket.is_escalated) {\n    escalatedTickets++;\n  }\n  \n  // Overdue tracking (Freshdesk specific)\n  if (isTicketOverdue(ticket)) {\n    overdueTickets++;\n  }\n  \n  // Enhanced SLA breach conditions for both platforms\n  let isSLABreached = false;\n  \n  if (isTicketResolved(ticket)) {\n    // For resolved tickets, check if resolution time exceeded threshold\n    if (ageHours > thresholds.resolution_time_hours) {\n      isSLABreached = true;\n    }\n  } else {\n    // For open tickets, check various conditions\n    if (priority === 'urgent' && ageHours > thresholds.response_time_hours) {\n      isSLABreached = true;\n    } else if ((priority === 'high' || priority === 'urgent') && ageHours > thresholds.high_priority_threshold) {\n      isSLABreached = true;\n    } else if (ageHours > thresholds.resolution_time_hours) {\n      isSLABreached = true;\n    }\n    \n    // Additional Freshdesk-YOUR_OPENAI_KEY_HERE SLA conditions\n    if (ticket.platform === 'freshdesk') {\n      if (ticket.is_escalated || isTicketOverdue(ticket)) {\n        isSLABreached = true;\n      }\n    }\n  }\n  \n  if (isSLABreached) {\n    slaBreachedTickets++;\n  }\n  \n  // Needs attention count\n  if (ticket.needs_attention) {\n    needsAttentionCount++;\n  }\n});\n\n// Calculate averages and percentages\nconst avgTicketAge = totalTickets > 0 ? Math.round(totalAgeHours / totalTickets) : 0;\nconst resolutionRate = totalTickets > 0 ? Math.round((resolvedTickets / totalTickets) * 100) : 0;\nconst slaBreachRate = totalTickets > 0 ? Math.round((slaBreachedTickets / totalTickets) * 100) : 0;\nconst urgentTicketRate = totalTickets > 0 ? Math.round((urgentTickets / totalTickets) * 100) : 0;\nconst highPriorityRate = totalTickets > 0 ? Math.round((highPriorityTickets / totalTickets) * 100) : 0;\nconst attentionRate = totalTickets > 0 ? Math.round((needsAttentionCount / totalTickets) * 100) : 0;\nconst escalationRate = totalTickets > 0 ? Math.round((escalatedTickets / totalTickets) * 100) : 0;\nconst overdueRate = totalTickets > 0 ? Math.round((overdueTickets / totalTickets) * 100) : 0;\n\n// Platform-specific rates\nconst platformRates = {};\nObject.keys(platformDistribution).forEach(platform => {\n  if (platformDistribution[platform] > 0) {\n    platformRates[platform] = Math.round((platformDistribution[platform] / totalTickets) * 100);\n  }\n});\n\n// Enhanced response and resolution time estimation\nconst avgResponseTimeHours = Math.round(avgTicketAge * 0.15); // Estimate 15% of total age\nconst avgResolutionTimeHours = avgTicketAge;\n\n// Enhanced CSAT score calculation\n// In production, fetch actual satisfaction ratings from both platforms\nlet csatScore = 0;\nlet csatCount = 0;\n\n// Simulate CSAT based on platform and performance metrics\ntickets.forEach(ticket => {\n  if (isTicketResolved(ticket)) {\n    // Simulate CSAT based on resolution time and priority\n    let simulatedCsat = 4.5;\n    \n    if (ticket.ticket_age_hours > thresholds.resolution_time_hours * 2) {\n      simulatedCsat -= 1.0;\n    } else if (ticket.ticket_age_hours > thresholds.resolution_time_hours) {\n      simulatedCsat -= 0.5;\n    }\n    \n    if (ticket.priority === 'urgent' || ticket.priority === 'high') {\n      simulatedCsat -= 0.2;\n    }\n    \n    // Add some randomness\n    simulatedCsat += (Math.random() - 0.5) * 0.6;\n    \n    // Ensure bounds\n    simulatedCsat = Math.max(1, Math.min(5, simulatedCsat));\n    \n    csatScore += simulatedCsat;\n    csatCount++;\n  }\n});\n\nconst avgCsatScore = csatCount > 0 ? Math.round((csatScore / csatCount) * 100) / 100 : 4.0;\n\n// Current timestamp\nconst now = new Date();\nconst dateString = now.toISOString().split('T')[0];\nconst timestamp = now.toISOString();\n\n// Build comprehensive KPI object\nconst kpis = {\n  // Metadata\n  date: dateString,\n  timestamp: timestamp,\n  calculation_time: now.toLocaleString(),\n  platforms_included: Object.keys(platformDistribution).filter(p => platformDistribution[p] > 0),\n  \n  // Volume Metrics\n  total_tickets: totalTickets,\n  open_tickets: openTickets,\n  resolved_tickets: resolvedTickets,\n  urgent_tickets: urgentTickets,\n  high_priority_tickets: highPriorityTickets,\n  needs_attention_count: needsAttentionCount,\n  sla_breached_tickets: slaBreachedTickets,\n  escalated_tickets: escalatedTickets,\n  overdue_tickets: overdueTickets,\n  \n  // Performance Metrics\n  avg_ticket_age_hours: avgTicketAge,\n  avg_response_time_hours: avgResponseTimeHours,\n  avg_resolution_time_hours: avgResolutionTimeHours,\n  csat_score: avgCsatScore,\n  csat_sample_size: csatCount,\n  \n  // Rate Metrics (Percentages)\n  resolution_rate: resolutionRate,\n  sla_breach_rate: slaBreachRate,\n  urgent_ticket_rate: urgentTicketRate,\n  high_priority_rate: highPriorityRate,\n  attention_required_rate: attentionRate,\n  escalation_rate: escalationRate,\n  overdue_rate: overdueRate,\n  \n  // Distribution Analysis\n  priority_distribution: priorityDistribution,\n  channel_distribution: channelDistribution,\n  platform_distribution: platformDistribution,\n  platform_rates: platformRates,\n  \n  // Thresholds for Reference\n  thresholds: thresholds\n};\n\n// Enhanced Alert Conditions\nkpis.alerts = {\n  high_sla_breach: slaBreachRate > 20,\n  high_urgent_rate: urgentTicketRate > 15,\n  high_backlog: openTickets > 10,\n  low_resolution_rate: resolutionRate < 70,\n  high_attention_rate: attentionRate > 50,\n  long_avg_age: avgTicketAge > thresholds.resolution_time_hours,\n  low_csat: avgCsatScore < thresholds.csat_threshold,\n  high_escalation_rate: escalationRate > 10,\n  high_overdue_rate: overdueRate > 5\n};\n\n// Overall Alert Flag\nkpis.any_alert = Object.values(kpis.alerts).some(alert => alert === true);\nkpis.alert_count = Object.values(kpis.alerts).filter(alert => alert === true).length;\n\n// Enhanced Performance Grading System\nlet gradePoints = 0;\n\n// Resolution rate (20 points max)\nif (resolutionRate >= 90) gradePoints += 20;\nelse if (resolutionRate >= 80) gradePoints += 16;\nelse if (resolutionRate >= 70) gradePoints += 12;\nelse if (resolutionRate >= 60) gradePoints += 8;\n\n// SLA compliance (20 points max)\nif (slaBreachRate <= 5) gradePoints += 20;\nelse if (slaBreachRate <= 10) gradePoints += 16;\nelse if (slaBreachRate <= 15) gradePoints += 12;\nelse if (slaBreachRate <= 20) gradePoints += 8;\n\n// Response time performance (20 points max)\nif (avgResponseTimeHours <= 2) gradePoints += 20;\nelse if (avgResponseTimeHours <= 4) gradePoints += 16;\nelse if (avgResponseTimeHours <= 8) gradePoints += 12;\nelse if (avgResponseTimeHours <= 12) gradePoints += 8;\n\n// Customer satisfaction (20 points max)\nif (avgCsatScore >= 4.5) gradePoints += 20;\nelse if (avgCsatScore >= 4.0) gradePoints += 16;\nelse if (avgCsatScore >= 3.5) gradePoints += 12;\nelse if (avgCsatScore >= 3.0) gradePoints += 8;\n\n// Platform-specific metrics (20 points max)\nlet platformBonus = 0;\nif (escalationRate <= 5) platformBonus += 10;\nelse if (escalationRate <= 10) platformBonus += 6;\n\nif (overdueRate <= 2) platformBonus += 10;\nelse if (overdueRate <= 5) platformBonus += 6;\n\ngradePoints += platformBonus;\n\nkpis.performance_grade = gradePoints >= 85 ? 'A' : gradePoints >= 70 ? 'B' : gradePoints >= 55 ? 'C' : 'D';\nkpis.grade_percentage = gradePoints;\nkpis.grade_description = \n  gradePoints >= 85 ? 'Excellent Performance' :\n  gradePoints >= 70 ? 'Good Performance' :\n  gradePoints >= 55 ? 'Needs Improvement' : 'Critical Issues';\n\n// Enhanced Productivity Insights\nkpis.insights = {\n  busiest_channel: Object.keys(channelDistribution).length > 0 ? \n    Object.keys(channelDistribution).reduce((a, b) => \n      channelDistribution[a] > channelDistribution[b] ? a : b) : 'none',\n  dominant_priority: Object.keys(priorityDistribution).length > 0 ?\n    Object.keys(priorityDistribution).reduce((a, b) => \n      priorityDistribution[a] > priorityDistribution[b] ? a : b) : 'none',\n  primary_platform: Object.keys(platformDistribution).length > 0 ?\n    Object.keys(platformDistribution).reduce((a, b) => \n      platformDistribution[a] > platformDistribution[b] ? a : b) : 'none',\n  tickets_per_priority: priorityDistribution,\n  tickets_per_channel: channelDistribution,\n  tickets_per_platform: platformDistribution,\n  oldest_ticket_age: tickets.length > 0 ? Math.max(...tickets.map(t => t.ticket_age_hours || 0)) : 0,\n  multi_platform: Object.keys(platformDistribution).filter(p => platformDistribution[p] > 0).length > 1\n};\n\n// Enhanced Recommendations based on KPIs\nkpis.recommendations = [];\n\nif (slaBreachRate > 15) {\n  kpis.recommendations.push(\"High SLA breach rate detected - consider increasing support staff or reviewing priority workflows\");\n}\nif (urgentTicketRate > 20) {\n  kpis.recommendations.push(\"High percentage of urgent tickets - review escalation criteria or provide customer education\");\n}\nif (avgTicketAge > 36) {\n  kpis.recommendations.push(\"Long average ticket age - implement automated routing and priority queue management\");\n}\nif (needsAttentionCount > totalTickets * 0.4) {\n  kpis.recommendations.push(\"High attention-required rate - consider workflow automation or additional training\");\n}\nif (resolutionRate < 75) {\n  kpis.recommendations.push(\"Low resolution rate - focus on closing resolved tickets and improving first-contact resolution\");\n}\nif (escalationRate > 10) {\n  kpis.recommendations.push(\"High escalation rate - review first-line resolution capabilities and knowledge base\");\n}\nif (overdueRate > 5) {\n  kpis.recommendations.push(\"High overdue rate - implement better due date tracking and reminder systems\");\n}\nif (kpis.insights.multi_platform) {\n  kpis.recommendations.push(\"Multi-platform support detected - ensure consistent SLA and quality standards across platforms\");\n}\n\n// Platform-specific insights\nif (platformDistribution.freshdesk > 0) {\n  kpis.freshdesk_metrics = {\n    escalated_tickets: escalatedTickets,\n    overdue_tickets: overdueTickets,\n    escalation_rate: escalationRate,\n    overdue_rate: overdueRate\n  };\n}\n\n// Return the comprehensive KPI data (n8n format)\nreturn [{ \n  json: {\n    kpis: kpis,\n    raw_ticket_count: totalTickets,\n    processing_timestamp: timestamp\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "9bf80187-1b1f-4810-baa7-7234d65f5099",
      "name": "Evaluate Alerts",
      "type": "n8n-nodes-base.if",
      "position": [
        1488,
        32
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "or",
          "conditions": [
            {
              "id": "d65c4d80-4613-4790-a08c-57b20182df82",
              "operator": {
                "type": "boolean",
                "operation": "equal"
              },
              "leftValue": "={{ $json.kpis.any_alert }}",
              "rightValue": true
            },
            {
              "id": "998efe97-543f-42d3-8950-55f7fd4ccbac",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.kpis.alerts.high_sla_breach }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "d9a716a3-9732-4d92-b5c3-d84881ca015f",
      "name": "Generate Slack Alert Message",
      "type": "n8n-nodes-base.code",
      "position": [
        1712,
        -64
      ],
      "parameters": {
        "jsCode": "// N8N Code Node - Format Support KPIs for Slack Alert\nconst data = $json.kpis;\n\n// Function to get grade emoji\nfunction getGradeEmoji(grade) {\n  const gradeEmojis = {\n    'A': '\ud83d\udfe2',\n    'B': '\ud83d\udfe1',\n    'C': '\ud83d\udfe0',\n    'D': '\ud83d\udd34',\n    'F': '\u26d4'\n  };\n  return gradeEmojis[grade] || '\u2753';\n}\n\n// Function to get alert emoji\nfunction getAlertEmoji(hasAlert) {\n  return hasAlert ? '\ud83d\udea8' : '\u2705';\n}\n\n// Format priority distribution\nfunction formatPriorityDistribution(priorities) {\n  return Object.entries(priorities)\n    .filter(([_, count]) => count > 0)\n    .map(([priority, count]) => `${priority.charAt(0).toUpperCase() + priority.slice(1)}: ${count}`)\n    .join(' | ');\n}\n\n// Format channel distribution\nfunction formatChannelDistribution(channels) {\n  return Object.entries(channels)\n    .map(([channel, count]) => `${channel.toUpperCase()}: ${count}`)\n    .join(' | ');\n}\n\n// Build the main message\nlet message = `${getAlertEmoji(data.any_alert)} *Support Ticket Dashboard Alert*\\n`;\nmessage += `\ud83d\udcca *Performance Grade:* ${getGradeEmoji(data.performance_grade)} ${data.performance_grade} (${data.grade_percentage}%) - ${data.grade_description}\\n\\n`;\n\n// Key Metrics Section\nmessage += `*\ud83d\udcc8 Key Metrics*\\n`;\nmessage += `\u2022 Total Tickets: ${data.total_tickets}\\n`;\nmessage += `\u2022 Open: ${data.open_tickets} | Resolved: ${data.resolved_tickets}\\n`;\nmessage += `\u2022 Resolution Rate: ${data.resolution_rate}%\\n`;\nmessage += `\u2022 CSAT Score: ${data.csat_score}/5.0\\n\\n`;\n\n// Performance Metrics\nmessage += `*\u23f1\ufe0f Performance Metrics*\\n`;\nmessage += `\u2022 Avg Response Time: ${data.avg_response_time_hours}h\\n`;\nmessage += `\u2022 Avg Resolution Time: ${data.avg_resolution_time_hours}h\\n`;\nmessage += `\u2022 Avg Ticket Age: ${data.avg_ticket_age_hours}h\\n\\n`;\n\n// Priority & Channel Breakdown\nmessage += `*\ud83c\udfaf Breakdown*\\n`;\nmessage += `\u2022 Priority: ${formatPriorityDistribution(data.priority_distribution)}\\n`;\nmessage += `\u2022 Channels: ${formatChannelDistribution(data.channel_distribution)}\\n\\n`;\n\n// Alerts Section (only if there are alerts)\nif (data.any_alert) {\n  message += `*\ud83d\udea8 Active Alerts (${data.alert_count})*\\n`;\n  \n  if (data.alerts.high_sla_breach) {\n    message += `\u2022 \u26a0\ufe0f High SLA Breach Rate: ${data.sla_breach_rate}%\\n`;\n  }\n  if (data.alerts.high_attention_rate) {\n    message += `\u2022 \u26a0\ufe0f High Attention Required Rate: ${data.attention_required_rate}%\\n`;\n  }\n  if (data.alerts.long_avg_age) {\n    message += `\u2022 \u26a0\ufe0f Long Average Ticket Age: ${data.avg_ticket_age_hours}h\\n`;\n  }\n  if (data.alerts.high_urgent_rate) {\n    message += `\u2022 \u26a0\ufe0f High Urgent Ticket Rate: ${data.urgent_ticket_rate}%\\n`;\n  }\n  if (data.alerts.high_backlog) {\n    message += `\u2022 \u26a0\ufe0f High Backlog: ${data.open_tickets} open tickets\\n`;\n  }\n  if (data.alerts.low_resolution_rate) {\n    message += `\u2022 \u26a0\ufe0f Low Resolution Rate: ${data.resolution_rate}%\\n`;\n  }\n  if (data.alerts.low_csat) {\n    message += `\u2022 \u26a0\ufe0f Low CSAT Score: ${data.csat_score}/5.0\\n`;\n  }\n  message += `\\n`;\n}\n\n// Recommendations Section\nif (data.recommendations && data.recommendations.length > 0) {\n  message += `*\ud83d\udca1 Recommendations*\\n`;\n  data.recommendations.forEach(rec => {\n    message += `\u2022 ${rec}\\n`;\n  });\n  message += `\\n`;\n}\n\n// Footer with timestamp\nconst reportTime = new Date(data.timestamp).toLocaleString();\nmessage += `_Report generated: ${reportTime}_`;\n\n// Return the formatted message for Slack\nreturn {\n  json: {\n    text: message,\n    // Optional: Add structured blocks for better formatting\n    blocks: [\n      {\n        \"type\": \"header\",\n        \"text\": {\n          \"type\": \"plain_text\",\n          \"text\": `${getAlertEmoji(data.any_alert)} Support Dashboard Alert`,\n          \"emoji\": true\n        }\n      },\n      {\n        \"type\": \"section\",\n        \"text\": {\n          \"type\": \"mrkdwn\",\n          \"text\": message\n        }\n      }\n    ]\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "fbdbe576-9d94-4328-933e-062e2ba090fb",
      "name": "Generate Weekly HTML Report",
      "type": "n8n-nodes-base.code",
      "position": [
        1712,
        128
      ],
      "parameters": {
        "jsCode": "// N8N Code Node - Generate Corporate Style HTML Email Report\n// Handle different data structures\nlet data;\nif ($json.kpis) {\n    data = $json.kpis;\n} else if ($json[0] && $json[0].kpis) {\n    data = $json[0].kpis;\n} else if (Array.isArray($json) && $json[0] && $json[0].kpis) {\n    data = $json[0].kpis;\n} else {\n    data = $json;\n}\n\n// Validate data\nif (!data || !data.performance_grade) {\n    return {\n        json: {\n            error: \"Invalid data structure. Expected KPIs object with performance_grade property.\",\n            receivedData: $json\n        }\n    };\n}\n\n// Function to get grade styling\nfunction getGradeStyle(grade) {\n    const styles = {\n        'A': { color: '#059669', bg: '#ECFDF5', border: '#10B981' },\n        'B': { color: '#D97706', bg: '#FFFBEB', border: '#F59E0B' },\n        'C': { color: '#DC2626', bg: '#FEF2F2', border: '#EF4444' },\n        'D': { color: '#B91C1C', bg: '#FEF2F2', border: '#DC2626' },\n        'F': { color: '#991B1B', bg: '#FEF2F2', border: '#B91C1C' }\n    };\n    return styles[grade] || { color: '#6B7280', bg: '#F9FAFB', border: '#D1D5DB' };\n}\n\n// Format timestamp\nfunction formatTimestamp(timestamp) {\n    return new Date(timestamp).toLocaleDateString('en-US', {\n        year: 'numeric',\n        month: 'long',\n        day: 'numeric',\n        hour: '2-digit',\n        minute: '2-digit'\n    });\n}\n\nconst gradeStyle = getGradeStyle(data.performance_grade);\nconst alertStatus = data.any_alert ? 'ALERT' : 'HEALTHY';\nconst alertColor = data.any_alert ? '#DC2626' : '#059669';\n\n// Generate corporate-style HTML email\nconst htmlReport = `\n<!DOCTYPE html>\n<html>\n<head>\n    <meta charset=\"utf-8\">\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n    <title>Support Dashboard Report</title>\n    <!--[if mso]>\n    <noscript>\n        <xml>\n            <o:OfficeDocumentSettings>\n                <o:PixelsPerInch>96</o:PixelsPerInch>\n            </o:OfficeDocumentSettings>\n        </xml>\n    </noscript>\n    <![endif]-->\n</head>\n<body style=\"margin: 0; padding: 0; font-family: Arial, sans-serif; background-color: #f4f4f4;\">\n    <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #f4f4f4; padding: 20px 0;\">\n        <tr>\n            <td align=\"center\">\n                <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"600\" style=\"background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.1);\">\n                    \n                    <!-- Header -->\n                    <tr>\n                        <td style=\"background: linear-gradient(135deg, #1e40af 0%, #3730a3 100%); padding: 30px; text-align: center;\">\n                            <h1 style=\"color: #ffffff; margin: 0; font-size: 24px; font-weight: 600;\">Support Dashboard Report</h1>\n                            <p style=\"color: #e0e7ff; margin: 8px 0 0 0; font-size: 16px;\">Daily Performance Analysis</p>\n                        </td>\n                    </tr>\n\n                    <!-- Status Banner -->\n                    <tr>\n                        <td style=\"padding: 0;\">\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                <tr>\n                                    <td style=\"background-color: ${alertColor}; color: #ffffff; text-align: center; padding: 12px; font-weight: 600; font-size: 14px; letter-spacing: 0.5px;\">\n                                        SYSTEM STATUS: ${alertStatus}\n                                    </td>\n                                </tr>\n                            </table>\n                        </td>\n                    </tr>\n\n                    <!-- Performance Grade Section -->\n                    <tr>\n                        <td style=\"padding: 30px;\">\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                <tr>\n                                    <td style=\"text-align: center; padding: 25px; background-color: ${gradeStyle.bg}; border: 2px solid ${gradeStyle.border}; border-radius: 8px;\">\n                                        <div style=\"font-size: 48px; font-weight: 900; color: ${gradeStyle.color}; line-height: 1; margin-bottom: 10px;\">${data.performance_grade}</div>\n                                        <div style=\"font-size: 18px; color: ${gradeStyle.color}; font-weight: 600;\">${data.grade_percentage}% - ${data.grade_description}</div>\n                                    </td>\n                                </tr>\n                            </table>\n                        </td>\n                    </tr>\n\n                    <!-- Key Metrics Grid -->\n                    <tr>\n                        <td style=\"padding: 0 30px 30px 30px;\">\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                <tr>\n                                    <td style=\"padding-right: 10px; width: 25%;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;\">\n                                            <tr>\n                                                <td style=\"text-align: center; padding: 20px;\">\n                                                    <div style=\"font-size: 28px; font-weight: 700; color: #1e40af; line-height: 1; margin-bottom: 5px;\">${data.total_tickets}</div>\n                                                    <div style=\"font-size: 12px; color: #64748b; font-weight: 500; text-transform: uppercase; letter-spacing: 0.5px;\">Total Tickets</div>\n                                                </td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                    <td style=\"padding-right: 10px; width: 25%;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #f0fdf4; border: 1px solid #bbf7d0; border-radius: 6px;\">\n                                            <tr>\n                                                <td style=\"text-align: center; padding: 20px;\">\n                                                    <div style=\"font-size: 28px; font-weight: 700; color: #059669; line-height: 1; margin-bottom: 5px;\">${data.resolved_tickets}</div>\n                                                    <div style=\"font-size: 12px; color: #064e3b; font-weight: 500; text-transform: uppercase; letter-spacing: 0.5px;\">Resolved</div>\n                                                </td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                    <td style=\"padding-right: 10px; width: 25%;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: ${data.open_tickets > 0 ? '#fef3c7' : '#f0fdf4'}; border: 1px solid ${data.open_tickets > 0 ? '#fbbf24' : '#bbf7d0'}; border-radius: 6px;\">\n                                            <tr>\n                                                <td style=\"text-align: center; padding: 20px;\">\n                                                    <div style=\"font-size: 28px; font-weight: 700; color: ${data.open_tickets > 0 ? '#d97706' : '#059669'}; line-height: 1; margin-bottom: 5px;\">${data.open_tickets}</div>\n                                                    <div style=\"font-size: 12px; color: ${data.open_tickets > 0 ? '#92400e' : '#064e3b'}; font-weight: 500; text-transform: uppercase; letter-spacing: 0.5px;\">Open</div>\n                                                </td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                    <td style=\"width: 25%;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #faf5ff; border: 1px solid #d8b4fe; border-radius: 6px;\">\n                                            <tr>\n                                                <td style=\"text-align: center; padding: 20px;\">\n                                                    <div style=\"font-size: 28px; font-weight: 700; color: #7c3aed; line-height: 1; margin-bottom: 5px;\">${data.csat_score}</div>\n                                                    <div style=\"font-size: 12px; color: #581c87; font-weight: 500; text-transform: uppercase; letter-spacing: 0.5px;\">CSAT Score</div>\n                                                </td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                </tr>\n                            </table>\n                        </td>\n                    </tr>\n\n                    <!-- Performance Metrics Section -->\n                    <tr>\n                        <td style=\"padding: 0 30px 30px 30px;\">\n                            <h2 style=\"color: #1f2937; font-size: 18px; font-weight: 600; margin: 0 0 15px 0; border-bottom: 2px solid #e5e7eb; padding-bottom: 8px;\">Performance Metrics</h2>\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #f8fafc; border-radius: 6px; border: 1px solid #e2e8f0;\">\n                                <tr>\n                                    <td style=\"padding: 15px 20px; border-bottom: 1px solid #e2e8f0;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                            <tr>\n                                                <td style=\"font-weight: 500; color: #374151;\">Average Response Time</td>\n                                                <td align=\"right\" style=\"font-weight: 600; color: ${data.avg_response_time_hours <= data.thresholds.response_time_hours ? '#059669' : '#dc2626'};\">${data.avg_response_time_hours}h</td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                </tr>\n                                <tr>\n                                    <td style=\"padding: 15px 20px; border-bottom: 1px solid #e2e8f0;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                            <tr>\n                                                <td style=\"font-weight: 500; color: #374151;\">Average Resolution Time</td>\n                                                <td align=\"right\" style=\"font-weight: 600; color: ${data.avg_resolution_time_hours <= data.thresholds.resolution_time_hours ? '#059669' : '#dc2626'};\">${data.avg_resolution_time_hours}h</td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                </tr>\n                                <tr>\n                                    <td style=\"padding: 15px 20px; border-bottom: 1px solid #e2e8f0;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                            <tr>\n                                                <td style=\"font-weight: 500; color: #374151;\">Resolution Rate</td>\n                                                <td align=\"right\" style=\"font-weight: 600; color: #059669;\">${data.resolution_rate}%</td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                </tr>\n                                <tr>\n                                    <td style=\"padding: 15px 20px;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                            <tr>\n                                                <td style=\"font-weight: 500; color: #374151;\">SLA Breach Rate</td>\n                                                <td align=\"right\" style=\"font-weight: 600; color: ${data.sla_breach_rate > 20 ? '#dc2626' : '#059669'};\">${data.sla_breach_rate}%</td>\n                                            </tr>\n                                        </table>\n                                    </td>\n                                </tr>\n                            </table>\n                        </td>\n                    </tr>\n\n                    ${data.any_alert ? `\n                    <!-- Alerts Section -->\n                    <tr>\n                        <td style=\"padding: 0 30px 30px 30px;\">\n                            <h2 style=\"color: #dc2626; font-size: 18px; font-weight: 600; margin: 0 0 15px 0; border-bottom: 2px solid #fecaca; padding-bottom: 8px;\">\u26a0\ufe0f Active Alerts (${data.alert_count})</h2>\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #fef2f2; border-radius: 6px; border: 1px solid #fecaca;\">\n                                ${data.alerts.high_sla_breach ? `\n                                <tr>\n                                    <td style=\"padding: 12px 20px; border-bottom: 1px solid #fecaca; color: #b91c1c; font-weight: 500;\">\n                                        \ud83d\udd34 High SLA Breach Rate: ${data.sla_breach_rate}%\n                                    </td>\n                                </tr>` : ''}\n                                ${data.alerts.high_attention_rate ? `\n                                <tr>\n                                    <td style=\"padding: 12px 20px; ${data.alerts.long_avg_age ? 'border-bottom: 1px solid #fecaca;' : ''} color: #b91c1c; font-weight: 500;\">\n                                        \ud83d\udfe0 High Attention Required Rate: ${data.attention_required_rate}%\n                                    </td>\n                                </tr>` : ''}\n                                ${data.alerts.long_avg_age ? `\n                                <tr>\n                                    <td style=\"padding: 12px 20px; color: #b91c1c; font-weight: 500;\">\n                                        \u23f0 Long Average Ticket Age: ${data.avg_ticket_age_hours}h\n                                    </td>\n                                </tr>` : ''}\n                            </table>\n                        </td>\n                    </tr>\n                    ` : ''}\n\n                    ${data.recommendations && data.recommendations.length > 0 ? `\n                    <!-- Recommendations Section -->\n                    <tr>\n                        <td style=\"padding: 0 30px 30px 30px;\">\n                            <h2 style=\"color: #1e40af; font-size: 18px; font-weight: 600; margin: 0 0 15px 0; border-bottom: 2px solid #dbeafe; padding-bottom: 8px;\">\ud83d\udca1 Recommendations</h2>\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #eff6ff; border-radius: 6px; border: 1px solid #bfdbfe;\">\n                                ${data.recommendations.map((rec, index) => `\n                                <tr>\n                                    <td style=\"padding: 12px 20px; ${index < data.recommendations.length - 1 ? 'border-bottom: 1px solid #bfdbfe;' : ''} color: #1e40af; font-weight: 500;\">\n                                        \u2022 ${rec}\n                                    </td>\n                                </tr>`).join('')}\n                            </table>\n                        </td>\n                    </tr>\n                    ` : ''}\n\n                    <!-- Distribution Section -->\n                    <tr>\n                        <td style=\"padding: 0 30px 30px 30px;\">\n                            <h2 style=\"color: #1f2937; font-size: 18px; font-weight: 600; margin: 0 0 15px 0; border-bottom: 2px solid #e5e7eb; padding-bottom: 8px;\">Distribution Analysis</h2>\n                            <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                <tr>\n                                    <td style=\"padding-right: 15px; width: 50%; vertical-align: top;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #ffffff; border: 1px solid #e2e8f0; border-radius: 6px;\">\n                                            <tr>\n                                                <td style=\"padding: 15px 20px; background-color: #f8fafc; font-weight: 600; color: #374151; border-bottom: 1px solid #e2e8f0;\">Priority Distribution</td>\n                                            </tr>\n                                            ${Object.entries(data.priority_distribution).map(([priority, count]) => `\n                                            <tr>\n                                                <td style=\"padding: 10px 20px; border-bottom: 1px solid #f1f5f9;\">\n                                                    <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                                        <tr>\n                                                            <td style=\"text-transform: capitalize; color: #64748b;\">${priority}</td>\n                                                            <td align=\"right\" style=\"font-weight: 600; color: #1f2937;\">${count}</td>\n                                                        </tr>\n                                                    </table>\n                                                </td>\n                                            </tr>`).join('')}\n                                        </table>\n                                    </td>\n                                    <td style=\"width: 50%; vertical-align: top;\">\n                                        <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\" style=\"background-color: #ffffff; border: 1px solid #e2e8f0; border-radius: 6px;\">\n                                            <tr>\n                                                <td style=\"padding: 15px 20px; background-color: #f8fafc; font-weight: 600; color: #374151; border-bottom: 1px solid #e2e8f0;\">Channel Distribution</td>\n                                            </tr>\n                                            ${Object.entries(data.channel_distribution).map(([channel, count]) => `\n                                            <tr>\n                                                <td style=\"padding: 10px 20px; border-bottom: 1px solid #f1f5f9;\">\n                                                    <table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n                                                        <tr>\n                                                            <td style=\"text-transform: uppercase; color: #64748b;\">${channel}</td>\n                                                            <td align=\"right\" style=\"font-weight: 600; color: #1f2937;\">${count}</td>\n                                                        </tr>\n                                                    </table>\n                                                </td>\n                                            </tr>`).join('')}\n                                        </table>\n                                    </td>\n                                </tr>\n                            </table>\n                        </td>\n                    </tr>\n\n                    <!-- Footer -->\n                    <tr>\n                        <td style=\"background-color: #f8fafc; padding: 20px; text-align: center; border-top: 1px solid #e2e8f0;\">\n                            <p style=\"margin: 0; color: #64748b; font-size: 14px; line-height: 1.5;\">\n                                <strong>Report Generated:</strong> ${formatTimestamp(data.timestamp)}<br>\n                                Support Dashboard Analytics System\n                            </p>\n                        </td>\n                    </tr>\n\n                </table>\n            </td>\n        </tr>\n    </table>\n</body>\n</html>\n`;\n\n// Return the corporate-style HTML report\nreturn {\n    json: {\n        html: htmlReport,\n        subject: `Support Dashboard Report - Grade ${data.performance_grade} | ${alertStatus}`,\n        plainText: `Support Ticket Dashboard Report\n\nPerformance Grade: ${data.performance_grade} (${data.grade_percentage}%) - ${data.grade_description}\nStatus: ${alertStatus}\n\nKey Metrics:\n- Total Tickets: ${data.total_tickets}\n- Open: ${data.open_tickets} | Resolved: ${data.resolved_tickets}\n- Resolution Rate: ${data.resolution_rate}%\n- CSAT Score: ${data.csat_score}/5.0\n\nPerformance:\n- Avg Response Time: ${data.avg_response_time_hours}h\n- Avg Resolution Time: ${data.avg_resolution_time_hours}h\n- SLA Breach Rate: ${data.sla_breach_rate}%\n\n${data.any_alert ? `Active Alerts: ${data.alert_count}` : 'No active alerts'}\n\nReport generated: ${formatTimestamp(data.timestamp)}`\n    }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "a8dd2d83-a156-4290-a846-19cf9abd1591",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -96
      ],
      "parameters": {
        "height": 352,
        "content": "## \u23f0 Weekly Cron Trigger\n\nThis node runs the workflow automatically on a **weekly schedule**.  \nConfigured to execute every week at the specified hour (20:00).\n\n**Purpose:**\n- Ensures weekly performance reporting.\n- Automates ticket fetching and KPI calculations.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "94e3f206-14af-4b8d-a162-f08942b823a2",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        112,
        -368
      ],
      "parameters": {
        "height": 336,
        "content": "## \ud83c\udf9f\ufe0f Fetch Tickets from Zendesk\n\nFetches all ticket data from your **Zendesk account**.\n\n**Details:**\n- Uses Zendesk API credentials.\n- Retrieves tickets with statuses (open, pending, solved, closed).\n- Provides raw ticket data for further formatting."
      },
      "typeVersion": 1
    },
    {
      "id": "862bbe05-8d70-4c61-aeab-9e8c0983610e",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1392,
        192
      ],
      "parameters": {
        "height": 400,
        "content": "## \ud83d\udea8 Evaluate Alerts\n\nChecks if KPI thresholds have been exceeded and if alerts need to be triggered.\n\n**Alert Triggers:**\n- SLA breach > 20%\n- Low resolution rate\n- High backlog\n- Low CSAT score\n- High urgent or attention rate\n- Long average ticket age\n\n**Output:**\n- Boolean flag `any_alert`\n- Alert count\n"
      },
      "typeVersion": 1
    },
    {
      "id": "811a9d1b-4a72-4aad-8cd6-b369ca74025e",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        976,
        224
      ],
      "parameters": {
        "height": 336,
        "content": "## \ud83d\udcd1 Log Alerts in Google Sheets\n\nStores ticket and performance data into a **Google Sheet** for auditing.\n\n**Purpose:**\n- Provides a compliance audit trail.\n- Enables historical trend analysis.\n- Logs KPIs and alerts  for future reporting."
      },
      "typeVersion": 1
    },
    {
      "id": "4eb74eb7-93c5-4cc1-bf16-2ae9cac49d59",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1200,
        -512
      ],
      "parameters": {
        "height": 528,
        "content": "## \ud83d\udcca Calculate Support KPIs\n\nProcesses formatted ticket data to compute **key performance metrics**.\n\n**Metrics Calculated:**\n- SLA breach rate\n- Resolution rate\n- Avg response & resolution times\n- CSAT score (simulated or from Zendesk API)\n- Ticket distributions (priority, channel)\n- Attention required tickets\n\n**Also:**\n- Applies thresholds for alerts\n- Generates performance grade (A\u2013D)\n- Builds recommendations list"
      },
      "typeVersion": 1
    },
    {
      "id": "7dc027d7-e021-47c7-9f57-e2b8ac3d0a41",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        736,
        -400
      ],
      "parameters": {
        "height": 416,
        "content": "## \ud83e\uddf9 Format Ticket Data (Zendesk & FreshDesk)\n\nCleans and standardizes raw ticket data from both platforms.\nSteps:\n\nAuto-detects platform source (Zendesk vs FreshDesk)\nNormalizes priority, status, and channel fields across platforms\nAdds calculated fields: ticket age, needs_attention flag\nCreates unified JSON output structure"
      },
      "typeVersion": 1
    },
    {
      "id": "f858aee3-3dd8-4f92-a0af-e3c2dcd9aa5d",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1648,
        -496
      ],
      "parameters": {
        "height": 416,
        "content": "## \ud83d\udcac Generate Slack Alert Message\n\nFormats KPI and alert details into a **Slack-ready message**.\n\n**Message Includes:**\n- Performance grade & percentage\n- Key metrics (tickets, SLA, CSAT)\n- Breakdown by priority & channel\n- Active alerts with emojis\n- Recommendations for improvement\n- Timestamp for traceability"
      },
      "typeVersion": 1
    },
    {
      "id": "a0ac0fc2-4ee4-4cda-a16c-01171d483515",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1664,
        288
      ],
      "parameters": {
        "height": 464,
        "content": "## \ud83d\udce7 Generate Weekly HTML Report\n\nBuilds a **corporate-style HTML email report**.\n\n**Report Sections:**\n- System status (Healthy / Alert)\n- Performance grade (A\u2013D)\n- Key ticket metrics\n- KPI values with thresholds\n- Active alerts\n- Recommendations\n- Distribution analysis (priority & channel)\n\n**Format:** HTML + plain text fallback"
      },
      "typeVersion": 1
    },
    {
      "id": "eaf1f4d6-c806-40d7-80d7-f6a44185a923",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2048,
        -400
      ],
      "parameters": {
        "height": 336,
        "content": "## \ud83d\udce2 Send Alert to Slack\n\nDelivers the formatted alert message into a configured **Slack channel**.\n\n**Purpose:**\n- Immediate visibility for critical issues.\n- Alerts team to SLA breaches, backlog spikes, or low CSAT."
      },
      "typeVersion": 1
    },
    {
      "id": "6bfaa7e3-eb49-4c03-b069-f679de45574a",
      "name": "Sticky Note9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2064,
        224
      ],
      "parameters": {
        "height": 336,
        "content": "## \u2709\ufe0f Email Weekly Report\n\nSends the **weekly HTML report** via Gmail.\n\n**Recipients:** Configured in this node.  \n**Purpose:**\n- Provides managers with weekly performance summaries.\n- Ensures leadership visibility into support KPIs."
      },
      "typeVersion": 1
    },
    {
      "id": "1eb5d03c-4862-4cde-b580-e4c5b6fbf479",
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "position": [
        608,
        32
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "db824881-6986-4114-a95b-e3dfd6a3e7d1",
      "name": "Sticky Note10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        96,
        224
      ],
      "parameters": {
        "height": 352,
        "content": " ## \ud83c\udfab Fetch Tickets from FreshDesk\nFetches all ticket data from your FreshDesk account.\nDetails:\n\nUses FreshDesk API creden

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.

• freshdeskApi
• gmailOAuth2
• googleSheetsOAuth2Api
• slackApi
• zendeskApi

About this workflow

Automatically consolidate Zendesk and Freshdesk ticket data into a unified performance dashboard with KPI calculations, Google Sheets logging, real-time Slack alerts, and weekly Gmail email reports. Provides complete visibility into support operations, SLA compliance, and…

Source: https://n8n.io/workflows/8814/ — original creator credit. Request a take-down →

More Email & Gmail workflows → · Browse all categories →

Related workflows

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