lección 6

Día 4 -- Dashboard para inversores y recomendaciones

Construyes el dashboard que Marta mostrará mañana a Meridian Capital. Implementas un sistema de recomendaciones con collaborative filtering simple.

⏱ 45 min

### Jueves, 8:30 AM -- Un día para el dashboard

El dashboard y el motor de recomendaciones se construyen sobre las métricas ya calculadas a partir de `datos/reproducciones_eventos.parquet` (`user_id, content_id, timestamp, properties`) y `datos/usuarios.csv`. Para el bloque de crecimiento del dashboard puedes apoyarte además en `datos/gasto_marketing_semanal.csv` (`week_start, channel, spend_eur, impressions, clicks, signups_attributed`) si quieres relacionar la adquisición de usuarios con la inversión de marketing. Guarda todo en `datos/`; recuerda que el esquema PostgreSQL del contexto es ilustrativo y que debes usar las columnas reales de los ficheros.

Mañana es el día. A las 11:00 Marta se sienta frente a Meridian Capital con tu dashboard proyectado en la pantalla de la sala de reuniones. Hoy tu trabajó es transformar los datos calculados ayer en una narrativa visual que un inversor pueda entender en 5 minutos sin que nadie le explique nada. Los buenos dashboards no necesitan manual de usuario.

En AdPulse aprendiste algo fundamental sobre presentar datos a personas no técnicas: "Menos es más. Un dashboard con 3 métricas bien contadas vale más que uno con 20 gráficos que nadie mira." Carmen, la directora de datos, siempre decía: "Si tienes que explicar el gráfico, el gráfico está mal diseñado."

### Diseño del dashboard -- la narrativa visual

El dashboard para inversores tiene 3 secciones que cuentan una historia secuencial. Un inversor lo lee de arriba a abajo y al terminar piensa "está startup tiene tracción real":

01.HEADER: KPIs hero -- los 4 números más importantes en grande (MAU, crecimiento MoM, stickiness, D7 retention)
02.CRECIMIENTO: Gráfico de línea con MAU de los últimos 12 meses + línea de tendencia. La curva sube y se acelera.
03.ENGAGEMENT: Distribución de segmentos (power/regular/casual/at-risk) + completion rate por género. Demuestra que el contenido engancha.
04.RETENCIÓN: Tabla de cohortes coloreada (heatmap). Las últimas cohortes retienen mejor que las primeras -- señal de mejora de producto.

1-- Queries para el dashboard de Metabase
2-- Cada query alimenta una "question" en el dashboard
3
4-- 1. KPIs HERO (tarjetas grandes arriba)
5SELECT
6    (SELECT COUNT(DISTINCT user_id) FROM v_playbacks
7     WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
8       AND watch_seconds > 30) AS mau_actual,
9
10    (SELECT ROUND(
11        (curr.mau - prev.mau) * 100.0 / prev.mau, 1
12    ) FROM
13        (SELECT COUNT(DISTINCT user_id) AS mau FROM v_playbacks
14         WHERE event_date >= CURRENT_DATE - INTERVAL '30 days' AND watch_seconds > 30) curr,
15        (SELECT COUNT(DISTINCT user_id) AS mau FROM v_playbacks
16         WHERE event_date BETWEEN CURRENT_DATE - INTERVAL '60 days'
17                              AND CURRENT_DATE - INTERVAL '30 days'
18           AND watch_seconds > 30) prev
19    ) AS growth_mom_pct,
20
21    (SELECT ROUND(
22        COUNT(DISTINCT CASE WHEN event_date = CURRENT_DATE - 1 THEN user_id END) * 100.0 /
23        NULLIF(COUNT(DISTINCT user_id), 0), 1)
24     FROM v_playbacks
25     WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
26       AND watch_seconds > 30) AS stickiness_pct,
27
28    (SELECT ROUND(retained * 100.0 / total, 1) FROM (
29        SELECT
30            COUNT(DISTINCT d0.user_id) AS total,
31            COUNT(DISTINCT d7.user_id) AS retained
32        FROM (SELECT DISTINCT user_id FROM v_playbacks
33              WHERE event_date = CURRENT_DATE - INTERVAL '14 days'
34                AND watch_seconds > 30) d0
35        LEFT JOIN (SELECT DISTINCT user_id FROM v_playbacks
36              WHERE event_date BETWEEN CURRENT_DATE - INTERVAL '8 days'
37                                   AND CURRENT_DATE - INTERVAL '6 days'
38                AND watch_seconds > 30) d7
39        ON d0.user_id = d7.user_id
40    )) AS retention_d7_pct;

