Task Time Analysis: Automated Overspend Time Insights for Clickup and More

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": "h7ZyTA0VjSeZqsAI",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Insight of Overspent time on the task",
  "tags": [
    {
      "id": "1SefZAAaE6fahCis",
      "name": "Extra Effort Requested",
      "createdAt": "2025-05-16T09:37:50.710Z",
      "updatedAt": "2025-05-16T09:37:50.710Z"
    },
    {
      "id": "Cgaq1wafpvP8Ts91",
      "name": "Delivery Logs",
      "createdAt": "2025-05-16T09:15:54.071Z",
      "updatedAt": "2025-05-16T09:15:54.071Z"
    },
    {
      "id": "MTdWgh7kWuzq1arv",
      "name": "Software Release Notes",
      "createdAt": "2025-05-16T09:15:54.122Z",
      "updatedAt": "2025-05-16T09:15:54.122Z"
    },
    {
      "id": "S8pHYN2eFwOS5Ay7",
      "name": "Over Estimation",
      "createdAt": "2025-05-16T09:37:50.665Z",
      "updatedAt": "2025-05-16T09:37:50.665Z"
    },
    {
      "id": "Y82lXPBFFAZ6SDav",
      "name": "Ship Logs",
      "createdAt": "2025-05-16T09:15:54.097Z",
      "updatedAt": "2025-05-16T09:15:54.097Z"
    },
    {
      "id": "Z2Gof9jSDo30wxTR",
      "name": "Release Notes",
      "createdAt": "2025-05-16T09:15:54.147Z",
      "updatedAt": "2025-05-16T09:15:54.147Z"
    }
  ],
  "nodes": [
    {
      "id": "749b4a34-abca-448e-8039-a57801c00689",
      "name": "When clicking \u2018Test workflow\u2019",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -3260,
        540
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "7f4fe2c5-3ef6-4887-805b-b2ec73817af2",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1940,
        200
      ],
      "parameters": {
        "content": "Why requested for extra time?\n\nChecklist Name = Why needed Extra time?\n\nList of reasons with the comment link if possible."
      },
      "typeVersion": 1
    },
    {
      "id": "2753b25c-1ded-4f33-91cc-d1b00f593388",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2240,
        200
      ],
      "parameters": {
        "content": "Why goes over estimation.\n\nChecklist Name = Why goes over estimation?\n\nEvaluate comments and discussions and based on that prepare list of reasons about why it goes over estimation."
      },
      "typeVersion": 1
    },
    {
      "id": "714798ea-c08e-4b92-bc28-76e0cefa28ba",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        2280,
        800
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "gpt-4o-mini"
        },
        "options": {
          "maxRetries": 1
        }
      },
      "credentials": {
        "openAiApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "20f433ee-3723-4e8e-997b-90c6870ef29c",
      "name": "Simple Memory",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        2440,
        800
      ],
      "parameters": {
        "sessionKey": "={$json.id}",
        "sessionIdType": "customKey",
        "contextWindowLength": "=3"
      },
      "typeVersion": 1.3
    },
    {
      "id": "b5d541fb-ff1c-4387-adc7-4fd7d3ec0197",
      "name": "Convert to File",
      "type": "n8n-nodes-base.convertToFile",
      "position": [
        3540,
        520
      ],
      "parameters": {
        "options": {
          "fileName": "=Task-{{ $json.taskId }}"
        },
        "operation": "toJson"
      },
      "typeVersion": 1.1
    },
    {
      "id": "3d05bf5b-7e97-473c-bef0-a45d8055577f",
      "name": "Get Clickup Tasks",
      "type": "n8n-nodes-base.clickUp",
      "position": [
        -3020,
        540
      ],
      "parameters": {
        "list": "901403418531",
        "team": "9014350065",
        "space": "90141295066",
        "filters": {
          "statuses": [
            "internal review",
            "in progress"
          ],
          "subtasks": "={{ true }}",
          "assignees": [
            82359490
          ]
        },
        "operation": "getAll",
        "folderless": true
      },
      "credentials": {
        "clickUpApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1,
      "alwaysOutputData": true
    },
    {
      "id": "730d01c6-57f8-48ec-9fbe-9fc8d149408c",
      "name": "Fetch Time entries via task IDs",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -560,
        1280
      ],
      "parameters": {
        "url": "=https://api.clickup.com/api/v2/task/{{ $json.id }}/time ",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "clickUpApi"
      },
      "credentials": {
        "clickUpApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "331d1286-f720-45f6-8e77-d6e822a517b6",
      "name": "Filter out unnecessary data from Tasks",
      "type": "n8n-nodes-base.code",
      "position": [
        -2800,
        540
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Destructure the fields you want from the current comment\nconst { id, name, text_content, status,date_created,date_closed,date_done,date_updated,creator,assignees,group_assignees,checklists,tags, time_estimate,time_estimates_by_user,time_spent,team_id,\n      } = $json;\n\n// Return a new JSON object with the outer fields\nreturn {\n  json: {\n    id,name, text_content, status,date_created,date_closed,date_done,date_updated,creator,assignees,group_assignees,checklists,tags, time_estimate,time_estimates_by_user,time_spent,team_id,\n  }\n};\n\n"
      },
      "typeVersion": 2
    },
    {
      "id": "2be708ea-adf5-4f41-ae87-ad87539d5704",
      "name": "If task has crossed estimation",
      "type": "n8n-nodes-base.if",
      "position": [
        -2580,
        540
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "fc079044-556f-4a4f-99a9-4928f91c3b5e",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.time_spent }}",
              "rightValue": "={{ $json.time_estimate }}"
            },
            {
              "id": "c1101bc1-3fad-44d5-8b67-eb89faf90ffa",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "86b4frgaw",
              "rightValue": "={{ $json.id }}"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "040f3bcf-edef-42c6-aa4f-fd54c2cb1287",
      "name": "Fetch Master comments",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -1620,
        160
      ],
      "parameters": {
        "url": "=https://api.clickup.com/api/v2/task/{{ $json.id }}/comment ",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "clickUpApi"
      },
      "credentials": {
        "clickUpApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "9ff0e6a3-9701-4686-908d-dce489103dc2",
      "name": "Loop Over Master comments",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -1220,
        160
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "8749943a-1834-4adc-90cc-4c0ff3094bc5",
      "name": "If comments got thread comments",
      "type": "n8n-nodes-base.if",
      "position": [
        -280,
        180
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "91e31fc5-8ac2-484b-827d-0fe66965595d",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.reply_count }}",
              "rightValue": 0
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "98f6f0b5-70d1-4341-b4cf-e6b313cc806a",
      "name": "Fetch comment threads",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -40,
        -80
      ],
      "parameters": {
        "url": "=https://api.clickup.com/api/v2/comment/{{ $json.id }}/reply",
        "options": {},
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "clickUpApi"
      },
      "credentials": {
        "clickUpApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.2,
      "alwaysOutputData": true
    },
    {
      "id": "e4af0433-6cb0-4f80-a284-1520b1329971",
      "name": "Merge thread comments with master comments",
      "type": "n8n-nodes-base.merge",
      "position": [
        440,
        -220
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.1
    },
    {
      "id": "236d2c78-0af5-4f78-809d-a740f68beae0",
      "name": "Re-merge all master comments",
      "type": "n8n-nodes-base.merge",
      "position": [
        860,
        180
      ],
      "parameters": {},
      "typeVersion": 3.1,
      "alwaysOutputData": true
    },
    {
      "id": "91ddd52e-7c72-466d-b054-4eb9bf4a08b2",
      "name": "Re-structure comments to process them in loop node",
      "type": "n8n-nodes-base.code",
      "position": [
        1080,
        180
      ],
      "parameters": {
        "jsCode": "\n\n// 1. Extract all incoming comment objects into a simple array\nconst commentsArray = items.map(item => item.json);\n\n// 2. Wrap that array under the 'comment' key in one object\nreturn [\n  {\n    json: {\n      comments: commentsArray\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "aafe2a4c-3b8b-4eee-bde6-b8cf19ca8c10",
      "name": "Merge task data, comments and time entries",
      "type": "n8n-nodes-base.merge",
      "position": [
        1940,
        520
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition",
        "numberInputs": 3
      },
      "typeVersion": 3.1
    },
    {
      "id": "b531daf1-ceeb-4739-bd3c-1cd0f9ca4d29",
      "name": "Modify Task data",
      "type": "n8n-nodes-base.code",
      "position": [
        -300,
        540
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const { id, name, text_content, status, date_created, date_updated, tags, time_estimate, time_estimates_by_user, time_spent } = $json;\n\n\nreturn {\nid, name, text_content, status, date_created, date_updated, tags, time_estimate, time_estimates_by_user, time_spent\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "8838e6b2-2e60-42a0-864b-e7b2cc640504",
      "name": "Move to next master comment",
      "type": "n8n-nodes-base.noOp",
      "position": [
        1300,
        180
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "dfa59ea4-1ed2-49fa-8490-41711685b032",
      "name": "Modify Master comment data",
      "type": "n8n-nodes-base.code",
      "position": [
        720,
        -220
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Destructure comments out of the payload, collecting the rest in `rest`\nconst { comments,assignee, group_assignee, reactions, ...rest } = $json;\n\n// Return a new JSON object with the outer fields + renamed thread\nreturn {\n  json: {\n    ...rest,\n    comment_thread: comments,\n  },\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "b9d06844-c5bb-4897-85cb-4bf992535b84",
      "name": "Return Comments data",
      "type": "n8n-nodes-base.code",
      "position": [
        560,
        -360
      ],
      "parameters": {
        "jsCode": "\nreturn $input.all();"
      },
      "typeVersion": 2
    },
    {
      "id": "61f7e795-d952-4161-a27e-338c3d7744a9",
      "name": "Modify threads comment data",
      "type": "n8n-nodes-base.code",
      "position": [
        200,
        -80
      ],
      "parameters": {
        "jsCode": "const result = [];\n\nfor (const item of items) {\n  const comments = item.json.comments || [];\n\n  // Sort comments by date in descending order\n  const sortedComments = comments.sort((a, b) => {\n    const dateA = parseInt(a.date || '0', 10);\n    const dateB = parseInt(b.date || '0', 10);\n    return dateA - dateB;\n  });\n\n  // Map the sorted comments into desired format\n  const filteredComments = sortedComments.map(comment => {\n    return {\n      id: comment.id,\n      comment_text: comment.comment_text,\n      date: comment.date,\n      user: comment.user,\n      reactions: comment.reactions\n    };\n  });\n\n  result.push({\n    json: {\n      comments: filteredComments\n    }\n  });\n}\n\nreturn result;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "3435d757-2812-46be-a0a5-546fb3cc7f82",
      "name": "Sort Master comments old to new",
      "type": "n8n-nodes-base.code",
      "position": [
        -600,
        180
      ],
      "parameters": {
        "jsCode": "// Assuming input data is in the format you provided\nconst sortedItems = items.sort((a, b) => {\n  console.log('check data',a,b)\n  const dateA = parseInt(a.json.date, 10);\n  const dateB = parseInt(b.json.date, 10);\n  return dateA - dateB;\n});\n\n// Return each sorted item individually for downstream use\nreturn sortedItems;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "e657b291-a333-449b-82e7-e3e27c4c0c70",
      "name": "Modify Time entries data",
      "type": "n8n-nodes-base.code",
      "position": [
        -340,
        1280
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const { data, ...rest } = $json;\n\nconst getTimeInMinutes = (data) => Math.round(parseInt(data || '0', 10) / 60000) \n\nconst sortedData = data.map(item => {\n  const processedIntervals = [...(item.intervals || [])]\n    .sort((a, b) => parseInt(a.date_added || '0', 10) - parseInt(b.date_added || '0', 10))\n    .map(interval => ({\n      id: interval.id,\n      time_in_minutes: getTimeInMinutes(interval.time),\n  description: interval.description,\n  tags: (interval.tags || []).map(tag => tag.name),\n  category: \"\"\n    }));\n\n  const {time, ...restItemdata} = item;\n\n  return {\n    ...restItemdata,\n \"total_time_in_minutes\" : getTimeInMinutes(time),\n    intervals: processedIntervals,\n  };\n});\n\nreturn {\n  json: {\n    ...rest,\n    time_entries: sortedData,\n  },\n};\n"
      },
      "typeVersion": 2
    },
    {
      "id": "51e5cf99-7128-4008-b9d5-19f9818f29de",
      "name": "Code",
      "type": "n8n-nodes-base.code",
      "position": [
        2820,
        520
      ],
      "parameters": {
        "jsCode": "const rawOutput = $json[\"output\"];\nconst parsed = JSON.parse(rawOutput);\nreturn parsed;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "8ffc6a60-44c0-4a9e-8a3e-517d598b6845",
      "name": "Destructure & Filter comments array to loop them",
      "type": "n8n-nodes-base.code",
      "position": [
        -900,
        180
      ],
      "parameters": {
        "jsCode": "const comments = $input.first().json.comments;\n\n// Filter out any comment where user.username is 'Clickbot'\nconst filteredComments = comments.filter(comment => comment.user?.username !== 'ClickBot');\n\nreturn filteredComments;\n"
      },
      "typeVersion": 2,
      "alwaysOutputData": true
    },
    {
      "id": "ffa220b7-5097-4068-ad06-72ccaabcfd0d",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3320,
        420
      ],
      "parameters": {
        "width": 940,
        "height": 320,
        "content": "## Fetch Overtime Tasks\n\nPull ClickUp tasks in target statuses and folders with time_spent > time_estimate.\n\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "90acbb60-344b-4858-95e0-414af00ad3ec",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -700,
        1200
      ],
      "parameters": {
        "color": 3,
        "width": 540,
        "height": 260,
        "content": "## Get Time Entries  \nFetch time entries for each task to analyze where time was spent.\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "8ee5b71b-b9c6-4630-878c-fbbfc8574d57",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1700,
        -380
      ],
      "parameters": {
        "color": 4,
        "width": 3240,
        "height": 800,
        "content": "## Get Comments and Its threads\nPull all user comments and thread replies from ClickUp.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "3377bb1b-94b8-48e7-b8d0-bcd80ba0b41d",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1840,
        420
      ],
      "parameters": {
        "color": 6,
        "width": 860,
        "height": 520,
        "content": "## AI-Generated Checklist\n \nSend task data to GPT to extract two reason lists (extra time + overrun reasons).\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "99e3f2b0-8c6a-4780-85c6-f01bed99c0f9",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1020,
        1040
      ],
      "parameters": {
        "color": 2,
        "width": 1200,
        "height": 720,
        "content": "## Time spent on \n1) Development   \n2) Scoping \n3) Commenting+Call+Documentation  \n4) PR Review  \n5) QA  \n6) and miscellaneous time "
      },
      "typeVersion": 1
    },
    {
      "id": "fd221230-7152-4187-83df-bbd490192ffa",
      "name": "OpenAI Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        1380,
        1560
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "gpt-4o-mini"
        },
        "options": {
          "maxRetries": 1
        }
      },
      "credentials": {
        "openAiApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "bb32a71f-a59a-4a1d-a48f-4bbd87d69c87",
      "name": "Simple Memory1",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        1540,
        1560
      ],
      "parameters": {
        "sessionKey": "={$json.id}",
        "sessionIdType": "customKey",
        "contextWindowLength": "=3"
      },
      "typeVersion": 1.3
    },
    {
      "id": "e3ea3616-d303-4691-bbd9-a0a619cf7511",
      "name": "Generate time insights",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1500,
        1280
      ],
      "parameters": {
        "text": "={{$json.prompt}}\n",
        "options": {
          "maxIterations": 1
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 1.9
    },
    {
      "id": "07941593-50ca-4c08-b20e-b127fc5b8c85",
      "name": "Generate Reason checklist",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        2400,
        520
      ],
      "parameters": {
        "text": "=TASK INFO\n---------\ntaskId: {{$json.id}}\ntaskName: {{$json.name}}\ntextContent: {{$json.text_content}}\nstatus: {{$json.status.status}} (type: {{$json.status.type}}, color: {{$json.status.color}})\ntags: {{ JSON.stringify($json.tags) }}\n\ntimeEstimate: {{$json.time_estimate}}\ntimeEstimatesByUser: {{ JSON.stringify($json.time_estimates_by_user) }}\ntimeSpent: {{$json.time_spent}}\n\nCOMMENTS\n--------\ncomments: {{ JSON.stringify($json.comments) }}\n\nEach comment object includes:\n- id\n- comment_text\n- date\n- user (id, username)\n- reply_count\n- comment_thread (array of replies)\n\n\u2192 You must analyze the full thread of each comment (main + replies) to extract **literal**, factual insights only.\n\nTIME ENTRIES\n------------\ntimeEntries: {{ JSON.stringify($json.time_entries) }}\n\nEach time entry includes:\n- user (id, username)\n- time\n- intervals (each with start, end, time, description, tags)\n\nYOUR TASK\n---------\nYou must determine **if and why** more time was needed or estimates were exceeded, **based only on literal phrases present in the data**. Do **not infer** or invent. Follow these strict rules:\n\n1. **Analyze COMMENTS**\n   - Extract a reason only if you find an **exact statement** such as:\n     - Request for more time\n     - Mention of blockers\n     - Delays due to clarifications or product change\n     - Rework initiated from comments\n   - Ignore comments like: \u201cPR created\u201d, \u201cDone\u201d, \u201cTested\u201d, \u201cReviewed\u201d, or \u201cAdded field\u201d unless they **explicitly mention a delay or blocker**.\n\n2. **Analyze TIME ENTRIES**\n   - Extract reasons only if **descriptions or tags** mention:\n     - Debugging, research, clarification, or rework\n     - A specific delay reason like \u201cAPI didn\u2019t respond\u201d or \u201cIssue with X logic\u201d\n   - If interval notes are vague or missing, ignore them.\n\n3. **Build Checklist Output**\n   - If no factual, literal reason is found from either comments or time entries, return the following fallback result:\n\n```json\n[\n  {\n    \"taskId\": \"{{$json.id}}\",\n    \"check_list\": [\n      {\n        \"check_list_name\": \"why_needed_extra_time\",\n        \"check_list_items\": [\n          \"Not enough data available to determine why extra time was needed.\"\n        ]\n      },\n      {\n        \"check_list_name\": \"why_gone_over_estimate\",\n        \"check_list_items\": [\n          \"Not enough data available to determine why the estimate was exceeded.\"\n        ]\n      }\n    ]\n  }\n]\n\nDO NOT skip this fallback. It is mandatory if data lacks solid insights.\n\nCHECKLIST WRITING RULES\n------------------------\nThe checklist must contain **short, meaningful bullet points**, not copied sentences or chat-style remarks.\n\nExamples:\n\u274c \"I need 10hr extra on top of spent time to...\"\n\u2705 \"Requested 10 additional hours due to technical differences in extension vs DA frontend.\"\n\n\u274c \"During the demo we noticed a bug...\"\n\u2705 \"Bug identified during demo required a workaround due to Chrome extension restrictions.\"\n\n\u2714 Each checklist item must be:\n- Condensed to a few words or a sentence\n- Free of filler phrases like \"I noticed\", \"we saw\", \"I need\", etc.\n- Framed as standalone explanations \u2014 useful even when seen alone\n- Group similar reasons (e.g., debugging multiple issues = one bullet on extended debugging)\n- Start with verbs when possible: \u201cRequested\u201d, \u201cDebugged\u201d, \u201cReworked\u201d, \u201cIntegrated\u201d, \u201cAdjusted\u201d, etc.\n\nDO NOT:\n- Copy-paste comment text\n- Repeat multiple versions of the same cause\n- Include vague, conversational, or non-actionable text\n\nStrict Rules:\n-------------\n1. \u274c **Do not infer**, assume, or fabricate any reasons.\n2. \u2705 Extract a reason only if it is **clearly stated** in:\n   - a comment or its replies (e.g., \"need more time\", \"blocker found\", \"needs rework\")\n   - a time entry description (e.g., \"debugging API issue\", \"researching logic\")\n3. \u26d4\ufe0f Ignore vague or default phrases like \"work done\", \"tested\", \"code pushed\", etc.\n4. \u2705 If no valid reasons are found in a section, insert this **mandatory fallback reason**:\n\n\"Not enough data available to determine why extra time was needed.\"\nor\n\n\"Not enough data available to determine why the estimate was exceeded.\"\nYou MUST include these fallback messages if no real reasons are found. Do NOT return an empty checklist.\n\nOUTPUT FORMAT\nReturn only the raw JSON object in this format (no quotes, markdown, or stringification):\n\n[\n{\n\"taskId\": \"{{$json.id}}\",\n\"check_list\": [\n{\n\"check_list_name\": \"why_needed_extra_time\",\n\"check_list_items\": [\n/* factual reasons based on real comment or entry content \u2014 or fallback if none /\n]\n},\n{\n\"check_list_name\": \"why_gone_over_estimate\",\n\"check_list_items\": [\n/ factual reasons based on real comment or entry content \u2014 or fallback if none */\n]\n}\n]\n}\n]\nDO NOT wrap the result in quotes or return it as a string. Just return pure raw JSON array as output.\n",
        "options": {
          "maxIterations": 1
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 1.9
    },
    {
      "id": "bb11bcba-3420-454f-ac54-4d02af4a06c4",
      "name": "Code2",
      "type": "n8n-nodes-base.code",
      "position": [
        2040,
        1280
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "const rawOutput = $json[\"output\"];\nconst parsed = JSON.parse(rawOutput);\nreturn {time_entries_by_category:parsed};\n"
      },
      "typeVersion": 2
    },
    {
      "id": "f4778407-9c8c-471d-ac18-b4c6c2c8ddd7",
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "position": [
        3140,
        520
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.1
    },
    {
      "id": "8237e6ce-da95-4106-bd19-a09e8bfd92f8",
      "name": "Code3",
      "type": "n8n-nodes-base.code",
      "position": [
        540,
        1700
      ],
      "parameters": {
        "jsCode": "// n8n Code node: Categorize & Sum Time Entries\n\n// 1) Grab the array of users\nconst users = $input.first().json.time_entries || [];\n\n// 2) Define keyword lists & precedence\nconst categories = [\n  { name: 'Development', keywords: ['development','dev','feature','implement','coding','build'] },\n  { name: 'Commenting+Call+Documentation', \n    keywords: ['comment','comments','call','calls','documentation','docs','doc'] },\n  { name: 'Miscellaneous', keywords: [] },  // fallback\n  { name: 'PR Review',   keywords: ['review','pr','merge','approve'] },\n  { name: 'QA',          keywords: ['qa','test','bug','verify','validate'] },\n  { name: 'Scoping',     keywords: ['scope','estimation','plan','analy'] },\n];\n\n// Helper to find category\nfunction categorizeInterval(interval) {\n  const text = [\n    interval.description || '',\n    ...(interval.tags || [])\n  ].join(' | ').toLowerCase();\n\n  for (let cat of categories.slice(0, -1)) {\n    if (cat.keywords.some(k => text.includes(k))) {\n      return cat.name;\n    }\n  }\n  // No match \u2192 fallback\n  return 'Miscellaneous';\n}\n\n// Helper to format minutes as Xh Ym\nfunction formatMinutes(mins) {\n  const h = Math.floor(mins / 60);\n  const m = mins % 60;\n  return `${h}h ${m}m`;\n}\n\n// 3) Process each user\nconst result = users.map(userObj => {\n  const sums = {};\n  // Initialize sums\n  for (let cat of categories) sums[cat.name] = 0;\n\n  // Categorize & accumulate\n  for (let interval of userObj.intervals || []) {\n    const cat = categorizeInterval(interval);\n    sums[cat] += interval.time_in_minutes || 0;\n  }\n\n  // Build output array, omitting zeros\n  const time_spent_by_category = Object.entries(sums)\n    .filter(([, total]) => total > 0)\n    .map(([category, total]) => ({\n      category,\n      time: formatMinutes(total)\n    }));\n\n  return {\n    user: userObj.user.username,\n    time_spent_by_category\n  };\n});\n\n// 4) Return as JSON\nreturn {\n   result\n};\n"
      },
      "typeVersion": 2
    },
    {
      "id": "c2c2dd87-4a59-4f28-9a2d-4ac82b3baa01",
      "name": "Generate Prompt with Time entires",
      "type": "n8n-nodes-base.code",
      "position": [
        1100,
        1280
      ],
      "parameters": {
        "jsCode": "// Function node: \u201cBuild Prompt With time_entries + Keyword\u2011Count + Resources\u201d\nconst timeEntries = $input.first().json.time_entries || [];\n\nlet msg = { prompt: '' };\nconst rawJson = JSON.stringify(timeEntries, null, 2);\nconst taskInfo = `**TASK INFO**  \n${rawJson}`;\n\nconst instruction = `You are a Time\u2011Tracking Categorization Agent.  \nYou must process an array of users\u2019 intervals and assign each interval to exactly one category using **keyword match counting**.\n\n---\n\n### CATEGORIES & KEYWORDS  \n(Case-insensitive, substring match on description or tags.)\n\n- Development:      development, dev, feature, implement, coding, build  \n- PR Review:        review, pr, merge, approve  \n- QA:               qa, test, bug, verify, validate  \n- Scoping:          scope, estimation, plan, analy  \n- Commenting+Call+Documentation: comment, comments, call, calls, documentation, docs, doc  \n- Miscellaneous:    (fallback \u2014 when no keywords match)\n\n---\n\n### PROCESS PER INTERVAL:\n1. Combine description and tags into a single string.  \n2. Count keyword matches for each category.\n3. Assign the interval to the category with the **most matches**.  \n4. Break ties by choosing the category with the **alphabetically first name**.  \n5. If no keywords match, assign to **Miscellaneous**.  \n6. **Each interval must appear in only one category.**\n\n---\n\n### OUTPUT RULES:\n\n- For each user:\n   - Group intervals by category.\n   - Include exact intervals as an array under \\`resources\\`.\n   - Set the \"time\" field strictly by summing time_in_minutes from each item in resources.\n   - Format it as Xh Ym (e.g., 1h 45m).\n   - You must not guess, round, or calculate it separately.   \n  - Format the total as \u201cXh Ym\u201d.\n   - Omit any category with 0 total time.\n\n---\n\n### OUTPUT FORMAT:\n\nRespond with only the final JSON array (no markdown or code fences):\n\n[\n  {\n    \"user\": \"<username>\",\n    \"time_spent_by_category\": [\n      {\n        \"category\": \"Development\",\n        \"time\": \"Xh Ym\",\n        \"resources\": [<interval objects>]\n      },\n      {\n        \"category\": \"PR Review\",\n        \"time\": \"Xh Ym\",\n        \"resources\": [...]\n      },\n      ...\n    ]\n  },\n  \u2026 more users \u2026\n]`;\n\n\nmsg.prompt = [instruction, taskInfo].join('\\n\\n');\nreturn msg;\n"
      },
      "typeVersion": 2
    }
  ],
  "active": false,
  "settings": {
    "callerPolicy": "workflowsFromSameOwner",
    "executionOrder": "v1"
  },
  "versionId": "54ab225c-bc77-4a90-801c-8b2673494faa",
  "connections": {
    "Code": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code2": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "Convert to File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Simple Memory": {
      "ai_memory": [
        [
          {
            "node": "Generate Reason checklist",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "Simple Memory1": {
      "ai_memory": [
        [
          {
            "node": "Generate time insights",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "Modify Task data": {
      "main": [
        [
          {
            "node": "Merge task data, comments and time entries",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Get Clickup Tasks": {
      "main": [
        [
          {
            "node": "Filter out unnecessary data from Tasks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "Generate Reason checklist",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model1": {
      "ai_languageModel": [
        [
          {
            "node": "Generate time insights",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Return Comments data": {
      "main": [
        [
          {
            "node": "Merge task data, comments and time entries",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Master comments": {
      "main": [
        [
          {
            "node": "Loop Over Master comments",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch comment threads": {
      "main": [
        [
          {
            "node": "Modify threads comment data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate time insights": {
      "main": [
        [
          {
            "node": "Code2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Modify Time entries data": {
      "main": [
        [
          {
            "node": "Merge task data, comments and time entries",
            "type": "main",
            "index": 2
          },
          {
            "node": "Generate Prompt with Time entires",
            "type": "main",
            "index": 0
          },
          {
            "node": "Code3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Reason checklist": {
      "main": [
        [
          {
            "node": "Code",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Over Master comments": {
      "main": [
        [
          {
            "node": "Return Comments data",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Destructure & Filter comments array to loop them",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Modify Master comment data": {
      "main": [
        [
          {
            "node": "Re-merge all master comments",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Modify threads comment data": {
      "main": [
        [
          {
            "node": "Merge thread comments with master comments",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Move to next master comment": {
      "main": [
        [
          {
            "node": "Loop Over Master comments",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Re-merge all master comments": {
      "main": [
        [
          {
            "node": "Re-structure comments to process them in loop node",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If task has crossed estimation": {
      "main": [
        [
          {
            "node": "Fetch Time entries via task IDs",
            "type": "main",
            "index": 0
          },
          {
            "node": "Modify Task data",
            "type": "main",
            "index": 0
          },
          {
            "node": "Fetch Master comments",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Time entries via task IDs": {
      "main": [
        [
          {
            "node": "Modify Time entries data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If comments got thread comments": {
      "main": [
        [
          {
            "node": "Fetch comment threads",
            "type": "main",
            "index": 0
          },
          {
            "node": "Merge thread comments with master comments",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Re-merge all master comments",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Sort Master comments old to new": {
      "main": [
        [
          {
            "node": "If comments got thread comments",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Prompt with Time entires": {
      "main": [
        [
          {
            "node": "Generate time insights",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking \u2018Test workflow\u2019": {
      "main": [
        [
          {
            "node": "Get Clickup Tasks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter out unnecessary data from Tasks": {
      "main": [
        [
          {
            "node": "If task has crossed estimation",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge task data, comments and time entries": {
      "main": [
        [
          {
            "node": "Generate Reason checklist",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge thread comments with master comments": {
      "main": [
        [
          {
            "node": "Modify Master comment data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Destructure & Filter comments array to loop them": {
      "main": [
        [
          {
            "node": "Sort Master comments old to new",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Re-structure comments to process them in loop node": {
      "main": [
        [
          {
            "node": "Move to next master comment",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

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.

• clickUpApi
• openAiApi

About this workflow

This workflow automatically analyses tasks to uncover why the actual time spent exceeds the original estimates. It connects with ClickUp(Can do with any PMS like JIRA, Asana, Monday and more) and other project management tools to generate clear insights on overspending trends.…

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

More AI & RAG workflows → · Browse all categories →

Related workflows

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