统一支持团队绩效仪表板（Zendesk & Freshdesk → Google Sheets + Slack + Gmail）
高级
这是一个Content Creation, Multimodal AI领域的自动化工作流，包含 24 个节点。主要使用 If, Code, Cron, Gmail, Merge 等节点。 使用Sheets、Slack和Gmail跨Zendesk和Freshdesk跟踪支持绩效指标
前置要求
- •Google 账号和 Gmail API 凭证
- •Slack Bot Token 或 Webhook URL
- •Google Sheets API 凭证
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"id": "QyegIMfwfC0kN3kN",
"meta": {
"instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
"templateCredsSetupCompleted": true
},
"name": "统一支持团队绩效仪表板 (Zendesk & Freshdesk → Google Sheets + Slack + Gmail)",
"tags": [],
"nodes": [
{
"id": "af98b36c-8053-4882-b3ba-2b90fb5c7523",
"name": "发送 Slack 警报",
"type": "n8n-nodes-base.slack",
"position": [
1936,
-64
],
"webhookId": "830d4420-a0bc-4f78-b5d2-178ee2154efc",
"parameters": {
"text": "={{ $json.text }}",
"select": "channel",
"channelId": {
"__rl": true,
"mode": "list",
"value": "C09FM9N8UEA",
"cachedResultName": "zendesk-churn-alerts"
},
"otherOptions": {
"mrkdwn": true
}
},
"credentials": {
"slackApi": {
"id": "rNqvWj9TfChPVRYY",
"name": "Slack account"
}
},
"typeVersion": 2.2
},
{
"id": "df0ba9dc-3737-4030-a225-e58cd3c17a86",
"name": "发送周报邮件",
"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": {
"id": "gEIaWCTvGfYjMSb3",
"name": "Gmail credentials"
}
},
"typeVersion": 1
},
{
"id": "b96bf81b-8486-4545-b23e-518643ca9b89",
"name": "每周触发器",
"type": "n8n-nodes-base.cron",
"position": [
0,
32
],
"parameters": {
"triggerTimes": {
"item": [
{
"hour": 20,
"mode": "everyWeek"
}
]
}
},
"typeVersion": 1
},
{
"id": "bb68236c-376b-4c00-80fd-6db781100bc2",
"name": "从 Zendesk 获取工单",
"type": "n8n-nodes-base.zendesk",
"position": [
368,
-64
],
"parameters": {
"options": {},
"operation": "getAll",
"returnAll": true
},
"credentials": {
"zendeskApi": {
"id": "gyKfBorFigBI1uj8",
"name": "Zendesk account vivek"
}
},
"typeVersion": 1
},
{
"id": "972d1914-47a0-48a3-8018-46519599bc2f",
"name": "在 Google Sheets 中记录 KPI",
"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": {
"id": "kpPEOLCGn963qpoh",
"name": "automations@techdome.ai"
}
},
"typeVersion": 4.4
},
{
"id": "42d5b2ac-2c2b-44ad-bb68-654213112f67",
"name": "计算支持 KPI",
"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-specific 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": "评估警报",
"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": "生成 Slack 警报消息",
"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': '🟢',\n 'B': '🟡',\n 'C': '🟠',\n 'D': '🔴',\n 'F': '⛔'\n };\n return gradeEmojis[grade] || '❓';\n}\n\n// Function to get alert emoji\nfunction getAlertEmoji(hasAlert) {\n return hasAlert ? '🚨' : '✅';\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 += `📊 *Performance Grade:* ${getGradeEmoji(data.performance_grade)} ${data.performance_grade} (${data.grade_percentage}%) - ${data.grade_description}\\n\\n`;\n\n// Key Metrics Section\nmessage += `*📈 Key Metrics*\\n`;\nmessage += `• Total Tickets: ${data.total_tickets}\\n`;\nmessage += `• Open: ${data.open_tickets} | Resolved: ${data.resolved_tickets}\\n`;\nmessage += `• Resolution Rate: ${data.resolution_rate}%\\n`;\nmessage += `• CSAT Score: ${data.csat_score}/5.0\\n\\n`;\n\n// Performance Metrics\nmessage += `*⏱️ Performance Metrics*\\n`;\nmessage += `• Avg Response Time: ${data.avg_response_time_hours}h\\n`;\nmessage += `• Avg Resolution Time: ${data.avg_resolution_time_hours}h\\n`;\nmessage += `• Avg Ticket Age: ${data.avg_ticket_age_hours}h\\n\\n`;\n\n// Priority & Channel Breakdown\nmessage += `*🎯 Breakdown*\\n`;\nmessage += `• Priority: ${formatPriorityDistribution(data.priority_distribution)}\\n`;\nmessage += `• Channels: ${formatChannelDistribution(data.channel_distribution)}\\n\\n`;\n\n// Alerts Section (only if there are alerts)\nif (data.any_alert) {\n message += `*🚨 Active Alerts (${data.alert_count})*\\n`;\n \n if (data.alerts.high_sla_breach) {\n message += `• ⚠️ High SLA Breach Rate: ${data.sla_breach_rate}%\\n`;\n }\n if (data.alerts.high_attention_rate) {\n message += `• ⚠️ High Attention Required Rate: ${data.attention_required_rate}%\\n`;\n }\n if (data.alerts.long_avg_age) {\n message += `• ⚠️ Long Average Ticket Age: ${data.avg_ticket_age_hours}h\\n`;\n }\n if (data.alerts.high_urgent_rate) {\n message += `• ⚠️ High Urgent Ticket Rate: ${data.urgent_ticket_rate}%\\n`;\n }\n if (data.alerts.high_backlog) {\n message += `• ⚠️ High Backlog: ${data.open_tickets} open tickets\\n`;\n }\n if (data.alerts.low_resolution_rate) {\n message += `• ⚠️ Low Resolution Rate: ${data.resolution_rate}%\\n`;\n }\n if (data.alerts.low_csat) {\n message += `• ⚠️ 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 += `*💡 Recommendations*\\n`;\n data.recommendations.forEach(rec => {\n message += `• ${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": "生成每周 HTML 报告",
"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;\">⚠️ 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 🔴 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 🟠 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 ⏰ 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;\">💡 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 • ${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": "便签",
"type": "n8n-nodes-base.stickyNote",
"position": [
-288,
-96
],
"parameters": {
"height": 352,
"content": "## ⏰ 每周定时触发器"
},
"typeVersion": 1
},
{
"id": "94e3f206-14af-4b8d-a162-f08942b823a2",
"name": "便签1",
"type": "n8n-nodes-base.stickyNote",
"position": [
112,
-368
],
"parameters": {
"height": 336,
"content": "## 🎟️ 从 Zendesk 获取工单"
},
"typeVersion": 1
},
{
"id": "862bbe05-8d70-4c61-aeab-9e8c0983610e",
"name": "便签2",
"type": "n8n-nodes-base.stickyNote",
"position": [
1392,
192
],
"parameters": {
"height": 400,
"content": "## 🚨 评估警报"
},
"typeVersion": 1
},
{
"id": "811a9d1b-4a72-4aad-8cd6-b369ca74025e",
"name": "便签3",
"type": "n8n-nodes-base.stickyNote",
"position": [
976,
224
],
"parameters": {
"height": 336,
"content": "## 📑 在 Google Sheets 中记录警报"
},
"typeVersion": 1
},
{
"id": "4eb74eb7-93c5-4cc1-bf16-2ae9cac49d59",
"name": "便签4",
"type": "n8n-nodes-base.stickyNote",
"position": [
1200,
-512
],
"parameters": {
"height": 528,
"content": "## 📊 计算支持 KPI"
},
"typeVersion": 1
},
{
"id": "7dc027d7-e021-47c7-9f57-e2b8ac3d0a41",
"name": "便签5",
"type": "n8n-nodes-base.stickyNote",
"position": [
736,
-400
],
"parameters": {
"height": 416,
"content": "## 🧹 格式化工单数据 (Zendesk & FreshDesk)"
},
"typeVersion": 1
},
{
"id": "f858aee3-3dd8-4f92-a0af-e3c2dcd9aa5d",
"name": "便签6",
"type": "n8n-nodes-base.stickyNote",
"position": [
1648,
-496
],
"parameters": {
"height": 416,
"content": "## 💬 生成 Slack 警报消息"
},
"typeVersion": 1
},
{
"id": "a0ac0fc2-4ee4-4cda-a16c-01171d483515",
"name": "便签7",
"type": "n8n-nodes-base.stickyNote",
"position": [
1664,
288
],
"parameters": {
"height": 464,
"content": "## 📧 生成每周 HTML 报告"
},
"typeVersion": 1
},
{
"id": "eaf1f4d6-c806-40d7-80d7-f6a44185a923",
"name": "便签8",
"type": "n8n-nodes-base.stickyNote",
"position": [
2048,
-400
],
"parameters": {
"height": 336,
"content": "## 📢 发送警报到 Slack"
},
"typeVersion": 1
},
{
"id": "6bfaa7e3-eb49-4c03-b069-f679de45574a",
"name": "便签9",
"type": "n8n-nodes-base.stickyNote",
"position": [
2064,
224
],
"parameters": {
"height": 336,
"content": "## ✉️ 邮件发送周报"
},
"typeVersion": 1
},
{
"id": "1eb5d03c-4862-4cde-b580-e4c5b6fbf479",
"name": "合并",
"type": "n8n-nodes-base.merge",
"position": [
608,
32
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "db824881-6986-4114-a95b-e3dfd6a3e7d1",
"name": "便签10",
"type": "n8n-nodes-base.stickyNote",
"position": [
96,
224
],
"parameters": {
"height": 352,
"content": "## 🎫 从 FreshDesk 获取工单"
},
"typeVersion": 1
},
{
"id": "ced09d5d-a36c-4979-82e1-4375f87df16d",
"name": "便利贴11",
"type": "n8n-nodes-base.stickyNote",
"position": [
528,
192
],
"parameters": {
"height": 352,
"content": "## 🔗 合并工单数据"
},
"typeVersion": 1
},
{
"id": "3860e769-9d69-4564-9c3a-5584c01e4510",
"name": "格式化工单数据 (Zendesk & Freshdesk)",
"type": "n8n-nodes-base.code",
"position": [
816,
32
],
"parameters": {
"jsCode": "// N8N Code Node - Unified Ticket Formatter (Zendesk & Freshdesk)\n// This code processes both Zendesk and Freshdesk ticket data and formats them into a clean unified JSON structure\n\n// Function to detect ticket source\nfunction detectTicketSource(ticketData) {\n // Zendesk tickets have 'via' field and specific URL structure\n if (ticketData.via || ticketData.url?.includes('zendesk.com')) {\n return 'zendesk';\n }\n // Freshdesk tickets have 'source' as number and different structure\n if (typeof ticketData.source === 'number' || ticketData.email_config_id) {\n return 'freshdesk';\n }\n return 'unknown';\n}\n\n// Function to format Zendesk ticket data\nfunction formatZendeskTicket(ticketData) {\n const createdAt = new Date(ticketData.created_at);\n const updatedAt = new Date(ticketData.updated_at);\n const now = new Date();\n \n return {\n platform: 'zendesk',\n ticket_id: ticketData.id,\n ticket_url: `https://softwarecompany-66332.zendesk.com/agent/tickets/${ticketData.id}`,\n subject: ticketData.subject || \"No Subject\",\n priority: ticketData.priority || \"normal\",\n priority_level: getZendeskPriorityLevel(ticketData.priority),\n status: ticketData.status,\n status_normalized: normalizeZendeskStatus(ticketData.status),\n created_timestamp: createdAt.toISOString(),\n updated_timestamp: updatedAt.toISOString(),\n original_created_at: ticketData.created_at,\n description_preview: cleanDescription(ticketData.description),\n description: ticketData.description || \"No Description\",\n tags: Array.isArray(ticketData.tags) ? ticketData.tags.join(', ') : '',\n requester_id: ticketData.requester_id,\n assignee_id: ticketData.assignee_id || 'Unassigned',\n channel: ticketData.via?.channel || \"unknown\",\n ticket_age_hours: Math.round((now - createdAt) / (1000 * 60 * 60)),\n needs_attention: checkZendeskNeedsAttention(ticketData, now, createdAt),\n ticket_type: ticketData.type || 'ticket'\n };\n}\n\n// Function to format Freshdesk ticket data\nfunction formatFreshdeskTicket(ticketData) {\n const createdAt = new Date(ticketData.created_at);\n const updatedAt = new Date(ticketData.updated_at);\n const now = new Date();\n \n return {\n platform: 'freshdesk',\n ticket_id: ticketData.id,\n ticket_url: `https://your-domain.freshdesk.com/helpdesk/tickets/${ticketData.id}`, // Update with your domain\n subject: ticketData.subject || \"No Subject\",\n priority: getFreshdeskPriorityText(ticketData.priority),\n priority_level: ticketData.priority || 1,\n status: getFreshdeskStatusText(ticketData.status),\n status_normalized: normalizeFreshdeskStatus(ticketData.status),\n created_timestamp: createdAt.toISOString(),\n updated_timestamp: updatedAt.toISOString(),\n original_created_at: ticketData.created_at,\n description_preview: cleanDescription(ticketData.structured_description),\n description: ticketData.structured_description || \"No Description\",\n tags: Array.isArray(ticketData.tags) ? ticketData.tags.join(', ') : '',\n requester_id: ticketData.requester_id,\n assignee_id: ticketData.responder_id || 'Unassigned',\n channel: getFreshdeskSourceText(ticketData.source),\n ticket_age_hours: Math.round((now - createdAt) / (1000 * 60 * 60)),\n needs_attention: checkFreshdeskNeedsAttention(ticketData, now, createdAt),\n ticket_type: ticketData.type || 'ticket',\n due_by: ticketData.due_by,\n is_escalated: ticketData.is_escalated || false,\n sentiment_score: ticketData.sentiment_score\n };\n}\n\n// Helper functions for Zendesk\nfunction getZendeskPriorityLevel(priority) {\n const levels = {\n 'low': 1,\n 'normal': 2,\n 'high': 3,\n 'urgent': 4\n };\n return levels[priority] || 2;\n}\n\nfunction normalizeZendeskStatus(status) {\n const statusMap = {\n 'new': 'open',\n 'open': 'open',\n 'pending': 'pending',\n 'solved': 'closed',\n 'closed': 'closed'\n };\n return statusMap[status] || status;\n}\n\nfunction checkZendeskNeedsAttention(ticketData, now, createdAt) {\n const ageHours = Math.round((now - createdAt) / (1000 * 60 * 60));\n const hasUrgentTag = (ticketData.tags || []).includes('urgent');\n const isHighPriority = ['high', 'urgent'].includes(ticketData.priority);\n \n return ageHours > 24 || hasUrgentTag || isHighPriority;\n}\n\n// Helper functions for Freshdesk\nfunction getFreshdeskPriorityText(priority) {\n const priorityMap = {\n 1: 'low',\n 2: 'medium',\n 3: 'high',\n 4: 'urgent'\n };\n return priorityMap[priority] || 'medium';\n}\n\nfunction getFreshdeskStatusText(status) {\n const statusMap = {\n 2: 'open',\n 3: 'pending',\n 4: 'resolved',\n 5: 'closed'\n };\n return statusMap[status] || 'open';\n}\n\nfunction normalizeFreshdeskStatus(status) {\n const statusMap = {\n 2: 'open',\n 3: 'pending',\n 4: 'closed',\n 5: 'closed'\n };\n return statusMap[status] || 'open';\n}\n\nfunction getFreshdeskSourceText(source) {\n const sourceMap = {\n 1: 'email',\n 2: 'portal',\n 3: 'phone',\n 7: 'chat',\n 8: 'mobihelp',\n 9: 'feedback_widget',\n 10: 'outbound_email'\n };\n return sourceMap[source] || 'unknown';\n}\n\nfunction checkFreshdeskNeedsAttention(ticketData, now, createdAt) {\n const ageHours = Math.round((now - createdAt) / (1000 * 60 * 60));\n const isHighPriority = ticketData.priority >= 3; // High or Urgent\n const isEscalated = ticketData.is_escalated;\n const isOverdue = ticketData.due_by ? new Date(ticketData.due_by) < now : false;\n \n return ageHours > 24 || isHighPriority || isEscalated || isOverdue;\n}\n\n// Common helper function to clean description for preview\nfunction cleanDescription(description) {\n if (!description) return \"No description available\";\n \n const cleaned = description\n .replace(/<[^>]*>/g, '') // Remove HTML tags\n .replace(/\\s+/g, ' ') // Replace multiple spaces with single space\n .trim();\n \n return cleaned.length > 100 ? cleaned.substring(0, 100) + '...' : cleaned;\n}\n\n// Main processing function\nfunction formatTicketData(ticketData) {\n const source = detectTicketSource(ticketData);\n \n switch (source) {\n case 'zendesk':\n return formatZendeskTicket(ticketData);\n case 'freshdesk':\n return formatFreshdeskTicket(ticketData);\n default:\n console.warn('Unknown ticket source:', ticketData);\n return {\n platform: 'unknown',\n ticket_id: ticketData.id,\n subject: ticketData.subject || \"No Subject\",\n error: 'Unable to determine ticket platform',\n raw_data: ticketData\n };\n }\n}\n\n// Process all input items\nconst processedItems = [];\nfor (const item of $input.all()) {\n const originalTicket = item.json;\n const formattedTicket = formatTicketData(originalTicket);\n \n processedItems.push({\n json: formattedTicket\n });\n}\n\nreturn processedItems;"
},
"typeVersion": 2
},
{
"id": "dda2d4aa-d81e-4066-b650-8f4e749889c3",
"name": "从 Freshdesk 获取工单",
"type": "n8n-nodes-base.freshdesk",
"position": [
368,
128
],
"parameters": {
"options": {},
"operation": "getAll",
"returnAll": true
},
"credentials": {
"freshdeskApi": {
"id": "bAKXYKz3snwrMW38",
"name": "Freshdesk account"
}
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "d4fc6ea9-a3dc-436c-bf4d-a3777e8b2827",
"connections": {
"Merge": {
"main": [
[
{
"node": "Format Ticket Data (Zendesk & Freshdesk)",
"type": "main",
"index": 0
}
]
]
},
"Weekly Trigger": {
"main": [
[
{
"node": "Fetch Tickets From Zendesk",
"type": "main",
"index": 0
},
{
"node": "Fetch Tickets From Freshdesk",
"type": "main",
"index": 0
}
]
]
},
"Evaluate Alerts": {
"main": [
[
{
"node": "Generate Slack Alert Message",
"type": "main",
"index": 0
},
{
"node": "Generate Weekly HTML Report",
"type": "main",
"index": 0
}
]
]
},
"Calculate Support KPIs": {
"main": [
[
{
"node": "Evaluate Alerts",
"type": "main",
"index": 0
}
]
]
},
"Log KPIs in Google Sheets": {
"main": [
[
{
"node": "Calculate Support KPIs",
"type": "main",
"index": 0
}
]
]
},
"Fetch Tickets From Zendesk": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Generate Weekly HTML Report": {
"main": [
[
{
"node": "Send Weekly Email",
"type": "main",
"index": 0
}
]
]
},
"Fetch Tickets From Freshdesk": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"Generate Slack Alert Message": {
"main": [
[
{
"node": "Send Slack Alert",
"type": "main",
"index": 0
}
]
]
},
"Format Ticket Data (Zendesk & Freshdesk)": {
"main": [
[
{
"node": "Log KPIs in Google Sheets",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 内容创作, 多模态 AI
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
从Gmail和Slack自动创建Zendesk工单并进行跟踪
从Gmail和Slack创建Zendesk工单，并使用Google Sheets进行跟踪
If
Code
Merge
+6
22 节点Rahul Joshi
内容创作
自动化 Zendesk 中待处理工单的跟进
Zendesk 待处理工单跟进系统，集成 Gmail、Google Sheets 和 ClickUp
If
Code
Cron
+5
16 节点Rahul Joshi
内容创作
GoHighLevel管道速度跟踪器和自动化停滞交易提醒
使用GoHighLevel、Gmail和Slack分析管道速度并提醒停滞交易
If
Code
Gmail
+5
25 节点Rahul Joshi
内容创作
## 仅限自托管N8N用户：
使用GPT-4o-mini、Google Sheets和Gmail自动化Zendesk支持回复
Code
Gmail
Merge
+6
24 节点Rahul Joshi
内容创作
客户入职帮助请求（Typeform 到 Gmail 和 Sheets）
客户入职帮助请求（Typeform 到 Gmail 和 Sheets）
If
Code
Gmail
+10
28 节点Rahul Joshi
内容创作
雇佣后留存跟踪
使用GPT-4o和Gmail摘要生成员工留存分析报告
If
Code
Gmail
+6
19 节点Rahul Joshi
内容创作
工作流信息
难度等级
高级
节点数量24
分类2
节点类型10
作者
Rahul Joshi
@rahul08Rahul Joshi is a seasoned technology leader specializing in the n8n automation tool and AI-driven workflow automation. With deep expertise in building open-source workflow automation and self-hosted automation platforms, he helps organizations eliminate manual processes through intelligent n8n ai agent automation solutions.
外部链接
在 n8n.io 查看 →
分享此工作流