Módulo 6: Accumulating And Cumulative Patterns

Modelando el funnel de sesiones de Kiosko

Siguiente →

Descripción

Esta lección construye fact_sessions completo: diecisiete filas, una por cada sesión de navegación que aparece en los 32 events de Kiosko, usando una sola consulta agregada que aplica el mecanismo de la lección 2 —milestone por columna— a todas las sesiones a la vez. Antes de escribir esa consulta, esta lección resuelve un problema que la lección 2 evitó a propósito: events no trae store_id en ninguna de sus columnas, así que hace falta declarar, de forma explícita y fija, a qué tienda pertenece cada sesión.

Conexión con el módulo. Esta lección toma el mecanismo verificado en la lección 2 (una sesión, tres milestones, una fila) y lo aplica a la población completa de sesiones de Kiosko, usando MAX(CASE WHEN ...) en vez de INSERT/UPDATE manuales — el equivalente de un recálculo completo desde cero. La lección 4 va a reconstruir la misma tabla evento por evento, para comparar los dos enfoques.

Los 32 events canónicos de Kiosko, sin ningún cambio

events es el mismo dataset que dbt-analytics-engineering-guide declaró como source — mismos event_id, event_type, session_id, event_ts, sin inventar ni un evento nuevo. Esta guía lo reutiliza exacto:

# events.py
RAW_EVENTS = [
    # 2026-08-03
    ("E5001", "page_view", "SESS-01", "2026-08-03T08:00:12"),
    ("E5002", "add_to_cart", "SESS-01", "2026-08-03T08:02:45"),
    ("E5003", "purchase", "SESS-01", "2026-08-03T08:03:10"),
    ("E5004", "page_view", "SESS-02", "2026-08-03T08:05:00"),
    # 2026-08-04
    ("E5005", "page_view", "SESS-03", "2026-08-04T08:10:00"),
    ("E5006", "add_to_cart", "SESS-03", "2026-08-04T08:12:30"),
    ("E5007", "purchase", "SESS-03", "2026-08-04T08:13:05"),
    ("E5008", "page_view", "SESS-04", "2026-08-04T08:20:00"),
    ("E5009", "page_view", "SESS-05", "2026-08-04T08:45:00"),
    # 2026-08-05
    ("E5010", "page_view", "SESS-06", "2026-08-05T08:00:00"),
    ("E5011", "page_view", "SESS-07", "2026-08-05T08:15:00"),
    ("E5012", "add_to_cart", "SESS-07", "2026-08-05T08:16:20"),
    # 2026-08-06
    ("E5013", "page_view", "SESS-08", "2026-08-06T08:05:00"),
    ("E5014", "add_to_cart", "SESS-08", "2026-08-06T08:07:15"),
    ("E5015", "purchase", "SESS-08", "2026-08-06T08:08:00"),
    ("E5016", "page_view", "SESS-09", "2026-08-06T08:30:00"),
    # 2026-08-07
    ("E5017", "page_view", "SESS-10", "2026-08-07T08:00:00"),
    ("E5018", "add_to_cart", "SESS-10", "2026-08-07T08:03:10"),
    ("E5019", "purchase", "SESS-10", "2026-08-07T08:04:00"),
    ("E5020", "page_view", "SESS-11", "2026-08-07T08:20:00"),
    ("E5021", "add_to_cart", "SESS-11", "2026-08-07T08:22:00"),
    ("E5022", "page_view", "SESS-12", "2026-08-07T08:50:00"),
    # 2026-08-08
    ("E5023", "page_view", "SESS-13", "2026-08-08T07:55:00"),
    ("E5024", "add_to_cart", "SESS-13", "2026-08-08T07:58:00"),
    ("E5025", "purchase", "SESS-13", "2026-08-08T07:59:10"),
    ("E5026", "page_view", "SESS-14", "2026-08-08T08:10:00"),
    ("E5027", "add_to_cart", "SESS-14", "2026-08-08T08:12:45"),
    ("E5028", "purchase", "SESS-14", "2026-08-08T08:13:30"),
    ("E5029", "page_view", "SESS-15", "2026-08-08T08:40:00"),
    # 2026-08-09
    ("E5030", "page_view", "SESS-16", "2026-08-09T09:00:00"),
    ("E5031", "page_view", "SESS-17", "2026-08-09T09:20:00"),
    ("E5032", "add_to_cart", "SESS-17", "2026-08-09T09:22:00"),
]

32 filas — 17 page_view, 9 add_to_cart, 6 purchase — repartidas entre 17 sesiones distintas (SESS-01 a SESS-17), exactamente como lo declaró la guía hermana.

Una analogía: el directorio de la tienda, no el recibo de un cliente

store_id no está en events por una razón de diseño real, no por descuido: el clickstream de una app de delivery registra lo que un cliente hace (ver una página, agregar algo, comprar), no en qué tienda física ocurre — muchas apps de delivery ni siquiera le muestran al cliente el concepto de "tienda", solo un catálogo. Asignar cada sesión a una tienda es, entonces, una decisión de negocio que vive fuera del evento — parecida a cómo un directorio telefónico asigna cada número a una sucursal, sin que el número en sí mismo lleve esa información codificada.

Kiosko todavía no tiene, en los datos que esta guía usa, un sistema real de atribución de sesión a tienda (algo que normalmente vendría de la ubicación del cliente, o de qué catálogo de tienda estaba navegando). Para poder construir fact_sessions con una columna store_id completa, esta guía declara un mapeo fijo y determinista, aditivo a lo que la guía hermana dbt-analytics-engineering-guide modela — esa guía nunca asigna sesiones a tiendas, así que esta declaración no la contradice, solo completa una pieza que Kiosko necesita para este análisis específico.

El mapeo sesión → tienda, declarado de forma explícita

El mapeo rota las tres tiendas de Kiosko en el mismo orden en que aparecen desde el módulo 1 (S01 Bogotá, S02 Lima, S03 Santiago), asignando cada sesión según su número de secuencia: SESS-01 -> S01, SESS-02 -> S02, SESS-03 -> S03, SESS-04 -> S01 (la rotación empieza de nuevo), y así sucesivamente.

# session_store_map.py
STORE_ROTATION = ["S01", "S02", "S03"]


def store_for_session(session_id: str) -> str:
    """Mapeo fijo y deterministico de sesion a tienda: rota S01/S02/S03 segun
    el numero de secuencia de la sesion. SESS-01 -> S01, SESS-02 -> S02,
    SESS-03 -> S03, SESS-04 -> S01 (la rotacion reinicia), etc.

    Esta asignacion es aditiva: events (el source canonico de la guia
    dbt-analytics-engineering-guide) nunca declara store_id, asi que esta
    guia la agrega para poder construir fact_sessions con contexto de tienda.
    """
    session_number = int(session_id.split("-")[1])
    return STORE_ROTATION[(session_number - 1) % 3]


ALL_SESSIONS = [f"SESS-{n:02d}" for n in range(1, 18)]  # SESS-01 .. SESS-17

for session_id in ALL_SESSIONS:
    print(f"{session_id} -> {store_for_session(session_id)}")

Qué esperar. Al correr python3 session_store_map.py, la salida es exactamente esta:

SESS-01 -> S01
SESS-02 -> S02
SESS-03 -> S03
SESS-04 -> S01
SESS-05 -> S02
SESS-06 -> S03
SESS-07 -> S01
SESS-08 -> S02
SESS-09 -> S03
SESS-10 -> S01
SESS-11 -> S02
SESS-12 -> S03
SESS-13 -> S01
SESS-14 -> S02
SESS-15 -> S03
SESS-16 -> S01
SESS-17 -> S02

Diecisiete sesiones, repartidas 6/6/5 entre las tres tiendas (S01 y S02 con seis cada una, S03 con cinco) — una consecuencia aritmética de que 17 no es múltiplo de 3, no una decisión de negocio adicional.

Ejemplo trabajado: fact_sessions completo, con MAX(CASE WHEN ...)

Con events cargado en DuckDB y el mapeo declarado como tabla auxiliar, la consulta que construye fact_sessions completo usa el mismo patrón MAX(CASE WHEN event_type = '...' THEN event_ts END) de la lección 2, ahora agrupado por session_id para las diecisiete sesiones a la vez:

# fact_sessions_build.py -- continua sobre events cargado y session_store_map declarado
import duckdb
from datetime import datetime