Query para los KPIs hero del dashboard. Cuatro números que cuentan toda la historia.

### Sistema de recomendaciones -- collaborative filtering

Mientras Marta prepara la presentación, decides ir un pasó más allá. Los inversores siempre preguntan: "¿Cómo vais a escalar el engagement?" La respuesta es personalización -- recomendaciones. No necesitas un equipo de ML para una versión 1.0. Collaborative filtering simple ("usuarios que vieron X también vieron Y") se puede implementar con SQL y funciona sorprendentemente bien para un catálogo de 1200 títulos.

La intuición detrás de collaborative filtering es elegante: si dos usuarios tienen gustos similares (vieron y completaron contenido parecido), lo que uno vio y el otro no es una buena recomendación. No necesitas entender POR QUÉ les gusta algo -- solo necesitas encontrar patrones de co-visualización.

1# Sistema de recomendaciones v1: Collaborative Filtering básico
2# "Usuarios que completaron X también completaron Y"
3
4import pandas as pd
5import numpy as np
6from collections import defaultdict
7
8
9def build_user_content_matrix(df_playbacks: pd.DataFrame) -> pd.DataFrame:
10    """Construye la matriz usuario-contenido con scores de interacción."""
11    # Solo considerar contenido que el usuario VIO significativamente
12    significant = df_playbacks[df_playbacks['watch_seconds'] > 120].copy()
13
14    # Score de interacción: completion_ratio (0-1)
15    # Si completó = 1.0, si vio la mitad = 0.5, etc.
16    interaction = significant.groupby(['user_id', 'content_id']).agg(
17        max_completion=('completion_ratio', 'max'),
18        times_watched=('playback_id', 'count'),
19    ).reset_index()
20
21    # Score final: max_completion * (1 + 0.1 * rewatches)
22    interaction['score'] = interaction['max_completion'] * (
23        1 + 0.1 * (interaction['times_watched'] - 1).clip(upper=5)
24    )
25    interaction['score'] = interaction['score'].clip(upper=1.5)
26
27    return interaction[['user_id', 'content_id', 'score']]
28
29
30def find_similar_users(
31    user_id: int,
32    interaction_matrix: pd.DataFrame,
33    top_k: int = 20
34) -> list[int]:
35    """Encuentra los K usuarios más similares usando coseno."""
36    # Contenido visto por el usuario target
37    target_items = set(
38        interaction_matrix[interaction_matrix['user_id'] == user_id]['content_id']
39    )
40
41    if not target_items:
42        return []
43
44    # Calcular similaridad con todos los demás usuarios
45    # (overlap de contenido visto / tamaño del universo compartido)
46    similarities = []
47    other_users = interaction_matrix[
48        interaction_matrix['user_id'] != user_id
49    ]['user_id'].unique()
50
51    for other_id in other_users:
52        other_items = set(
53            interaction_matrix[interaction_matrix['user_id'] == other_id]['content_id']
54        )
55        overlap = len(target_items & other_items)
56        if overlap >= 3:  # Mínimo 3 títulos en común
57            # Jaccard similarity
58            union = len(target_items | other_items)
59            similarity = overlap / union
60            similarities.append((other_id, similarity))
61
62    # Top K más similares
63    similarities.sort(key=lambda x: x[1], reverse=True)
64    return [uid for uid, _ in similarities[:top_k]]
65
66
67def recommend_for_user(
68    user_id: int,
69    interaction_matrix: pd.DataFrame,
70    content_df: pd.DataFrame,
71    n_recommendations: int = 10
72) -> pd.DataFrame:
73    """Genera N recomendaciones para un usuario."""
74    # Contenido ya visto
75    seen = set(
76        interaction_matrix[interaction_matrix['user_id'] == user_id]['content_id']
77    )
78
79    # Encontrar usuarios similares
80    similar_users = find_similar_users(user_id, interaction_matrix)
81
82    if not similar_users:
83        # Fallback: contenido más popular no visto
84        popular = interaction_matrix.groupby('content_id').agg(
85            viewers=('user_id', 'nunique'),
86            avg_score=('score', 'mean')
87        ).reset_index()
88        popular = popular[~popular['content_id'].isin(seen)]
89        popular = popular.sort_values('avg_score', ascending=False)
90        return popular.head(n_recommendations).merge(content_df, on='content_id')
91
92    # Contenido visto por usuarios similares pero NO por el target
93    similar_content = interaction_matrix[
94        interaction_matrix['user_id'].isin(similar_users)
95        & ~interaction_matrix['content_id'].isin(seen)
96    ]
97
98    # Rankear por score promedio ponderado
99    recommendations = similar_content.groupby('content_id').agg(
100        rec_score=('score', 'mean'),
101        similar_viewers=('user_id', 'nunique'),
102    ).reset_index()
103
104    # Ponderar por número de usuarios similares que lo vieron
105    recommendations['final_score'] = (
106        recommendations['rec_score'] * np.log1p(recommendations['similar_viewers'])
107    )
108
109    recommendations = recommendations.sort_values('final_score', ascending=False)
110    top_recs = recommendations.head(n_recommendations)
111
112    # Enriquecer con metadata del contenido
113    return top_recs.merge(content_df[['content_id', 'title', 'genre', 'content_type']], on='content_id')

