¡Hola! ¡Buenos días! ¡Buenos días! ¡Buenas noches! ¡Cuando y donde quiera que estés!

¿Alguna vez te has preguntado cómo determinar si tus clientes están comprando en tu tienda continuamente?

Pero, ¿no sabes cómo?

Este artículo sería la respuesta.

“El análisis de cohortes es una investigación cuantitativa que utiliza una medida del concepto de cohorte y estudia su efecto en alguna variable de resultado. En este sentido, una cohorte está formada por personas que comparten una experiencia común durante un período de tiempo específico”. (Glenn, 2005)

En este caso, el análisis de cohortes es una herramienta para medir y estudiar el comportamiento de los clientes a la hora de comprar en una tienda. Este análisis funciona calculando el número de clientes que realizaron su primera transacción en un período específico y calculando el número de clientes que realizaron transacciones en una secuencia de períodos posteriores a la primera. Esto nos ayuda a determinar qué tan leales son los clientes al volver a comprar en nuestra tienda. Por ejemplo, mira la siguiente tabla.

Como podemos ver, la tabla consta de varias filas y columnas. Las filas representan el n-ésimo período y las columnas representan el n-ésimo período después del mes en la fila respectiva. La columna c0 consiste en el número de clientes que realizaron su primera transacción en cada período, la columna c1 consta del número de clientes que realizaron transacciones en 1 período después de la primera, y así sucesivamente. Por ejemplo, la celda c1,2 contiene el número de clientes que realizaron transacciones en el tercer período, mientras que realizaron su primera transacción en el primer período. Al comparar los valores de la columna c0 y otros valores de columna, podemos determinar cuántos clientes vuelven a comprar en nuestra tienda y no lo hacen.

Sin embargo, necesitamos una tabla para no tener que comparar valores entre las columnas. Por lo tanto, debemos usar la tasa de retención.

¿Qué es la tasa de retención?

La tasa de retención es el porcentaje de clientes que continúan comprando los productos vendidos durante un período determinado. En otras palabras, la tasa de retención es el porcentaje del análisis de cohorte.

Sin embargo, la tasa de abandono es el porcentaje de clientes que ya no compran el producto vendido durante un período determinado.

Las tasas de retención y abandono se pueden calcular de la siguiente manera.

Tasa de retención (%) = ((número de clientes al final del período – número de clientes adquiridos durante el período) / número de clientes al inicio del período) * 100%

Tasa de abandono (%) = ((número de clientes al inicio del período – (número de clientes al final del período – número de clientes adquiridos durante el período)) / número de clientes al inicio del período) * 100%

Tenga en cuenta que la tasa de abandono (%) = 1 – tasa de retención (%). Con esas fórmulas, podemos aplicar el análisis en SQL.

¿Cómo aplicar el análisis en SQL?

La forma más fácil de encontrar la tasa de retención es utilizando el análisis de cohortes. El análisis de cohortes funciona midiendo cuántos clientes realizaron transacciones al comienzo del período (el denominador) y cuántos de esos clientes aún realizaron transacciones en períodos posteriores (el numerador). Por lo tanto, el algoritmo consiste en establecer la tabla de cohortes y calcular las tasas. En esta ocasión, utilizo los datos de mi análisis anterior, Comportamiento del cliente y hábitos de compra.

1. Establecer la tabla de cohortes

a. Crear una tabla de los meses de la primera transacción
Se identifica a cada cliente en qué mes realizó su primera transacción. La tabla se puede crear mediante el siguiente código.

-- First Month Transaction
SELECT customer_id, 
       MIN(EXTRACT(MONTH FROM order_date)) AS first_month 
FROM customer_transaction
GROUP BY customer_id;

b. Crear una tabla de meses de transacciones realizadas
Cada cliente se identifica en qué meses realizó su transacción. La tabla se genera mediante el siguiente código.

-- Transaction Month
SELECT DISTINCT customer_id, 
    EXTRACT(MONTH FROM order_date) AS month
FROM customer_transaction;

