Agregando el funnel de sesiones y la actividad acumulativa

Descripción

El star schema que construiste en la lección anterior responde preguntas sobre una venta: cuánto, de qué categoría, en qué tienda. Pero dos de las cuatro exigencias del brief de la lección 2 —"¿qué tan bien convierten las sesiones?" y "¿qué tan activa estuvo cada tienda?"— no se pueden responder con fact_orders, sin importar cuántas dimensiones le agregues. Esta lección incorpora al warehouse las dos tablas de hechos que el módulo 6 construyó específicamente para esas preguntas: fact_sessions —un accumulating snapshot que rastrea el funnel completo, desde que alguien mira un producto hasta que lo compra— y fact_store_activity —un cumulative table design que acumula la actividad diaria de cada tienda en ventanas móviles de 7 y 30 días—.

Conexión con el módulo. Esta lección agrega, al mismo warehouse que ya tiene fact_orders y el star completo, dos hechos con un grano y un mecanismo de actualización completamente distintos al transaccional. No modifica ni una fila de lo que construyó la lección 4 — las dos tablas nuevas conviven con fact_orders y dim_product_scd, compartiendo dim_store como dimensión conformada, sin necesitar ningún JOIN directo entre ellas.

Una analogía: el mismo negocio, visto desde dos cámaras distintas

Piensa en una tienda física con dos sistemas de vigilancia completamente distintos: una cámara en la caja registradora, que graba cada transacción exacta —quién compró qué, por cuánto, a qué hora—, y un contador de personas en la puerta, que no sabe qué compró cada visitante, pero sabe cuántas personas entraron, cuántas se detuvieron a mirar un estante, y cuántas salieron sin comprar nada. Ninguna de las dos cámaras reemplaza a la otra — la caja registradora nunca podría decirte la tasa de conversión de visitantes a compradores, y el contador de personas nunca podría decirte el revenue exacto de una venta. Un negocio que solo mira una de las dos cámaras tiene una visión incompleta de lo que realmente pasa en la tienda.

fact_orders es la caja registradora: exacta, transaccional, una fila por venta. fact_sessions y fact_store_activity son las otras dos cámaras: una que sigue el recorrido completo de cada sesión (vista → carrito → compra), otra que acumula el pulso diario de cada tienda. Esta lección no reemplaza ninguna cámara — agrega las dos que le faltaban al warehouse.

Ejemplo trabajado: el funnel y la actividad, integrados al mismo warehouse

Parte 1 — fact_sessions: el accumulating snapshot del funnel

Este script sigue sobre la misma conexión con de la lección 4, con fact_orders, el star y dim_product_scd ya construidos — y agrega events, tipada desde la lección 3.

# capstone_funnel_activity.py -- Parte 1: fact_sessions (continua sobre con, con events ya tipada)
STORE_ROTATION = ["S01", "S02", "S03"]


def store_for_session(session_id: str) -> str:
    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)]
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
""")
funnel = con.sql("""
    SELECT COUNT(*), COUNT(view_ts), COUNT(add_to_cart_ts), COUNT(purchase_ts) FROM fact_sessions
""").fetchone()
conversion_pct = con.sql("SELECT ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) FROM fact_sessions").fetchone()[0]

print("Parte 1 -- fact_sessions: el accumulating snapshot del funnel")
print(f"  fact_sessions  {con.sql('SELECT COUNT(*) FROM fact_sessions').fetchone()[0]:3} filas (17 sesiones, mapeadas a S01/S02/S03)")
print(f"  funnel: total={funnel[0]} vieron={funnel[1]} carrito={funnel[2]} compraron={funnel[3]}")
print(f"  conversion total: {conversion_pct}%")
assert funnel == (17, 17, 9, 6) and conversion_pct == 35.3

Qué esperar.

Parte 1 -- fact_sessions: el accumulating snapshot del funnel
  fact_sessions   17 filas (17 sesiones, mapeadas a S01/S02/S03)
  funnel: total=17 vieron=17 carrito=9 compraron=6
  conversion total: 35.3%

Diecisiete sesiones, cada una con una sola fila —el sello distintivo del accumulating snapshot: nadie insertó una fila nueva cuando una sesión pasó de "vio" a "agregó al carrito"; en vez de eso, view_ts y add_to_cart_ts se llenaron dentro de la misma fila, con el MAX(CASE WHEN ...) que ya construiste en el módulo 6. El funnel completo de Kiosko convierte 35.3% de sus sesiones en compras — un número que fact_orders, aunque tenga cada venta perfectamente registrada, nunca podría calcular por sí solo, porque no sabe nada de las sesiones que no terminaron en compra.

Parte 2 — fact_store_activity: el cumulative table design de 7 y 30 días

con.execute("""
    CREATE TABLE fact_store_activity (
        store_id VARCHAR, activity_date DATE, daily_revenue DOUBLE,
        revenue_array_7d DOUBLE[], active_days_7d INTEGER,
        revenue_array_30d DOUBLE[], active_days_30d INTEGER
    )
