ProHoster > Blog > administración > Cómo recopilar cohortes de usuarios como gráficos en Grafana [+ imagen acoplable con ejemplo]

Cómo recopilar cohortes de usuarios como gráficos en Grafana [+ imagen acoplable con ejemplo]

Cómo recopilar cohortes de usuarios como gráficos en Grafana [+ imagen acoplable con ejemplo]

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

Promopulta - un servicio potente con una gran cantidad de usuarios. Durante los 10 años de funcionamiento, el número de registros en el sistema ha superado el millón. Quienes se han topado con servicios similares saben que este conjunto de usuarios dista mucho de ser homogéneo.

Alguien se registró y “se quedó dormido” para siempre. Alguien olvidó su contraseña y se registró un par de veces más en el transcurso de seis meses. Alguien trae dinero a la caja registradora y alguien vino a buscar regalos. instrumentos. Y sería bueno obtener algún beneficio de todos.

En conjuntos de datos tan grandes como el nuestro, no tiene sentido analizar el comportamiento de un usuario individual y tomar microdecisiones. Pero captar tendencias y trabajar con grupos grandes es posible y necesario. Que es exactamente lo que hacemos.

Resumen

  1. ¿Qué es el análisis de cohortes y por qué es necesario?
  2. Cómo hacer cohortes por mes de registro de usuario en SQL.
  3. Cómo transferir cohortes a Grafana.

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

1. ¿Qué es el análisis de cohortes y por qué es necesario?

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, nuestros grupos se forman por la semana o mes en que el usuario comenzó a utilizar el servicio. A partir de aquí se calcula la vida útil del usuario, que es un indicador a partir del cual se puede realizar un análisis bastante complejo. Por ejemplo, comprenda:

  • ¿Cómo influye el canal de adquisición en la vida del usuario?
  • cómo el uso de cualquier característica o servicio afecta la vida;
  • Cómo afectó el lanzamiento de la función X a 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 nos permiten presentar aquí nuestros datos reales: en el volcado de prueba, estadísticas de un año y medio: 1200 usuarios y 53 000 transacciones. Para que puedas jugar con estos datos, hemos preparado una imagen de Docker con MySQL y Grafana en la que podrás experimentarlo todo tú mismo. Enlace a GitHub al final del artículo.

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

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

Para responder a estas preguntas, necesitamos construir cohortes basadas en el mes de registro. Mediremos la actividad por gastos en cada mes. En lugar de gastos, puede haber pedidos, cuotas de suscripción o cualquier otra actividad basada en el tiempo.

Datos iniciales

Los ejemplos se hicieron en MySQL, pero para otros DBMS no debería haber diferencias significativas.

Tabla de usuarios - usuarios:

ID de usuario
Fecha de Registro

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabla de costos - facturación:

ID de usuario
Fecha
Suma

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

Seleccione todas las bajas de usuarios y 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:

ID de usuario
Fecha
Fecha 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

Construimos cohortes por mes; para hacer esto, convertimos todas las fechas en meses:

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

Ahora necesitamos saber cuántos meses estuvo activo el usuario; esta es la diferencia entre el mes de débito y el mes de registro. MySQL tiene una función PERIOD_DIFF() - la diferencia entre dos meses. Agregue PERIOD_DIFF() a la solicitud:

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 de usuario
Mes de facturación
Fecha de Registro
MesesDiff

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 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:

Número de usuarios
Mes de facturación
Mes de registro
MesesDiff

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, apareció un nuevo usuario cada uno: MonthsDiff = 0. Un usuario de enero estuvo activo en febrero: RegistrationMonth = 2019-01, BillingMonth = 2019-02 y un usuario de febrero estuvo activo en marzo.

Naturalmente, los patrones son mejor visibles en un gran conjunto de datos.

Cómo transferir cohortes a Grafana

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

Las cohortes se pueden mostrar como un gráfico interactivo en Grafana.

Para ello, agregamos otra consulta para convertir los datos a 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 (
  ## старый запрос, возвращающий когорты
  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 sube los datos a Grafana.

Gráfico de ejemplo de manifestación:

Cómo recopilar cohortes de usuarios como gráficos en Grafana [+ imagen acoplable con ejemplo]

Toca con tus manos:

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

Si lo deseas, puedes cargar tus datos en esta imagen.

PD 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

Añadir un comentario