La solicitud
De Robert Kim, Director de Operaciones
“La semana que viene presento la retención ante la junta. Necesito tres cosas que pueda defender: cuántos de los clientes del año pasado volvieron a comprar este año, si el canal por el que un cliente compró por primera vez cambia qué tan probable es que se vaya, y qué parte de las ventas de 2025 vino de clientes que conservamos frente a los nuevos.”
Tu objetivo
Paso 1 de 3. Retención año contra año. Para cada año Y en 2023 y 2024, devuelve from_year (Y), customers (clientes con un pedido registrado en Y), retained (de ellos, clientes que también tienen un pedido registrado en Y + 1) y retention_pct (retained × 100 / customers, redondeado a 1), ordenado por from_year.
- Lee de la tabla
customerspendiente - Lee de la tabla
orderspendiente - Muestra exactamente
from_yearcustomersretainedretention_pctpendiente
Paso 2 de 3. Abandono por canal del primer pedido. El canal del primer pedido de un cliente es el channel de su primer pedido registrado. Entre los clientes cuyo primer pedido registrado es anterior a 2025-07-04, un cliente ha abandonado si su último pedido registrado es anterior a 2025-07-04. Devuelve channel, customers, churned y churn_pct (churned × 100 / customers, redondeado a 1), ordenado por channel.
- Lee de la tabla
customerspendiente - Lee de la tabla
orderspendiente - Muestra exactamente
channelcustomerschurnedchurn_pctpendiente
Paso 3 de 3. Ventas netas de 2025 por tipo de cliente. Toma a todos los clientes con un pedido registrado en 2025. Etiquétalos new si su primer pedido registrado es de 2025, retained si tienen un pedido registrado en 2024, y reactivated en otro caso. Las ventas netas son Σ quantity × unit_price × (1 − discount_pct / 100) sobre las líneas con quantity > 0 de sus pedidos registrados de 2025. Devuelve customer_type, customers, net_sales (redondeado a 2) y share_pct (la proporción del tipo sobre todas las ventas netas de 2025 × 100, redondeada a 1), ordenado por net_sales descendente.
- Lee de la tabla
customerspendiente - Lee de la tabla
orderspendiente - Muestra exactamente
customer_typecustomersnet_salesshare_pctpendiente
Paso 1 · tu consulta
Cargando el editor…
Solución de referencia
| customer_id | first_name | last_name | city | |
|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Paso 2 · tu consulta
Cargando el editor…
Solución de referencia
| customer_id | first_name | last_name | city | |
|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Paso 3 · tu consulta
Cargando el editor…
Solución de referencia
| customer_id | first_name | last_name | city | |
|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Lo que recibe Robert, y por qué
Tablas
Datos de la tienda Harbor & Pine, listos para consultar.
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 |
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 |
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 |
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 |
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
Tres preguntas, tres definiciones
Una revisión de retención para una junta responde tres preguntas, y cada una necesita su propia definición escrita en la diapositiva:
- ¿Los clientes del año pasado vuelven a comprar? Retención año contra año: de los clientes activos en el año Y, la proporción que también está activa en Y + 1.
- ¿Quién se va? Abandono con una ventana de silencio fija, dividido por algo sobre lo que puedas actuar.
- ¿De dónde viene el dinero de este año? Ventas divididas entre clientes nuevos, retenidos y que regresan.
Las tres usan solo pedidos registrados, como el resto del capítulo.
Paso 1: retención año contra año
Haz una fila por cliente y año, y luego une esa lista consigo misma desplazada un año:
FROM active a
LEFT JOIN active n ON n.customer_id = a.customer_id AND n.yr = a.yr + 1COUNT(*) cuenta a todos los activos en Y; COUNT(n.customer_id) cuenta solo a los que coincidieron en
Y + 1, porque las filas sin coincidencia tienen NULL ahí. 2025 no tiene un año siguiente en los datos,
así que no es un año de partida.
Esta tasa es fácil de explicar, pero mezcla a clientes de toda la vida con personas que compraron por primera vez en diciembre. Una caída puede significar “más clientes nuevos”, no “peor retención”.
Paso 2: abandono por primer canal
arg_min(value, key) de DuckDB devuelve el valor de la fila con la menor clave: el canal del primer
pedido de cada cliente. Luego reutiliza la regla del nivel 4: son elegibles los clientes que compraron
por primera vez antes del 2025-07-04, y han abandonado los que no han comprado nada desde entonces.
Paso 3: de dónde vinieron las ventas de 2025
Etiqueta a cada comprador de 2025 según su historial. Nuevo: su primer pedido registrado es de 2025.
Retenido: compró en 2024. Reactivado: compró antes de 2024, se saltó 2024 y regresó. Prueba “new” de
forma explícita: el último pedido de un cliente nuevo antes de 2025 es NULL, y sin esa rama caería en el
ELSE.
La columna de proporción necesita un total junto a cada grupo. Una ventana sobre el resultado agrupado lo hace en una sola pasada:
SUM(net_sales) * 100.0 / SUM(SUM(net_sales)) OVER ()Errores comunes
- Inner join para la retención. Elimina a los clientes que no volvieron, y la retención pasa a ser 100%.
- Abandono sobre todos los clientes. Conserva el filtro de elegibilidad del nivel 4.
- Sin rama “new”. Sin una prueba sobre
first_at, los clientes nuevos caen en “reactivated”.
¡Linterna encendida!
Practicaste: retentionchurnreactivationcohortcterevenue mix
- Resuelto
- Sin pistas
- Al primer intento