Sprint 4: User metrics and funnels

🥅 Objetivo de la sesión

Comprender cómo modelar, medir y analizar el recorrido del usuario (user journey) dentro de un producto digital usando SQL.
Al finalizar, los estudiantes podrán:

• Identificar y estructurar un journey en etapas claras.

• Medir conversión, abandono y tiempos con SQL.

• Crear tablas derivadas para journeys usando consultas SQL básicas.

• Construir embudos (funnels) y segmentaciones.

• Introducir y aplicar CTEs (Common Table Expressions) para journeys complejos.

---

🗂️ Agenda de la sesión

1. Introducción al User Journey (Teoría)

• ¿Qué es un user journey?

• Diferencias entre macro-journey y micro-journey.

• Dimensiones clave de análisis: usuario, sesión, evento.

• Métricas fundamentales: conversión, drop-off, tiempo en etapa y volumen.

• Ejemplos en e-commerce, educación online y salud.

---

🧭 ¿Qué es un User Journey?

El user journey es la secuencia de pasos que realiza un usuario dentro de un producto o servicio hasta lograr (o no) un objetivo.

En bases de datos:
Cada paso = un evento registrado, ordenado por tiempo.

Ejemplo e-commerce:

1. page_view → visita general

2. view_item → ve un producto

3. add_to_cart → agrega al carrito

4. begin_checkout → inicia el pago

5. purchase → compra final

Analizar este recorrido permite identificar:

• Dónde se pierden usuarios.

• Qué tan eficiente es cada paso.

• Dónde intervenir para mejorar conversión.

---

🔽 Funnel (Embudo de conversión)

Un funnel es la representación visual del journey: muestra cuántos usuarios pasan por cada etapa y dónde se produce el abandono.

📉 Comportamientos típicos del embudo

Comportamiento	Qué indica
El embudo se estrecha gradualmente	Abandono natural. Flujo normal.
Caída abrupta en una etapa	Punto de dolor → fricción, error técnico o mala experiencia.
El embudo se ensancha	Error de medición, duplicados o eventos mal etiquetados.

Mas metricas clave

🧩 Tipos de Journey

1️⃣ Macro-Journey

• Recorre toda la experiencia del usuario de punta a punta.

• Requiere unir múltiples eventos y tablas en SQL.

• Útil para ver la imagen general y detectar cuellos de botella globales.

2️⃣ Micro-Journey

• Analiza solo un paso concreto del proceso.

• Usa consultas más simples.

• Ideal para investigar fricciones específicas.

---

🧱 Dimensiones clave de análisis

Antes de escribir SQL, define qué estás contando.

Dimensión	Representa	Pregunta clave
Usuario (user_id)	Persona única	¿Cuántas personas avanzan o compran?
Sesión (session_id)	Conjunto de acciones en un periodo	¿Cuántos intentos necesita el usuario para avanzar?
Evento (event_name)	Acción puntual	¿Cuántas veces ocurre algo crítico?

💡 Elegir la dimensión correcta cambia completamente la interpretación del funnel.

---

📐 Métricas fundamentales en un Funnel

Estas son las métricas esenciales para evaluar eficiencia, fricción y velocidad del journey.

1. Tasa de Conversión

Qué porcentaje avanza de A → B
Fórmula: usuarios_B / usuarios_A

Indica eficiencia del paso.

---

2. Tasa de Abandono (Drop-off)

Qué porcentaje NO avanza de A → B
Fórmula: 1 - (usuarios_B / usuarios_A)

Identifica el punto exacto donde se pierden usuarios.

---

3. Tiempo en etapa

Tiempo que pasa un usuario entre acciones consecutivas.

Revela demoras, dudas o pasos demasiado complejos.

---

4. Volumen

Cantidad absoluta de usuarios únicos en cada etapa.

Sirve para dimensionar el problema.

---

5. Velocidad de conversión