c. Crear una tabla de unión a partir de a y b
La tabla de un paso de a y b se integra con la función JOIN. Además, agregue una nueva columna que reste el mes de la tabla b por el mes de la tabla a para cada uno de los mismos clientes, denominada month_diff. Esa columna representa el intervalo de meses entre el mes de la primera transacción y el mes de la transacción en particular. La columna se utiliza para determinar los valores de cohorte. La tabla se crea utilizando el siguiente código que utiliza el CTE (Common Table Expression).

-- Join Table
WITH 
first_month_transaction AS (
 SELECT customer_id, 
        MIN(EXTRACT(MONTH FROM order_date)) AS first_month 
 FROM customer_transaction
 GROUP BY customer_id
),
transaction_month AS(
 SELECT DISTINCT customer_id, 
        EXTRACT(MONTH FROM order_date) AS month
 FROM customer_transaction
)
SELECT tm.customer_id, tm.month, 
       fmt.first_month,
       tm.month - fmt.first_month AS month_diff
FROM transaction_month AS tm
JOIN first_month_transaction AS fmt
ON tm.customer_id = fmt.customer_id

El join_table se parece a la tabla anterior. Como podemos ver, month_diff consta de números enteros del 0 al 11. Estos valores indican el intervalo de meses.

d. Crear la tabla de cohortes
Ahora, tenemos que contar cada month_diff único (0 a 11). Podemos utilizar las cláusulas SUM y CASE para ejecutar la lógica: devuelve 1 si month_diff es igual al valor deseado, mientras que devuelve 0 si month_diff no es igual al valor deseado, para cada columna. Por ejemplo, para contar month_diff igual a 0 (la columna m0), escribimos SUM(CASE WHEN month_diff = 0 THEN 1 ELSE 0 END) AS m0 para obtener el recuento de month_diff = 0. A continuación, utilizamos GROUP BY first_month para segregar el recuento por cada mes de la primera compra.

-- Cohort Table
WITH 
first_month_transaction AS (
 SELECT customer_id, 
        MIN(EXTRACT(MONTH FROM order_date)) AS first_month 
 FROM customer_transaction
 GROUP BY customer_id
),
transaction_month AS(
 SELECT DISTINCT customer_id, 
        EXTRACT(MONTH FROM order_date) AS month
 FROM customer_transaction
),
join_table AS
(
 SELECT tm.customer_id, tm.month, 
        fmt.first_month,
        tm.month - fmt.first_month AS month_diff
 FROM transaction_month AS tm
 JOIN first_month_transaction AS fmt
 ON tm.customer_id = fmt.customer_id
)
SELECT first_month,
       SUM(CASE WHEN month_diff = 0 THEN 1 ELSE 0 END) AS m0,
       SUM(CASE WHEN month_diff = 1 THEN 1 ELSE 0 END) AS m1,
       SUM(CASE WHEN month_diff = 2 THEN 1 ELSE 0 END) AS m2,
       SUM(CASE WHEN month_diff = 3 THEN 1 ELSE 0 END) AS m3,
       SUM(CASE WHEN month_diff = 4 THEN 1 ELSE 0 END) AS m4,
       SUM(CASE WHEN month_diff = 5 THEN 1 ELSE 0 END) AS m5,
       SUM(CASE WHEN month_diff = 6 THEN 1 ELSE 0 END) AS m6,
       SUM(CASE WHEN month_diff = 7 THEN 1 ELSE 0 END) AS m7,
       SUM(CASE WHEN month_diff = 8 THEN 1 ELSE 0 END) AS m8,
       SUM(CASE WHEN month_diff = 9 THEN 1 ELSE 0 END) AS m9,
       SUM(CASE WHEN month_diff = 10 THEN 1 ELSE 0 END) AS m10,
       SUM(CASE WHEN month_diff = 11 THEN 1 ELSE 0 END) AS m11
FROM join_table
GROUP BY first_month
ORDER BY first_month

2. Calcular la tasa de retención

Recordemos que la tasa de retención es el porcentaje de la cohorte o podemos escribirlo de la siguiente manera.

Tasa de retención (%) = mi/m0 × 100%, para i = 0,1,…,11.

Con la fórmula anterior, creamos la tabla de retención utilizando el siguiente código.

