Configuração completa para extração de dados do JIRA Cloud — Cenário de tempo por status/item
💡 Detalhes de como foram exportados os dados, além do script utilizado e overview das fórmulas. Concluindo em uma base completa para métricas de performance de times.
Índice
- Motivador
- Fluxos do JIRA
- Descrição e detalhamento da query
- Queries utilizadas
- Configuração inicial
- Criação das páginas
- Definição das colunas
- Data de transição de status
- Fórmula para gerar tempo por status
- Exportando os itens via script
- Configuração das Tabelas dinâmicas e WIP
- Tratamento de dados (tempo em impedimento, tempo por status, Action Time, Waiting Time, Efficiency, Lead Time, Cycle Time e Reaction Time)
- Configuração da página com os dados centralizados
- Configuração de agendamento
- Resultado Final
Observação: Todas as imagens foram retiradas de um projeto com dados de exemplo, restringindo os dados internos apenas aos times.
Ferramentas utilizadas
Google Sheets, JIRA e addon — JIRA Cloud for sheets.
Motivador
Existem inúmeras ferramentas para métricas de fluxo, cada uma com suas particularidades, preços, pontos positivos como automação e pontos negativos como mensalidades e falta de adaptação para alterações rápidas no fluxo.
Sendo assim, vimos a necessidade de criar uma ferramenta própria, com fácil manutenção e um planejamento rápido para evolução e adaptação para qualquer time ou área. E para essa ferramenta acontecer, precisávamos de MUITOS dados.
Como foi feito? Vamos contar nos próximos passos.
Fluxos do JIRA
O primeiro passo é entender o fluxo dos times e definir quais serão utilizados na base de dados.
No exemplo acima, vamos exportar os dados de transição entre todos os status e depois selecionar quais serão utilizados como configuração de cycle time e lead time.
Após definições com os times, estamos utilizando os status a partir do Para Refinamaneto Técnico até Donecomo lead time E para desenvolvimento até Done como cycle time.
Descrição e detalhamento da query
💡 Para realizar a busca de todos os itens, precisamos definir quais serão os filtros e campos utilizados na query.
O exemplo estruturado na ferramenta foi
Parâmetros de busca
- quais projetos
- que tipo de item
- data de finalização (em que o item mudou para o status done)
Campos para exibir informações dos itens
→ Padrão do JIRA
- Numero do item (key)
- Título
- Tipo do item (bug, story, spike, tech debt)
- Transição (status inicial, status final, data de transição)
- Data de criação
- Project
→ Personalizados (custom fields)
- Feature (sistema, labels, tags ou o que for necessário para identificar)
- Esforço investido
- Story points
- T-shirt size
Query
Utilizando a JQL (linguagem padrão do JIRA), temos as seguintes queries:
- Query para a pagina principal, exibindo os campos que iremos utilizar para dados
=JIRA("project in (proj1,proj2,proj3) AND issuetype in (problem, incident, bug, 'technical debt', spike, 'tech debt', story) AND status changed to done after '2021-01-01' ORDER BY updated DESC";"issuekey, summary, type, feature, Esforço Investido, created, project, story points, T-Shirt Size, Flagged, labels, priority";0;4000)
- Query para a pagina de manipulação de dados, onde iremos trabalhar com datas de transição de status
=JIRA("project in (proj1,proj2,proj3) AND issuetype in (problem, incident, bug, 'technical debt', spike, 'tech debt', story) AND status changed to done after '2021-01-01' ORDER BY updated DESC";"issuekey, transition.from, [transition.to](<http://transition.to/>),transition.date
Obs: A busca é a mesma e o que mudam são os campos, com exceção da key que será usada como chave estrangeira entre dados.
Configuração Inicial no Google Sheets
Sem segredos por aqui, precisamos apenas de uma planilha em branco e o plugin JIRA Cloud for sheets instalado A configuração inicial do plugin é um apenas um passo a passo de login e autorização do JIRA.
Também temos a opção de configurar a API manualmente, porém o script do componente citado acima já consegue trazer os dados.
Criação das páginas
A sequencia das página indica desde a configuração para manutenção a disponibilização final, sendo:
Config → Manutenção caso algum status seja alterado no JIRA.
Time in Status → obtenção inicial dos dados de transição de status.
Tabela dinâmica → estruturação de dados para gerar os itens de métricas que foram definidos.
WIP → página única para gerar um dado, com base nos status em que o item passou.
Auto Itens → página principal que disponibiliza os dados tratados e que será utilizada para base do DataStudio.
Config
Criamos uma pagina para configuração dos status, onde a coluna A recebe o nome dos status que serão calculados pela variação de datas entre o status inicial e status final.
Os nomes serão utilizados em fórmulas, para caso algum status do JIRA seja alterado, fique fácil para alterarmos na base base de dados, por isso é importante sempre deixar o nome exatamente igual o status do JIRA.
Time In Status
É nessa página que a mágica acontece
Aqui, recebemos via script as informações de data de transição dos itens
Definição das colunas
Atenção: Cada coluna é destinada para um parâmetro do script, então reservamos da coluna A até G para os dados automatizados.
As colunas configuradas manualmente foram:
H → Transition : Vamos transformar a data que o script trouxe em uma data “legível” para o datastudio
Fórmula
=SE(D2 = ""; ""; TEXTO(DATA(ESQUERDA(D2;4);midb(D2;6;2);midb(D2;9;2))+TEMPO(MIDB(D2;12;2);midb(D2;15;2);MIDB(D2;18;2));"yyyy-mm-dd hh:mm:ss"))
I → Resolved : Aqui deixamos a data onde o item passou para o status Resolved
Importante ter esse campo. Por padrão, o JIRA considera seu “resolved” como null e precisa ser corrigido ou configurado logo no início do projeto. Como temos projetos que passaram por muitas alterações, definimos o padrão de “Resolved” pela alteração para status Done
Fórmula =SE($C2=”Done”; H2;””)
Tempo por status
As linhas J até X receberam a seguinte fórmula J =Config!A6
K =Config!A7
L =Config!A8 (…)
Essas colunas buscam pelo nome de status do JIRA e comparam com o status que foi alterado
Consequentemente, as fórmulas abaixo do título com recebem a fórmula =SE(E($A2=$A3;$B2=J$1); DIATRABALHOTOTAL($H3;$H2);””)
Buscar se o valor do título está na coluna de transição de status
e retornar (em dias) o valor de diferença de datas entre quando o item entrou no status até quando o item saiu do status
Caso nao seja o mesmo valor, retorna vazio
Exportando os itens via script
Para a página atual, configuramos o plugin para receber a função =Jira()
💡 Para mais informações sobre o script, acesse aqui
E na célula A1, inserimos a Query de busca por transição de status e adicionamos os parâmetros Resultando a seguinte fórmula:
=JIRA("project in (proj1,proj2,proj3) AND issuetype in (problem, incident, bug, 'technical debt', spike, 'tech debt', story) AND status changed to done after '2021-01-01' ORDER BY updated DESC";"issuekey, transition.from, [transition.to](<http://transition.to/>),transition.date
Sendo assim, o script retorna e preenche as células de acordo com a função.
💡As colunas H → X serão preenchidas automaticamente, de acordo com o que a fórmula solicita.
Sendo a primeira parte via script e segunda parte via fórmula
Criação das Tabelas Dinâmicas
Como os itens ficam replicados em várias linhas para visualizar a data de cada status, precisamos de uma tabela dinâmica para disponibilizar os status individuais por linha, individualmente.
Selecionamos a tabela para replicar os dados da página “Time in Status”
❓ Selecionar colunas de A → X, clicar com o botão direito e gerar tabela dinâmica
Configurações
Linha como Key
Sem colunas
Valores como Colunas e adicionar todas as colunas de status como SUM Assim, obtemos a soma dos status de cada item
WIP
Também replicamos uma tabela dinâmica para o WIP, só que as seguintes configurações:
Linha como Key
Sem colunas
Valores como transition.to
Filtros como transition.to Status → Selecionando os status de WIP (que fazem parte do fluxo)
Resultado esperado
Tratamento de dados
Após gerar as tabelas dinâmicas e obtermos os valores base, precisamos extrair alguns valores que fazem sentido com as métricas, com base nos dados brutos.
Tempo em impedimento
❗ É necessário um alinhamento com os times para sinalizar um impedimento. No caso exemplo, os times utilizam a flag do JIRA para esse apontamento.
O tempo de impedimento foi gerado a partir da página Tempo por Status; Para identificar um item impedido, localizamos um item que fez uma transição e manteve no mesmo status
Utilizamos uma coluna vazia e definimos a seguinte fórmula
=SE(OU(C2 = “Done”;C2=””); “” ;SE(B2=C2;H2-H3;””))
Sendo assim, se um valor saiu de um status X e foi para o status X (Aguardando Deploy, no exemplo), consideramos o momento de ativação da flag.
A fórmula retorna a diferença de datas desse evento até o momento em que passa para o próximo status (Done, no caso do exemplo).
Tempo por status
A própria tabela dinâmica gera esse valor, tornando possível a criação das colunas:
- Touch Time = Soma de tempo das colunas de ação do fluxo = em desenvolvimento, em code review, em teste, (…)
- Waiting Time = Soma de tempo das colunas de pausa do fluxo = para desenvolvimento, aguardando deploy, (…)
- Efficiency = Fórmula com resultado de variação entre os dados de touch (TT) e waiting time (WT), especificamente
TT/(TT+WT)*100 - Lead Time = Soma de tempo de todos os status definidos como tempo de criação do item, no caso exemplo utilizamos de Para Refinamento Técnico até Done
- Cycle Time = Soma de tempo de todos os status a partir do momento de compromisso, no caso exemplo utilizamos de Para Desenvolvimento até Done
- Reaction Time = Diferença entre o Lead e Cycle time, resultando em quanto tempo o time inicia o desenvolvimento a partir do momento em que o item está refinado
Configuração da página com os dados centralizados
Agora temos todos os dados disponíveis!
Decidimos pela criação de uma página onde todos os dados seriam centralizados para realizar a exportação para outras ferramentas
Além dos dados criados com base no tempo por status, precisamos de informações retiradas direto do JIRA, para exibir filtros específicos que irão refletir na busca e criação de relatórios ou entendimentos sobre cases específicos dos times.
Então, configuramos na célula A1 o script com a seguinte query:
=JIRA("project in (proj1,proj2,proj3) AND issuetype in (problem, incident, bug, 'technical debt', spike, 'tech debt', story) AND status changed to done after '2021-01-01' ORDER BY updated DESC";"issuekey, summary, type, feature, Esforço Investido, created, project, story points, T-Shirt Size, Flagged, labels, priority";0;4000)
Com isso, refletimos a **Key (**que será utilizada no PROCV para buscar as métricas já tratadas em outras tabelas) e campos adicionais do JIRA para as métricas ( summary, type, t-shirt size, feature, Esforço Investido, created, project, story points).
❗Lembrando que para o script trazer os dados, precisamos das colunas liberadas Como no exemplo os dados chegaram até a coluna I, então os dados adicionais foram implementados a partir da coluna J
Enquanto às colunas a frente, buscamos os valores de acordo com a key pela seguinte fórmula:
Coluna J =PROCV($A2;’Time in Status’!$A:$I;8;FALSO) *Procuramos o valor de Key na time in status e retornar o *Resolved
Coluna K → S =PROCV($A2;’Tabela dinâmica’!$A:$AP;24;FALSO)**
Buscamos o valor da Key na tabela dinamica e time in status e retornamos Action Time, Waiting Time, Efficiency, Lead Time, Cycle Time, Reaction Time, Tempo em Impedimento, Throughput, WIP
Coluna T → AN =PROCV($A2;’Tabela dinâmica’!$A:$AP;2;FALSO) Identificamos o valor da Key na tabela dinâmica e retornamos os status cadastrados no fluxo.
Resultado esperado
Configuração de agendamento do script
O agendamento para obtenção dos dados via query pode ser feito dentro do próprio plugin, seguindo o seguinte passo:
Na guia de Schedule, selecione a frequência desejada e utilize o botão Get Issues Now para confirmar.
No exemplo, utilizamos o agendamento diário com horário para 2AM, sendo assim, os dados retornados são sempre do valor anterior ao analisado na ferramenta.
Resultado Final
Após a criação da base dados, exportamos para o DataStudio e estruturamos métricas que fazem sentido para o acompanhamento do fluxo e tomadas de decisão para vários cenários. Aqui temos algumas visões do projeto exemplo (com dados sensíveis retirados).
