🥇Como reunir coortes de usuários em forma de gráficos no Grafana [+imagem do Docker com exemplo]

Como resolvemos a tarefa de visualização de coortes de usuários no serviço Promopult com a ajuda do 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 ultrapassou um milhão. Aqueles que já se depararam com serviços similares sabem que esse conjunto de usuários é longe de ser homogêneo.

Alguns se registraram e "dormiram" para sempre. Outros esqueceram a senha e se registraram mais algumas vezes ao longo de seis meses. Alguns trazem dinheiro para o caixa, enquanto outros vieram em busca de recursos gratuitos ferramentas. E seria ótimo obter algum lucro de cada um deles.

Em grandes conjuntos de dados como o nosso, analisar o comportamento de um único usuário e tomar microdecisões é inútil. Mas capturar tendências e trabalhar com grandes grupos — isso sim pode e deve ser feito. E é o que estamos realmente fazendo.

Resumo

O que é análise de coorte e para que serve.
Como criar coortes com base no mês de registro dos usuários em SQL.
Como transferir coortes para Grafana.

Se você já sabe o que é análise de coorte e como fazê-la em SQL, vá diretamente para a última seção.

1. O que é análise de coorte e para que serve

A análise de coorte é um método baseado na comparação de diferentes grupos (coortes) de usuários. Geralmente, formamos grupos com base na semana ou mês em que o usuário começou a usar o serviço. A partir daí, calcula-se o tempo de vida do usuário, um indicador que pode ser a base para uma análise bastante complexa. Por exemplo, entender:

como o canal de aquisição impacta o tempo de vida do usuário;
como o uso de uma determinada função ou serviço afeta o tempo de vida;
como o lançamento da funcionalidade X influenciou o tempo de vida em comparação com o ano passado.

2. Como criar coortes em SQL?

O tamanho do artigo e o bom senso não permitem trazer aqui nossos dados reais — no conjunto de teste, a estatística abrange um ano e meio: 1200 usuários e 53.000 transações. Para que você possa brincar com esses dados, preparamos uma imagem do Docker com MySQL e Grafana, onde você pode experimentar tudo isso por si mesmo. O link para o GitHub está no final do artigo.

Aqui vamos mostrar a criação de coortes por meio de um exemplo simplificado.

Suponha que temos um serviço. Nele, os usuários se registram e gastam dinheiro em serviços. Com o tempo, os usuários se afastam. Queremos saber quanto tempo os usuários permanecem e quantos desistem após o primeiro e o segundo mês de uso do serviço.

Para responder a essas perguntas, precisamos formar coortes com base no mês de registro. Vamos medir a atividade pelos gastos em cada mês. Em vez de gastos, podem ser pedidos, assinaturas ou qualquer outra atividade vinculada ao tempo.

Dados de origem

Os exemplos são feitos em MySQL, mas não deve haver diferenças significativas para outros SGBDs.

Tabela de usuários — users:

userId
DataRegistro

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabela de despesas — billing:

userId
Date
Soma

1
2019-01-02
11

1
2019-02-22
11

2
2019-02-12
12

3
2019-02-11
13

3
2019-03-11
13

4
2019-03-01
14

4
2019-03-02
14

Selecionamos todos os débitos dos usuários e a data de registro:

SELECT 
  b.userId, 
  b.Data,
  u.DataRegistro
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Resultado:

userId
Date
DataRegistro

1
2019-01-02
2019-01-02

1
2019-02-22
2019-01-02

2
2019-02-12
2019-02-01

3
2019-02-11
2019-02-10

3
2019-03-11
2019-02-10

4
2019-03-01
2019-03-01

4
2019-03-02
2019-03-01

Construímos coortes por meses, para isso, convertendo todas as datas em meses:

DATE_FORMAT(Data, '%Y-%m')

Agora precisamos saber quantos meses o usuário esteve ativo — isso é a diferença entre o mês da despesa e o mês de registro. Em MySQL, existe a função PERIOD_DIFF() — a diferença entre dois meses. Adicionamos PERIOD_DIFF() à consulta:

SELECT
    b.userId,
    DATE_FORMAT(b.Data, '%Y-%m') AS MesCobrança,
    DATE_FORMAT(u.DataRegistro, '%Y-%m') AS MesRegistro,
    PERIOD_DIFF(DATE_FORMAT(b.Data, '%Y%m'), DATE_FORMAT(u.DataRegistro, '%Y%m')) AS DiffMeses
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

userId
MesCobrança
DataRegistro
DiffMeses

1
2019-01
2019-01
0

1
2019-02
2019-01
1

2
2019-02
2019-02
0

3
2019-02
2019-02
0

3
2019-03
2019-02
1

4
2019-03
2019-03
0

Contamos os usuários ativados em cada mês — agrupamos os registros por MesCobrança, MesRegistro e DiffMeses:

SELECT
    COUNT(DISTINCT(b.userId)) AS ContagemUsuarios,
    DATE_FORMAT(b.Data, '%Y-%m') AS MesCobrança,
    DATE_FORMAT(u.DataRegistro, '%Y-%m') AS MesRegistro,
    PERIOD_DIFF(DATE_FORMAT(b.Data, '%Y%m'), DATE_FORMAT(u.DataRegistro, '%Y%m')) AS DiffMeses
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
GROUP BY MesCobrança, MesRegistro, DiffMeses

Resultado:

ContagemUsuarios
MesCobrança
MesRegistro
DiffMeses

1
2019-01
2019-01
0

1
2019-02
2019-01
1

2
2019-02
2019-02
0

1
2019-03
2019-02
1

1
2019-03
2019-03
0

Em janeiro, fevereiro e março, surgiu um novo usuário por mês — DiffMeses = 0. Um usuário de janeiro esteve ativo também em fevereiro — MesRegistro = 2019-01, MesCobrança = 2019-02, assim como um usuário de fevereiro esteve ativo em março.

Em um grande conjunto de dados, as tendências, naturalmente, são mais visíveis.

Como transferir coortes para o Grafana

Aprendemos a formar coortes, mas quando as gravações se tornam muitas, analisá-las já não é fácil. Os registros podem ser exportados para o Excel e formar lindas tabelas, mas esse não é o nosso método!

As coortes podem ser apresentadas na forma de um gráfico interativo em Grafana.

Para isso, adicionamos mais uma consulta para transformar 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 (
  ## consulta antiga que retorna coortes
  SELECT 
    COUNT(DISTINCT(b.userId)) AS Users, 
    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
  WHERE
    u.RegistrationDate BETWEEN '2018-01-01' AND CURRENT_DATE
  GROUP BY 
    BillingMonth, RegistrationMonth, MonthsDiff 
) AS s
GROUP BY 
  time_sec, metric

E exportamos os dados para o Grafana.

Exemplo de gráfico de demonstração:

Poder tocar com as mãos:

Repositório do GitHub com o exemplo — é uma imagem do Docker com MySQL e Grafana, que pode ser executada no seu computador. O banco já contém dados de demonstração por um ano e meio, de janeiro de 2018 a julho de 2019.

Se desejar, você pode carregar seus dados nesta imagem.

P.S. Artigos sobre análise de coortes em SQL:

https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/

https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/

Fonte: habr.com