""")
DAYS = ["2026-08-03", "2026-08-04", "2026-08-05", "2026-08-06", "2026-08-07", "2026-08-08", "2026-08-09"]
for day in DAYS:
    for store_id in STORE_ROTATION:
        daily_revenue = con.sql(f"""
            SELECT COALESCE(ROUND(SUM(revenue), 2), 0.0) FROM fact_orders
            WHERE store_id = '{store_id}' AND CAST(order_ts AS DATE) = DATE '{day}'
        """).fetchone()[0]
        prev = con.sql(f"""
            SELECT revenue_array_7d, revenue_array_30d FROM fact_store_activity
            WHERE store_id = '{store_id}' ORDER BY activity_date DESC LIMIT 1
        """).fetchone()
        if prev is None:
            new_7d, new_30d = [daily_revenue], [daily_revenue]
        else:
            new_7d = ([daily_revenue] + list(prev[0]))[:7]
            new_30d = ([daily_revenue] + list(prev[1]))[:30]
        active_7d = sum(1 for v in new_7d if v > 0)
        active_30d = sum(1 for v in new_30d if v > 0)
        con.execute("INSERT INTO fact_store_activity VALUES (?, ?, ?, ?, ?, ?, ?)",
            (store_id, day, daily_revenue, new_7d, active_7d, new_30d, active_30d))

activity_rows = con.sql("SELECT COUNT(*) FROM fact_store_activity").fetchone()[0]
snapshot = con.sql("""
    SELECT store_id, ROUND(list_sum(revenue_array_7d), 2) AS revenue_7d, active_days_7d, active_days_30d
    FROM fact_store_activity WHERE activity_date = DATE '2026-08-09' ORDER BY store_id
