Cohort Excel – O que é e como fazer a análise Cohort no Excel

Neste artigo é apresentada o que é a análise Cohort e também como fazer esta análise no Excel.

A análise Cohort é um subconjunto de análises comportamentais que carrega os dados de um determinado conjunto de dados, (por exemplo uma plataforma de e-commerce, usuários de um sistema, um jogo online, pessoas ingressantes em uma universidade), e ao invés de olhar estes dados como unidade, os classifica em grupos para análise.

Estes grupos relacionados ou coortes, geralmente compartilham características ou experiências comuns dentro de um período de tempo definido.

A análise Cohort permite que sejam claramente visualizados padrões ao longo do ciclo de vida de um cliente.

No exemplo que usaremos para desenvolver nosso modelo no Excel, este case será como de um sistema que possui assinantes.

Estes assinantes são divididos em grupos de mês de início, e o estudo visualizará quantos meses os usuários mantém a assinatura, demonstrando assim a retenção dos contratantes.

No exemplo da imagem acima temos os clientes que assinaram um sistema separados pelo mês de início, Out/16, Nov/16, Dez/16 e assim por diante.

Nas linhas vemos como percentualmente este grupo de assinantes se manteve ao longo de 12 meses. Percebemos por exemplo que no grupo de clientes que iniciaram em Out/16 foram perdidas 67% dos mesmos no 9.º mês.

Conseguimos assim comparar os clientes que iniciaram em Out/16 com os  outros meses todos, verificando assim os padrões que são apresentados e aonde devem ser tomadas ações para evitar os cancelamentos, também chamados de churn.

Criando a planilha

Para criar a planilha de análise Cohort a primeira etapa é prepararmos a base de dados.

No nosso exemplo foram disponibilizados os dados da seguinte forma:

• Dt Compra: Data em que o cliente iniciou a assinatura do sistema.
• Data Churn: Data em que houve o cancelamento da assinatura.

Para realizarmos a análise Cohort no Excel é necessário que sejam inclusas ainda duas outras colunas. Estas colunas permitirão que sejam agrupados os clientes das vendas e também que sejam realizados de forma mais simples os cálculos dos assinantes ativos em cada mês.

Para a coluna Mês Compra: =DATA(ANO([@[Dt Compra]]);MÊS([@[Dt Compra]]);1)

Para a coluna Mês Churn: =DATA(ANO([@[Data Churn]]);MÊS([@[Data Churn]]);1)

Para a função DATA, que foi usada para a consulta monta os dados á partir do ano, mês e dia informados, todos dados simples numéricos. No nosso caso, passamos o ano do campo Dt Compra usando a função ANO, o mês também da data de compra pela Dt Compra com a função MÊS e o dia fixamos como 1. Tudo isso para fazer com que os clientes que compraram naquele mês sejam agrupados na mesma data do dia 1 do mês e ano.

O mesmo para o campo Data Churn, que é a data de cancelamento, que será também agrupado pelo dia 1.º período do cancelamento.

Entendendo os cálculos da planilha Cohort Excel

No gif acima você consegue ver a movimentação dos grupos de períodos para análise, indo de períodos anteriores á posteriores.

Esta movimentação é feita á partir do campo ao lado do botão de rotação, que possui a fórmula: =DATAM(Cálculos!$B$1;Cálculos!$B$3)

A função DATAM adiciona ou reduz o período de meses determinado no segundo parâmetro.

Ou seja, temos a data mínima da nossa base de dados no campo Cálculos!$B$1 e no segundo parâmetro Cálculos!$B$3 temos o número de meses, este segundo parâmetro é manipulado pelo botão de rotação que fica ao lado da data. Obs.: veja este e outros detalhes no vídeo no topo deste artigo.

Cálculos dos grupos de clientes: Este cálculo é feito pela função =DATAM(D3;1), a função DATAM como já vimos anteriormente serve para que sejam adicionados ou removidos meses de uma data. No caso da fórmula, o valor é baseado na célula que temos ao lado do botão de rotação, e é arrastada para todos os meses.

Os meses das linhas são somente digitados e arrastados para formar os 12 meses, não possuem fórmulas.

Fórmula que calcula os assinantes em cada período

Na parte central da planilha nós temos a fórmula que calcula a quantidade de assinantes para cada grupo e período.

A fórmula utilizada na primeira célula é:

=SEERRO(SE(DATAM(D$3;LIN()-4)<DATA(ANO(HOJE());MÊS(HOJE());1);(CONT.SES(tbBase[Mês Compra];Análises!D$3;tbBase[Mês Churn];”>=”&DATAM(Análises!D$3;LIN()-4))+CONT.SES(tbBase[Mês Compra];Análises!D$3;tbBase[Mês Churn];1))/CONT.SES(tbBase[Mês Compra];Análises!D$3);0);0)

Vamos ver por partes:

• SEERRO : Esta função está verificando se os o resultado do cálculo é um erro, se for ele retorna 0, e se não for ele retorna o cálculo.
• SE(DATAM(D$3;LIN()-4)<DATA(ANO(HOJE());MÊS(HOJE());1) : Esta parte da função SE verifica se a data calculada é maior que o dia primeiro do mês atual. Esta verificação é necessária para evitar que sejam feitos cálculos para meses que ainda nem existem. A parte desta fórmula DATAM(D$3;LIN()-4), faz uma movimentação da data pelos meses das linhas, Mês 1, Mês 2, Mês 3 e assim por diante, movimentados pela função LIN()-4, a função LIN() é a função que retorna o número da linha de uma referência, e é reduzido de 4 para que seja calculado o mês correto na fórmula.
• (CONT.SES(tbBase[Mês Compra];Análises!D$3;tbBase[Mês Churn];”>=”&DATAM(Análises!D$3;LIN()-4)): Esta função conta na coluna Mês compra, que foi criada para agrupar os clientes assinantes de um mesmo mês no Cohort, e contado caso sejam da mesma data do grupo descrito no topo, e ainda, verifica-se se a Data Churn não é maior do que o mês da linha.
• +CONT.SES(tbBase[Mês Compra];Análises!D$3;tbBase[Mês Churn];1)) : Há clientes que não cancelaram a assinatura, esta contagem condicional verifica exatamente isso, conta somente os que não tem Data Churn. Esta contagem é somada a quantidade dos que cancelaram em algum momento.
• /CONT.SES(tbBase[Mês Compra];Análises!D$3): Os clientes daquele grupo e mês são divididos pela quantidade total que havia inicialmente no grupo, gerando assim o percentual de assinantes daquele período.

Cálculo de Média e Crescimento Cohort Excel

Com base nos dados calculados para cada grupo e período são realizados cálculos de Média e Crescimento, conforme abaixo:

Para o cálculo da coluna Média foi utilizada a função =MÉDIASE($D4:$O4;”>0″), aonde é realizada a média somente dos valores maiores do que 0 para que não interfiram na média.

E na coluna Crescimento foi utilizada a função =-(P4-P5), que realiza o cálculo do crescimento entre a média anterior e a média atual, o – no começo é justamente para apontar o crescimento ou o decréscimo, ficando negativo para decréscimo e positivo para acréscimo.

Para um melhor entendimento assista o vídeo no topo deste artigo e faça o download do arquivo clicando no botão abaixo:

Baixe a planilha

Marcos Rieper

Referências: https://pt.wikipedia.org/wiki/An%C3%A1lise_de_coorte

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: