Como resolvemos a tarefa de visualizar coortes de usuários no serviço Promopult usando o Grafana.
Promopult é um serviço poderoso com um grande número de usuários. Em 10 anos de operação, o número de registros no sistema excedeu um milhão. Quem já se deparou com serviços semelhantes sabe que esse conjunto de usuários está longe de ser homogêneo.
Alguém se registrou e "adormeceu" para sempre. Alguém esqueceu a senha e se registrou mais algumas vezes em seis meses. Alguém leva dinheiro para o caixa e alguém veio buscar ferramentas gratuitas. E seria bom obter algum lucro de todos.
Em matrizes de dados tão grandes quanto as nossas, não faz sentido analisar o comportamento de um usuário individual e tomar micro-decisões. Mas pegar tendências e trabalhar com grandes grupos é possível e necessário. O que nós, de fato, estamos fazendo.
Sumário
- O que é análise de coorte e por que é necessário.
- Como fazer coortes por mês de registro do usuário no SQL.
- Como transferir coortes para Grafana .
Se você já sabe o que é a análise de coorte e como fazê-lo no SQL, pule para a última seção.
1. O que é análise de coorte e por que é necessária
A análise de coorte é um método baseado na comparação de diferentes grupos (coortes) de usuários. Na maioria das vezes, nossos grupos são formados pela semana ou mês em que o usuário começou a usar o serviço. A vida útil do usuário é calculada a partir daqui, e este é um indicador com base no qual uma análise bastante complicada pode ser realizada. Por exemplo, entenda:
- Como o canal atrai a vida do usuário;
- como o uso de uma função ou serviço afeta a vida toda;
- como o lançamento dos recursos X afetou o tempo de vida em comparação com o ano passado.
2. Como criar coortes no SQL?
O tamanho do artigo e o senso comum não nos permitem apresentar nossos dados reais aqui - no despejo de teste, as estatísticas de um ano e meio são: 1.200 usuários e 53.000 transações. Para que você possa brincar com esses dados, preparamos uma imagem do docker com MySQL e Grafana, na qual você pode sentir tudo sozinho. Link para o GitHub no final do artigo.
E aqui mostramos a criação de coortes com um exemplo simplificado.
Suponha que tenhamos um serviço. Os usuários se registram e gastam dinheiro em serviços. Com o tempo, os usuários caem. Queremos saber por quanto tempo os usuários vivem e quantos deles caem após o primeiro e o segundo mês de uso do serviço.
Para responder a essas perguntas, precisamos criar coortes por mês de registro. A atividade será medida pelas despesas em cada mês. Em vez de custos, pode haver pedidos, uma taxa mensal ou qualquer outra atividade com prazo determinado.
Dados de origem
Os exemplos são feitos no MySQL, mas para o restante do DBMS não deve haver diferenças significativas.
Tabela de usuários - usuários:
Tabela de gastos - cobrança:
Selecione todas as cobranças do usuário e data do registro:
SELECT b.userId, b.Date, u.RegistrationDate FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
Resultado:
Construímos coortes por meses, para isso converteremos todas as datas em meses:
DATE_FORMAT(Date, '%Y-%m')
Agora precisamos saber quantos meses o usuário esteve ativo - essa é a diferença entre o mês do débito e o mês do registro. O MySQL possui a função PERIOD_DIFF () - a diferença entre dois meses. Adicione PERIOD_DIFF () à solicitação:
SELECT b.userId, DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth, DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth, PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
Contamos os usuários ativados em cada mês - agrupamos os registros por BillingMonth, RegistrationMonth e MonthsDiff:
SELECT COUNT(DISTINCT(b.userId)) AS UsersCount, DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth, DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth, PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId GROUP BY BillingMonth, RegistrationMonth, MonthsDiff
Resultado:
Em janeiro, fevereiro e março, um novo usuário apareceu - MonthsDiff = 0. Um usuário de janeiro estava ativo e em fevereiro RegistrationMonth = 2019-01, BillingMonth = 2019-02 e um usuário de fevereiro estava ativo em março.
Em um grande conjunto de dados, os padrões são naturalmente melhor visualizados.
Como transferir coortes para Grafana
Aprendemos como formar coortes, mas quando há muitos registros, não é mais fácil analisá-los. Os registros podem ser exportados para o Excel e criar tabelas bonitas, mas esse não é o nosso método!
As coortes podem ser exibidas como gráficos interativos no Grafana .
Para fazer isso, adicione outra consulta para converter os dados em um formato adequado para o Grafana:
SELECT DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec, SUM(s.Users) AS value, s.RegistrationMonth AS metric FROM (
E faça o upload dos dados para o Grafana.
Exemplo de gráfico da demonstração :
Toque em:
O repositório de exemplo do GitHub é uma imagem do docker com MySQL e Grafana que você pode executar no seu computador. O banco de dados já possui dados de demonstração por um ano e meio, de janeiro de 2018 a julho de 2019.
Se desejar, você pode enviar seus dados para esta imagem.
PS Artigos sobre análise de coorte no SQL:
https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/
https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/