En un artículo anterior, describí el uso del análisis de cohortes para determinar las razones de la dinámica de la base de clientes. Hoy es hora de hablar sobre trucos de preparación de datos para el análisis de cohortes.
Es fácil dibujar imágenes, pero para que se puedan leer y mostrar correctamente "debajo del capó", se necesita mucho trabajo. En este artículo, hablaremos sobre cómo implementar el análisis de cohortes. Hablaré sobre la implementación usando Excel, y en otro artículo usando R.
Nos guste o no, pero de hecho Excel es una herramienta de análisis de datos. Los analistas más "arrogantes" creerán que esta es una herramienta débil y no conveniente. Por otro lado, de hecho, cientos de miles de personas hacen análisis de datos en Excel y, en este sentido, fácilmente vencerá a R / python. Por supuesto, cuando hablamos de análisis avanzado y aprendizaje automático, trabajaremos en R / python. Y me gustaría que la mayoría de los análisis se realizaran solo con estas herramientas. Pero vale la pena reconocer los hechos, la gran mayoría de las empresas procesan y presentan datos en Excel, y esta es la herramienta que utilizan los analistas, gerentes y propietarios de productos comunes. Además, Excel es difícil de vencer en términos de simplicidad y claridad del proceso, porque dominas tus cálculos y modelos literalmente con tus manos.
Y entonces, ¿cómo hacemos análisis de cohortes en Excel? Para resolver estos problemas, debe determinar 2 cosas:
¿Qué datos tenemos al comienzo del proceso?
Cómo deberían verse nuestros datos al final del proceso.
Para recopilar un análisis de cohorte, no solo necesitaremos datos retrospectivos sobre fechas y divisiones. Necesitamos datos a nivel del cliente individual. Al comienzo del proceso necesitamos:
Fecha del calendario
ID del cliente
Fecha de registro del cliente
Volumen de ventas de este cliente en esta fecha calendario
La primera dificultad a superar es obtener estos datos. Si tiene el almacenamiento correcto, entonces ya debería tenerlos. Por otro lado, si hasta ahora ha implementado solo el registro de datos sobre las ventas totales por día, entonces solo tiene datos de clientes en "prod". Para el análisis de cohortes, deberá implementar ETL y colocar los datos en el contexto de los clientes en su almacenamiento, de lo contrario no tendrá éxito. Y lo mejor de todo, si separa "productos" y análisis en diferentes bases de datos, porque Las tareas analíticas y las tareas de funcionamiento de su producto tienen diferentes objetivos: competencia por los recursos. Los analistas necesitan agregados y cálculos rápidos para muchos usuarios, el producto necesita servir rápidamente a un usuario específico. Escribiré un artículo separado sobre la organización del almacenamiento.
Entonces tienes datos de inicio:
Lo primero que debemos hacer es transformarlos en "escaleras". Para hacer esto, debe crear una tabla dinámica sobre esta tabla, en filas, la fecha de registro, en columnas, la fecha del calendario, como valores, el número de identificación de clientes. Si extrajo correctamente los datos, entonces debería obtener dicho triángulo / escalera:
En general, una escalera es nuestro gráfico de cohorte, en el que cada línea muestra la dinámica de una cohorte separada. Los clientes a tiempo en esta pantalla se mueven solo dentro de una línea. Por lo tanto, la dinámica de la cohorte refleja el desarrollo de las relaciones con un grupo de clientes que llegaron en un período de tiempo. A menudo, para mayor comodidad y sin pérdida de calidad, puede combinar cohortes en "bloques" de filas. Por ejemplo, puede agruparlos por semana y mes. Del mismo modo, también puede agrupar una columna. Quizás su ritmo de desarrollo de productos no requiera detallar hasta días.
Con base en esta escalera, puede construir un gráfico a partir de mi artículo (realmente señalé que había agrupado varias líneas en una para que la cohorte fuera más pequeña):
Este es un gráfico con áreas acumulativas donde cada fila es una fila, horizontalmente de una fecha.
Una lógica un poco más complicada para implementar el cronograma de "flujos". Para los hilos, necesitamos hacer algunos cálculos adicionales. En la lógica del hilo, cada cliente llega en diferentes estados:
- Nuevo: cualquier cliente que tenga una diferencia entre la fecha de registro y la fecha del calendario <7 días
- Reactivado: cualquier cliente que ya no es nuevo, pero en el último mes calendario no generó ingresos
- Válido: cualquier cliente que no sea nuevo, pero haya generado ingresos en el mes calendario
- Partió: cualquier cliente que no genera ingresos durante 2 meses consecutivos
En primer lugar, debe corregir estas definiciones en la empresa para que pueda implementar correctamente esta lógica y calcular automáticamente los estados. Estas 4 definiciones tienen implicaciones de largo alcance para el marketing en general. Sus estrategias para atraer, retener y regresar se basarán en el estado en el que cree que se encuentra el cliente. Y si comienza a implementar modelos de aprendizaje automático para predecir las salidas de los clientes, las definiciones se convertirán en su piedra angular para el éxito de estos modelos. En general, escribiré un artículo separado sobre la organización del trabajo y la importancia de la metodología analítica. Arriba, di solo un ejemplo de lo que pueden ser estas definiciones.
En Excel, debe crear una columna adicional donde ingresar la lógica descrita anteriormente. En nuestro caso, tenemos que "sudar". Tenemos 2 tipos de criterios:
- La diferencia entre la fecha de registro y la fecha del calendario: cada fila tiene estos datos y luego solo necesita calcularlos (restar fechas en Excel solo da la diferencia en días)
- Datos de ingresos para el mes actual y el último. Estos datos no están disponibles para nosotros en la línea. Además, teniendo en cuenta el hecho de que el pedido no está garantizado en nuestra tabla, no puede decir exactamente dónde tiene datos en otros días del mes para este cliente.
Hay 2 formas de resolver el problema de 2 tipos de criterios:
- Pida hacer esto en la base de datos. SQL permite el uso de la función analítica para calcular para cada cliente la cantidad de ingresos para el mes actual y el último (para el mes actual SUMA (ingresos) OVER (PARTICIÓN POR client_id, calendar_month, y luego LAG para obtener la compensación para el último mes):
- En Excel, debe implementarlo así:
- Para el mes actual: SUMMES (), los criterios serán la identificación del cliente y el mes de la celda del día calendario
- Para el último mes: SUMMES (), el criterio será la identificación del cliente y el mes de la celda del día calendario menos exactamente 1 mes calendario. En este caso, llamo la atención sobre el hecho de que debe restar el mes calendario y no 30 días. De lo contrario, corre el riesgo de obtener una imagen borrosa debido a la cantidad desigual de días en los meses. Utilice también la función SI ERROR para reemplazar valores erróneos para clientes que no han tenido el mes pasado.
Al agregar las columnas de ingresos del mes actual del último mes, puede crear una condición incrustada SI tiene en cuenta todos los factores (la diferencia en las fechas y la cantidad de ingresos en el mes actual / pasado):
IF (diferencia de fecha <7; "nuevo";
SI (Y (ingresos del mes pasado = 0; ingresos del mes actual> 0); "reactivación";
SI (Y (ingresos del último mes> 0; ingresos del mes actual> 0); "actual"
IF (AND (ingresos del mes pasado = 0; ingresos del mes actual = 0); "partió"; "error"))))
Aquí se necesita "error" solo para controlar que no se haya equivocado en la grabación. La lógica de los criterios de estado MECE ( https://en.wikipedia.org/wiki/MECE_principle ), es decir Si todo se hace correctamente, cada uno de ellos tendrá un estado de 4
Deberías obtenerlo así:
Ahora esta tabla se puede reconstruir utilizando una tabla dinámica en una tabla para el trazado. Necesitas transformarlo en una tabla:
Fecha del calendario (columnas)
Estado (filas)
Número de identificación de clientes (valores en celdas)
A continuación, simplemente necesitamos construir un diagrama de gráfico de barras sobre la base de datos, con acumulaciones, en el eje X la fecha del calendario, las filas son estados, el número de clientes es la altura de la columna. Puede cambiar el orden de los estados en el gráfico cambiando el orden de las filas en el menú "Seleccionar datos". Como resultado, obtenemos la siguiente imagen:
Ahora podemos comenzar a interpretar y analizar.