con = duckdb.connect()
con.execute("CREATE TABLE events (event_id VARCHAR, event_type VARCHAR, session_id VARCHAR, event_ts TIMESTAMP)")
con.executemany(
    "INSERT INTO events VALUES (?, ?, ?, ?)",
    [(r[0], r[1], r[2], datetime.fromisoformat(r[3])) for r in RAW_EVENTS],
)

con.execute("CREATE TABLE session_store_map (session_id VARCHAR, store_id VARCHAR)")
con.executemany(
    "INSERT INTO session_store_map VALUES (?, ?)",
    [(sid, store_for_session(sid)) for sid in ALL_SESSIONS],
)

con.execute("""
    CREATE TABLE fact_sessions AS
    SELECT
        e.session_id,
        m.store_id,
        MIN(CAST(e.event_ts AS DATE)) AS session_date,
        MAX(CASE WHEN e.event_type = 'page_view'   THEN e.event_ts END) AS view_ts,
        MAX(CASE WHEN e.event_type = 'add_to_cart' THEN e.event_ts END) AS add_to_cart_ts,
        MAX(CASE WHEN e.event_type = 'purchase'    THEN e.event_ts END) AS purchase_ts,
        MAX(CASE WHEN e.event_type = 'purchase' THEN true ELSE false END) AS is_converted
    FROM events e
    JOIN session_store_map m ON e.session_id = m.session_id
    GROUP BY e.session_id, m.store_id
""")

print(f"fact_sessions rows: {con.sql('SELECT COUNT(*) FROM fact_sessions').fetchone()[0]}\n")
con.sql("SELECT * FROM fact_sessions ORDER BY session_id").show(max_width=300)

Qué esperar.

fact_sessions rows: 17

┌────────────┬──────────┬──────────────┬─────────────────────┬─────────────────────┬─────────────────────┬──────────────┐
│ session_id │ store_id │ session_date │       view_ts       │   add_to_cart_ts    │     purchase_ts     │ is_converted │
│  varchar   │ varchar  │     date     │      timestamp      │      timestamp      │      timestamp      │   boolean    │
├────────────┼──────────┼──────────────┼─────────────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ SESS-01    │ S01      │ 2026-08-03   │ 2026-08-03 08:00:12 │ 2026-08-03 08:02:45 │ 2026-08-03 08:03:10 │ true         │
│ SESS-02    │ S02      │ 2026-08-03   │ 2026-08-03 08:05:00 │ NULL                │ NULL                │ false        │
│ SESS-03    │ S03      │ 2026-08-04   │ 2026-08-04 08:10:00 │ 2026-08-04 08:12:30 │ 2026-08-04 08:13:05 │ true         │
│ SESS-04    │ S01      │ 2026-08-04   │ 2026-08-04 08:20:00 │ NULL                │ NULL                │ false        │
│ SESS-05    │ S02      │ 2026-08-04   │ 2026-08-04 08:45:00 │ NULL                │ NULL                │ false        │
│ SESS-06    │ S03      │ 2026-08-05   │ 2026-08-05 08:00:00 │ NULL                │ NULL                │ false        │
│ SESS-07    │ S01      │ 2026-08-05   │ 2026-08-05 08:15:00 │ 2026-08-05 08:16:20 │ NULL                │ false        │
│ SESS-08    │ S02      │ 2026-08-06   │ 2026-08-06 08:05:00 │ 2026-08-06 08:07:15 │ 2026-08-06 08:08:00 │ true         │
│ SESS-09    │ S03      │ 2026-08-06   │ 2026-08-06 08:30:00 │ NULL                │ NULL                │ false        │
│ SESS-10    │ S01      │ 2026-08-07   │ 2026-08-07 08:00:00 │ 2026-08-07 08:03:10 │ 2026-08-07 08:04:00 │ true         │
│ SESS-11    │ S02      │ 2026-08-07   │ 2026-08-07 08:20:00 │ 2026-08-07 08:22:00 │ NULL                │ false        │
│ SESS-12    │ S03      │ 2026-08-07   │ 2026-08-07 08:50:00 │ NULL                │ NULL                │ false        │
│ SESS-13    │ S01      │ 2026-08-08   │ 2026-08-08 07:55:00 │ 2026-08-08 07:58:00 │ 2026-08-08 07:59:10 │ true         │
│ SESS-14    │ S02      │ 2026-08-08   │ 2026-08-08 08:10:00 │ 2026-08-08 08:12:45 │ 2026-08-08 08:13:30 │ true         │
│ SESS-15    │ S03      │ 2026-08-08   │ 2026-08-08 08:40:00 │ NULL                │ NULL                │ false        │
│ SESS-16    │ S01      │ 2026-08-09   │ 2026-08-09 09:00:00 │ NULL                │ NULL                │ false        │
│ SESS-17    │ S02      │ 2026-08-09   │ 2026-08-09 09:20:00 │ 2026-08-09 09:22:00 │ NULL                │ false        │
└────────────┴──────────┴──────────────┴─────────────────────┴─────────────────────┴─────────────────────┴──────────────┘

