🥇Cómo crear cohortes de usuarios en forma de gráficos en Grafana [+imagen de docker con ejemplo]

Cómo resolvimos el problema de visualización de cohortes de usuarios en el servicio Promopult utilizando Grafana.

Promopult — un potente servicio con un gran número de usuarios. En 10 años de operación, el número de registros en el sistema ha superado el millón. Aquellos que han tratado con servicios similares saben que esta masa de usuarios no es homogénea.

Algunos se registraron y "se durmieron" para siempre. Algunos olvidaron su contraseña y se registraron un par de veces más en medio año. Algunos llevan dinero a la caja, mientras que otros vinieron en busca de cosas gratis herramientas. Y sería bueno obtener algún tipo de beneficio de cada uno de ellos.

En grandes volúmenes de datos como los nuestros, analizar el comportamiento de un solo usuario y tomar micro-decisiones es inútil. Sin embargo, detectar tendencias y trabajar con grandes grupos es algo que se puede y se debe hacer. Eso es precisamente lo que hacemos.

Resumen

Qué es el análisis de cohortes y para qué sirve.
Cómo crear cohortes por mes de registro de usuarios en SQL.
Cómo trasladar las cohortes a Grafana.

Si ya sabes qué es el análisis de cohortes y cómo hacerlo en SQL, pasa directamente a la última sección.

1. Qué es el análisis de cohortes y para qué sirve

El análisis de cohortes es un método basado en la comparación de diferentes grupos (cohortes) de usuarios. La mayoría de las veces, nuestras grupos se forman por semana o mes en que el usuario comenzó a utilizar el servicio. A partir de ahí, se calcula la vida del usuario, que ya es un indicador sobre el cual se puede realizar un análisis bastante complejo. Por ejemplo, entender:

cómo el canal de adquisición influye en la vida del usuario;
cómo el uso de alguna función o servicio afecta la vida útil;
cómo el lanzamiento de la característica X afectó la vida útil en comparación con el año pasado.

2. ¿Cómo hacer cohortes en SQL?

El tamaño del artículo y el sentido común no permiten incluir aquí nuestros datos reales: en el volcado de prueba hay estadísticas de un año y medio: 1200 usuarios y 53,000 transacciones. Para que puedas jugar con estos datos, preparamos una imagen de docker con MySQL y Grafana, donde puedes explorar todo esto por ti mismo. El enlace a GitHub se encuentra al final del artículo.

Y aquí mostraremos la creación de cohortes con un ejemplo simplificado.

Supongamos que tenemos un servicio. Los usuarios se registran y gastan dinero en los servicios. Con el tiempo, los usuarios se van. Queremos saber cuánto tiempo viven los usuarios y cuántos de ellos se marchan después del primer y segundo mes de uso del servicio.

Para responder a estas preguntas, necesitamos construir cohortes por mes de registro. La actividad la mediremos a través del gasto en cada mes. En lugar de gastos, pueden ser pedidos, tarifas de suscripción o cualquier otra actividad vinculada al tiempo.

Datos de entrada

Los ejemplos están hechos en MySQL, pero no debería haber diferencias significativas para otros SGBD.

Tabla de usuarios — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabla de gastos — billing:

userId
Date
Sum

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

Seleccionamos todos los cargos de los usuarios y la fecha de registro:

SELECT 
  b.userId, 
  b.Date,
  u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Resultado:

userId
Date
RegistrationDate

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

Construimos las cohortes por meses, para ello convertimos todas las fechas a meses:

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

Ahora necesitamos saber cuántos meses ha estado activo el usuario — es la diferencia entre el mes del cargo y el mes del registro. En MySQL hay una función PERIOD_DIFF() — la diferencia entre dos meses. Agregamos PERIOD_DIFF() a la consulta:

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

userId
BillingMonth
RegistrationDate
MonthsDiff

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 los usuarios activados en cada mes — agrupamos los registros por BillingMonth, RegistrationMonth y 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:

UsersCount
BillingMonth
RegistrationMonth
MonthsDiff

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

En enero, febrero y marzo se registró un nuevo usuario — MonthsDiff = 0. Un usuario de enero estuvo activo también en febrero — RegistrationMonth = 2019-01, BillingMonth = 2019-02, así como un usuario de febrero estuvo activo en marzo.

Con grandes volúmenes de datos, las tendencias son, por supuesto, más fáciles de ver.

Cómo trasladar cohortes a Grafana

Hemos aprendido a formar cohortes, pero cuando hay muchos registros, analizarlos ya no es tan fácil. Los registros se pueden exportar a Excel y formar tablas bonitas, pero ¡ese no es nuestro método!

Las cohortes se pueden mostrar en forma de gráfico interactivo en Grafana.

Para esto, añadimos otra consulta para convertir los datos en un formato adecuado para 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 antigua que devuelve cohortes
  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

Y exportamos los datos a Grafana.

Ejemplo de gráfico de demo:

Tocarlo con las manos:

Repositorio de GitHub con un ejemplo — es una imagen de docker con MySQL y Grafana, que se puede ejecutar en su computadora. La base ya contiene datos de demostración de un año y medio, desde enero de 2018 hasta julio de 2019.

Si lo desea, puede cargar sus propios datos en esta imagen.

P.D. Artículos sobre análisis de cohortes en SQL:

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

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

Fuente: habr.com