Sync KPI Metrics from ClickUp and Google Sheets to Slack and Gmail
説明
Categories
⚙️ Automation
Nodes Used
n8n-nodes-base.setn8n-nodes-base.coden8n-nodes-base.cronn8n-nodes-base.gmailn8n-nodes-base.mergen8n-nodes-base.slackn8n-nodes-base.slackn8n-nodes-base.clickUpn8n-nodes-base.stickyNoten8n-nodes-base.stickyNote
Price無料
Views0
最終更新11/28/2025
workflow.json
{
"id": "H39e9rSjzJIcbpt4",
"meta": {
"instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
"templateCredsSetupCompleted": true
},
"name": "Sync KPI Metrics from ClickUp and Google Sheets to Slack and Gmail",
"tags": [],
"nodes": [
{
"id": "7cad3d2c-f079-481d-bfe3-cdc1f7c68e2f",
"name": "Workflow Overview",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3360,
-896
],
"parameters": {
"color": 4,
"width": 360,
"height": 672,
"content": "## 📊 Sync KPI Metrics from ClickUp and Google Sheets to Slack and Gmail\n\n### Workflow Overview\nThis workflow automatically generates and distributes a comprehensive KPI dashboard report combining:\n- **ClickUp Tasks**: Fetch task metrics\n- **Google Sheets Leads**: Retrieve lead generation data and responses\n- **Analytics Engine**: Process and compute KPI trends\n- **Multi-Channel Distribution**: Send reports via Slack and Gmail\n\n### Key Features\n✅ Automated daily execution via cron schedule\n✅ Real-time task and lead analytics\n✅ Slack dashboard snapshots\n✅ Error handling and alerting\n\n### Setup Requirements\n1. ClickUp OAuth2 credentials\n2. Google Sheets OAuth2 credentials\n3. Slack API credentials\n4. Gmail OAuth2 credentials\n\n### Execution Flow\n1. Trigger daily at scheduled time\n2. Fetch data from ClickUp and Google Sheets in parallel\n3. Merge datasets\n4. Compute KPI metrics and trends\n5. Format and send reports to Slack and Email\n6. Handle errors with Slack notifications\n"
},
"typeVersion": 1
},
{
"id": "aa0e932d-ad35-435e-9305-d806dcc81a28",
"name": "Cron Trigger Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2896,
-896
],
"parameters": {
"height": 200,
"content": "## ⏰ Daily Trigger\n\nSchedules workflow execution\nat a specific time each day.\n\nConfigure your preferred time\nand timezone in node settings."
},
"typeVersion": 1
},
{
"id": "67817fbe-5283-4be8-b0f6-22e452afefc2",
"name": "ClickUp Tasks Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2560,
-1072
],
"parameters": {
"height": 280,
"content": "## 🔗 Fetch ClickUp Tasks\n\nRetrieves task data:\n- Status & priority\n- Assignees\n- Time tracking\n- Due dates\n\nUses OAuth2 authentication"
},
"typeVersion": 1
},
{
"id": "b38f8201-079c-4bd9-8b2a-41a1f7b5b8ea",
"name": "Google Sheets Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2608,
-384
],
"parameters": {
"height": 248,
"content": "## 📑 Fetch Lead Data\n\nRetrieves lead information\nfrom Google Sheets:\n- Contact details\n- Reply content\n- Source tracking\n- Status updates"
},
"typeVersion": 1
},
{
"id": "6f599e7f-063d-45cb-9e92-17f7c7c86f33",
"name": "Merge Data Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2224,
-896
],
"parameters": {
"height": 192,
"content": "## 🔀 Merge Data\n\nCombines ClickUp tasks and\nGoogle Sheets leads into\na single dataset for analysis."
},
"typeVersion": 1
},
{
"id": "4f4b18ab-89c0-4ab6-8d0b-35583341f914",
"name": "KPI Computation Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2064,
-480
],
"parameters": {
"width": 260,
"height": 304,
"content": "## 🧮 KPI Analytics\n\n**Calculates metrics:**\n- Task completion rates\n- Overdue tasks\n- Lead sentiment analysis\n- Source performance\n- Trend indicators\n\nCustomize the JavaScript\ncode to add your own KPIs."
},
"typeVersion": 1
},
{
"id": "07cbcf4f-3de8-40cb-9b96-3fdeb452474d",
"name": "Data Formatter Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1856,
-880
],
"parameters": {
"height": 180,
"content": "## 📤 Format Data\n\nStructures KPI metrics for\nSlack and Gmail outputs.\n\nExtracts key values and\nprepares template variables."
},
"typeVersion": 1
},
{
"id": "738d9580-b855-43fd-8be1-986759fae45e",
"name": "Slack Dashboard Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1408,
-928
],
"parameters": {
"height": 180,
"content": "## 💬 Slack Summary\n\nPosts quick KPI snapshot\nto your Slack channel.\n\nUpdate channel ID in\nnode configuration."
},
"typeVersion": 1
},
{
"id": "9780b484-e977-44d3-b123-43f74d412cb3",
"name": "Gmail Report Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1392,
-496
],
"parameters": {
"height": 248,
"content": "## 📧 Email Report\n\nSends detailed HTML report\nwith comprehensive KPI analysis,\ncharts, and insights.\n\nUpdate recipient email address\nin node settings."
},
"typeVersion": 1
},
{
"id": "cfc0967b-c5cc-49a6-9480-01615b9b449d",
"name": "Error Handling Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3024,
96
],
"parameters": {
"height": 180,
"content": "## ⚠️ Error Alerts\n\nCaptures workflow failures\nand sends alerts to Slack.\n\nConfigure alerts channel\nfor team notifications."
},
"typeVersion": 1
},
{
"id": "a9ce4348-b49b-47b8-a723-72328fc7c928",
"name": "Error Trigger Handler",
"type": "n8n-nodes-base.errorTrigger",
"position": [
-2720,
96
],
"parameters": {},
"typeVersion": 1
},
{
"id": "172f269d-41ce-4d11-8d2d-3442315a8d2d",
"name": "Slack - Send Error Alert",
"type": "n8n-nodes-base.slack",
"position": [
-2496,
96
],
"webhookId": "1abab466-eb85-4d88-a60d-c9028cff110b",
"parameters": {
"text": "=⌠*KPI Dashboard Workflow Failed*\n\n*Error Details:*\n{{ $json.error?.message || 'Unknown error occurred' }}\n\n*Failed Node:* {{ $json.node?.name || 'Unknown node' }}\n*Execution ID:* {{ $execution.id }}\n*Timestamp:* {{ $now.format('YYYY-MM-DD HH:mm:ss') }}\n\n*Action Required:*\n1. Check execution logs in n8n\n2. Verify data source connections\n3. Review node configurations\n4. Restart workflow if needed\n\n_Automated alert from KPI Dashboard System_",
"select": "channel",
"channelId": {
"__rl": true,
"mode": "list",
"value": "YOUR_ALERTS_CHANNEL_ID",
"cachedResultName": "workflow-alerts"
},
"otherOptions": {}
},
"credentials": {
"slackApi": {
"id": "rNqvWj9TfChPVRYY",
"name": "Slack account vivek"
}
},
"typeVersion": 2
},
{
"id": "d7e79592-f836-4053-b917-c741784a61dd",
"name": "Daily Cron Trigger",
"type": "n8n-nodes-base.cron",
"position": [
-2672,
-672
],
"parameters": {},
"typeVersion": 1
},
{
"id": "9cad37f4-6719-439d-9652-5eeec89ad983",
"name": "ClickUp - Fetch Tasks",
"type": "n8n-nodes-base.clickUp",
"position": [
-2448,
-768
],
"parameters": {
"list": "901611392518",
"team": "9016683627",
"limit": 5,
"space": "90162844741",
"folder": "90164394824",
"filters": {
"subtasks": false
},
"operation": "getAll",
"returnAll": false,
"authentication": "oAuth2"
},
"credentials": {
"clickUpOAuth2Api": {
"id": "6UikDwF6BMx3ln9O",
"name": "ClickUp account"
}
},
"typeVersion": 1
},
{
"id": "d5bb389e-dc40-45ad-bd5e-7c59d9a536c9",
"name": "Google Sheets - Fetch Lead Data",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2448,
-576
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1856159589,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/17rcNd_ZpUQLm0uWEVbD-NY6GyFUkrD4BglvawlyBygM/edit#gid=1856159589",
"cachedResultName": "KPI Data"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "17rcNd_ZpUQLm0uWEVbD-NY6GyFUkrD4BglvawlyBygM",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/17rcNd_ZpUQLm0uWEVbD-NY6GyFUkrD4BglvawlyBygM/edit?usp=drivesdk",
"cachedResultName": "sample_leads_50"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "kpPEOLCGn963qpoh",
"name": "[email protected]"
}
},
"typeVersion": 3
},
{
"id": "75c5f34a-d851-4eae-bff7-7289d608a03c",
"name": "Merge - Consolidate Datasets",
"type": "n8n-nodes-base.merge",
"position": [
-2224,
-672
],
"parameters": {},
"typeVersion": 1
},
{
"id": "fa353353-2fd0-424a-a09a-639831c2dabc",
"name": "Code - Compute KPI Trends",
"type": "n8n-nodes-base.code",
"position": [
-2000,
-672
],
"parameters": {
"jsCode": "// n8n Code Node - Compute KPI Trends\n// This code analyzes ClickUp tasks and lead data to generate KPIs\n\nconst items = $input.all();\nconst data = items.map(item => item.json);\n\n// Separate tasks and leads\nconst tasks = data.filter(item => item.id && item.id.startsWith('86d0'));\nconst leads = data.filter(item => item.leadId);\n\n// Initialize KPI object\nconst kpis = {\n timestamp: new Date().toISOString(),\n tasks: {},\n leads: {},\n overall: {}\n};\n\n// ===== TASK KPIs =====\nif (tasks.length > 0) {\n // Status distribution\n const statusCount = {};\n const priorityCount = {};\n const assigneeCount = {};\n let totalTimeSpent = 0;\n let tasksWithTime = 0;\n let overdueTasks = 0;\n const today = Date.now();\n\n tasks.forEach(task => {\n // Status counting\n const status = task.status?.status || 'unknown';\n statusCount[status] = (statusCount[status] || 0) + 1;\n\n // Priority counting\n const priority = task.priority?.priority || 'none';\n priorityCount[priority] = (priorityCount[priority] || 0) + 1;\n\n // Assignee counting\n task.assignees?.forEach(assignee => {\n assigneeCount[assignee.username] = (assigneeCount[assignee.username] || 0) + 1;\n });\n\n // Time tracking\n if (task.time_spent) {\n totalTimeSpent += task.time_spent;\n tasksWithTime++;\n }\n\n // Overdue calculation\n if (task.due_date && parseInt(task.due_date) < today && !task.date_done) {\n overdueTasks++;\n }\n });\n\n kpis.tasks = {\n total: tasks.length,\n byStatus: statusCount,\n byPriority: priorityCount,\n byAssignee: assigneeCount,\n overdue: overdueTasks,\n overduePercentage: ((overdueTasks / tasks.length) * 100).toFixed(2),\n avgTimeSpent: tasksWithTime > 0 ? Math.round(totalTimeSpent / tasksWithTime) : 0,\n avgTimeSpentHours: tasksWithTime > 0 ? (totalTimeSpent / tasksWithTime / 3600000).toFixed(2) : 0,\n completion: {\n done: statusCount['done'] || 0,\n inProgress: statusCount['in progress'] || 0,\n finalReview: statusCount['final review'] || 0,\n backlogs: statusCount['backlogs'] || 0\n }\n };\n}\n\n// ===== LEAD KPIs =====\nif (leads.length > 0) {\n const sourceCount = {};\n const statusCount = {};\n const responsesByDate = {};\n let positiveResponses = 0;\n let negativeResponses = 0;\n let neutralResponses = 0;\n\n // Keywords for sentiment analysis\n const positiveKeywords = ['interested', 'love', 'promising', 'sounds interesting', 'count me in', 'yes', 'great'];\n const negativeKeywords = ['not interested', 'not sure', 'concerns', 'difficult', 'already have'];\n\n leads.forEach(lead => {\n // Source counting\n const source = lead.source || 'unknown';\n sourceCount[source] = (sourceCount[source] || 0) + 1;\n\n // Status counting\n const status = lead.status || 'unknown';\n statusCount[status] = (statusCount[status] || 0) + 1;\n\n // Date grouping\n const date = lead.replyDate || 'unknown';\n responsesByDate[date] = (responsesByDate[date] || 0) + 1;\n\n // Simple sentiment analysis\n if (lead.reply) {\n const replyLower = lead.reply.toLowerCase();\n const hasPositive = positiveKeywords.some(kw => replyLower.includes(kw));\n const hasNegative = negativeKeywords.some(kw => replyLower.includes(kw));\n\n if (hasPositive && !hasNegative) {\n positiveResponses++;\n } else if (hasNegative && !hasPositive) {\n negativeResponses++;\n } else {\n neutralResponses++;\n }\n }\n });\n\n kpis.leads = {\n total: leads.length,\n bySource: sourceCount,\n byStatus: statusCount,\n byDate: responsesByDate,\n sentiment: {\n positive: positiveResponses,\n negative: negativeResponses,\n neutral: neutralResponses,\n positiveRate: ((positiveResponses / leads.length) * 100).toFixed(2),\n negativeRate: ((negativeResponses / leads.length) * 100).toFixed(2)\n },\n topSources: Object.entries(sourceCount)\n .sort((a, b) => b[1] - a[1])\n .slice(0, 3)\n .map(([source, count]) => ({ source, count }))\n };\n}\n\n// ===== OVERALL KPIs =====\nkpis.overall = {\n totalItems: data.length,\n tasksCount: tasks.length,\n leadsCount: leads.length,\n dataQuality: {\n tasksWithDueDate: tasks.filter(t => t.due_date).length,\n tasksWithAssignees: tasks.filter(t => t.assignees?.length > 0).length,\n leadsWithCompany: leads.filter(l => l.company).length,\n leadsWithPhone: leads.filter(l => l.phone).length\n }\n};\n\n// ===== TREND INDICATORS =====\nkpis.trends = {\n taskTrend: tasks.length > 0 ? 'stable' : 'no data',\n leadTrend: leads.length > 5 ? 'active' : 'low activity',\n urgentTasks: tasks.filter(t => t.priority?.priority === 'urgent').length,\n newReplies: leads.filter(l => l.status === 'New Reply').length\n};\n\n// Return as single item with comprehensive KPI data\nreturn [{ json: kpis }];"
},
"typeVersion": 1
},
{
"id": "414f7e49-df31-4690-950c-d7f451fc6014",
"name": "Set - Format Output Data",
"type": "n8n-nodes-base.set",
"position": [
-1776,
-672
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "dbb44ee9-0676-45ac-8c9e-e1ab1b801a6e",
"name": "tasks",
"type": "object",
"value": "={{ $json.tasks }}"
},
{
"id": "63779c8a-c71e-4267-9ec0-22098ecabd28",
"name": "tasks.overdue",
"type": "number",
"value": "={{ $json.tasks.overdue }}"
},
{
"id": "5d3f4a88-1526-4cb3-bc24-963efd1e50bf",
"name": "tasks.overduePercentage",
"type": "string",
"value": "={{ $json.tasks.overduePercentage }}"
},
{
"id": "709e0406-4d96-49ab-b0d1-40e35bc876f1",
"name": "tasks.avgTimeSpent",
"type": "number",
"value": "={{ $json.tasks.avgTimeSpent }}"
},
{
"id": "6aebe018-4dd6-4ca9-826a-8290c7b3c355",
"name": "tasks.avgTimeSpentHours",
"type": "string",
"value": "={{ $json.tasks.avgTimeSpentHours }}"
},
{
"id": "c5912159-c328-4403-8e50-8ae94511d307",
"name": "tasks.completion",
"type": "object",
"value": "={{ $json.tasks.completion }}"
},
{
"id": "0141ac77-5e6d-46f1-a8fe-8e781f33a632",
"name": "leads",
"type": "object",
"value": "={{ $json.leads }}"
},
{
"id": "54567372-ce0a-4aa1-b163-aed6f0b1d8db",
"name": "leads.topSources",
"type": "array",
"value": "={{ $json.leads.topSources }}"
},
{
"id": "6ea32021-40ad-454e-ad96-f905f8cf9967",
"name": "overall",
"type": "object",
"value": "={{ $json.overall }}"
},
{
"id": "f77c2c65-3b7e-43f1-9bbe-71b7e60468ea",
"name": "trends",
"type": "object",
"value": "={{ $json.trends }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "5449a5fb-aa27-4280-8e2f-dc1333b4611f",
"name": "Slack - Post Dashboard Snapshot",
"type": "n8n-nodes-base.slack",
"position": [
-1552,
-768
],
"webhookId": "d987216d-dd09-4518-aacd-01fbda90908f",
"parameters": {
"text": "=📊 *Daily KPI Dashboard Updated*\n\n*Task Metrics:* \n• Total Task: {{ $json.tasks.total }} \n• Overdue: {{ $json.tasks.overdue }} ({{ $json.tasks.overduePercentage }}%)\n• Average Time Spent: {{ $json.tasks.avgTimeSpentHours }} hours\n\n*Lead Generation:*\n• Total: {{ $json.leads.total }} leads \n• Positive Sentiment: {{ $json.leads.sentiment.positiveRate }}%\n• New Replies: {{ $json.trends.newReplies }}\n\n*Top Sources:*\n{{ Object.entries($json.leads.bySource).map(([source, count]) => `• ${source}: ${count}`).join('\\n') }}\n\n*Overall Status:*\n• Total Items: {{ $json.overall.totalItems }} \n• Tasks: {{ $json.overall.tasksCount }} | Leads: {{ $json.overall.leadsCount }}\n• Urgent Tasks: {{ $json.trends.urgentTasks }}\n\n_Report generated at {{ $now.format('HH:mm') }}_",
"select": "channel",
"channelId": {
"__rl": true,
"mode": "list",
"value": "C09GNB90TED",
"cachedResultName": "general-information"
},
"otherOptions": {}
},
"credentials": {
"slackApi": {
"id": "rNqvWj9TfChPVRYY",
"name": "Slack account vivek"
}
},
"typeVersion": 2
},
{
"id": "3853cf8a-ca5c-44e0-93d1-b31f6c222b10",
"name": "Gmail - Send KPI Report",
"type": "n8n-nodes-base.gmail",
"position": [
-1552,
-576
],
"webhookId": "ac83548d-967a-4ebc-b011-25668788c29c",
"parameters": {
"sendTo": "={{ $env.REPORT_EMAIL || '[email protected]' }}",
"message": "=<!DOCTYPE html> <html lang=\"en\"> <head> <meta charset=\"UTF-8\"> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> <title>Daily KPI Report</title> <style> body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; line-height: 1.6; color: #333; max-width: 800px; margin: 0 auto; padding: 20px; background-color: #f4f4f4; } .container { background-color: #ffffff; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); overflow: hidden; } .header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 30px; text-align: center; } .header h1 { margin: 0; font-size: 28px; font-weight: 600; } .header p { margin: 10px 0 0 0; opacity: 0.9; font-size: 14px; } .section { padding: 25px 30px; border-bottom: 1px solid #e0e0e0; } .section:last-child { border-bottom: none; } .section-title { font-size: 20px; font-weight: 600; margin-bottom: 15px; color: #2c3e50; display: flex; align-items: center; } .section-title .icon { margin-right: 10px; font-size: 24px; } .stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin-top: 15px; } .stat-card { background-color: #f8f9fa; border-left: 4px solid #667eea; padding: 15px; border-radius: 5px; } .stat-card.urgent { border-left-color: #dc3545; background-color: #fff5f5; } .stat-card.success { border-left-color: #28a745; background-color: #f0fff4; } .stat-card.warning { border-left-color: #ffc107; background-color: #fffbf0; } .stat-label { font-size: 13px; color: #6c757d; font-weight: 500; text-transform: uppercase; letter-spacing: 0.5px; } .stat-value { font-size: 28px; font-weight: 700; margin: 5px 0; color: #2c3e50; } .stat-detail { font-size: 12px; color: #6c757d; } .progress-bar { background-color: #e9ecef; border-radius: 10px; height: 10px; margin-top: 8px; overflow: hidden; } .progress-fill { height: 100%; background: linear-gradient(90deg, #667eea 0%, #764ba2 100%); border-radius: 10px; transition: width 0.3s ease; } .progress-fill.danger { background: linear-gradient(90deg, #dc3545 0%, #c82333 100%); } .progress-fill.success { background: linear-gradient(90deg, #28a745 0%, #218838 100%); } .sentiment-container { display: flex; gap: 15px; margin-top: 15px; } .sentiment-item { flex: 1; text-align: center; padding: 15px; border-radius: 8px; background-color: #f8f9fa; } .sentiment-item.positive { background-color: #d4edda; border: 2px solid #28a745; } .sentiment-item.negative { background-color: #f8d7da; border: 2px solid #dc3545; } .sentiment-item.neutral { background-color: #fff3cd; border: 2px solid #ffc107; } .sentiment-emoji { font-size: 36px; margin-bottom: 5px; } .sentiment-count { font-size: 24px; font-weight: 700; color: #2c3e50; } .sentiment-label { font-size: 12px; color: #6c757d; font-weight: 600; text-transform: uppercase; } .trend-badge { display: inline-block; padding: 5px 12px; border-radius: 20px; font-size: 12px; font-weight: 600; margin-top: 5px; } .trend-badge.stable { background-color: #cce5ff; color: #004085; } .trend-badge.active { background-color: #d4edda; color: #155724; } .footer { background-color: #f8f9fa; padding: 20px 30px; text-align: center; color: #6c757d; font-size: 12px; } .assignee-list { display: flex; flex-wrap: wrap; gap: 10px; margin-top: 10px; } .assignee-badge { background-color: #e9ecef; padding: 6px 12px; border-radius: 20px; font-size: 12px; font-weight: 500; } .source-list { display: grid; grid-template-columns: repeat(auto-fill, minmax(150px, 1fr)); gap: 10px; margin-top: 10px; } .source-item { background-color: #f8f9fa; padding: 8px 12px; border-radius: 5px; font-size: 12px; border-left: 3px solid #667eea; } </style> </head> <body> <div class=\"container\"> <div class=\"header\"> <h1>📊 Daily KPI Dashboard Report</h1> <p>Generated on {{ $now.format('MMMM DD, YYYY') }} at {{ $now.format('HH:mm:ss') }}</p> </div> <div class=\"section\"> <div class=\"section-title\"> <span class=\"icon\">✅</span> <span>Tasks Overview</span> </div> <div class=\"stats-grid\"> <div class=\"stat-card\"> <div class=\"stat-label\">Total Tasks</div> <div class=\"stat-value\">{{ $json.tasks.total }}</div> </div> <div class=\"stat-card urgent\"> <div class=\"stat-label\">Overdue Tasks</div> <div class=\"stat-value\">{{ $json.tasks.overdue }}</div> <div class=\"stat-detail\">{{ $json.tasks.overduePercentage }}% of total</div> <div class=\"progress-bar\"> <div class=\"progress-fill danger\" style=\"width: {{ $json.tasks.overduePercentage }}%\"></div> </div> </div> <div class=\"stat-card warning\"> <div class=\"stat-label\">Urgent Priority</div> <div class=\"stat-value\">{{ $json.trends.urgentTasks }}</div> <div class=\"stat-detail\">Requires immediate attention</div> </div> <div class=\"stat-card\"> <div class=\"stat-label\">Avg Time Spent</div> <div class=\"stat-value\">{{ $json.tasks.avgTimeSpentHours }}</div> <div class=\"stat-detail\">hours per task</div> </div> </div> <div style=\"margin-top: 25px;\"> <strong style=\"font-size: 14px; color: #6c757d;\">TASK STATUS BREAKDOWN</strong> <div class=\"stats-grid\" style=\"margin-top: 10px;\"> <div class=\"stat-card\"> <div class=\"stat-label\">In Progress</div> <div class=\"stat-value\" style=\"font-size: 22px; color: #5f55ee;\">{{ $json.tasks.completion.inProgress }}</div> </div> <div class=\"stat-card\"> <div class=\"stat-label\">Final Review</div> <div class=\"stat-value\" style=\"font-size: 22px; color: #f8ae00;\">{{ $json.tasks.completion.finalReview }}</div> </div> <div class=\"stat-card\"> <div class=\"stat-label\">Backlogs</div> <div class=\"stat-value\" style=\"font-size: 22px; color: #87909e;\">{{ $json.tasks.completion.backlogs }}</div> </div> <div class=\"stat-card success\"> <div class=\"stat-label\">Completed</div> <div class=\"stat-value\" style=\"font-size: 22px; color: #28a745;\">{{ $json.tasks.completion.done }}</div> </div> </div> </div> <div style=\"margin-top: 25px;\"> <strong style=\"font-size: 14px; color: #6c757d;\">TASK DISTRIBUTION BY ASSIGNEE</strong> <div class=\"assignee-list\"> {{ Object.entries($json.tasks.byAssignee).map(([name, count]) => `<div class=\"assignee-badge\">${name}: <strong>${count}</strong> task(s)</div>`).join('') }} </div> </div> </div> <div class=\"section\"> <div class=\"section-title\"> <span class=\"icon\">👥</span> <span>Leads Overview</span> </div> <div class=\"stats-grid\"> <div class=\"stat-card\"> <div class=\"stat-label\">Total Leads</div> <div class=\"stat-value\">{{ $json.leads.total }}</div> </div> <div class=\"stat-card success\"> <div class=\"stat-label\">New Replies</div> <div class=\"stat-value\">{{ $json.trends.newReplies }}</div> <div class=\"stat-detail\">Awaiting response</div> </div> </div> <div style=\"margin-top: 25px;\"> <strong style=\"font-size: 14px; color: #6c757d;\">SENTIMENT ANALYSIS</strong> <div class=\"sentiment-container\"> <div class=\"sentiment-item positive\"> <div class=\"sentiment-emoji\">😊</div> <div class=\"sentiment-count\">{{ $json.leads.sentiment.positive }}</div> <div class=\"sentiment-label\">Positive</div> <div style=\"font-size: 14px; font-weight: 600; margin-top: 5px; color: #28a745;\">{{ $json.leads.sentiment.positiveRate }}%</div> </div> <div class=\"sentiment-item neutral\"> <div class=\"sentiment-emoji\">😐</div> <div class=\"sentiment-count\">{{ $json.leads.sentiment.neutral }}</div> <div class=\"sentiment-label\">Neutral</div> <div style=\"font-size: 14px; font-weight: 600; margin-top: 5px; color: #856404;\">{{ (($json.leads.sentiment.neutral / $json.leads.total) * 100).toFixed(2) }}%</div> </div> <div class=\"sentiment-item negative\"> <div class=\"sentiment-emoji\">😟</div> <div class=\"sentiment-count\">{{ $json.leads.sentiment.negative }}</div> <div class=\"sentiment-label\">Negative</div> <div style=\"font-size: 14px; font-weight: 600; margin-top: 5px; color: #dc3545;\">{{ $json.leads.sentiment.negativeRate }}%</div> </div> </div> </div> <div style=\"margin-top: 25px;\"> <strong style=\"font-size: 14px; color: #6c757d;\">TOP LEAD SOURCES</strong> <div class=\"source-list\"> {{ Object.entries($json.leads.bySource).map(([source, count]) => `<div class=\"source-item\"><strong>${source}</strong><br>${count} lead(s)</div>`).join('') }} </div> </div> <div style=\"margin-top: 25px;\"> <strong style=\"font-size: 14px; color: #6c757d;\">REPLIES BY DATE</strong> <div style=\"margin-top: 10px;\"> {{ Object.entries($json.leads.byDate).map(([date, count]) => ` <div style=\"display: flex; justify-content: space-between; padding: 8px 12px; background-color: #f8f9fa; margin-bottom: 5px; border-radius: 5px;\"> <span style=\"font-weight: 500;\">${date}</span> <span style=\"font-weight: 700; color: #667eea;\">${count} replies</span> </div> `).join('') }} </div> </div> </div> <div class=\"section\"> <div class=\"section-title\"> <span class=\"icon\">📈</span> <span>Trends & Insights</span> </div> <div style=\"display: grid; grid-template-columns: 1fr 1fr; gap: 20px;\"> <div> <div style=\"font-size: 14px; color: #6c757d; margin-bottom: 8px;\">TASK TREND</div> <div style=\"font-size: 20px; font-weight: 600; color: #2c3e50;\"> {{ $json.trends.taskTrend }} <span class=\"trend-badge stable\">{{ $json.trends.taskTrend }}</span> </div> </div> <div> <div style=\"font-size: 14px; color: #6c757d; margin-bottom: 8px;\">LEAD TREND</div> <div style=\"font-size: 20px; font-weight: 600; color: #2c3e50;\"> {{ $json.trends.leadTrend }} <span class=\"trend-badge active\">{{ $json.trends.leadTrend }}</span> </div> </div> </div> <div style=\"margin-top: 20px; padding: 15px; background-color: #e7f3ff; border-left: 4px solid #0066cc; border-radius: 5px;\"> <strong style=\"color: #004085;\">💡 Key Insights:</strong> <ul style=\"margin: 10px 0 0 0; padding-left: 20px; color: #004085;\"> <li>You have <strong>{{ $json.tasks.overdue }}</strong> overdue tasks that need immediate attention</li> <li><strong>{{ $json.leads.sentiment.positiveRate }}%</strong> of leads show positive interest</li> <li>Average task completion time: <strong>{{ $json.tasks.avgTimeSpentHours }} hours</strong></li> <li><strong>{{ $json.trends.newReplies }}</strong> new lead replies to follow up on</li> </ul> </div> </div> <div class=\"footer\"> <p style=\"margin: 0;\">This report was automatically generated by your KPI Dashboard System</p> <p style=\"margin: 5px 0 0 0;\">For questions or support, please contact your system administrator</p> </div> </div> </body> </html>",
"options": {},
"subject": "=Daily KPI Dashboard - {{ $now.format('MMMM DD, YYYY') }}"
},
"credentials": {
"gmailOAuth2": {
"id": "gEIaWCTvGfYjMSb3",
"name": "Gmail credentials"
}
},
"typeVersion": 2
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "3d7eb7d9-714c-4f9a-b953-4b3cbe9a1cf4",
"connections": {
"Daily Cron Trigger": {
"main": [
[
{
"node": "Google Sheets - Fetch Lead Data",
"type": "main",
"index": 0
},
{
"node": "ClickUp - Fetch Tasks",
"type": "main",
"index": 0
}
]
]
},
"ClickUp - Fetch Tasks": {
"main": [
[
{
"node": "Merge - Consolidate Datasets",
"type": "main",
"index": 0
}
]
]
},
"Error Trigger Handler": {
"main": [
[
{
"node": "Slack - Send Error Alert",
"type": "main",
"index": 0
}
]
]
},
"Set - Format Output Data": {
"main": [
[
{
"node": "Slack - Post Dashboard Snapshot",
"type": "main",
"index": 0
},
{
"node": "Gmail - Send KPI Report",
"type": "main",
"index": 0
}
]
]
},
"Code - Compute KPI Trends": {
"main": [
[
{
"node": "Set - Format Output Data",
"type": "main",
"index": 0
}
]
]
},
"Merge - Consolidate Datasets": {
"main": [
[
{
"node": "Code - Compute KPI Trends",
"type": "main",
"index": 0
}
]
]
},
"Google Sheets - Fetch Lead Data": {
"main": [
[
{
"node": "Merge - Consolidate Datasets",
"type": "main",
"index": 1
}
]
]
}
}
}