Diecisiete filas — exactamente el número de session_id distintos en events, sin importar que algunas sesiones tengan un solo evento (SESS-02, con solo un page_view) y otras tengan los tres (SESS-01, SESS-03, SESS-08, SESS-10, SESS-13, SESS-14). El patrón MAX(CASE WHEN ...) deja NULL automáticamente en cualquier columna cuyo event_type correspondiente nunca aparezca para esa sesión — el mismo significado de "milestone no alcanzado" que viste en la lección 2, ahora aplicado sin escribir ningún INSERT/UPDATE manual.

Analizando el funnel: dónde se cae Kiosko

Con fact_sessions construido, el funnel completo se responde con COUNT() sobre cada columna de milestone — COUNT() ignora los NULL automáticamente, así que cuenta exactamente las sesiones que alcanzaron cada etapa:

print("=== Conteo por etapa del funnel ===")
con.sql("""
    SELECT COUNT(*) AS total_sessions, COUNT(view_ts) AS viewed,
           COUNT(add_to_cart_ts) AS added_to_cart, COUNT(purchase_ts) AS purchased
    FROM fact_sessions
""").show(max_width=200)

print("=== Tasas de conversion por etapa ===")
con.sql("""
    SELECT
        ROUND(100.0 * COUNT(add_to_cart_ts) / COUNT(view_ts), 1) AS view_to_cart_pct,
        ROUND(100.0 * COUNT(purchase_ts) / NULLIF(COUNT(add_to_cart_ts), 0), 1) AS cart_to_purchase_pct,
        ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) AS overall_conversion_pct
    FROM fact_sessions
""").show(max_width=200)

print("=== Conversion por tienda ===")
con.sql("""
    SELECT store_id, COUNT(*) AS sessions, COUNT(add_to_cart_ts) AS carts,
           COUNT(purchase_ts) AS purchases,
           ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) AS conversion_pct
    FROM fact_sessions GROUP BY store_id ORDER BY store_id
""").show(max_width=200)

Qué esperar.

=== Conteo por etapa del funnel ===
┌────────────────┬────────┬───────────────┬───────────┐
│ total_sessions │ viewed │ added_to_cart │ purchased │
│     int64      │ int64  │     int64     │   int64   │
├────────────────┼────────┼───────────────┼───────────┤
│             17 │     17 │             9 │         6 │
└────────────────┴────────┴───────────────┴───────────┘

=== Tasas de conversion por etapa ===
┌──────────────────┬──────────────────────┬────────────────────────┐
│ view_to_cart_pct │ cart_to_purchase_pct │ overall_conversion_pct │
│      double      │        double        │         double         │
├──────────────────┼──────────────────────┼────────────────────────┤
│             52.9 │                 66.7 │                   35.3 │
└──────────────────┴──────────────────────┴────────────────────────┘

=== Conversion por tienda ===
┌──────────┬──────────┬───────┬───────────┬────────────────┐
│ store_id │ sessions │ carts │ purchases │ conversion_pct │
│ varchar  │  int64   │ int64 │   int64   │     double     │
├──────────┼──────────┼───────┼───────────┼────────────────┤
│ S01      │        6 │     4 │         3 │           50.0 │
│ S02      │        6 │     4 │         2 │           33.3 │
│ S03      │        5 │     1 │         1 │           20.0 │
└──────────┴──────────┴───────┴───────────┴────────────────┘