Collaborative filtering simple: sin ML, sin infraestructura pesada, funciona para 15K usuarios.

Para 15K usuarios y 1200 títulos, collaborative filtering basado en Jaccard similarity se ejecuta en menos de 1 segundo. No necesitas embeddings, no necesitas neural networks, no necesitas Spark. Cuando tengas 500K usuarios, migras a ALS (Alternating Least Squares) con implicitlib. Pero para el MVP, esto funciona perfectamente y puedes mostrarlo a los inversores como "tenemos personalización".

### Resumen del Día 4

Diseñaste dashboard con narrativa secuencial: KPIs hero -> Crecimiento -> Engagement -> Retención
Creaste queries optimizadas para Metabase con las métricas calculadas ayer
Implementaste sistema de recomendaciones v1 con collaborative filtering (Jaccard)
El dashboard está listo y funcionando en Metabase para la presentación de mañana
Marta revisó el dashboard a las 19:00 y dijo: "Es exactamente lo que necesitaba."

## ejercicios

[01]

Queries para el dashboard de inversores

Implementa las 4 queries principales que alimentan el dashboard de Metabase: KPIs hero, serie temporal de MAU, distribución de segmentos y tabla de cohortes de retención.

Cargando editor...

[02]

Sistema de recomendaciones: collaborative filtering

Implementa el motor de recomendaciones completo: construye la matriz de interacciones, calcula similitud entre usuarios (Jaccard) y genera recomendaciones personalizadas para un usuario dado.

Cargando editor...

[03]

Modelo de predicción de churn simplificado

Los inversores preguntaron sobre churn. Implementa un modelo simple basado en reglas que identifique usuarios en riesgo de abandono usando las señales de engagement disponibles.

Cargando editor...

Regístrate para guardar tu progreso.

## comentarios

Reporta erratas, ayuda a otros o comparte tu opinión. Sé constructivo.

Inicia sesión para comentar y responder.

cargando comentarios...