Tiempo total desde el inicio del journey hasta la conversión.

Evalúa la eficiencia temporal del proceso completo.

---

📚 Diccionario de eventos clave (E-commerce)

Orden	Evento	Significado	Etapa
1	page_view	Vista de página	Visita general
2	view_item	Ver producto	Interés
3	add_to_cart	Agregar al carrito	Intención
4	begin_checkout	Iniciar checkout	Decisión
5	purchase	Compra	Conversión

---

🎯 ¿Qué se mide en cada etapa? (Objetivos de negocio)

Evento	Conversion %	Drop-off %	Tiempo promedio
page_view	¿Cuántos llegan al producto?	¿Quién no sigue?	Tiempo viendo info general
view_item	Interés real	Abandono del producto	Tiempo para decidir
add_to_cart	Intención de compra	Fricción antes del checkout	Tiempo antes de iniciar pago
begin_checkout	Decisión fuerte	Abandono del pago	Tiempo en completar datos
purchase	Conversión final	Oportunidad perdida	Velocidad total hacia la compra

---

🧠 Ideas clave

• Un journey bien definido nace de objetivos de negocio, no de la base de datos.

• Macro y micro journeys permiten análisis estratégico y táctico.

• Elegir entre usuario, sesión o evento cambia la historia.

• El funnel muestra qué pasa, las métricas muestran por qué pasa.

• Cada etapa “cuenta una historia” sobre conversión, abandono y fricción.

---

✔️ Conclusión

Dominar user journeys te permite:

• Conectar comportamiento real con decisiones de negocio.

• Detectar dónde mejorar la experiencia del usuario.

• Construir análisis sólidos en SQL para embudos de conversión.

---

🧪 Práctica guiada - Análisis de Usuarios, Ventas y Métricas de Negocio con SQL

Base de datos: ecommerce_demo.db

Tablas:

• customers

• sales

• discount_coupon

• marketing_spend

• tax_amount

🧪 SQL para Journeys y Funnels (MySQL)

Este notebook muestra consultas SQL y cómo ejecutarlas desde Python usando sqlite3 y pandas.

import sqlite3
import pandas as pd

# Hacemos la conexión a la Database
conn = sqlite3.connect("ecommerce_demo.db")

# Explora la tabla eventos y su contenido

query= ''' 

SELECT *
FROM events'''

pd.read_sql(query, conn)

	event_id	customer_id	session_id	event_name	event_time	product_category	is_purchase_session
0	1	17850	txn_16679	page_view	2018-12-31 23:45:00	Nest-USA	1
1	2	17850	txn_16679	view_item	2018-12-31 23:50:00	Nest-USA	1
2	3	17850	txn_16679	add_to_cart	2018-12-31 23:55:00	Nest-USA	1
3	4	17850	txn_16679	begin_checkout	2018-12-31 23:58:00	Nest-USA	1
4	5	17850	txn_16679	purchase	2019-01-01 00:00:00	Nest-USA	1
...	...	...	...	...	...	...	...
269519	269520	14410	cust14410_extra2	view_item	2019-12-12 05:03:00	Notebooks & Journals	0
269520	269521	14410	cust14410_extra2	add_to_cart	2019-12-12 05:08:00	Notebooks & Journals	0
269521	269522	14410	cust14410_extra3	page_view	2019-06-22 00:15:00	Nest-Canada	0
269522	269523	14410	cust14410_extra3	view_item	2019-06-22 00:20:00	Nest-Canada	0
269523	269524	14600	cust14600_extra1	page_view	2019-08-09 10:54:00	Bags	0

269524 rows × 7 columns

• Eventos por tipo

# Eventos por tipo

query= ''' SELECT event_name, 
COUNT(*) AS total 
FROM events 
GROUP BY event_name '''
pd.read_sql(query, conn)

	event_name	total
0	add_to_cart	53794
1	begin_checkout	53265
2	page_view	55096
3	purchase	52924
4	view_item	54445

