La solicitud
De Priya Shah, Directora de Datos
“La retención del mes 1 del trimestre pasado generó más preguntas que respuestas. Construye la cuadrícula completa para las cohortes de enero a junio de 2025: meses 0 a 3 después del primer pedido, en formato largo, y yo la pivoto en la presentación.”
Tu objetivo
Usando solo pedidos completados, asigna cada cliente a cohort_month = el mes (primer día, como DATE) de su primer pedido completado. Un cliente está activo en un mes si tiene al menos un pedido completado en él. Para las cohortes de 2025-01-01 a 2025-06-01, devuelve una fila por cohorte y month_offset 0, 1, 2 y 3 (month_offset = meses calendario completos entre cohort_month y el mes activo) con: cohort_month, month_offset, active_customers (clientes de la cohorte activos en ese mes) y retention_pct (active_customers / número de clientes de la cohorte * 100, redondeado a 1). Ordena por cohort_month y luego por month_offset.
- Lee de la tabla
orderspendiente - Muestra exactamente
cohort_monthmonth_offsetactive_customersretention_pctpendiente - 24 filas, ordenadas como se pidependiente
Tu consulta
Cargando el editor…
Solución de referencia
| order_id | customer_id | order_date | status | channel |
|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Lo que recibe Priya, y por qué
Tablas
Datos de la tienda Harbor & Pine, listos para consultar.
orders12,000 filas · 7 columnas
order_idintegercustomer_idintegerorder_datetimestampstatusvarcharchannelvarcharshipping_feedecimalcoupon_codevarchar
| order_id | customer_id | order_date | status | channel | shipping_fee | coupon_code |
|---|---|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web | 0.00 | NULL |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web | 0.00 | NULL |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace | 4.99 | WELCOME10 |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace | 4.99 | WELCOME10 |
| 1005 | 82 | 2023-01-02 11:43:36 | completed | web | 0.00 | WELCOME10 |
customers2,018 filas · 9 columnas
customer_idintegerfirst_namevarcharlast_namevarcharemailvarcharcityvarcharstatevarcharsignup_datedatesegmentvarcharreferral_sourcevarchar
| customer_id | first_name | last_name | city | state | signup_date | segment | referral_source | |
|---|---|---|---|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco | CA | 2022-06-02 | consumer | search |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta | GA | 2022-06-02 | consumer | NULL |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles | CA | 2022-06-04 | business | NULL |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle | WA | 2022-06-06 | business | |
| 5 | Kyle | Jimenez | kjimenez27@yahoo.com | Indianapolis | IN | 2022-06-07 | consumer | NULL |
products120 filas · 8 columnas
product_idintegernamevarcharcategoryvarcharsubcategoryvarcharunit_pricedecimalunit_costdecimallaunched_ondateis_activeboolean
| product_id | name | category | subcategory | unit_price | unit_cost | launched_on | is_active |
|---|---|---|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 | NULL | 2023-09-11 | true |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 | 503.50 | 2024-05-16 | true |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 | 429.87 | 2023-05-27 | true |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 | 214.70 | 2022-12-20 | true |
| 105 | Avery Leather Bed Frame | Furniture | Beds | 449.00 | 232.70 | 2021-08-31 | true |
order_items30,338 filas · 6 columnas
order_item_idintegerorder_idintegerproduct_idintegerquantityintegerunit_pricedecimaldiscount_pctdecimal
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1001 | 121 | 9 | 31.99 | 0.00 |
| 2 | 1001 | 154 | 3 | 11.99 | 0.00 |
| 3 | 1002 | 154 | 4 | 11.99 | 0.00 |
| 4 | 1002 | 194 | 1 | 149.00 | 0.00 |
| 5 | 1002 | 193 | 2 | 19.99 | 0.00 |
returns1,673 filas · 5 columnas
return_idintegerorder_item_idintegerreturned_ondatereasonvarcharrefund_amountdecimal
| return_id | order_item_id | returned_on | reason | refund_amount |
|---|---|---|---|---|
| 1 | 14 | 2023-01-10 | damaged | 83.69 |
| 2 | 42 | 2023-01-10 | wrong_item | 19.99 |
| 3 | 105 | 2023-01-14 | wrong_item | 10.99 |
| 4 | 24 | 2023-01-16 | changed_mind | 26.99 |
| 5 | 126 | 2023-01-16 | late | 20.99 |
employees60 filas · 8 columnas
employee_idintegerfirst_namevarcharlast_namevarchardepartmentvarchartitlevarcharmanager_idintegerhire_datedatesalaryinteger
| employee_id | first_name | last_name | department | title | manager_id | hire_date | salary |
|---|---|---|---|---|---|---|---|
| 1 | Catherine | Hale | Executive | Chief Executive Officer | NULL | 2016-03-01 | 285000 |
| 2 | Robert | Kim | Executive | Chief Operating Officer | 1 | 2017-01-09 | 235000 |
| 3 | Megan | Collins | Sales | Head of Sales | 1 | 2018-04-16 | 172000 |
| 4 | Chloe | Gutierrez | Sales | Account Executive | 3 | 2020-01-21 | 72000 |
| 5 | Frank | Bell | Sales | Account Executive | 3 | 2023-05-21 | 73000 |
web_sessions40,000 filas · 7 columnas
session_idintegercustomer_idintegerstarted_attimestampdevicevarcharsourcevarcharpages_viewedintegerconvertedboolean
| session_id | customer_id | started_at | device | source | pages_viewed | converted |
|---|---|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid | 1 | false |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid | 1 | false |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct | 7 | false |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic | 3 | false |
| 5 | NULL | 2023-01-01 13:06:21 | mobile | social | 2 | false |
marketing_spend3,276 filas · 3 columnas
spend_datedatechannelvarcharamountdecimal
| spend_date | channel | amount |
|---|---|---|
| 2023-01-01 | paid | 492.99 |
| 2023-01-01 | 37.38 | |
| 2023-01-01 | social | 200.53 |
| 2023-01-02 | paid | 709.13 |
| 2023-01-02 | 64.13 |
La lección
La retención del mes 1 que calculaste en el desafío final de funciones de ventana es una columna de un panorama mayor. Una cuadrícula de retención muestra, para cada cohorte, cuántos clientes estaban activos 0, 1, 2, 3 meses después de su primer pedido. Leer una columna hacia abajo compara cohortes; leer una fila de lado a lado muestra cómo decae una cohorte.
Desfases de mes
La columna clave de la cuadrícula es el desfase: cuántos meses calendario separan un mes activo del mes de
la cohorte. DATEDIFF('month', start, end) cuenta los límites de mes cruzados, así que
DATEDIFF('month', DATE '2025-01-01', DATE '2025-03-01') es 2. Como ambas fechas son el primer día del
mes, eso es exactamente “dos meses después”.
DATEDIFF('month', cohort_month, order_month) AS month_offsetEl desfase 0 es el propio mes de la cohorte. Todo cliente de la cohorte está activo ahí por definición, lo que hace que el conteo del desfase 0 sea el tamaño de la cohorte.
Primero formato largo
SQL es bueno con tablas largas (una fila por cohorte y desfase) y torpe con las anchas. Produce la
cuadrícula larga y luego pivótala en una hoja de cálculo o en una herramienta de BI. Si tienes que pivotar
en SQL, la agregación condicional lo hace: COUNT(*) FILTER (WHERE month_offset = 1) AS m1.
Ejemplo resuelto: las dos primeras cohortes de 2025
| cohort_month | month_offset | active_customers | retention_pct |
|---|---|---|---|
| 2025-01-01 | 0 | 37 | 100.0 |
| 2025-01-01 | 1 | 4 | 10.8 |
| 2025-01-01 | 2 | 3 | 8.1 |
| 2025-01-01 | 3 | 4 | 10.8 |
| 2025-02-01 | 0 | 28 | 100.0 |
| 2025-02-01 | 3 | 7 | 25.0 |
Dos cosas que notar. La retención puede subir entre desfases: esta cuadrícula pregunta “activo en ese mes”, no “volvió al menos una vez para entonces”, así que un cliente puede saltarse el mes 2 y volver en el mes 3. Y estas cohortes son pequeñas (de 28 a 52 clientes en el primer semestre de 2025, porque la mayoría de los clientes de Harbor & Pine hicieron su primer pedido antes), así que un solo cliente mueve una celda entre 2 y 4 puntos.
Elegir el rango de cohortes
El desfase 3 de la cohorte de junio de 2025 es septiembre de 2025, que está completo. Una cohorte de noviembre de 2025 aún no tiene mes 3; incluirla mostraría celdas vacías o engañosamente bajas. Muestra solo los desfases que ya han ocurrido por completo.
Errores comunes
- Contar pedidos, no clientes. Deduplica primero a cliente-mes.
- Filtrar antes de la ventana de cohortes. Halla el primer pedido sobre todo el historial y luego filtra las cohortes.
- Ceros ausentes. Una cohorte sin clientes activos en cierto desfase no tiene ninguna fila. Para una cuadrícula completa, haz un cross join de las cohortes con los desfases 0 a 3 y un LEFT JOIN con los conteos.
En SQL Server
DATEDIFF(month, cohort_month, order_month) cuenta límites de mes exactamente igual que DuckDB. Construye
los inicios de mes con DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1), y el mes siguiente con
DATEADD(month, 1, cohort_month).
¡Linterna encendida!
Practicaste: cohort retentiondatediffmonth offsetctewindow functions
- Resuelto
- Sin pistas
- Al primer intento