""").fetchall()

print("\nParte 2 -- fact_store_activity: el cumulative table design")
print(f"  fact_store_activity  {activity_rows:3} filas (3 tiendas x 7 dias)")
print("  snapshot del 2026-08-09 (ultimo dia disponible):")
for store_id, revenue_7d, active_7d, active_30d in snapshot:
    print(f"    {store_id}: revenue_7d={revenue_7d:6}  active_days_7d={active_7d}  active_days_30d={active_30d}")

weekly_revenue = con.sql("SELECT store_id, ROUND(SUM(revenue), 2) FROM fact_orders GROUP BY store_id ORDER BY store_id").fetchall()
for (store_id, revenue_7d, _, _), (_, weekly) in zip(snapshot, weekly_revenue):
    assert revenue_7d == weekly, f"revenue_7d de {store_id} no coincide con el revenue semanal conocido"
assert activity_rows == 21
print("  Verificacion OK: revenue_7d == revenue semanal de fact_orders, para las 3 tiendas")

Qué esperar.

Parte 2 -- fact_store_activity: el cumulative table design
  fact_store_activity   21 filas (3 tiendas x 7 dias)
  snapshot del 2026-08-09 (ultimo dia disponible):
    S01: revenue_7d=  38.3  active_days_7d=7  active_days_30d=7
    S02: revenue_7d=  38.8  active_days_7d=7  active_days_30d=7
    S03: revenue_7d= 29.05  active_days_7d=6  active_days_30d=6
  Verificacion OK: revenue_7d == revenue semanal de fact_orders, para las 3 tiendas

Fíjate en el patrón de construcción, línea por línea: cada nueva fila de fact_store_activity se arma a partir de daily_revenue (el número de hoy, calculado sobre fact_orders) y prev (el arreglo de ayer, leído de la fila anterior de la misma tienda) — nunca se releen las siete filas completas de fact_orders para reconstruir el arreglo desde cero. Ese es, con precisión, el patrón cumulative de Zach Wilson/DataExpert: crecer un día a la vez, sobre el resumen del día anterior, no recalcular todo el historial en cada corrida. Y la verificación cruzada —revenue_7d del último día contra el revenue semanal de fact_orders— confirma algo importante: el arreglo acumulado, aunque se construyó de forma completamente distinta a una simple SUM(), llega exactamente al mismo número que el hecho transaccional original.

Diagrama: dos hechos nuevos, la misma dimensión conformada

flowchart TD
    subgraph Transaccional["fact_orders (leccion 3-4)"]
        A["40 lineas de orden\nrevenue = 106.15"]
    end
    subgraph Accumulating["fact_sessions (accumulating snapshot)"]
        B["17 sesiones\nfunnel 17 -> 9 -> 6\nconversion 35.3%"]
    end
    subgraph Cumulative["fact_store_activity (cumulative)"]
        C["21 filas (3 tiendas x 7 dias)\nrevenue_7d == revenue semanal"]
    end
    D["dim_store (3 filas)\nDIMENSION CONFORMADA"]
    A --> D
    B --> D
    C --> D
    D -.comparte store_id.-> A
    D -.comparte store_id.-> B
    D -.comparte store_id.-> C

Las tres tablas de hechos nunca se unen entre sí directamente —tienen grano distinto: línea de orden, sesión, día+tienda—, pero las tres comparten dim_store como dimensión conformada. Eso es lo que le permite a la gerencia de Kiosko preguntar "¿S03 tuvo baja conversión Y baja actividad esta semana?" sin necesitar un JOIN entre fact_sessions y fact_store_activity — solo agregar cada una por separado, a nivel de tienda, y comparar los resultados lado a lado.

Profundización: por qué esta lección no une fact_sessions con fact_store_activity

Vale la pena ser explícitos sobre algo que esta lección no hace, porque la tentación de hacerlo es real cuando dos tablas nuevas aparecen juntas en el mismo módulo: fact_sessions (grano: una sesión) y fact_store_activity (grano: una tienda por día) nunca se unen entre sí en ningún punto de este warehouse. La razón no es una limitación técnica —técnicamente, un JOIN por store_id sería trivial de escribir—; es una razón de grano, la misma disciplina que el módulo 1 enseñó a declarar con tanto cuidado. Una fila de fact_sessions describe una sesión completa (que puede durar minutos); una fila de fact_store_activity describe un día entero de una tienda (que agrupa docenas de sesiones y ventas). Unirlas directamente, por store_id solo, multiplicaría cada sesión por cada día de actividad de su tienda, produciendo un resultado sin ningún significado de negocio real —exactamente el tipo de fan-out sin sentido que el ejercicio 3 de la lección 1 del módulo 7 ya advirtió—.

Si algún día Kiosko necesitara responder "¿las sesiones de S03 el 5 de agosto tuvieron menor conversión que las del resto de la semana?", la forma correcta no sería un JOIN entre las dos tablas — sería agregar fact_sessions por store_id + session_date primero, y después comparar ese resultado, ya en el mismo grano, contra fact_store_activity. Grano distinto siempre implica agregar antes de comparar, nunca unir directamente.

Errores comunes

Intentar unir fact_sessions con fact_store_activity directamente por store_id. Qué pasa: alguien, motivado por tener las dos tablas en la misma conexión, escribe SELECT * FROM fact_sessions JOIN fact_store_activity ON fact_sessions.store_id = fact_store_activity.store_id, esperando un resultado combinado con sentido. Por qué pasa: técnicamente el JOIN no produce ningún error —ambas tablas tienen la columna store_id—, así que el problema no se manifiesta como una excepción, sino como un resultado sin significado. Cómo detectarlo: si tu consulta produce más de 17 filas (el número de sesiones) o más de 21 (el número de días-tienda), tienes un producto cartesiano parcial —cada sesión de una tienda se multiplicó por cada uno de los siete días de actividad de esa misma tienda—. Cómo corregirlo: como explica la sección de profundización de esta lección, agrega cada tabla a un grano compatible antes de comparar sus resultados — nunca las unas directamente por una dimensión compartida cuando sus granos son distintos.

Construir fact_store_activity fuera de orden, salteando algún día de la semana. Qué pasa: alguien, al adaptar el código de esta lección, itera sobre DAYS en un orden distinto al cronológico, o se salta un día por error. Por qué pasa: el patrón cumulative depende de que cada fila nueva lea la fila anterior de la misma tienda con ORDER BY activity_date DESC LIMIT 1 — si el orden de inserción no es cronológico, esa "fila anterior" no es realmente la de ayer. Cómo detectarlo: si active_days_7d del último día no coincide con el número de días reales con ventas, o si revenue_array_7d tiene menos de siete elementos cuando ya pasaron siete días, revisa el orden de tu bucle. Cómo corregirlo: DAYS, en el código de esta lección, está declarado en orden cronológico explícito —no se genera con ningún cálculo de fecha dinámico— precisamente para que el patrón "lee la fila de ayer, agrégale hoy" funcione sin ambigüedad.

Pensar que fact_sessions necesita unirse con fact_orders para calcular conversión. Qué pasa: alguien, buscando "confirmar" la conversión de fact_sessions, intenta cruzarla con fact_orders para verificar que cada purchase_ts corresponde a una fila real de venta. Por qué pasa: parece razonable querer una doble verificación entre dos fuentes de la misma realidad de negocio (una compra). Cómo detectarlo: si buscas una columna común entre fact_sessions y fact_orders —algo como order_id dentro de fact_sessions, o session_id dentro de fact_orders—, no la vas a encontrar en ningún módulo de esta guía. Cómo corregirlo: como ya explicó el ejercicio 3 de la lección 1 del módulo 7, el dominio actual de Kiosko no tiene ninguna llave que conecte una sesión de navegación con la orden específica que originó — is_converted en fact_sessions viene exclusively de los events de tipo purchase, sin ninguna relación directa con fact_orders. Conectar ambas fuentes con una llave real sería una extensión del modelo, no algo que esta guía construyó.

Ejercicios

Ejercicio 1 — Calcula la conversión por tienda, usando fact_sessions. Sin mirar el módulo 6, escribe la consulta que agrupe fact_sessions por store_id y calcule la tasa de conversión de cada tienda.

print(con.sql("""
    SELECT store_id, COUNT(*) AS sessions, 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