El funnel completo de Kiosko: todas las 17 sesiones vieron al menos una página (100%, por definición del dataset), 9 agregaron algo al carrito (52.9%), y 6 compraron (35.3% de conversión total, 66.7% de quienes llegaron al carrito). La caída más pronunciada ocurre entre "vio" y "agregó al carrito" —casi la mitad de las sesiones se pierden ahí—, no entre "agregó al carrito" y "compró", donde dos de cada tres sesiones sí terminan comprando. S03 (Santiago) tiene la conversión más baja (20%) con la muestra más pequeña de las tres tiendas — un número que, con solo cinco sesiones, hay que leer como una señal débil, no una conclusión firme.

Diagrama: el funnel como embudo

flowchart TD
    A["17 sesiones\npage_view (100%)"] -->|"52.9%"| B["9 sesiones\nadd_to_cart"]
    B -->|"66.7%"| C["6 sesiones\npurchase"]
    A -.->|"35.3% conversion total"| C

Errores comunes

Olvidar el JOIN contra session_store_map y perder sesiones o duplicarlas. Qué pasa: alguien construye fact_sessions sin unir contra el mapeo de tienda, o usa un LEFT JOIN mal escrito que produce múltiples filas por sesión. Por qué pasa: session_store_map es una tabla nueva, fácil de olvidar cuando el foco está en el patrón MAX(CASE WHEN ...). Cómo detectarlo: si SELECT COUNT(*) FROM fact_sessions da un número distinto a 17 —más, por un JOIN que multiplica filas, o menos, por un INNER JOIN que descarta sesiones sin mapeo—, el JOIN está mal. Cómo corregirlo: session_store_map debe tener exactamente una fila por cada una de las 17 sesiones, sin repetir ninguna — verifica con SELECT COUNT(DISTINCT session_id) FROM session_store_map antes de construir fact_sessions, y confirma que da 17.

Confundir COUNT(*) con COUNT(columna) al medir el funnel. Qué pasa: alguien usa COUNT(*) para contar cuántas sesiones "agregaron al carrito", en vez de COUNT(add_to_cart_ts). Por qué pasa: COUNT(*) es el patrón más común para contar filas, y es fácil olvidar que se comporta distinto a COUNT(columna) cuando la columna tiene NULL. Cómo detectarlo: si tu conteo de "sesiones que agregaron al carrito" da 17 en vez de 9, estás contando todas las filas, no las que tienen add_to_cart_ts lleno. Cómo corregirlo: COUNT(columna) ignora automáticamente los NULL de esa columna específica —es exactamente el comportamiento que este funnel necesita—, mientras que COUNT(*) cuenta filas sin mirar ningún valor. Usa COUNT(*) solo para el total de sesiones, y COUNT(columna) para cada etapa del funnel.

Asumir que session_date es la fecha de la compra, no la fecha de inicio de la sesión. Qué pasa: alguien usa session_date para responder preguntas sobre cuándo ocurrió una compra, sin darse cuenta de que esta columna se calculó como MIN(CAST(event_ts AS DATE)) —la fecha del primer evento de la sesión, típicamente el page_view—. Por qué pasa: en la mayoría de las sesiones de Kiosko, todos los eventos ocurren el mismo día, así que la diferencia no se nota. Cómo detectarlo: si necesitas la fecha exacta de una compra, y no solo el día en que empezó la sesión, session_date no es la columna correcta — para eso existe purchase_ts, con fecha y hora completas. Cómo corregirlo: usa session_date para agrupar sesiones por día de inicio (el uso que le da este módulo); usa purchase_ts::DATE si necesitas específicamente la fecha de la conversión.

Ejercicios

Ejercicio 1 — Cuenta cuántas sesiones tuvo cada tienda, sin mirar el funnel. Usando solo fact_sessions y GROUP BY store_id, confirma que la distribución de sesiones por tienda es 6/6/5, como predijo el mapeo de la lección.

Ver solución 
print(con.sql("SELECT store_id, COUNT(*) AS sessions FROM fact_sessions GROUP BY store_id ORDER BY store_id"))

Salida esperada:

