Olá pessoal estamos aqui com mais uma aplicação interessante no Excel, irei demonstrar como fazer o método Cohort (Efeito de Coorte), pegando o material do grande amigo Marcos Rieper, ele fez um artigo muito interessante sobre esse método e fez até um vídeo no Youtube, recomendo a todos lerem primeiro e depois ver ele potencializado, segue o link abaixo:
Efeito de Coorte (Cohort)
Resumindo o que é o Cohort, este método utiliza as quantidades de clientes "entrantes" (novos) e clientes "saintes" (que cancelaram a assinatura), realizando um cálculo para verificar a taxa de retenção ao longo dos meses e a variação mês a mês.
O que será demonstrado aqui será o Cohort de maneira Dinâmica realizado no Excel, onde você poderá aumentar a quantidade de meses e avançar ou retroagir os meses, conforme a imagem abaixo:
Primeiramente você irá precisar de uma base de dados com a Data de Compra da Assinatura e a Data de Cancelamento, caso o cliente não tiver data de cancelamento é só não colocar nada. Segue a base abaixo um exemplo, a que foi utilizada na imagem acima não é a mesma que esta abaixo.
Esta base recomendo deixar em uma guia separada (no meu exemplo eu deixei separada) com o nome de "Base", onde na Coluna A inseri a "Data de Compra" e na Coluna B inseri a "Data de Cancelamento".
Voltando a guia principal (deixe com o nome de "Cohort" e será guia principal onde será adicionado os cálculos).
Vamos colocar na célula C3 a maior data do nosso modelo, segue abaixo:
=MÁXIMO(Base!$A:$A) ou em inglês =MAX(Base!$A:$A)na Célula G4 insira esta fórmula:
=SE(COLS(F$2:$G2)=$C$4;"Média";SE((COLS(F$2:$G2)-1)=$C$4;"Crescimento";SE(COLS(F$2:$G2)>=$C$4;"";DATAM(DATA(ANO($C$3);MÊS($C$3);1);COLS($G$2:G2)-$C$4-$C$5))))ou em inglês
=IF(COLUMNS(F$2:$G2)=$C$4;"Média";IF((COLUMNS(F$2:$G2)-1)=$C$4;"Crescimento";IF(COLUMNS(F$2:$G2)>=$C$4;"";EDATE(DATE(YEAR($C$3);MONTH($C$3);1);COLUMNS($G$2:G2)-$C$4-$C$5))))E arraste até a célula AA4
Nesta etapa o Excel irá adicionar os meses referente ao período que você deseja mostrar, será adicionado a Média e o Crescimento, todo de maneira dinâmica para caso seja alterado a quantidade de meses, que o Excel adapte automaticamente.
Agora vamos adicionar os meses, na Célula F5 insira essa fórmula:
=SE(LINS($F$5:F5)>$C$4;"";"Mês "&LINS($F$5:F5)) ou em inglês
=IF(ROWS($F$5:F5)>$C$4;"";"Mês "&ROWS($F$5:F5)) E arraste até a célula F22 (neste caso você poderá adicionar mais linhas mas deixei limitado até em 18 meses, mas é possível estender para a quantidade de meses desejada)
E por último só colocar na Célula G5 a seguinte fórmula abaixo:
=SEERRO(SE(OU(G$4="";$F5="");"";SE(G$4="Média";MÉDIASE(F5:$G5;">0");SE(G$4="Crescimento";SEERRO(-(F4-F5);"");SEERRO(SE(DATAM(G$4;ROWS(G$5:G5)-1)<DATA(ANO($C$2);MÊS($C$2);1);(CONT.SES(Base!$A:$A;">="&G$4;Base!$A:$A;"<="&FIMMÊS(G$4;0);Base!$B:$B;">="&DATAM(G$4;ROWS(G$5:G5)-1))+CONT.SES(Base!$A:$A;">="&G$4;Base!$A:$A;"<="&FIMMÊS(G$4;0);Base!$B:$B;"="))/CONT.SES(Base!$A:$A;">="&G$4;Base!$A:$A;"<="&FIMMÊS(G$4;0));0);0))));"")ou em inglês
=IFERROR(IF(OR(G$4="";$F5="");"";IF(G$4="Média";AVERAGEIF(F5:$G5;">0");IF(G$4="Crescimento";IFERROR(-(F4-F5);"");IFERROR(IF(EDATE(G$4;ROWS(G$5:G5)-1)<DATE(YEAR($C$2);MONTH($C$2);1);(COUNTIFS(Base!$A:$A;">="&G$4;Base!$A:$A;"<="&EOMONTH(G$4;0);Base!$B:$B;">="&EDATE(G$4;ROWS(G$5:G5)-1))+COUNTIFS(Base!$A:$A;">="&G$4;Base!$A:$A;"<="&EOMONTH(G$4;0);Base!$B:$B;"="))/COUNTIFS(Base!$A:$A;">="&G$4;Base!$A:$A;"<="&EOMONTH(G$4;0));0);0))));"")Algumas das fórmulas acima somente funcionarão em versões mais recentes do Excel. Ao tentar replica-lá, verifique se houver um erro de isenção das fórmula. Se inserir a fórmula corretamente, e persistir o erro poderá ser sua versão.
Esta fórmula realiza tanto os cálculos proporcionais dos meses, quanto a média e o percentual de crescimento.
Pronto! Agora é só mudar o período necessário de meses na Célula C4 e a adição mensal na Célula C5 que o Excel irá ajustar todos os dados.
Espero que tenham gostado desse artigo, gostaria de agradecer novamente ao Marcos Rieper pela a disponibilização da planilha com seus dados.
Gostou do artigo? Curta e compartilhe em suas redes sociais.
E como você aplicaria a análise Cohort em suas atividades?
Se este artigo tiver mais de 100 compartilhamentos colocarei o link aqui para Download do arquivo.
Abraços a todos e até o próximo artigo!
BALDINI Fabio
Frase do Dia - Retirado do livro "Uma senhora toma chá..." do Autor David Salsburg
To understand God’s thoughts, we must study statistics, for these are the measure of His purpose.
(Para entender as ideias de Deus, precisamos estudar estatística, porque esta é a medida do Seu propósito)
Florence Nightingale
