Jira Kanban Insights

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 →

{
  "name": "Jira Kanban Insights",
  "nodes": [
    {
      "parameters": {},
      "type": "n8n-nodes-base.manualTrigger",
      "typeVersion": 1,
      "position": [
        176,
        -64
      ],
      "id": "k1-trigger-001",
      "name": "When clicking 'Execute workflow'"
    },
    {
      "parameters": {
        "jsCode": "// Configura\u00e7\u00e3o do per\u00edodo de an\u00e1lise\nconst periodoDias = 30; // Ajuste conforme necess\u00e1rio: 7, 14, 30, 60, 90\n\nconst dataFim = new Date();\nconst dataInicio = new Date();\ndataInicio.setDate(dataFim.getDate() - periodoDias);\n\n// Formata datas para YYYY-MM-DD\nconst formatDate = (d) => d.toISOString().split('T')[0];\n\nreturn [{\n  json: {\n    periodoInicio: formatDate(dataInicio),\n    periodoFim: formatDate(dataFim),\n    periodoDias: periodoDias,\n    periodoNome: `\u00daltimos ${periodoDias} dias`,\n    periodoInicioTimestamp: dataInicio.toISOString(),\n    periodoFimTimestamp: dataFim.toISOString()\n  }\n}];"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        368,
        -64
      ],
      "id": "k2-periodo-001",
      "name": "1. Definir Per\u00edodo"
    },
    {
      "parameters": {
        "operation": "getAll",
        "returnAll": true,
        "options": {
          "jql": "=project = DH AND (resolved >= \"{{ $json.periodoInicio }}\" OR (status != Done AND updated >= \"{{ $json.periodoInicio }}\"))"
        }
      },
      "type": "n8n-nodes-base.jira",
      "typeVersion": 1,
      "position": [
        576,
        -64
      ],
      "id": "k3-buscar-issues-001",
      "name": "2. Buscar Issues do Per\u00edodo",
      "credentials": {
        "jiraSoftwareCloudApi": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "// Busca dados do per\u00edodo do node \"1. Definir Per\u00edodo\"\nconst periodoNode = $('1. Definir Per\u00edodo');\nconst periodoData = {\n  periodoInicio: periodoNode.item.json.periodoInicio,\n  periodoFim: periodoNode.item.json.periodoFim,\n  periodoDias: periodoNode.item.json.periodoDias,\n  periodoNome: periodoNode.item.json.periodoNome,\n  periodoInicioTimestamp: periodoNode.item.json.periodoInicioTimestamp,\n  periodoFimTimestamp: periodoNode.item.json.periodoFimTimestamp\n};\n\n// Retorna um item por issue key com dados do per\u00edodo\nreturn $input.all().map(item => ({\n  json: {\n    issueKey: item.json.key,\n    ...periodoData\n  }\n}));"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        784,
        -64
      ],
      "id": "k4-preparar-lista-001",
      "name": "3. Preparar Lista de Issues"
    },
    {
      "parameters": {
        "url": "=https://gupy-io.atlassian.net/rest/api/3/issue/{{$json.issueKey}}",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "jiraSoftwareCloudApi",
        "sendQuery": true,
        "queryParameters": {
          "parameters": [
            {
              "name": "expand",
              "value": "changelog,renderedFields"
            },
            {
              "name": "fields",
              "value": "summary,description,status,project,assignee,reporter,labels,created,updated,resolutiondate,issuetype,priority,parent,subtasks,duedate,comment"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        992,
        -64
      ],
      "id": "k5-detalhes-001",
      "name": "4. Buscar Detalhes Completos",
      "credentials": {
        "jiraSoftwareCloudApi": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "// Busca dados do per\u00edodo do node \"1. Definir Per\u00edodo\"\nconst periodoNode = $('1. Definir Per\u00edodo');\n\nif (!periodoNode || !periodoNode.item || !periodoNode.item.json) {\n  throw new Error('N\u00e3o foi poss\u00edvel acessar dados do per\u00edodo do node \"1. Definir Per\u00edodo\"');\n}\n\nconst periodo = periodoNode.item.json;\nconst periodoData = {\n  periodoInicio: periodo.periodoInicio,\n  periodoFim: periodo.periodoFim,\n  periodoDias: periodo.periodoDias,\n  periodoNome: periodo.periodoNome,\n  periodoInicioTimestamp: periodo.periodoInicioTimestamp,\n  periodoFimTimestamp: periodo.periodoFimTimestamp\n};\n\n// Verifica se temos os dados essenciais\nif (!periodoData.periodoInicio || !periodoData.periodoFim) {\n  throw new Error('Dados do per\u00edodo incompletos: ' + JSON.stringify(periodoData));\n}\n\n// Pega todas as issues do step anterior\nconst allItems = $input.all();\nconst issues = allItems.map(item => item.json).filter(item => item.key);\n\nif (issues.length === 0) {\n  throw new Error('Nenhuma issue encontrada no input');\n}\n\n// Retorna um \u00fanico objeto com dados do per\u00edodo + array de issues\nconst result = {\n  ...periodoData,\n  total_issues: issues.length,\n  issues: issues\n};\n\n// Log para debug\nconsole.log('Per\u00edodo:', result.periodoNome);\nconsole.log('Total Issues:', result.total_issues);\n\nreturn [{json: result}];"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1200,
        -64
      ],
      "id": "k6-consolidar-001",
      "name": "5. Consolidar Issues"
    },
    {
      "parameters": {
        "jsCode": "// ============================================\n// HELPERS DE TEXTO E ARRAYS\n// ============================================\n\n// Converte descri\u00e7\u00e3o ADF para texto simples\nfunction adfToText(desc) {\n  if (!desc) return '';\n  if (typeof desc === 'string') return desc;\n  try {\n    const walk = (n) => {\n      if (!n) return '';\n      if (n.type === 'text') return n.text || '';\n      if (Array.isArray(n.content)) {\n        return n.content.map(walk).join(n.type === 'paragraph' ? '\\n' : '');\n      }\n      return '';\n    };\n    return walk(desc).trim();\n  } catch {\n    return '';\n  }\n}\n\n// Estat\u00edsticas simples (percentis sobre valores num\u00e9ricos)\nfunction stats(arr) {\n  const v = arr.filter(x => Number.isFinite(x)).sort((a, b) => a - b);\n  if (!v.length) return { p50: null, p85: null, p90: null, p95: null, avg: null, min: null, max: null, stdev: null, cv: null };\n  const mid = Math.floor(v.length / 2);\n  const p50 = v.length % 2 ? v[mid] : (v[mid - 1] + v[mid]) / 2;\n  const p85 = v[Math.max(0, Math.ceil(v.length * 0.85) - 1)];\n  const p90 = v[Math.max(0, Math.ceil(v.length * 0.9) - 1)];\n  const p95 = v[Math.max(0, Math.ceil(v.length * 0.95) - 1)];\n  const avg = v.reduce((a, b) => a + b, 0) / v.length;\n  const variance = v.reduce((acc, val) => acc + Math.pow(val - avg, 2), 0) / v.length;\n  const stdev = Math.sqrt(variance);\n  const cv = avg > 0 ? stdev / avg : null;\n  return { p50, p85, p90, p95, avg, min: v[0], max: v[v.length - 1], stdev, cv };\n}\n\n// Normaliza strings (acentos, caixa, espa\u00e7os)\nfunction norm(s) {\n  return (s || '')\n    .toString()\n    .normalize('NFD')\n    .replace(/\\p{Diacritic}/gu, '')\n    .toLowerCase()\n    .trim();\n}\n\n// ============================================\n// DIAS \u00daTEIS (exclui s\u00e1b/dom)\n// ============================================\n\nfunction nextBusinessStart(d) {\n  const x = new Date(d);\n  while (x.getDay() === 0 || x.getDay() === 6) {\n    x.setDate(x.getDate() + 1);\n    x.setHours(0,0,0,0);\n  }\n  return x;\n}\n\nfunction prevBusinessEnd(d) {\n  const x = new Date(d);\n  while (x.getDay() === 0 || x.getDay() === 6) {\n    x.setDate(x.getDate() - 1);\n    x.setHours(23,59,59,999);\n  }\n  return x;\n}\n\nfunction businessDiffDays(a, b) {\n  if (!a || !b) return null;\n  let start = new Date(a);\n  let end = new Date(b);\n  if (end <= start) return 0;\n\n  start = nextBusinessStart(start);\n  end = prevBusinessEnd(end);\n  if (end <= start) return 0;\n\n  let days = 0;\n  const cur = new Date(start);\n  cur.setHours(0,0,0,0);\n  end.setHours(23,59,59,999);\n\n  while (cur <= end) {\n    const dow = cur.getDay();\n    if (dow !== 0 && dow !== 6) days += 1;\n    cur.setDate(cur.getDate() + 1);\n  }\n\n  const firstDayStart = new Date(start); firstDayStart.setHours(0,0,0,0);\n  if (firstDayStart.getDay() !== 0 && firstDayStart.getDay() !== 6) {\n    const frac = (start - firstDayStart) / (24*3600*1000);\n    days -= frac;\n  }\n\n  const lastDayStart = new Date(end); lastDayStart.setHours(0,0,0,0);\n  const lastDayEnd = new Date(end); lastDayEnd.setHours(23,59,59,999);\n  if (lastDayStart.getDay() !== 0 && lastDayStart.getDay() !== 6) {\n    const frac = (lastDayEnd - end) / (24*3600*1000);\n    days -= frac;\n  }\n\n  return Math.max(0, Number(days));\n}\n\n// ============================================\n// DEFINI\u00c7\u00c3O DE FLUXO\n// ============================================\n\nconst DONE_STATUSES = [\n  'done','concluido','conclu\u00eddo','fechado','closed','itens concluidos','itens conclu\u00eddos'\n].map(norm);\n\nconst PREP_STATUSES = [\n  'to do','priorizado','tarefas pendentes',\n  'ideias e insights',\n  'exploracao e analise','explora\u00e7\u00e3o e an\u00e1lise',\n  'prototipagem e validacao','prototipagem e valida\u00e7\u00e3o',\n  'design review',\n  'refinamento tecnico','refinamento t\u00e9cnico',\n  'pronto para desenvolvimento','pronto para desenvolver'\n].map(norm);\n\nconst WORK_STATUSES = [\n  'in progress','em andamento',\n  'code review',\n  'validacao','valida\u00e7\u00e3o',\n  'aceitacao','aceita\u00e7\u00e3o',\n  'pronto para deploy'\n].map(norm);\n\nfunction phaseOfStatus(sNorm) {\n  if (['ideias e insights','exploracao e analise','explora\u00e7\u00e3o e an\u00e1lise','prototipagem e validacao','prototipagem e valida\u00e7\u00e3o','design review','refinamento tecnico','refinamento t\u00e9cnico'].includes(sNorm)) {\n    return 'discovery';\n  }\n  if (['priorizado','tarefas pendentes','pronto para desenvolvimento','pronto para desenvolver','to do'].includes(sNorm)) {\n    return 'backlog';\n  }\n  if (['in progress','em andamento'].includes(sNorm)) return 'build';\n  if (['code review','design review'].includes(sNorm)) return 'review';\n  if (['validacao','valida\u00e7\u00e3o','aceitacao','aceita\u00e7\u00e3o'].includes(sNorm)) return 'validation';\n  if (['pronto para deploy'].includes(sNorm)) return 'deploy';\n  if (DONE_STATUSES.includes(sNorm)) return 'done';\n  return 'other';\n}\n\n// ============================================\n// HELPERS COM CHANGELOG\n// ============================================\n\nfunction extractStatusEvents(changelog) {\n  if (!changelog?.histories) return [];\n  const events = [];\n  for (const h of changelog.histories) {\n    const at = new Date(h.created);\n    for (const it of h.items || []) {\n      if (it.field === 'status') {\n        events.push({\n          at,\n          from: norm(it.fromString || it.from),\n          to: norm(it.toString || it.to)\n        });\n      }\n    }\n  }\n  events.sort((a,b) => a.at - b.at);\n  return events;\n}\n\nfunction firstTransitionToNormalized(changelog, targetSet) {\n  const events = extractStatusEvents(changelog);\n  const hit = events.find(e => targetSet.includes(e.to));\n  return hit ? hit.at : null;\n}\n\nfunction firstWorkStart(changelog) {\n  const events = extractStatusEvents(changelog);\n  if (!events.length) return null;\n  const toWork = events.find(e => WORK_STATUSES.includes(e.to));\n  if (toWork) return toWork.at;\n  const exitPrep = events.find(e => PREP_STATUSES.includes(e.from) && !PREP_STATUSES.includes(e.to));\n  return exitPrep ? exitPrep.at : null;\n}\n\nfunction countReopened(changelog) {\n  const events = extractStatusEvents(changelog);\n  let c = 0;\n  for (const e of events) {\n    if (DONE_STATUSES.includes(e.from) && !DONE_STATUSES.includes(e.to)) c++;\n  }\n  return c;\n}\n\nfunction totalTimeInStatusHours(changelog, statusName, now) {\n  const sNorm = norm(statusName);\n  const events = extractStatusEvents(changelog);\n  if (!events.length) return 0;\n\n  let totalMs = 0;\n  let inStatus = false;\n  let enteredAt = null;\n\n  for (const e of events) {\n    if (!inStatus && e.to === sNorm) {\n      inStatus = true; enteredAt = e.at;\n    } else if (inStatus && e.from === sNorm) {\n      totalMs += (e.at - enteredAt);\n      inStatus = false; enteredAt = null;\n    }\n  }\n  if (inStatus && enteredAt) totalMs += (now - enteredAt);\n  return totalMs / 3600000;\n}\n\nfunction phaseTimesBusinessDays(changelog, fields, now) {\n  const events = extractStatusEvents(changelog);\n  const out = { discovery: 0, backlog: 0, build: 0, review: 0, validation: 0, deploy: 0, done: 0 };\n\n  if (!events.length) return out;\n\n  let curStatus = events[0].from || norm(fields?.status?.name);\n  let curAt = new Date(fields?.created || events[0].at);\n\n  for (const e of events) {\n    const phase = phaseOfStatus(curStatus);\n    const days = businessDiffDays(curAt, e.at);\n    if (Number.isFinite(days)) {\n      if (!out[phase]) out[phase] = 0;\n      out[phase] += days;\n    }\n    curStatus = e.to;\n    curAt = e.at;\n  }\n\n  const lastPhase = phaseOfStatus(curStatus);\n  const days = businessDiffDays(curAt, now);\n  if (Number.isFinite(days)) {\n    if (!out[lastPhase]) out[lastPhase] = 0;\n    out[lastPhase] += days;\n  }\n\n  for (const k of Object.keys(out)) out[k] = Number(out[k].toFixed(2));\n  return out;\n}\n\nfunction getStatusFlow(changelog) {\n  const events = extractStatusEvents(changelog);\n  return events.map(e => ({\n    from: e.from || null,\n    to: e.to || null,\n    at: e.at.toISOString()\n  }));\n}\n\n// ============================================\n// PROCESSAMENTO\n// ============================================\n\nconst input = $json;\nconst periodoInicio = new Date(input.periodoInicioTimestamp);\nconst periodoFim = new Date(input.periodoFimTimestamp);\nconst periodoDias = input.periodoDias;\nconst periodoNome = input.periodoNome;\nconst issues = Array.isArray(input.issues) ? input.issues : [];\n\nconst now = new Date();\nconst bugTypes = ['bug','defeito'].map(norm);\nconst blockedStatusName = 'Blocked';\n\n// ===== Enriquecimento por issue =====\nconst enrichedIssues = issues.map(issue => {\n  const fields = issue.fields || {};\n  const changelog = issue.changelog || {};\n\n  const created = fields.created ? new Date(fields.created) : null;\n  const resolved = fields.resolutiondate ? new Date(fields.resolutiondate) : null;\n  const updated = fields.updated ? new Date(fields.updated) : null;\n\n  const statusName = fields.status?.name || null;\n  const statusNorm = norm(statusName);\n  const statusCategory = fields.status?.statusCategory?.name || null;\n  const isDone = DONE_STATUSES.includes(statusNorm) || norm(statusCategory) === 'done';\n  const issueType = fields.issuetype?.name || 'Unknown';\n  const isBug = bugTypes.includes(norm(issueType));\n  const priority = fields.priority?.name || 'None';\n\n  const assignee = fields.assignee?.displayName || 'Unassigned';\n  const reporter = fields.reporter?.displayName || 'Unknown';\n\n  const firstDone = firstTransitionToNormalized(changelog, DONE_STATUSES);\n  const workStartAt = firstWorkStart(changelog);\n\n  const leadTimeDays  = (created && resolved) ? businessDiffDays(created, resolved) : null;\n  const cycleTimeDays = (workStartAt && resolved) ? businessDiffDays(workStartAt, resolved) : null;\n  const waitTimeDays  = (created && workStartAt) ? businessDiffDays(created, workStartAt) : null;\n\n  let lastChangeDate = updated || created;\n  if (changelog?.histories?.length > 0) {\n    const lastHistory = changelog.histories[changelog.histories.length - 1];\n    const lastHistoryDate = new Date(lastHistory.created);\n    if (lastHistoryDate > lastChangeDate) lastChangeDate = lastHistoryDate;\n  }\n  const agingDays = !isDone && lastChangeDate ? businessDiffDays(lastChangeDate, now) : 0;\n\n  const reopenedCount = countReopened(changelog);\n  const hasRework = reopenedCount > 0;\n  const blockedTimeHours = blockedStatusName ? totalTimeInStatusHours(changelog, blockedStatusName, now) : 0;\n\n  const statusFlow = getStatusFlow(changelog);\n  const statusChanges = statusFlow.length;\n\n  const description = adfToText(fields.description);\n  const hasDescription = description.length > 10;\n\n  const hasSubtasks = Array.isArray(fields.subtasks) && fields.subtasks.length > 0;\n  const subtasksCount = hasSubtasks ? fields.subtasks.length : 0;\n\n  const labels = Array.isArray(fields.labels) ? fields.labels : [];\n  const phaseTimesDays = phaseTimesBusinessDays(changelog, fields, now);\n  const firstInProgress = firstTransitionToNormalized(changelog, WORK_STATUSES);\n\n  // Calcula Flow Efficiency\n  const tempoAtivo = phaseTimesDays.build + phaseTimesDays.review + phaseTimesDays.validation;\n  const tempoEspera = phaseTimesDays.backlog + (waitTimeDays || 0);\n  const flowEfficiency = (tempoAtivo + tempoEspera) > 0 ? tempoAtivo / (tempoAtivo + tempoEspera) : null;\n\n  return {\n    key: issue.key,\n    summary: fields.summary || '',\n    description_text: description,\n    has_description: hasDescription,\n\n    status_current: statusName,\n    status_category: statusCategory,\n    is_done: isDone,\n    issue_type: issueType,\n    is_bug: isBug,\n    priority: priority,\n\n    assignee,\n    reporter,\n\n    created: created?.toISOString() || null,\n    resolved: resolved?.toISOString() || null,\n    updated: updated?.toISOString() || null,\n    first_in_progress: firstInProgress?.toISOString() || null,\n    first_done: firstDone?.toISOString() || null,\n\n    lead_time_days: leadTimeDays !== null ? Number(leadTimeDays.toFixed(2)) : null,\n    cycle_time_days: cycleTimeDays !== null ? Number(cycleTimeDays.toFixed(2)) : null,\n    wait_time_days:  waitTimeDays  !== null ? Number(waitTimeDays.toFixed(2))  : null,\n    aging_days: Number((agingDays || 0).toFixed(2)),\n\n    reopened_count: reopenedCount,\n    has_rework: hasRework,\n    blocked_time_hours: blockedTimeHours ? Number(blockedTimeHours.toFixed(2)) : 0,\n\n    status_changes: statusChanges,\n    status_flow: statusFlow,\n\n    has_subtasks: hasSubtasks,\n    subtasks_count: subtasksCount,\n    labels,\n\n    phase_times_days: {\n      discovery: phaseTimesDays.discovery || 0,\n      backlog: phaseTimesDays.backlog || 0,\n      build: phaseTimesDays.build || 0,\n      review: phaseTimesDays.review || 0,\n      validation: phaseTimesDays.validation || 0,\n      deploy: phaseTimesDays.deploy || 0\n    },\n    \n    flow_efficiency: flowEfficiency !== null ? Number(flowEfficiency.toFixed(2)) : null\n  };\n});\n\n// ============================================\n// M\u00c9TRICAS AGREGADAS\n// ============================================\n\nconst doneIssues = enrichedIssues.filter(i => i.is_done);\nconst openIssues = enrichedIssues.filter(i => !i.is_done);\nconst bugsTotal = enrichedIssues.filter(i => i.is_bug);\nconst bugsDone = doneIssues.filter(i => i.is_bug);\nconst bugsOpen = openIssues.filter(i => i.is_bug);\n\nconst leadTimeStats = stats(doneIssues.map(i => i.lead_time_days));\nconst cycleTimeStats = stats(doneIssues.map(i => i.cycle_time_days));\nconst waitTimeStats = stats(doneIssues.map(i => i.wait_time_days));\nconst flowEfficiencyStats = stats(enrichedIssues.map(i => i.flow_efficiency));\n\nconst wipAgingStats = stats(openIssues.map(i => i.aging_days));\nconst currentWip = openIssues.length;\n\nconst throughput = doneIssues.length;\nconst throughputPerDay = periodoDias > 0 ? (throughput / periodoDias) : null;\n\nconst reworkIssues = enrichedIssues.filter(i => i.has_rework).length;\nconst reworkRatio = doneIssues.length > 0 ? (reworkIssues / doneIssues.length) : 0;\nconst bugRatio = enrichedIssues.length > 0 ? (bugsTotal.length / enrichedIssues.length) : 0;\nconst blockedTotalHours = enrichedIssues.reduce((sum, i) => sum + (i.blocked_time_hours || 0), 0);\nconst issuesWithoutDescription = enrichedIssues.filter(i => !i.has_description).length;\n\nconst loadBalance = {};\nfor (const it of enrichedIssues) {\n  const who = it.assignee || 'Unassigned';\n  if (!loadBalance[who]) loadBalance[who] = { total: 0, done: 0, open: 0, bugs: 0 };\n  loadBalance[who].total++;\n  if (it.is_done) loadBalance[who].done++; else loadBalance[who].open++;\n  if (it.is_bug) loadBalance[who].bugs++;\n}\n\nconst issueTypesDist = {};\nfor (const it of enrichedIssues) {\n  const t = it.issue_type || 'Unknown';\n  if (!issueTypesDist[t]) issueTypesDist[t] = { total: 0, done: 0 };\n  issueTypesDist[t].total++;\n  if (it.is_done) issueTypesDist[t].done++;\n}\n\nconst priorityDist = {};\nfor (const it of enrichedIssues) {\n  const p = it.priority || 'None';\n  if (!priorityDist[p]) priorityDist[p] = { total: 0, done: 0 };\n  priorityDist[p].total++;\n  if (it.is_done) priorityDist[p].done++;\n}\n\nconst topAgingIssues = [...openIssues]\n  .sort((a,b) => (b.aging_days||0) - (a.aging_days||0))\n  .slice(0, 10)\n  .map(i => ({\n    key: i.key, summary: i.summary, assignee: i.assignee, status: i.status_current,\n    aging_days: i.aging_days, priority: i.priority, issue_type: i.issue_type\n  }));\n\nconst topStatusChanges = [...enrichedIssues]\n  .sort((a,b) => (b.status_changes||0) - (a.status_changes||0))\n  .slice(0, 10)\n  .map(i => ({\n    key: i.key, summary: i.summary, status_changes: i.status_changes,\n    is_done: i.is_done, reopened_count: i.reopened_count\n  }));\n\nconst cycleCV = cycleTimeStats.cv;\nconst predictabilityScore = cycleCV !== null ? Math.max(0, Math.min(100, 100 * (1 - cycleCV))) : null;\n\n// WIP Stability (ideal: baixa varia\u00e7\u00e3o)\nconst wipIdeal = throughputPerDay ? Math.ceil(throughputPerDay * 1.5 * 7) : null; // 1.5x throughput semanal\n\nreturn [{\n  json: {\n    periodo: {\n      inicio: input.periodoInicio,\n      fim: input.periodoFim,\n      dias: periodoDias,\n      nome: periodoNome\n    },\n    metrics: {\n      scope: {\n        total: enrichedIssues.length,\n        completed: doneIssues.length,\n        incomplete: openIssues.length\n      },\n      flow: {\n        throughput: throughput,\n        throughput_per_day: throughputPerDay ? Number(throughputPerDay.toFixed(2)) : null,\n        lead_time: {\n          p50: leadTimeStats.p50 ? Number(leadTimeStats.p50.toFixed(2)) : null,\n          p85: leadTimeStats.p85 ? Number(leadTimeStats.p85.toFixed(2)) : null,\n          p90: leadTimeStats.p90 ? Number(leadTimeStats.p90.toFixed(2)) : null,\n          p95: leadTimeStats.p95 ? Number(leadTimeStats.p95.toFixed(2)) : null,\n          avg: leadTimeStats.avg ? Number(leadTimeStats.avg.toFixed(2)) : null,\n          min: leadTimeStats.min ? Number(leadTimeStats.min.toFixed(2)) : null,\n          max: leadTimeStats.max ? Number(leadTimeStats.max.toFixed(2)) : null\n        },\n        cycle_time: {\n          p50: cycleTimeStats.p50 ? Number(cycleTimeStats.p50.toFixed(2)) : null,\n          p85: cycleTimeStats.p85 ? Number(cycleTimeStats.p85.toFixed(2)) : null,\n          p90: cycleTimeStats.p90 ? Number(cycleTimeStats.p90.toFixed(2)) : null,\n          p95: cycleTimeStats.p95 ? Number(cycleTimeStats.p95.toFixed(2)) : null,\n          avg: cycleTimeStats.avg ? Number(cycleTimeStats.avg.toFixed(2)) : null,\n          min: cycleTimeStats.min ? Number(cycleTimeStats.min.toFixed(2)) : null,\n          max: cycleTimeStats.max ? Number(cycleTimeStats.max.toFixed(2)) : null\n        },\n        wait_time: {\n          p50: waitTimeStats.p50 ? Number(waitTimeStats.p50.toFixed(2)) : null,\n          p85: waitTimeStats.p85 ? Number(waitTimeStats.p85.toFixed(2)) : null,\n          p90: waitTimeStats.p90 ? Number(waitTimeStats.p90.toFixed(2)) : null,\n          avg: waitTimeStats.avg ? Number(waitTimeStats.avg.toFixed(2)) : null\n        },\n        flow_efficiency: {\n          avg: flowEfficiencyStats.avg ? Number(flowEfficiencyStats.avg.toFixed(2)) : null,\n          p50: flowEfficiencyStats.p50 ? Number(flowEfficiencyStats.p50.toFixed(2)) : null,\n          note: \"Tempo ativo / tempo total (incluindo espera)\"\n        }\n      },\n      wip: {\n        current: currentWip,\n        ideal: wipIdeal,\n        aging_p50: wipAgingStats.p50 ? Number(wipAgingStats.p50.toFixed(2)) : null,\n        aging_p85: wipAgingStats.p85 ? Number(wipAgingStats.p85.toFixed(2)) : null,\n        aging_p90: wipAgingStats.p90 ? Number(wipAgingStats.p90.toFixed(2)) : null,\n        aging_max: wipAgingStats.max ? Number(wipAgingStats.max.toFixed(2)) : null,\n        by_phase: {\n          discovery: Number(openIssues.reduce((s,i)=>s+(i.phase_times_days?.discovery||0),0).toFixed(0)),\n          backlog:   Number(openIssues.reduce((s,i)=>s+(i.phase_times_days?.backlog||0),0).toFixed(0)),\n          build:     Number(openIssues.reduce((s,i)=>s+(i.phase_times_days?.build||0),0).toFixed(0)),\n          review:    Number(openIssues.reduce((s,i)=>s+(i.phase_times_days?.review||0),0).toFixed(0)),\n          validation:Number(openIssues.reduce((s,i)=>s+(i.phase_times_days?.validation||0),0).toFixed(0)),\n          deploy:    Number(openIssues.reduce((s,i)=>s+(i.phase_times_days?.deploy||0),0).toFixed(0)),\n        }\n      },\n      quality: {\n        bugs_total: bugsTotal.length,\n        bugs_done: bugsDone.length,\n        bugs_open: bugsOpen.length,\n        bug_ratio: Number(bugRatio.toFixed(2)),\n        rework_issues: reworkIssues,\n        rework_ratio: Number(reworkRatio.toFixed(2)),\n        blocked_total_hours: Number(blockedTotalHours.toFixed(2)),\n        issues_without_description: issuesWithoutDescription\n      },\n      predictability: {\n        score: predictabilityScore !== null ? Number(predictabilityScore.toFixed(1)) : null,\n        coefficient_variation: cycleCV !== null ? Number(cycleCV.toFixed(2)) : null,\n        note: \"Score 0-100: quanto maior, mais previs\u00edvel (cycle time em dias \u00fateis)\"\n      },\n      distributions: {\n        by_assignee: loadBalance,\n        by_issue_type: issueTypesDist,\n        by_priority: priorityDist\n      }\n    },\n    insights: {\n      top_aging_issues: topAgingIssues,\n      top_status_changes: topStatusChanges\n    },\n    issues_detail: enrichedIssues\n  }\n}];"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1408,
        -64
      ],
      "id": "k7-calcular-metricas-001",
      "name": "6. Calcular M\u00e9tricas Kanban"
    },
    {
      "parameters": {
        "modelId": {
          "__rl": true,
          "value": "models/gemini-2.5-pro",
          "mode": "list",
          "cachedResultName": "models/gemini-2.5-pro"
        },
        "messages": {
          "values": [
            {
              "content": "=Voc\u00ea \u00e9 um gerente de engenharia experiente em Kanban e m\u00e9tricas de fluxo cont\u00ednuo.\n\nAnalise os dados dos \u00faltimos {{ $json.periodo.dias }} dias e gere um resumo executivo t\u00e9cnico focado em:\n- **Estabilidade e previsibilidade do fluxo**\n- **Sa\u00fade do WIP** (volume e aging)\n- **Efici\u00eancia do sistema** (throughput sustent\u00e1vel)\n\n**Refer\u00eancias (fluxo cont\u00ednuo):**\n- Cycle time (p85): \u2264 7 dias \u00fateis\n- Lead time (p85): \u2264 10 dias \u00fateis\n- WIP: est\u00e1vel (varia\u00e7\u00e3o < 20% entre per\u00edodos)\n- Aging m\u00e1ximo: \u2264 2x o p85 do cycle time\n- Throughput: est\u00e1vel e sustent\u00e1vel\n- Rework: \u2264 5%\n- Bloqueios: < 2% do tempo total\n- Flow Efficiency: \u2265 40%\n\n**IMPORTANTE: N\u00e3o mencione sprints, commitments ou escopo planejado.**\nFoque em: fluidez, estabilidade, gargalos e oportunidades de otimiza\u00e7\u00e3o do fluxo cont\u00ednuo.\n\n**Instru\u00e7\u00f5es:**\n- Seja direto e t\u00e9cnico, sem floreios\n- Use valores do JSON sempre que poss\u00edvel\n- Considere apenas dias \u00fateis (ignore fins de semana) nos c\u00e1lculos de tempo\n- SEMPRE que mencionar uma issue (DH-XXX), inclua o summary: \"DH-XXX: T\u00edtulo\"\n- **AGING/ESPERA**: S\u00f3 considere problema se o item estiver em trabalho ativo ('Em andamento', 'Code Review', 'Valida\u00e7\u00e3o', 'Aceita\u00e7\u00e3o', 'Pronto para Deploy'). IGNORE aging/espera de itens em prepara\u00e7\u00e3o ('Tarefas Pendentes', 'Priorizado', 'Refinamento T\u00e9cnico', 'Design Review', etc.) - \u00e9 NORMAL que fiquem esperando.\n- **DISTRIBUI\u00c7\u00c3O**: O time tem especializa\u00e7\u00e3o t\u00e9cnica. N\u00c3O mencione \"capacidade ociosa\" ou sugira \"puxar novo item\" para pessoas com poucos itens. Distribui\u00e7\u00e3o desigual \u00e9 NORMAL devido a skills diferentes.\n- **IMPORTANTE: Inclua TODAS as pessoas em per_participant** \u2014 use metrics.distributions.by_assignee. N\u00e3o omita ningu\u00e9m.\n- Retorne SOMENTE JSON v\u00e1lido\n- Copie periodo.nome e periodo.dias do input\n\n**Formato de sa\u00edda:**\n{\n  \"periodo_nome\": \"{{ $json.periodo.nome }}\",\n  \"periodo_dias\": {{ $json.periodo.dias }},\n  \"executive_summary_html\": \"1-2 par\u00e1grafos em HTML sobre fluidez, gargalos e estabilidade do sistema Kanban\",\n  \"metrics_summary\": [\n    {\n      \"metric\": \"Cycle Time (p85)\",\n      \"value\": \"X dias\",\n      \"status\": \"ideal | aceit\u00e1vel | alerta\",\n      \"comment\": \"breve explica\u00e7\u00e3o focada em estabilidade\"\n    }\n  ],\n  \"key_insights\": [\n    {\n      \"title\": \"t\u00edtulo curto\",\n      \"description\": \"observa\u00e7\u00e3o pr\u00e1tica sobre aging/espera de itens em TRABALHO ATIVO, balanceamento e fluxo. N\u00c3O mencione espera/aging de itens em prepara\u00e7\u00e3o.\",\n      \"action\": \"a\u00e7\u00e3o recomendada para otimizar o fluxo cont\u00ednuo\",\n      \"priority\": \"alta | m\u00e9dia | baixa\"\n    }\n  ],\n  \"wip_health\": {\n    \"current\": \"n\u00famero\",\n    \"ideal\": \"n\u00famero (se dispon\u00edvel)\",\n    \"aging_hotspots\": [\n      {\"key\":\"DH-XXX\",\"summary\":\"t\u00edtulo\",\"assignee\":\"...\",\"status\":\"...\",\"aging_days\":0}\n    ]\n  },\n  \"per_participant\": [\n    {\"person\":\"nome (TODAS as pessoas de metrics.distributions.by_assignee)\",\"observation\":\"breve observa\u00e7\u00e3o sobre balanceamento de carga. N\u00c3O mencione capacidade ociosa - o time tem especializa\u00e7\u00e3o.\",\"suggestion\":\"sugest\u00e3o focada em qualidade/efici\u00eancia, n\u00e3o em volume\"},\n    {\"person\":\"Unassigned\",\"observation\":\"SOMENTE mencione se houver itens Unassigned nos seguintes status: 'Em andamento', 'Code Review', 'Valida\u00e7\u00e3o', 'Aceita\u00e7\u00e3o', 'Pronto para Deploy'. Ignore completamente itens Unassigned em status de prepara\u00e7\u00e3o/discovery como: 'Tarefas Pendentes', 'Priorizado', 'Refinamento T\u00e9cnico', 'Design Review', 'Ideias e Insights', 'Explora\u00e7\u00e3o e An\u00e1lise', 'Prototipagem e Valida\u00e7\u00e3o', 'To Do' - nesses casos \u00e9 ESPERADO que n\u00e3o tenham assignee.\",\"suggestion\":\"atribuir respons\u00e1vel para itens em trabalho ativo\"}\n  ],\n  \"rationale\": \"1-2 frases explicando o porqu\u00ea das tend\u00eancias e estabilidade do fluxo\"\n}\n\n**Dados do per\u00edodo:**\n{{ JSON.stringify($json) }}"
            }
          ]
        },
        "options": {}
      },
      "type": "@n8n/n8n-nodes-langchain.googleGemini",
      "typeVersion": 1,
      "position": [
        1616,
        -64
      ],
      "id": "k8-gemini-001",
      "name": "7. An\u00e1lise com Gemini",
      "credentials": {
        "googlePalmApi": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "// Extrai o JSON do Gemini\nconst rawGemini = $json;\n\nlet geminiResponse;\ntry {\n  let textContent = rawGemini.content?.parts?.[0]?.text || JSON.stringify(rawGemini);\n  \n  // Remove markdown code blocks\n  textContent = textContent.trim();\n  if (textContent.startsWith('```json')) {\n    textContent = textContent.replace(/^```json\\s*/g, '').replace(/```\\s*$/g, '');\n  } else if (textContent.startsWith('```')) {\n    textContent = textContent.replace(/^```\\s*/g, '').replace(/```\\s*$/g, '');\n  }\n  \n  geminiResponse = JSON.parse(textContent);\n  \n} catch (error) {\n  throw new Error(`Erro ao processar Gemini: ${error.message}`);\n}\n\nconst periodoNome = geminiResponse.periodo_nome || 'Kanban Review';\nconst periodoDias = geminiResponse.periodo_dias || 30;\n\n// Gera HTML para Confluence\nfunction generateConfluenceHTML(data) {\n  let html = '<ac:structured-macro ac:name=\"info\">';\n  html += '<ac:rich-text-body>';\n  html += `<p><strong>Per\u00edodo:</strong> ${data.periodo_nome || periodoNome} (${data.periodo_dias || periodoDias} dias)</p>`;\n  html += '</ac:rich-text-body>';\n  html += '</ac:structured-macro>';\n  \n  html += '<h2>\ud83d\udcca Resumo Executivo</h2>';\n  html += data.executive_summary_html || '<p>Sem resumo dispon\u00edvel.</p>';\n  \n  if (data.metrics_summary && data.metrics_summary.length > 0) {\n    html += '<h2>\ud83d\udcc8 M\u00e9tricas</h2>';\n    html += '<table><tbody>';\n    html += '<tr><th>M\u00e9trica</th><th>Valor</th><th>Status</th><th>Coment\u00e1rio</th></tr>';\n    for (const m of data.metrics_summary) {\n      const statusIcon = m.status === 'ideal' ? '\ud83d\udfe2' : (m.status === 'aceit\u00e1vel' ? '\ud83d\udfe1' : '\ud83d\udd34');\n      html += `<tr>`;\n      html += `<td>${m.metric}</td>`;\n      html += `<td><strong>${m.value}</strong></td>`;\n      html += `<td>${statusIcon} ${m.status}</td>`;\n      html += `<td>${m.comment || ''}</td>`;\n      html += `</tr>`;\n    }\n    html += '</tbody></table>';\n  }\n  \n  if (data.wip_health) {\n    html += '<h2>\ud83d\udd04 Sa\u00fade do WIP</h2>';\n    html += `<p><strong>WIP Atual:</strong> ${data.wip_health.current || 0}`;\n    if (data.wip_health.ideal) {\n      html += ` | <strong>WIP Ideal:</strong> \u2264 ${data.wip_health.ideal}`;\n    }\n    html += '</p>';\n    \n    if (data.wip_health.aging_hotspots && data.wip_health.aging_hotspots.length > 0) {\n      html += '<h3>\u26a0\ufe0f Hotspots de Aging</h3>';\n      html += '<table><tbody>';\n      html += '<tr><th>Issue</th><th>Assignee</th><th>Status</th><th>Aging (dias)</th></tr>';\n      for (const item of data.wip_health.aging_hotspots) {\n        const issueDisplay = item.summary ? `${item.key}: ${item.summary}` : item.key;\n        html += `<tr><td>${issueDisplay}</td><td>${item.assignee}</td><td>${item.status}</td><td>${item.aging_days}</td></tr>`;\n      }\n      html += '</tbody></table>';\n    }\n  }\n  \n  if (data.key_insights && data.key_insights.length > 0) {\n    html += '<h2>\ud83d\udca1 Insights Acion\u00e1veis</h2>';\n    for (const insight of data.key_insights) {\n      const priorityIcon = insight.priority === 'alta' ? '\ud83d\udd34' : (insight.priority === 'm\u00e9dia' ? '\ud83d\udfe1' : '\ud83d\udfe2');\n      const title = insight.title || insight.theme || 'Insight';\n      html += '<ac:structured-macro ac:name=\"expand\">';\n      html += `<ac:parameter ac:name=\"title\">${priorityIcon} ${title}</ac:parameter>`;\n      html += '<ac:rich-text-body>';\n      html += `<p><strong>Descri\u00e7\u00e3o:</strong> ${insight.description || insight.evidence || 'N/A'}</p>`;\n      html += `<p><strong>A\u00e7\u00e3o recomendada:</strong> ${insight.action || 'N/A'}</p>`;\n      html += '</ac:rich-text-body>';\n      html += '</ac:structured-macro>';\n    }\n  }\n  \n  if (data.per_participant && data.per_participant.length > 0) {\n    html += '<h2>\ud83d\udc65 Por Participante</h2>';\n    html += '<table><tbody>';\n    html += '<tr><th>Pessoa</th><th>Observa\u00e7\u00e3o</th><th>Sugest\u00e3o</th></tr>';\n    for (const p of data.per_participant) {\n      html += `<tr><td>${p.person}</td><td>${p.observation || p.tone || ''}</td><td>${p.suggestion || p.suggested_next_step || ''}</td></tr>`;\n    }\n    html += '</tbody></table>';\n  }\n  \n  if (data.rationale) {\n    html += '<h2>\ud83c\udfaf An\u00e1lise Geral</h2>';\n    html += `<p>${data.rationale}</p>`;\n  }\n  \n  return html;\n}\n\nconst pageTitle = `Kanban Insights: ${periodoNome}`;\nconst htmlBody = generateConfluenceHTML(geminiResponse);\n\nreturn [{\n  json: {\n    pageTitle: pageTitle,\n    spaceKey: 'DHub',\n    htmlBody: htmlBody,\n    geminiData: geminiResponse\n  }\n}];"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1824,
        -64
      ],
      "id": "k9-formatar-001",
      "name": "8. Formatar para Confluence"
    },
    {
      "parameters": {
        "method": "POST",
        "url": "https://gupy-io.atlassian.net/wiki/rest/api/content",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpBasicAuth",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={{ {\n  \"type\": \"page\",\n  \"title\": $json.pageTitle,\n  \"space\": {\n    \"key\": $json.spaceKey\n  },\n  \"ancestors\": [\n    {\n      \"id\": \"4709056599\"\n    }\n  ],\n  \"body\": {\n    \"storage\": {\n      \"value\": $json.htmlBody,\n      \"representation\": \"storage\"\n    }\n  }\n} }}",
        "options": {}
      },
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        2032,
        -64
      ],
      "id": "k10-publicar-001",
      "name": "9. Publicar no Confluence",
      "credentials": {
        "httpBasicAuth": {
          "name": "<your credential>"
        }
      }
    }
  ],
  "connections": {
    "When clicking 'Execute workflow'": {
      "main": [
        [
          {
            "node": "1. Definir Per\u00edodo",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1. Definir Per\u00edodo": {
      "main": [
        [
          {
            "node": "2. Buscar Issues do Per\u00edodo",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2. Buscar Issues do Per\u00edodo": {
      "main": [
        [
          {
            "node": "3. Preparar Lista de Issues",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3. Preparar Lista de Issues": {
      "main": [
        [
          {
            "node": "4. Buscar Detalhes Completos",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4. Buscar Detalhes Completos": {
      "main": [
        [
          {
            "node": "5. Consolidar Issues",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5. Consolidar Issues": {
      "main": [
        [
          {
            "node": "6. Calcular M\u00e9tricas Kanban",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6. Calcular M\u00e9tricas Kanban": {
      "main": [
        [
          {
            "node": "7. An\u00e1lise com Gemini",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7. An\u00e1lise com Gemini": {
      "main": [
        [
          {
            "node": "8. Formatar para Confluence",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8. Formatar para Confluence": {
      "main": [
        [
          {
            "node": "9. Publicar no Confluence",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "kanban-v1",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "id": "KanbanFlowMetrics",
  "tags": []
}

Credentials you'll need

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

• googlePalmApi
• httpBasicAuth
• jiraSoftwareCloudApi

About this workflow

Jira Kanban Insights. Uses jira, httpRequest, googleGemini. Event-driven trigger; 10 nodes.

Source: https://github.com/amandanery-o/metricas_fluxo/blob/618bf0f629e5e9bcac8f285af2c8a65a74bf5c1e/workflows/kanban.json — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

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