-- Retention Rate Table
WITH 
first_month_transaction AS (
 SELECT customer_id, 
        MIN(EXTRACT(MONTH FROM order_date)) AS first_month 
 FROM customer_transaction
 GROUP BY customer_id
),
transaction_month AS(
 SELECT DISTINCT customer_id, 
        EXTRACT(MONTH FROM order_date) AS month
 FROM customer_transaction
),
join_table AS
(
 SELECT tm.customer_id, tm.month, 
        fmt.first_month,
        tm.month - fmt.first_month AS month_diff
 FROM transaction_month AS tm
 JOIN first_month_transaction AS fmt
 ON tm.customer_id = fmt.customer_id
),
cohort_table AS 
(
 SELECT first_month,
        SUM(CASE WHEN month_diff = 0 THEN 1 ELSE 0 END) AS m0,
        SUM(CASE WHEN month_diff = 1 THEN 1 ELSE 0 END) AS m1,
        SUM(CASE WHEN month_diff = 2 THEN 1 ELSE 0 END) AS m2,
        SUM(CASE WHEN month_diff = 3 THEN 1 ELSE 0 END) AS m3,
        SUM(CASE WHEN month_diff = 4 THEN 1 ELSE 0 END) AS m4,
        SUM(CASE WHEN month_diff = 5 THEN 1 ELSE 0 END) AS m5,
        SUM(CASE WHEN month_diff = 6 THEN 1 ELSE 0 END) AS m6,
        SUM(CASE WHEN month_diff = 7 THEN 1 ELSE 0 END) AS m7,
        SUM(CASE WHEN month_diff = 8 THEN 1 ELSE 0 END) AS m8,
        SUM(CASE WHEN month_diff = 9 THEN 1 ELSE 0 END) AS m9,
        SUM(CASE WHEN month_diff = 10 THEN 1 ELSE 0 END) AS m10,
        SUM(CASE WHEN month_diff = 11 THEN 1 ELSE 0 END) AS m11
 FROM join_table
 GROUP BY first_month
 ORDER BY first_month
)
SELECT first_month AS cohort_month,
       ROUND((m0/CAST(m0 AS decimal))*100,0) AS m0,
       ROUND((m1/CAST(m0 AS decimal))*100,0) AS m1,
       ROUND((m2/CAST(m0 AS decimal))*100,0) AS m2,
       ROUND((m3/CAST(m0 AS decimal))*100,0) AS m3,
       ROUND((m4/CAST(m0 AS decimal))*100,0) AS m4,
       ROUND((m5/CAST(m0 AS decimal))*100,0) AS m5,
       ROUND((m6/CAST(m0 AS decimal))*100,0) AS m6,
       ROUND((m7/CAST(m0 AS decimal))*100,0) AS m7,
       ROUND((m8/CAST(m0 AS decimal))*100,0) AS m8,
       ROUND((m9/CAST(m0 AS decimal))*100,0) AS m9,
       ROUND((m10/CAST(m0 AS decimal))*100,0) AS m10,
       ROUND((m11/CAST(m0 AS decimal))*100,0) AS m11
FROM cohort_table;

Cuanto mayor sea la tasa de retención, más clientes leales serán al realizar transacciones en GenggamData Store. Como ejemplo de explicación de la tasa de retención en enero de 2023, se encuentra que se encuentra en un promedio del 47% y no muestra ninguna tendencia. Se puede concluir que alrededor del 47% de los clientes que realizaron transacciones por primera vez en enero de 2023 volvieron a realizar transacciones en los meses siguientes de 2023. En general, los valores se encuentran en un rango de 20 a 60, lo que indica que entre el 20% y el 60% de los clientes volvieron a realizar transacciones después de su primera transacción.

3. Calcular la tasa de abandono

La tasa de abandono se conoce por la tasa de abandono (%) = 1 – tasa de retención (%). Por lo tanto, podemos ejecutar el código de la siguiente manera.

