Neste artigo você aprenderá como criar uma planilha de análise Cohort no Excel passo-a-passo com download gratuito da planilha exemplo.
O que é Análise Cohort?
A análise cohort, ou análise de coorte, é uma abordagem essencial na interpretação de dados, oferecendo uma perspectiva única ao examinar grupos específicos ao longo do tempo.
Em vez de tratar todos os usuários, clientes ou pacientes como um todo homogêneo, a análise cohort segmenta esses grupos em conjuntos distintos, permitindo uma compreensão mais profunda das mudanças e padrões ao longo de suas jornadas.
Esta análise pode ser feita em uma planilha no Excel e poderá criar ela passo-a-passo seguindo o vídeo ao topo do artigo e também fazer download da planilha no final do artigo.
Base de Dados da Planilha da Análise Cohort Excel
O exemplo que fizemos para este artigo é uma planilha com os clientes que fizeram assinatura do sistema por mês.
E a data churn, ou data de cancelamento da assinatura.
A ideia é termos quantas assinaturas foram mantidas ao longo dos meses desde que começou a assinatura.
A tabela possui os seguintes campos:
- Data da Compra: Data da compra de cada uma das assinaturas.
- Data Churn: Data do cancelamento da compra.
- Mês Compra: Mês da compra usando a fórmula =DATA(ANO([@[Dt Compra]]);MÊS([@[Dt Compra]]);1) que retorna a data do primeiro dia de cada mês que será usada na análise Cohort.
- Mês Churn: Mês do churn é o primeiro dia do mês da análise Cohort =DATA(ANO([@[Data Churn]]);MÊS([@[Data Churn]]);1)
Cálculos e Controle da Planiha Cohort Excel
Na planilha temos um controle de botão de rotação que usamos para mudar as datas.
Então mudamos a célula em cálculos ao lado de Atual.
Após isso clicamos na guia Desenvolvedor e em Inserir->Botão de Rotação.
Depois clicamos com o botão direito sobre o botão e na guia Controle mudamos o vínculo da célula para a célula para a célula B3 em Cálculos.
Cálculos da Planilha Cohort Excel
Para o cálculo da planilha de análise Cohort criamos a seguinte fórmula para o campo de data ao topo:
=DATAM(DATA(ANO(D5);MÊS(D5);1);SEQUÊNCIA(1;12;0))
Nas células de cálculo da planilha usamos o seguinte cálculo:
=SE(Cálculos!$B$4=1;SEERRO((CONT.SES(tbBase[Mês Compra];E$6)-CONT.SES(tbBase[Mês Compra];E$6;tbBase[Mês Churn];”>1″;tbBase[Mês Churn];”<=”&DATAM(E$6;LIN()-LIN(E$6)-1)))/ CONT.SES(tbBase[Mês Compra];E$6);0);CONT.SES(tbBase[Mês Compra];E$6)-CONT.SES(tbBase[Mês Compra];E$6;tbBase[Mês Churn];”>1″;tbBase[Mês Churn];”<=”&DATAM(E$6;LIN()-LIN(E$6)-1)))
Também clicamos na guia Desenvolvedor e incluímos os botões de opção que estão ligados com a célula B4 na planilha Cálculos.
Depois copiamos a fórmula para todas as outras células das colunas e linhas.
A fórmula irá alterar conforme vemos na planilha abaixo, ao rotacionar o controle e mudar a opção temos o seguinte resultado.
Como podemos ver os meses são mudados e também é alterado o resultado das células para percentual e quantidade conforme é alterado nos controles.
Download Planilha Análise Cohort Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo: