![Como coletar coortes de usuários como gráficos no Grafana [+ imagem docker com exemplo]](https://prohoster.info/wp-content/plugins/wp-fastest-cache-premium/pro/images/blank.gif)
Como resolvemos o problema de visualização de coortes de usuários no serviço Promopult usando Grafana.
- um serviço poderoso com um grande número de usuários. Ao longo dos 10 anos de operação, o número de cadastros no sistema ultrapassou um milhão. Quem já encontrou serviços semelhantes sabe que esse leque de usuários está longe de ser homogêneo.
Alguém se inscreveu e “adormeceu” para sempre. Alguém esqueceu a senha e se registrou mais algumas vezes ao longo de seis meses. Alguém traz dinheiro para a caixa registradora e alguém veio buscar brindes . E seria bom obter algum lucro de todos.
Em conjuntos de dados tão grandes como o nosso, analisar o comportamento de um usuário individual e tomar microdecisões é inútil. Mas captar tendências e trabalhar com grandes grupos é possível e necessário. Que é exatamente o que fazemos.
Resumo
- O que é análise de coorte e por que é necessária?
- Como fazer coortes por mês de cadastro de usuários em SQL.
- Como transferir coortes para .
Se você já sabe o que é análise de coorte e como fazê-la em SQL, pule direto 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 utilizar o serviço. A partir daqui é calculado o tempo de vida do usuário, e este é um indicador com base no qual uma análise bastante complexa pode ser realizada. Por exemplo, entenda:
- como o canal de aquisição influencia a vida do usuário;
- como o uso de qualquer recurso ou serviço afeta a vida útil;
- Como o lançamento do recurso X afetou a vida útil em comparação com o ano passado.
2. Como fazer coortes em SQL?
O tamanho do artigo e o bom senso não nos permitem apresentar aqui nossos dados reais - no dump de teste, estatísticas de um ano e meio: 1200 usuários e 53 transações. Para que você possa brincar com esses dados, preparamos uma imagem docker com MySQL e Grafana na qual você pode experimentar tudo sozinho. Link para GitHub no final do artigo.
E aqui mostraremos a criação de coortes usando um exemplo simplificado.
Vamos supor que temos um serviço. Os usuários se cadastram lá e gastam dinheiro em serviços. Com o tempo, os usuários desistem. Queremos saber quanto tempo vivem os usuários e quantos deles caem após o 1º e 2º mês de utilização do serviço.
Para responder a essas perguntas, precisamos construir coortes com base no mês de registro. Mediremos a atividade por despesas em cada mês. Em vez de despesas, pode haver pedidos, taxas de assinatura ou qualquer outra atividade baseada no tempo.
Dados iniciais
Os exemplos foram feitos em MySQL, mas para outros SGBDs não deve haver diferenças significativas.
Tabela de usuários - usuários:
ID do usuário
Data de registro
1
2019-01-01
2
2019-02-01
3
2019-02-10
4
2019-03-01
Tabela de custos – faturamento:
ID do usuário
Data
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
Selecione todas as baixas de usuários e data de registro:
SELECT
b.userId,
b.Date,
u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
Resultado:
ID do usuário
Data
Data de registro
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 mês; para fazer isso, convertemos todas as datas em meses:
DATE_FORMAT(Date, '%Y-%m')Agora precisamos saber há quantos meses o usuário esteve ativo – essa é a diferença entre o mês do débito e o mês do cadastro. MySQL tem uma 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
ID do usuário
Mês de faturamento
Data de registro
MesesDiferença
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
4
2019-03
2019-03
0
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:
Contagem de usuários
Mês de faturamento
Mês de Registro
MesesDiferença
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, um novo usuário apareceu cada - MonthsDiff = 0. Um usuário de janeiro estava ativo em fevereiro - RegistrationMonth = 2019-01, BillingMonth = 2019-02, e um usuário de fevereiro estava ativo em março.
Naturalmente, os padrões são mais visíveis em um grande conjunto de dados.
Como transferir coortes para Grafana
Aprendemos a formar coortes, mas quando há muitos registros não é mais fácil analisá-los. Os registros podem ser exportados para Excel e transformados em lindas tabelas, mas esse não é o nosso método!
As coortes podem ser mostradas como um gráfico interativo em .
Para fazer isso, adicionamos outra consulta para converter os dados em um formato adequado ao 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 (
## старый запрос, возвращающий когорты
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 carregue os dados para o Grafana.
Gráfico de exemplo de :
Toque com as mãos:
é uma imagem docker com MySQL e Grafana que você pode executar em seu computador. O banco de dados já contém dados de demonstração de um ano e meio, de janeiro de 2018 a julho de 2019.
Se desejar, você pode carregar seus dados nesta imagem.
Artigos PS sobre análise de coorte em SQL:
Fonte: habr.com