"""))

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

Ejercicio 2 — Confirma que active_days_30d nunca puede ser mayor que 7 en esta semana de datos. Sin correr ninguna consulta nueva, explica en 2-3 frases por qué, aunque el arreglo revenue_array_30d esté diseñado para acumular hasta treinta días, active_days_30d no puede pasar de 7 en el snapshot del 2026-08-09.

Ejercicio 3 — Explica, de memoria, por qué esta lección no reconstruye ningún JOIN punto-en-el-tiempo, aunque dim_product_scd ya existe en el warehouse. En 2-3 frases, explica por qué fact_sessions y fact_store_activity no necesitan unirse contra dim_product_scd, a diferencia de lo que hizo la lección 4.

Resumen y siguiente paso

En esta lección agregaste dos hechos completamente distintos al warehouse: fact_sessions —el accumulating snapshot del funnel, diecisiete sesiones, conversión total 35.3%— y fact_store_activity —el cumulative table design, veintiuna filas, con revenue_7d idéntico al revenue semanal conocido desde el módulo 1 en las tres tiendas—. Ninguna de las dos se une directamente con la otra, ni con fact_orders: las tres comparten dim_store como dimensión conformada, respondiendo preguntas de negocio distintas sobre el mismo warehouse.

Antes de avanzar deberías poder: explicar por qué fact_sessions y fact_store_activity nunca se unen directamente entre sí; recitar de memoria el funnel completo (17 → 9 → 6, 35.3%) y los activos del último día (S01: 7/7, S02: 7/7, S03: 6/6); y explicar por qué ninguna de las dos tablas necesita el join punto-en-el-tiempo de la lección 4.

La lección 6 cierra la capa gold del warehouse: construye mart_daily_sales_obt, la tabla ancha que el equipo de BI va a consultar directamente, con el join punto-en-el-tiempo de la lección 4 ya resuelto por dentro — la pieza que el brief de la lección 2 pidió explícitamente.

Recursos

• Kimball Group — "Accumulating Snapshot Fact Table" — la definición formal que sostiene fact_sessions, integrada en este módulo dentro del warehouse completo. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/accumulating-snapshot-fact-table. En inglés.
• DataExpert-io — repositorio cumulative-table-design (Zach Wilson) — el patrón que sostiene fact_store_activity, aplicado aquí sobre el warehouse integrado. github.com/DataExpert-io/cumulative-table-design. En inglés.
• DuckDB — documentación de funciones de listas (list_sum, slicing) y funciones de ventana, la base técnica de fact_store_activity. duckdb.org/docs/current/sql/functions/list. En inglés.
• DuckDB — documentación oficial del cliente Python, la interfaz que ejecuta cada consulta de esta lección. duckdb.org/docs/current/clients/python/overview. En inglés.