La solicitud
De Jordan Reyes, Director de Marketing
“Acordamos el plan: un embudo por dispositivo, de sesiones a interacción a conversión, y luego las mismas tasas por año. Antes de construirlo, dime para qué se puede confiar en los datos de sesiones. He oído que la mitad de nuestros visitantes son anónimos.”
Tu objetivo
Paso 1 de 3. Verifica los datos. Devuelve una fila con: sessions (todas las filas), distinct_ids (session_id distintos), anonymous (customer_id es NULL), anonymous_converted (anónima y convertida), empty_sessions (pages_viewed < 1), converted_under_3_pages (convertidas con pages_viewed < 3) y converted (todas las sesiones convertidas).
- Lee de la tabla
web_sessionspendiente - Muestra exactamente
sessionsdistinct_idsanonymousanonymous_convertedempty_sessionsconverted_under_3_pagesconvertedpendiente - 1 fila, ordenadas como se pidependiente
Paso 2 de 3. Construye el embudo por dispositivo. Devuelve device, sessions, engaged (pages_viewed >= 3), known_engaged (engaged y customer_id IS NOT NULL), converted, engaged_pct (engaged * 100 / sessions, redondeado a 1), close_pct (converted * 100 / known_engaged, redondeado a 1) y conversion_pct (converted * 100 / sessions, redondeado a 2). Ordena por device.
- Lee de la tabla
web_sessionspendiente - Muestra exactamente
devicesessionsengagedknown_engagedconvertedengaged_pctclose_pctconversion_pctpendiente - 3 filas, ordenadas como se pidependiente
Paso 3 de 3. Sigue la mezcla por año. Devuelve yr (año de started_at), sessions, mobile_share_pct (sesiones móviles * 100 / sessions, redondeado a 1), desktop_conv_pct, mobile_conv_pct y tablet_conv_pct (converted de cada dispositivo * 100 / sus sessions, redondeado a 2) y overall_conv_pct (todas las convertidas * 100 / todas las sesiones, redondeado a 2). Ordena por yr.
- Lee de la tabla
web_sessionspendiente - Muestra exactamente
yrsessionsmobile_share_pctdesktop_conv_pctmobile_conv_pcttablet_conv_pctoverall_conv_pctpendiente - 3 filas, ordenadas como se pidependiente
Este caso se desbloquea cuando terminas cualquiera de estos:
- SQL · CASE, fechas y cadenas de texto
- Python · Columnas nuevas, groupby y agregación
- Excel · Totales condicionales
La solicitud y la lección ya se pueden leer. Resuelve todos los niveles de uno de esos capítulos, en la ruta que prefieras, y este caso se abre.
Paso 1 · tu consulta
Cargando el editor…
Solución de referencia
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic |
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
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic |
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
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Lo que recibe Jordan, y por qué
Tablas
Datos de la tienda Harbor & Pine, listos para consultar.
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 |
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 |
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 |
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 |
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
Qué estás verificando y por qué
web_sessions es una tabla de seguimiento: cada visita al sitio, su dispositivo y fuente, cuántas páginas
vio y si terminó en una compra. Las tablas de seguimiento están llenas de huecos, así que antes de construir un
embudo sobre una, averigua lo que puede y no puede mostrar. El paso 1 hace cuatro preguntas de una sola pasada:
- ¿Se cuenta alguna sesión dos veces? Compara
COUNT(*)conCOUNT(DISTINCT session_id). - ¿Hay sesiones vacías? Una visita con cero páginas es un fallo de seguimiento.
- ¿Quién puede convertir? Cuenta las sesiones anónimas y cuántas de ellas convirtieron.
- ¿Se sostiene la definición? Cuenta las conversiones con menos de tres páginas.
Las cuatro son conteos condicionales sin GROUP BY, así que regresan como una sola fila:
SUM(CASE WHEN customer_id IS NULL AND converted THEN 1 ELSE 0 END) AS anonymous_convertedEl cero en esa columna es el número más importante del caso. Más de la mitad de las sesiones nunca pueden mostrar una compra, porque las compras solo se registran contra una cuenta. Las tasas que construyas a continuación son tasas de compras registradas, y el embudo necesita una etapa para iniciar sesión.
Un embudo es una cadena de conteos condicionales
Un embudo sigue a las mismas sesiones a través de etapas que se estrechan:
| etapa | condición |
|---|---|
| sessions | cada fila |
| engaged | pages_viewed >= 3 |
| known and engaged | además customer_id IS NOT NULL |
| converted | converted es verdadero |
Cada etapa es un SUM(CASE WHEN ...) en la misma consulta GROUP BY device. Las tasas dividen una etapa
entre la anterior, para ver dónde abandona la gente: engaged_pct es el paso de navegación,
close_pct es el paso de pago. Multiplica por 100.0, con el punto decimal, antes de dividir;
563 * 100 / 2634 en aritmética entera pierde los decimales.
Cómo leer el embudo
| device | engaged_pct | close_pct | conversion_pct |
|---|---|---|---|
| desktop | 44.1 | 21.4 | 3.87 |
| mobile | 44.1 | 12.9 | 2.41 |
| tablet | 44.8 | 14.6 | 2.81 |
El paso de navegación es el mismo en todos los dispositivos. El paso de pago es donde el móvil se queda atrás. Eso cambia el encargo al equipo móvil: las páginas de producto cumplen su función; la cesta, el inicio de sesión y el formulario de pago son donde hay que mirar.
Por qué importa la vista por año
El paso 3 repite la tasa de conversión por año, una columna por dispositivo. La proporción de tráfico móvil creció cada año, y el móvil convierte menos, así que la tasa global es un promedio ponderado cuyos pesos se siguen moviendo hacia la tasa baja. Todos los dispositivos mejoraron de 2023 a 2025, mientras la tasa global creció mucho menos. Si mantuvieras la mezcla de dispositivos de 2023 y aplicaras las tasas de 2025, la tasa global sería aproximadamente 3.24% en lugar de 3.08%. Divide siempre una tasa por sus segmentos antes de llamarla tendencia.
Trampas
AVG(converted)sobre un booleano funciona en DuckDB, peroSUM(CASE ...)hace explícito el numerador y funciona en todas las bases de datos.- Dividir entre
COUNT(*)para la tasa de cierre mezcla etapas: divide entre la etapa inmediatamente anterior. year(started_at)agrupa por año calendario; 2025 tiene más sesiones que 2023, así que compara tasas, no conteos.
¡Linterna encendida!
Practicaste: data validationconditional aggregationfunnelconversion ratemix shiftsimpsons paradox
- Resuelto
- Sin pistas
- Al primer intento