BookinglyTech News
Software

Cálculo de churn mensual con SQL: un ejemplo paso a paso

Un esquema completo para determinar la tasa de cancelaciones mes a mes a partir de una tabla de suscripciones, con calendario, usuarios activos y cálculo de churn.

2 min de lecturaDev.to0 vistas

Problema

Para un negocio con suscripciones, la tasa de churn mensual indica cuántos usuarios se fueron cada mes. El objetivo es obtener esta métrica para todo 2025. La tabla de suscripciones tiene:

  • subscription_id (INT)
  • user_id (INT)
  • start_date (DATE)
  • end_date (DATE, NULL si sigue activa)

Un usuario se considera activo en un mes cuando su suscripción empezó antes del siguiente mes y terminó después del inicio del mes actual o no tiene fecha de fin.

La tasa de churn se calcula como: [ churn = \frac{\text{usuarios que se fueron durante el mes}}{\text{usuarios activos el mes anterior}} ]

1. Calendario de meses

Se crea un CTE months que contiene cada mes de 2025, el mes previo y el próximo. Se incluye diciembre 2024 para que enero tenga un mes anterior.

WITH months (month_start, previous_month_start, next_month_start) AS (
    VALUES
        (DATE '2024-12-01', DATE '2024-11-01', DATE '2025-01-01'),
        (DATE '2025-01-01', DATE '2024-12-01', DATE '2025-02-01'),
        ...
        (DATE '2025-12-01', DATE '2025-11-01', DATE '2026-01-01')
)

Cada fila representa un mes con su referencia al anterior y al siguiente.

2. Usuarios activos por mes

Se unen los meses con la tabla subscriptions usando la lógica de activación. El DISTINCT evita duplicados cuando un usuario tiene varias suscripciones activas.

active_users AS (
    SELECT DISTINCT m.month_start, s.user_id
    FROM months AS m
    JOIN subscriptions AS s
        ON s.start_date < m.next_month_start
        AND (s.end_date IS NULL OR s.end_date >= m.month_start)
)

3. Usuarios que churned

El churn se detecta con un anti‑join: se toman los usuarios activos en el mes anterior y se comprueba que no estén activos en el mes actual.

monthly_churn AS (
    SELECT m.month_start,
           COUNT(previous_users.user_id) AS active_last_month,
           SUM(CASE WHEN previous_users.user_id IS NOT NULL
                     AND current_users.user_id IS NULL THEN 1 ELSE 0 END) AS churned_users
    FROM months AS m
    LEFT JOIN active_users AS previous_users
        ON previous_users.month_start = m.previous_month_start
    LEFT JOIN active_users AS current_users
        ON current_users.month_start = m.month_start
        AND current_users.user_id = previous_users.user_id
    WHERE m.month_start >= DATE '2025-01-01'
    GROUP BY m.month_start
)

4. Tasa de churn

Finalmente se divide el número de churns por el número de usuarios activos el mes anterior. Se usa un CASE para evitar la división por cero y se multiplica por 100.0 para obtener un porcentaje.

SELECT month_start AS month,
       active_last_month,
       churned_users,
       CASE
           WHEN active_last_month = 0 THEN 0.00
           ELSE ROUND(100.0 * churned_users / active_last_month, 2)
       END AS churn_rate
FROM monthly_churn
ORDER BY month;

Resultado esperado

El resultado muestra, para cada mes de 2025, cuántos usuarios estaban activos el mes anterior, cuántos se fueron y el churn en porcentaje. Esta consulta es un recurso práctico para entrevistas de datos, análisis de negocio y para validar métricas en producción.

Importancia

Conocer el churn mensual permite ajustar la retención, identificar patrones de cancelación y medir el impacto de nuevas funcionalidades. La solución presentada es reproducible en cualquier motor SQL que soporte CTEs, y se puede integrar directamente en dashboards o scripts de ETL.

Historias Relacionadas