Consultas de cohortes y embudos
Responda preguntas sobre la retención
Consultas de cohortes y embudos es una lección gratuita de Digital Marketing Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de Digital Marketing Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de Digital Marketing Academy incluye 4 lecciones en total.
¿Qué es una cohorte?
Una cohorte es un grupo de usuarios que comparte un evento inicial, normalmente su mes de registro. El seguimiento de cada cohorte a lo largo del tiempo revela la retención real.
Las métricas agregadas ocultan el abandono; las cohortes muestran si los usuarios nuevos realmente permanecen.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;Definir la cohorte
El primer paso es asignar a cada usuario su cohorte. DATE_TRUNC redondea las fechas de registro al inicio del mes para agrupar a los usuarios correctamente.
Guarde esto como un componente reutilizable para todo lo que viene a continuación.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;Periodos de actividad
La retención mide la actividad durante los meses posteriores al registro. Calcule el periodo como la diferencia entre el mes de un pedido y el mes de la cohorte.
El periodo 0 es el mes de registro, el periodo 1 es el mes siguiente y así sucesivamente.
SELECT o.user_id,
(DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
+ (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;Crear una matriz de retención
Combine el mes de la cohorte y el periodo, y después cuente los usuarios activos distintos de cada celda. El resultado es el clásico triángulo de retención.
Cada fila es una cohorte y cada columna representa los meses transcurridos desde el registro.
SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;Tasa de retención
Es difícil comparar los recuentos sin procesar entre cohortes de distintos tamaños. Divida los usuarios activos de cada periodo por el tamaño original de la cohorte.
Una función de ventana lleva el tamaño del periodo 0 a cada fila para calcular la proporción.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;¿Qué es un embudo?
Un embudo sigue a los usuarios a través de pasos ordenados: visitar, registrarse, añadir al carrito y comprar. La caída entre pasos muestra dónde pierde usuarios.
El análisis de embudos convierte las quejas imprecisas sobre una «baja conversión» en una etapa concreta con fugas.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;Contar cada paso
La agregación condicional cuenta los usuarios de cada etapa en una sola pasada. FILTER contabiliza los usuarios distintos que alcanzaron cada paso.
Una consulta, una fila y todo el embudo de un vistazo.
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;Tasas de conversión por paso
La cifra interesante es la proporción entre pasos consecutivos. Dividir cada paso por el anterior revela la mayor caída.
Convierta el resultado a decimal para que la división entera no reduzca la tasa a cero.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;Embudo ordenado con marcas de tiempo
Un embudo estricto exige que los pasos ocurran en orden. Relacione cada paso con el siguiente solo cuando su marca de tiempo sea posterior.
Así evita contar una compra que ocurrió antes del registro correspondiente.
SELECT COUNT(DISTINCT v.user_id) AS visited,
COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
ON p.user_id = v.user_id
AND p.step = 'purchase'
AND p.event_time > v.event_time
WHERE v.step = 'visit';Embudo por canal
Dividir el embudo por canal de adquisición muestra qué fuentes atraen a usuarios que realmente convierten, no solo que hacen clic.
Agrupe por canal los recuentos condicionales para comparar la calidad de las distintas fuentes.
SELECT channel,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;Del análisis a la acción
Las cohortes indican si la retención mejora de un lanzamiento al siguiente; los embudos indican qué paso debe corregir primero.
Juntos, hacen que el marketing pase de informar sobre lo ocurrido a diagnosticar por qué ocurrió y dónde invertir después.
Comprobación rápida
Su embudo cuenta usuarios en las etapas de visita, registro y compra. ¿Qué revelan las proporciones entre pasos consecutivos?
Resumen
Las cohortes agrupan a los usuarios por mes de inicio y realizan el seguimiento de la retención a lo largo de varios periodos mediante DATE_TRUNC y AGE. Los embudos cuentan los usuarios distintos de cada paso ordenado y comparan las proporciones consecutivas.
Ahora dispone de las herramientas avanzadas necesarias para diagnosticar la retención y la conversión completamente en SQL.
Aprende Digital Marketing Academy con un tutor de IA — gratis
Escribe y ejecuta código real en tu navegador, obtén ayuda instantánea de un tutor de IA disponible 24/7 y continúa donde lo dejaste en la web o en la aplicación.
- Cursos
- 63
- Lecciones
- 239
Preguntas frecuentes
¿La lección «Consultas de cohortes y embudos» es gratis?
Sí — el texto completo de «Consultas de cohortes y embudos» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de Digital Marketing Academy, actualiza a CoddyKit PRO. El curso de Digital Marketing Academy incluye 4 lecciones en total.
¿Qué aprenderé en «Consultas de cohortes y embudos»?
Responda preguntas sobre la retención Practicas Digital Marketing Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar Digital Marketing Academy?
No se requiere experiencia previa. Digital Marketing Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.
¿Cuánto tiempo toma la lección «Consultas de cohortes y embudos»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de Digital Marketing Academy?
Sí. Cada lección de Digital Marketing Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- Por qué los profesionales del marketing aprenden SQL
- SELECT, WHERE, GROUP BY
- Unión de tablas de marketing
- Consultas de cohortes y embudos