# sesiones UNICAS por evento

query= ''' SELECT event_name, 
COUNT(DISTINCT session_id)
FROM events
GROUP BY event_name '''
pd.read_sql(query, conn)

	event_name	COUNT(DISTINCT session_id)
0	add_to_cart	25931
1	begin_checkout	25402
2	page_view	27233
3	purchase	25061
4	view_item	26582

# sesiones UNICAS por usuario y evento

query= '''SELECT customer_id, event_name,
COUNT(DISTINCT session_id) AS sesiones
FROM events
GROUP BY customer_id, event_name '''
pd.read_sql(query, conn)

	customer_id	event_name	sesiones
0	12346	add_to_cart	1
1	12346	begin_checkout	1
2	12346	page_view	2
3	12346	purchase	1
4	12346	view_item	2
...	...	...	...
7335	18283	add_to_cart	54
7336	18283	begin_checkout	54
7337	18283	page_view	54
7338	18283	purchase	53
7339	18283	view_item	54

7340 rows × 3 columns

2️⃣ Subqueries

• Sesiones de page_view que NO llegaron a purchase

# Query 1: Sesiones que llegaron a purchase

query= '''SELECT 
DISTINCT session_id
FROM events
WHERE event_name = 'purchase'
'''
pd.read_sql(query, conn)

	session_id
0	txn_16679
1	txn_16680
2	txn_16681
3	txn_16682
4	txn_16684
...	...
25056	txn_48493
25057	txn_48494
25058	txn_48495
25059	txn_48496
25060	txn_48497

25061 rows × 1 columns

# Query 2: Sesiones de page view

query= '''SELECT 
DISTINCT session_id
FROM events
WHERE event_name = 'page_view'  '''
pd.read_sql(query, conn)

	session_id
0	txn_16679
1	txn_16680
2	txn_16681
3	txn_16682
4	txn_16684
...	...
27228	cust15781_extra1
27229	cust14410_extra1
27230	cust14410_extra2
27231	cust14410_extra3
27232	cust14600_extra1

27233 rows × 1 columns

#Query 3: sesiones de page view que no llegaron a purchase

query= '''SELECT 
DISTINCT session_id, customer_id
FROM events
WHERE event_name = 'page_view'
AND session_id NOT IN (SELECT 
DISTINCT session_id
FROM events
WHERE event_name = 'purchase')
'''
pd.read_sql(query, conn)

	session_id	customer_id
0	cust17850_extra1	17850
1	cust17850_extra2	17850
2	cust12583_extra1	12583
3	cust15100_extra1	15100
4	cust15100_extra2	15100
...	...	...
2167	cust15781_extra1	15781
2168	cust14410_extra1	14410
2169	cust14410_extra2	14410
2170	cust14410_extra3	14410
2171	cust14600_extra1	14600

2172 rows × 2 columns

SESION PRACTICA: Common Table Expressions (CTE) y Métricas de usuario

📋 Qué es un CTE (Common Table Expression)

Un CTE es una consulta temporal cuyos resultados puedes reutilizar dentro de una sentencia SQL más grande. Se declara con la palabra clave WITH al inicio de tu query.

¿Cuándo usarlo?

• Cuando tu lógica necesita subconsultas anidadas complejas.

• Para dividir un problema grande en pasos intermedios legibles.

• Cuando vas a referenciar la misma subconsulta varias veces.

Principales ventajas

1. Legibilidad: cada bloque CTE tiene un nombre descriptivo.

2. Mantenimiento: si cambias la lógica intermedia, sólo ajustas un bloque.

3. Reutilización: no repites la misma subconsulta en distintos lugares.

---

Queremos listar los clientes cuyo revenue total supera el promedio de todos los clientes. Con una sola sentencia:

# Con subqueries
query= '''
    SELECT
  s.CustomerID,
  SUM(s.Quantity * s.Avg_Price) AS total_revenue
FROM sales s
GROUP BY s.CustomerID
HAVING total_revenue > (
    SELECT AVG(t.revenue) 
    FROM (
        SELECT CustomerID, SUM(Quantity * Avg_Price) AS revenue
        FROM sales
        GROUP BY CustomerID
    ) AS t)
    '''
pd.read_sql(query, conn)

	CustomerID	total_revenue
0	12347	13834.90
1	12370	6914.31
2	12377	9950.31
3	12383	4954.12
4	12431	5600.17
...	...	...
451	18202	4726.20
452	18223	4403.60
453	18239	5893.31
454	18245	7692.95
455	18283	6362.77

456 rows × 2 columns

#Con CTE
query= '''
WITH
  customer_revenue AS (
    SELECT 
      CustomerID,
      SUM(Quantity * Avg_Price) AS revenue
    FROM sales
    GROUP BY CustomerID
  ),
  avg_revenue AS (
    SELECT AVG(revenue) AS avg_rev
    FROM customer_revenue
  )
SELECT
  cr.CustomerID,
  cr.revenue AS total_revenue
FROM customer_revenue cr
JOIN avg_revenue ar ON 1=1
WHERE cr.revenue > ar.avg_rev;

    '''
pd.read_sql(query, conn)

	CustomerID	total_revenue
0	12347	13834.90
1	12370	6914.31
2	12377	9950.31
3	12383	4954.12
4	12431	5600.17
...	...	...
451	18202	4726.20
452	18223	4403.60
453	18239	5893.31
454	18245	7692.95
455	18283	6362.77

456 rows × 2 columns

Embudo de conversion

query= '''
WITH
  pv AS (
    SELECT COUNT(DISTINCT session_id) AS cnt_pv
    FROM events
    WHERE event_name = 'page_view'
  ),
  vi AS (
    SELECT COUNT(DISTINCT session_id) AS cnt_vi
    FROM events
    WHERE event_name = 'view_item'
  ),
  ac AS (
    SELECT COUNT(DISTINCT session_id) AS cnt_ac
    FROM events
    WHERE event_name = 'add_to_cart'
  ),
  co AS (
    SELECT COUNT(DISTINCT session_id) AS cnt_co
    FROM events
    WHERE event_name = 'begin_checkout'
  ),
  pu AS (
    SELECT COUNT(DISTINCT session_id) AS cnt_pu
    FROM events
    WHERE event_name = 'purchase'
  )
SELECT
  pv.cnt_pv    AS page_views,
  vi.cnt_vi    AS view_items,
  ac.cnt_ac    AS add_to_cart,
  co.cnt_co    AS begin_checkout,
  pu.cnt_pu    AS purchases,
  ROUND(vi.cnt_vi*100.0/pv.cnt_pv,1) AS conv_pv_vi_pct,
  ROUND(ac.cnt_ac*100.0/vi.cnt_vi,1) AS conv_vi_ac_pct,
  ROUND(pu.cnt_pu*100.0/ac.cnt_ac,1) AS conv_ac_pu_pct
FROM pv, vi, ac, co, pu;
    '''
pd.read_sql(query, conn)

	page_views	view_items	add_to_cart	begin_checkout	purchases	conv_pv_vi_pct	conv_vi_ac_pct	conv_ac_pu_pct
0	27233	26582	25931	25402	25061	97.6	97.6	96.6

Ejercicios:

1. Usa un CTE para calcular el Drop-off Rate entre add_to_cart y begin_checkout.

2. Calcular el tiempo promedio (en minutos) que tardan las sesiones en ir de view_item a add_to_cart. Usa un CTE que obtenga ambos timestamps por sesión y luego la diferencia.

3. Revenue neto por categoría con CTEs:

   • Usa dos CTEs:

   • ventas: suma Quantity * Avg_Price por categoría.

   • descuentos: aplica el % de discount_coupon por categoría.