-- Churn Rate Table
WITH 
first_month_transaction AS (
 SELECT customer_id, 
        MIN(EXTRACT(MONTH FROM order_date)) AS first_month 
 FROM customer_transaction
 GROUP BY customer_id
),
transaction_month AS(
 SELECT DISTINCT customer_id, 
        EXTRACT(MONTH FROM order_date) AS month
 FROM customer_transaction
),
join_table AS
(
 SELECT tm.customer_id, tm.month, 
        fmt.first_month,
        tm.month - fmt.first_month AS month_diff
 FROM transaction_month AS tm
 JOIN first_month_transaction AS fmt
 ON tm.customer_id = fmt.customer_id
),
cohort_table AS 
(
 SELECT first_month,
        SUM(CASE WHEN month_diff = 0 THEN 1 ELSE 0 END) AS m0,
        SUM(CASE WHEN month_diff = 1 THEN 1 ELSE 0 END) AS m1,
        SUM(CASE WHEN month_diff = 2 THEN 1 ELSE 0 END) AS m2,
        SUM(CASE WHEN month_diff = 3 THEN 1 ELSE 0 END) AS m3,
        SUM(CASE WHEN month_diff = 4 THEN 1 ELSE 0 END) AS m4,
        SUM(CASE WHEN month_diff = 5 THEN 1 ELSE 0 END) AS m5,
        SUM(CASE WHEN month_diff = 6 THEN 1 ELSE 0 END) AS m6,
        SUM(CASE WHEN month_diff = 7 THEN 1 ELSE 0 END) AS m7,
        SUM(CASE WHEN month_diff = 8 THEN 1 ELSE 0 END) AS m8,
        SUM(CASE WHEN month_diff = 9 THEN 1 ELSE 0 END) AS m9,
        SUM(CASE WHEN month_diff = 10 THEN 1 ELSE 0 END) AS m10,
        SUM(CASE WHEN month_diff = 11 THEN 1 ELSE 0 END) AS m11
 FROM join_table
 GROUP BY first_month
 ORDER BY first_month
)
SELECT first_month AS cohort_month,
       100 - ROUND((m0/CAST(m0 AS decimal))*100,0) AS m0,
       100 - ROUND((m1/CAST(m0 AS decimal))*100,0) AS m1,
       100 - ROUND((m2/CAST(m0 AS decimal))*100,0) AS m2,
       100 - ROUND((m3/CAST(m0 AS decimal))*100,0) AS m3,
       100 - ROUND((m4/CAST(m0 AS decimal))*100,0) AS m4,
       100 - ROUND((m5/CAST(m0 AS decimal))*100,0) AS m5,
       100 - ROUND((m6/CAST(m0 AS decimal))*100,0) AS m6,
       100 - ROUND((m7/CAST(m0 AS decimal))*100,0) AS m7,
       100 - ROUND((m8/CAST(m0 AS decimal))*100,0) AS m8,
       100 - ROUND((m9/CAST(m0 AS decimal))*100,0) AS m9,
       100 - ROUND((m10/CAST(m0 AS decimal))*100,0) AS m10,
       100 - ROUND((m11/CAST(m0 AS decimal))*100,0) AS m11
FROM cohort_table;

Cuanto menor sea la tasa de abandono, más leales serán los clientes al realizar transacciones en GenggamData Store. Como ejemplo de explicación de la tasa de abandono en enero de 2023, se encuentra que la tasa de abandono se encuentra en un promedio del 53% y no muestra ninguna tendencia. Se puede concluir que alrededor del 53% de los clientes que realizaron transacciones por primera vez en enero de 2023 no volvieron a realizar transacciones en los meses siguientes de 2023. En general, los valores se encuentran en un rango de 40 a 80, lo que indica que entre el 40% y el 80% de los clientes no volvieron a realizar transacciones después de su primera transacción.

En resumen, el análisis de cohortes, especialmente la tasa de retención, es una herramienta poderosa y de uso común para comprender el comportamiento del cliente. Muchas empresas utilizan este análisis para evaluar sus políticas, para el aumento de sus ingresos.

Eso es todo mi artículo. Si tiene interés en este artículo, dé un me gusta y síganos para obtener más información como esta. Es un placer para ti que quieras dejar un comentario en este artículo. ¡Gracias!