┌──────────┬──────────┐
│ store_id │ sessions │
│ varchar  │  int64   │
├──────────┼──────────┤
│ S01      │        6 │
│ S02      │        6 │
│ S03      │        5 │
└──────────┴──────────┘

6 + 6 + 5 = 17, confirmando que el JOIN contra session_store_map no perdió ni duplicó ninguna sesión.

Ejercicio 2 — Encuentra las sesiones que llegaron al carrito pero no compraron. Escribe una consulta que liste session_id, store_id y add_to_cart_ts para las sesiones donde add_to_cart_ts está lleno pero purchase_ts está vacío — el "carrito abandonado" de Kiosko.

Ver solución 
print(con.sql("""
    SELECT session_id, store_id, add_to_cart_ts
    FROM fact_sessions
    WHERE add_to_cart_ts IS NOT NULL AND purchase_ts IS NULL
    ORDER BY session_id
"""))

Salida esperada:

┌────────────┬──────────┬─────────────────────┐
│ session_id │ store_id │   add_to_cart_ts    │
│  varchar   │ varchar  │      timestamp      │
├────────────┼──────────┼─────────────────────┤
│ SESS-07    │ S01      │ 2026-08-05 08:16:20 │
│ SESS-11    │ S02      │ 2026-08-07 08:22:00 │
│ SESS-17    │ S02      │ 2026-08-09 09:22:00 │
└────────────┴──────────┴─────────────────────┘

Tres sesiones —SESS-07, SESS-11, SESS-17— representan el "carrito abandonado" exacto de esta semana: nueve sesiones llegaron al carrito, seis compraron, y estas tres son la diferencia. Este tipo de consulta —filtrar por una combinación específica de milestones llenos y vacíos— es exactamente el tipo de pregunta que un accumulating snapshot responde de forma directa, sin necesitar ningún JOIN adicional contra events.

Ejercicio 3 — Explica por qué is_converted y COUNT(purchase_ts) deberían dar siempre el mismo resultado. En 2-3 frases, explica la relación entre la columna booleana is_converted y la columna de timestamp purchase_ts, y por qué contar una u otra debería producir el mismo número de sesiones convertidas.

Ver solución

is_converted se calculó, en la misma consulta que construyó fact_sessions, directamente a partir de si existía algún evento purchase para esa sesión (MAX(CASE WHEN event_type = 'purchase' THEN true ELSE false END)) — es, en esencia, una versión booleana de la misma información que ya vive en purchase_ts. Por construcción, ambas columnas están sincronizadas: si purchase_ts está lleno, is_converted es true; si está vacío, es false. SELECT COUNT(*) FROM fact_sessions WHERE is_converted y SELECT COUNT(purchase_ts) FROM fact_sessions deberían dar, siempre, el mismo número (6) — si no coincidieran, sería evidencia de un error en la consulta que construyó la tabla, no una diferencia de negocio real.

Resumen y siguiente paso

Esta lección construyó fact_sessions completo: 17 filas, una por sesión, usando MAX(CASE WHEN event_type = '...' THEN event_ts END) agrupado por session_id sobre los 32 events canónicos de Kiosko —sin inventar ni un evento—, más un mapeo fijo y declarado de sesión a tienda (SESS-01 -> S01, rotando S01/S02/S03). El análisis del funnel mostró que Kiosko convierte el 35.3% de sus sesiones (52.9% llega al carrito, y de esas, 66.7% termina comprando), con la caída más grande ocurriendo antes del carrito, no después.

Antes de avanzar deberías poder: explicar por qué events necesitó un mapeo adicional de tienda que events mismo no provee; escribir de memoria el patrón MAX(CASE WHEN ...) para construir columnas de milestone desde una tabla de eventos; y calcular una tasa de conversión por etapa usando COUNT(columna) en vez de COUNT(*).

La lección 4 reconstruye esta misma tabla, con el mismo resultado final, pero de una forma completamente distinta: procesando los 32 eventos uno a la vez, en orden cronológico, con INSERT solo en el primer evento de cada sesión y UPDATE en cada uno de los siguientes — el mecanismo real de producción, no el recálculo agregado de esta lección.

Recursos

← AnteriorEl accumulating snapshot fact tableSiguiente →Actualizando milestones in-place