Como fazer uma Análise de Churn no Excel

fala DS seja muito bem-vindo ao canal esteja um Day acides e nesse vídeo eu vou ensinar os cinco passos para você fazer uma análise de turning no Excel então passo número um você vai definir o que que é turn para sua empresa Então você vai sentar com o time de produtos que projetou para o o produto para solucionar o problema de uma pessoa junto com o time de financeiro que tá monitorando quantos faturamento que esse produto traz e junto com as suas análises de dados vocês vão definir o que que é a categoria turning para empresa pode ser por exemplo que para empresa é definido turning todos aqueles clientes que fizeram a última compra nos últimos 90 dias então o cliente ele fez uma última compra passou 90 dias e ele não fez uma nova compra Então a partir desse dia ele entrou em turn para outras empresas por exemplo a definição do turn é quando o cliente não renova uma assinatura mensal então o cliente precisava pagar uma nova assinatura no mês ele não pagou essa assinatura então ele entrou em turn ou até mesmo algumas empresas que consideram tempo maior para entrar em turn então dois meses seguidos sem renovar assinatura é considerado um cliente em turning Então você precisa sentar você precisa munir as pessoas com algumas análises de dados e vocês em conjunto precisam definir essa regra de negócio que vai ser a definição do que que é turn do que que não é turn Esse é o passo número um o passo número 2 é você então pegar essa regra de negócio definida e classificar a sua base de clientes Então você vai pegar todos os seus clientes da base e você vai aplicar essa regra de negócio para poder dizer se um cliente nesse exato momento ele está em turn ou ele não está em turn Então você vai coletar todos os clientes da sua base e baseado nesse critério de negócio você vai classificar todos eles Como estando em turn ou não estando em turn nesse presente momento e o passo número três é você criar uma visão do cliente ou seja uma tabela visão cliente aonde você mostra todos os clientes e os seus estados né se ele está ou não incharne e qual foi o momento que Ele entrou em turn para exemplificar isso eu vou trazer aqui uma tabela uma uma planilha de dados para a gente trabalhar e conseguir enxergar isso aqui melhor tá a planilha que eu vou trazer para vocês aqui é uma planilha que tem mais ou menos essa cara aqui a gente tem algumas colunas né da coluna A até a coluna u que é uma visão cliente ou seja todas as linhas elas representam um cliente unicamente e as minhas colunas Elas mostram algumas características desse cliente inclusive se ele está no estado de turn ou não essa base de dados aqui eu peguei de um site chamado kegol que ele é um site público é um site pertence ao Google onde você tem ele hospeda competições de cientistas de dados Então as empresas elas publicam parte dos seus dados aqui dentro dessa competição e as pessoas elas podem se inscrever nas competições para resolver os problemas que são propostos pelas empresas aqueles times aqueles indivíduos que tem a melhor resolução eles ganham prêmios em dinheiro então isso aqui é um grande playground aí para cientistas para analista de dados e também é uma ótima forma para você aprender essas profissões então Os dados aqui disponíveis são Dados reais de empresas e os problemas de negócio que eles também escrevem aqui são problemas reais de uma empresa então é a melhor forma de você aprender a carreira de cientista ou analista de dados é você realmente pegar problemas reais as empresas e criar soluções que são pedidas aqui em cada uma dessas competições então eu vou usar o conjunto de dados de uma empresa chamada telco Aonde eles conseguiram criar essa visão cliente de algumas características né inclusive Se esse cliente está no estado de turn ou não Então aqui tem algumas características sobre o conjunto de dados né que tem algumas discussões é que tem os códigos que as pessoas fizeram também e aqui tem a planilha de dados efetivamente que a gente vai usar Então ela tem algumas colunas a gente vai passar por essas colunas aqui embaixo tem a explicação do que que é cada uma dessas colunas então é o crush e o custo ele é masculino e feminino né se o cruster ele é um Sr Citizen deve ser um cidadão mesmo né Não sei porque do Sênior mas parceiro né se o cliente ele tem um parceiro ou não Talvez seja casado ou alguma coisa assim né os dependentes e assim por diante mas a coluna que vai mais importar pra gente As coluna de taylory aqui que é o seguinte é o número de meses que o consumidor ele ficou com a empresa então cada número desse aqui mostra o tempo que essa pessoa está na empresa e a última coluna que a coluna que não aparece é que é quando a gente vai relacionar essa cola do temer junto com o turn para saber se esse tempo que a pessoa está na empresa ela está no turn ou ela não está no turning Então se a gente vai fazer a nossa análise e assim você precisa montar a tabela dentro da sua empresa para poder fazer uma análise de turn aqui no Excel então eu vou voltar pra nossa tabela e aqui a gente vai usar as colunas que são mais importantes para a gente nessa análise aqui que é basicamente a Crush também era de então a Jander até o i eu vou esconder essas colunas né e depois agir até o m aqui vou também esconder essas colunas do streaming até o total charges eu vou esconder também então são as três colunas mais importantes para nossa análise de Turner onde eu tenho o customerade identificador único do cliente eu tenho o tempo que ele está na empresa e eu tenho se ele está no estado de turn ou não então por exemplo esse primeiro cliente aqui ele está a empresa há um mês e ele não está em Turner esse segundo cliente está a 34 meses na empresa e também não está em turn já esse terceiro cliente aqui ele está na empresa dois meses e ele está em turn Então essa aqui é a visão cliente que a gente precisa para poder fazer uma análise de turning muito bem feita e antes obviamente de começar qualquer análise qualquer coisa que a gente vai fazer em termos de análise ou qualquer tipo de atividade a gente dá um passo para o lado e planejar certo não adianta nada a gente receber a demanda receber os dados de sair fazendo que nem um maluco é a chance de você perder tempo no meio do caminho porque você não tem um plano é muito grande então eu escrevi aqui algumas hipóteses que são algumas hipóteses que a gente vai procurar validar através desses dados Então quais são as três as quatro hipóteses que são muito importantes para uma análise A primeira é a seguinte o mês um cada um mês mais crítico para entrar em turning estou afirmando isso estou falando que o primeiro mês é o mês mais crítico para pessoa entrar em turning que significa que o primeiro mês ele precisa ser muito bem cuidado porque onde tem o maior risco dessa pessoa deixar de usar o produto porque ela ainda está se acostumando ela não sabe muito bem se ela fez a compra correta então ela tem um período ali crítico né que a empresa precisa cuidar para não deixar essa pessoa entrar em turning então a gente precisa descobrir se realmente o primeiro mês é esse mês mais crítico a segunda hipótese é uma taxa de turning média da empresa então a gente precisa dar um número para as pessoas de negócio terem na cabeça sobre mais ou menos qualquer taxa média de Turner na empresa então eu vou chutar um valor aqui de mais ou menos 30%, tá Que eu acho que é um turning mais ou menos aceitável pelo menos nas empresas que eu passei era mais ou menos por esse valor nós temos também uma terceira hipótese que é a taxa de turn ela tem que diminuir ao longo do tempo ou seja Quanto mais a pessoa está dentro da empresa usando os produtos menos ela tem deixar de usar porque aquilo faz parte da rotina dela resolve um problema dela ela vê aquilo como uma solução parte da vida dela e portanto quanto mais o tempo passa menos a pessoa deveria desistir se isso aqui for crescente a gente tem um problema muito grave com o produto porque as pessoas passam mais tempo e estão desistindo no longo prazo em que o produto não está legal e a quarta hipótese aqui a taxa de turn ela ser maior dependendo do meio de pagamento e o chute aqui que seja por boleto muitas vezes não porque a pessoa realmente ela não tá usando mais o produto mas porque simplesmente ela esqueceu de pagar a renovação por exemplo então a gente precisa analisar Qual é o meio de pagamento que impacta mais por exemplo na análise de Charme dado essas quatro hipóteses aqui vamos começar a avaliar essas hipóteses né a primeira hipótese é o mês é o mês mais crítico para cliente entrar em turning e a gente vai fazer aqui vamos desenhar um gráfico aonde a gente tem aqui o tempo que a pessoa está na empresa e se ela está em turn ou não a gente consegue visualmente ver aonde tem a maior taxa né de Turner por tempo que a pessoa está na empresa a gente consegue essa hipótese ou não para fazer isso é relativamente simples eu vou voltar aqui nos meus dados e aqui eu tenho dados que a gente chama de dados abertos né então por exemplo eu tenho cada cliente o tempo que ele está na empresa e se ele está em turn ou não eu preciso de alguma forma agrupar isso aqui então eu preciso olhar por mês quantos Clientes estão e não estão em turn para isso eu vou usar uma tabela dinâmica que é uma tabela que me permite fazer alguns cálculos agrupados Então vou ver aqui vou selecionar essas três colunas aqui na verdade da coluna a tecla coluna u vou vir aqui em inserir vou ver aqui em pivotable e vou criar um novo work City aqui né uma nova planilha vou dar um ok ele vai abrir aqui para mim e aqui da minha esquerda eu tenho a minha tabela dinâmica e a minha direita eu tenho as características dela tá Então olha só a primeira coisa que eu tenho que fazer é pensar é fato e dimensão Então qual é o fato que eu estou procurando nessa análise o fato que eu tô procurando é a quantidade de pessoas que estão em turn e não estão em turn então quando eu estou procurando esse fato eu estou querendo contar número de pessoas Então a primeira coisa que eu faço eu vim aqui em customer e arrasto aqui para velhos e ele vai mostrar para mim qual é a operação matemática que ele tá fazendo que aqui é o count Então estou realmente Contando os meus clientes então tô contando aqui os meus clientes aqui no customer ID eu posso vir aqui também nessa engrenagem e colocar aqui também como fio de sessions E aí eu posso colocar aqui também tá na mesma coisa tá depois eu posso trocar aqui também pelo Fields desse jeito e voltar do jeito que a gente tava vendo tá eu tô contando aqui o número de clientes se você olhar a tabela dinâmica que já mostrou que tem 7. 043 clientes Só que eu não quero os clientes totais eu quero os clientes que estão em turn e não estão em turn então eu vou procurar aqui a coluna que representa esse estado de Turner Vou colocar aqui nas colunas você vai mostrar para mim pessoas que não estão inchar E pessoas que estão incharnea E aí eu posso clicar aqui remover essa parte que é vazia certo eu trago aqui então Ó a soma disso aqui tem que dar o meu 7. 43 como eu tô vendo aqui embaixo certo depois disso eu venho aqui a minha coluna porque o meu interesse é saber isso ao longo dos meses e não só uma foto como está aqui agora então eu venho até o temer e arrasto aqui para o Rose então ele vai mostrar para mim agora é ao longo dos meses que a pessoa está na empresa Qual a porcentagem de pessoas que estão em turn e não estão em turn aí eu preciso posso fazer um filtro aqui também então posso tirar todo mundo que tá em Blank E aí me devolve isso aqui só que aqui olha só no mês zero ou seja no mês de compra da pessoa eu tenho 11 pessoas que não estão em turn e eu tenho então um total de 11 pessoas compradores no mês um que é o mês seguinte a primeira compra eu tenho 233 pessoas que não estão em turning e 380 que estão internado então tem 613 compradores aqui que passaram um mês após a compra mas não é não é legal enxergar isso aqui com números absolutos porque eu não tenho essa ideia de proporção então o ideal é transformar isso aqui por exemplo em porcentagem Como que eu faço isso eu venho aqui na tabela dinâmica botão direito faço aqui um summerise velho S então resumo para mim os valores como e eu coloco aqui ó porcentagem do Roll Total então ele calcula para mim aqui as porcentagens né das minhas colunas e aí eu só vou trocar a quantidade de zeros que eu tenho aqui para não ficar para ficar um negócio mais fácil de enxergar Então olha só a porcentagem de pessoas que não estão em Turner é 100% no mês zero no mês de compra passado um mês 62% entram e conta 38 que não entra em turn passado no segundo mês aí vai cair para 52 pessoas que entram em turning 48 pessoas que não estão em turn E aí a partir do mês 3 A gente já tem aqui uma inversão então de cara qual que é o Insight você já pode gerar que a empresa ela precisa fazer o máximo de esforço possível para que o cliente ele permaneça na empresa por três meses Porque a partir do quarto mês a porcentagem de Charlie channe ela se inverte então o mês crítico pra fidelizar um cliente a partir do mês 3 Então você já você vai sim site para o time de negócio só olhando diretamente para tabela aqui isso aqui nem era uma hipótese que a gente tinha planejado mas obviamente analisando os dados já consegue chegar nessas nessa nesse site E aí para fazer realmente o que a gente queria fazer é só a gente vir aqui por exemplo eu posso clicar aqui por exemplo em search coloco aqui gráfico de coluna E aí esse gráfico de coluna eu posso puxar aqui do lado posso tirar aqui e fazer ele ficar um pouco maior tá vendo Então olha só que legal eu tenho aqui no mês zero as pessoas que não tem o turn tá não estão em turn que é a maior porcentagem aqui tá para ficar mais fácil também eu posso filtrar aqui ó Então vem aqui embaixo no temer clica aqui no selectiol e coloca por exemplo sete primeiros dias e aí eu dou um Ok quando eu dou um ok ele vai filtrar aqui para mim olha só que legal no primeiro dia né no no mês da compra a gente tem aqui não tem nenhum turno obviamente não tem como dar o Turner primeiro mês depende novamente da definição nem na empresa tá mas aqui é zero depois olha só eu tenho a porcentagem de Turner aqui como 62% no primeiro mês após a compra depois ela cai para 52% no segundo mês após a compra depois já inverte então agora o meu turn já cai para 47 após o terceiro mês de compra e assim se mantém então conforme vai passando mais o tempo né a pessoa vai desistindo menos do produto e olha só que interessante a partir do sexto mês a gente já tem uma diferença bem considerável entre quem é turning não é turn Então quais são os dois insights aqui o terceiro mês é o mês crítico para pessoa fidelizar então o time de produto o time de marketing o time de customer Experience tem que fazer o que for possível para manter essa pessoa por três meses e para fidelizar mesmo para diminuir ainda mais a taxa de cancelamento né de Turner a pessoa tem que ficar pelo menos seis meses então o sexto mês né um semestre usando o produto deles ainda mais o cliente Então a nossa primeira hipótese ela verdadeira ou seja o mês 1 após a compra é o mês mais crítico para entrar em turn Então essa aqui é a nossa resposta Vou colocar aqui como resposta e aí a nossa hipótese está verdadeira certo depois a segunda hipótese é a taxa de turn da empresa de 30%, Então a gente tem que voltar aqui e calcular Qual que é a taxa de turn da empresa certo eu posso fazer de duas formas eu posso calcular de novo uma tabela dinâmica que eu acho que é mais legal né colocar isso numa outra aba Então vem aqui na minha tabela original clico aqui em inserir tabela dinâmica pivotable clico aqui em OK depois que eu quero aqui agora eu quero a taxa de turning média então o que que eu vou fazer eu vou contar todos os meus custers novamente contar eles aqui né e vou contar eles a partir por mês separados dentro do turning e do do turn E aí eu vou eliminar todas as linhas que estão va para não atrapalhar nem a minha conta vou fazer isso tanto para turn quanto para os meses e aí aqui no final eu posso converter isso aqui de novo resumir isso aqui na verdade pelo Total Row e aqui no final ó eu tenho aqui né 73% de pessoas que não estão em turn contra 26 pessoas que estão em turning Então o 26% que ele representa a porcentagem total né o estágio Total aqui de Turner se eu tirar uma média disso deixa eu ver quanto que vai dar se eu tirar uma média a média de turning vai dar 22 né então a média de Turner da empresa por mês é 22 mas se eu tirar uma foto agora exatamente e contar quantas pessoas em turn para calcular a porcentagem da empresa aí a 26.

54 porque isso aqui é a soma de todo mundo que tá em turn nesse momento dividido pela quantidade Total então 26. 54 de turn é o turn da empresa hoje e 22% é o turning médio tá então posso voltar aqui ó tua taxa de turn da empresa é 26. 54 então é 26.