Cómo resolvimos la tarea de visualizar cohortes de usuarios en el servicio Promopult usando Grafana.
Promopult es un servicio poderoso con una gran cantidad de usuarios. Durante 10 años de operación, el número de registros en el sistema ha superado el millón. Aquellos que se han encontrado con servicios similares saben que esta variedad de usuarios está lejos de ser homogénea.
Alguien se registró y "se durmió" para siempre. Alguien olvidó la contraseña y se registró un par de veces más en seis meses. Alguien trae dinero al cajero, y alguien vino por herramientas gratuitas. 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 detectar tendencias y trabajar con grupos grandes es posible y necesario. Lo que, de hecho, estamos haciendo.
Resumen
- ¿Qué es el análisis de cohorte y por qué es necesario?
- Cómo hacer cohortes por mes de registro de usuarios en SQL.
- Cómo transferir cohortes a Grafana .
Si ya sabe qué es el análisis de cohortes y cómo hacerlo en SQL, pase a la última sección.
1. ¿Qué es el análisis de cohorte y por qué es necesario?
El análisis de cohortes es un método basado en una comparación de diferentes grupos (cohortes) de usuarios. Muy a menudo, nuestros grupos se forman por la semana o el mes en que el usuario comenzó a usar el servicio. La vida útil del usuario se calcula a partir de aquí, y este es un indicador en base al cual se puede llevar a cabo un análisis bastante complicado. Por ejemplo, comprenda:
- ¿Cómo atrae el canal la vida del usuario?
- cómo el uso de una función o servicio afecta toda la vida;
- cómo el lanzamiento de las funciones X afectó el tiempo de vida 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 proporcionar nuestros datos reales aquí: en el volcado de prueba, las estadísticas de un año y medio son: 1200 usuarios y 53,000 transacciones. Para que pueda jugar con estos datos, hemos preparado una imagen acoplable con MySQL y Grafana, en la que puede sentir todo usted mismo. Enlace a GitHub al final del artículo.
Y aquí mostramos la creación de cohortes con un ejemplo simplificado.
Supongamos que tenemos un servicio. Los usuarios se registran y gastan dinero en servicios. Con el tiempo, los usuarios se caen. Queremos saber cuánto tiempo viven los usuarios y cuántos de ellos se caen después del primer y segundo mes de uso del servicio.
Para responder estas preguntas, necesitamos crear cohortes por mes de registro. La actividad se medirá por gastos en cada mes. En lugar de costos, puede haber pedidos, una tarifa mensual o cualquier otra actividad con límite de tiempo.
Datos de origen
Los ejemplos están hechos en MySQL, pero para el resto del DBMS no debería haber diferencias significativas.
Tabla de usuarios - usuarios:
Tabla de gastos - facturación:
Seleccione 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:
Construimos cohortes por meses, para esto convertiremos todas las fechas a 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 la 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
Contamos los usuarios activados 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:
En enero, febrero y marzo, apareció un nuevo usuario: MonthsDiff = 0. Un usuario de enero estaba activo y en febrero RegistrationMonth = 2019-01, BillingMonth = 2019-02, y un usuario de febrero estaba activo en marzo.
En un gran conjunto de datos, los patrones se ven mejor naturalmente.
Cómo transferir cohortes a Grafana
Aprendimos a formar cohortes, pero cuando hay muchos registros, ya no es fácil analizarlos. Los registros se pueden exportar a Excel y crear hermosas tablas, ¡pero este no es nuestro método!
Las cohortes se pueden mostrar como gráficos interactivos en Grafana .
Para hacer esto, agregue 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 (
Y suba los datos a Grafana.
Ejemplo de gráfico de la demostración :
Toque:
El repositorio de ejemplo de GitHub es una imagen acoplable con MySQL y Grafana, que se puede ejecutar en su computadora. La base de datos ya tiene datos de demostración durante un año y medio, desde enero de 2018 hasta julio de 2019.
Si lo desea, puede cargar sus datos a esta imagen.
Artículos de PS 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/