Día 4 -- Dashboard para inversores y recomendaciones

1-- TODO EL PANEL CUELGA DE ESTA VISTA, y la vista se ensena en pantalla.
2--
3-- Es lo primero que se escribe. Ningun numero del panel puede depender del reloj
4-- del ordenador: el dia que el pipeline deje de correr, un panel anclado a
5-- CURRENT_DATE no enseña un error -- enseña un CERO, y un cero en una sala de
6-- reuniones se lee como "el producto se ha parado".
7CREATE OR REPLACE VIEW v_frescura AS
8SELECT MAX(event_date)                                  AS datos_hasta,
9       COUNT(*)                                         AS sesiones,
10       COUNT(DISTINCT user_id)                          AS usuarios,
11       DATE_DIFF('day', MIN(event_date), MAX(event_date)) + 1 AS dias,
12       CURRENT_TIMESTAMP                                AS calculado_el
13FROM silver_playbacks
14WHERE is_meaningful;
15
16-- La linea que va DEBAJO de los cuatro numeros grandes, siempre visible:
17SELECT 'Datos hasta ' || CAST(datos_hasta AS VARCHAR)
18       || ' · ' || CAST(sesiones AS VARCHAR) || ' sesiones'
19       || ' · ' || CAST(usuarios AS VARCHAR) || ' usuarios'
20       || ' · calculado el ' || CAST(calculado_el AS VARCHAR) AS frescura
21FROM v_frescura;
22-- Datos hasta 2024-10-13 · 16371 sesiones · 1500 usuarios · calculado el ...
23
24-- CUATRO NUMEROS, CUATRO CONSULTAS.
25--
26-- No van en un solo SELECT. Si van juntos, un denominador a cero se lleva los
27-- otros tres por delante: en PostgreSQL con un error y el panel entero en
28-- blanco, y en DuckDB sin error ninguno -- cero entre cero da 'nan' y cualquier
29-- cosa entre cero da 'inf', que en una tarjeta se lee como un fallo de la
30-- pantalla. Cuatro consultas separadas fallan de una en una.
31
32-- 1. Usuarios activos en la ventana con datos.
33SELECT COUNT(DISTINCT user_id) AS usuarios_activos
34FROM silver_playbacks
35WHERE is_meaningful
36  AND event_date > (SELECT datos_hasta FROM v_frescura) - INTERVAL '30 days';
37-- 1500
38
39-- 2. Usuarios del ultimo dia.
40SELECT COUNT(DISTINCT user_id) AS dau
41FROM silver_playbacks
42WHERE is_meaningful
43  AND event_date = (SELECT datos_hasta FROM v_frescura);
44-- 405
45
46-- 3. Retencion D1, con su base al lado. Un porcentaje sin denominador en un
47--    panel de inversores es una invitacion a que te pregunten "sobre cuantos".
48WITH dia_cero AS (
49    SELECT user_id, MIN(event_date) AS cohorte
50    FROM silver_playbacks WHERE is_meaningful GROUP BY user_id
51),
52actividad AS (
53    SELECT DISTINCT c.cohorte, p.user_id,
54           DATE_DIFF('day', c.cohorte, p.event_date) AS dn
55    FROM silver_playbacks p JOIN dia_cero c USING (user_id)
56    WHERE p.is_meaningful
57)
58SELECT ROUND(COUNT(DISTINCT CASE WHEN dn = 1 THEN user_id END) * 100.0
59             / NULLIF(COUNT(DISTINCT user_id), 0), 1) AS retencion_d1_pct,
60       COUNT(DISTINCT user_id)                        AS sobre_usuarios
61FROM actividad
62-- Solo cohortes que ya han cumplido su plazo (leccion de ayer).
63WHERE cohorte <= (SELECT datos_hasta FROM v_frescura) - 1;
64-- 73,0 % sobre 1432 usuarios
65
66-- 4. Retencion D7 (+-1 dia), igual.
67WITH dia_cero AS (
68    SELECT user_id, MIN(event_date) AS cohorte
69    FROM silver_playbacks WHERE is_meaningful GROUP BY user_id
70),
71actividad AS (
72    SELECT DISTINCT c.cohorte, p.user_id,
73           DATE_DIFF('day', c.cohorte, p.event_date) AS dn
74    FROM silver_playbacks p JOIN dia_cero c USING (user_id)
75    WHERE p.is_meaningful
76)
77SELECT ROUND(COUNT(DISTINCT CASE WHEN dn BETWEEN 6 AND 8 THEN user_id END) * 100.0
78             / NULLIF(COUNT(DISTINCT user_id), 0), 1) AS retencion_d7_pct,
79       COUNT(DISTINCT user_id)                        AS sobre_usuarios
80FROM actividad
81WHERE cohorte <= (SELECT datos_hasta FROM v_frescura) - 8;
82-- 29,4 % sobre 913 usuarios
83
84-- LO QUE NO VA EN EL PANEL, y por que.
85--
86-- El stickiness (DAU/MAU) y el crecimiento mes a mes NO entran. No es que salgan
87-- feos: es que con 21 dias de historico la ventana de 30 dias del MAU no se
88-- llena nunca, asi que el MAU sale bajo por construccion y el stickiness sale
89-- inflado. Y no hay dos meses naturales completos que comparar.
90--
91-- En su sitio va una tarjeta que dice "Disponible a partir del 12 de noviembre",
92-- con la fecha calculada. Un hueco explicado da confianza; un numero inventado
93-- la quita entera en cuanto alguien pregunta de donde sale.

La vista de frescura primero, y después cuatro números en cuatro consultas separadas.

1-- La consulta de tres lineas que decide el diseno del recomendador.
2SELECT ROUND(AVG(titulos), 2)                                    AS titulos_por_usuario,
3       MIN(titulos)                                              AS minimo,
4       MAX(titulos)                                              AS maximo,
5       COUNT(*) FILTER (WHERE titulos >= 3)                      AS con_3_o_mas,
6       ROUND(100.0 * COUNT(*) FILTER (WHERE titulos >= 3) / COUNT(*), 1) AS pct
7FROM (SELECT user_id, COUNT(DISTINCT content_id) AS titulos
8      FROM silver_playbacks
9      WHERE is_meaningful AND user_id IS NOT NULL
10      GROUP BY user_id);
11-- titulos_por_usuario 9,34 | minimo 2 | maximo 23 | con_3_o_mas 1493 (99,5 %)
12
13-- Bien: casi todo el mundo tiene tres titulos o mas, asi que el minimo de tres
14-- no deja fuera a nadie por falta de historial. Pero eso es la COTA, no la
15-- cobertura. Para que el parecido entre personas sirva, dos personas tienen que
16-- haber visto LO MISMO, y eso es otra cosa:
17WITH vistos AS (
18    SELECT DISTINCT user_id, content_id FROM silver_playbacks
19    WHERE is_meaningful AND user_id IS NOT NULL
20),
21pares AS (
22    SELECT a.user_id AS u1, b.user_id AS u2, COUNT(*) AS comunes
23    FROM vistos a JOIN vistos b
24         ON a.content_id = b.content_id AND a.user_id <> b.user_id
25    GROUP BY 1, 2
26)
27SELECT COUNT(DISTINCT u1)                                                AS con_algun_vecino,
28       ROUND(100.0 * COUNT(DISTINCT u1)
29             / (SELECT COUNT(DISTINCT user_id) FROM vistos), 1)           AS pct,
30       MAX(comunes)                                                       AS max_titulos_comunes
31FROM pares WHERE comunes >= 3;
32-- con_algun_vecino 1077 (71,8 %) | max_titulos_comunes 6

Dos consultas antes de escribir el algoritmo. La segunda es la que importa.

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        # PLAN B: lo mas popular que no haya visto.
84        #
85        # Y ordenado por NUMERO DE ESPECTADORES, no por nota media. La version
86        # ingenua calcula los espectadores y despues ordena por avg_score, que es
87        # la media de la fraccion vista: los titulos que ha visto UNA sola
88        # persona, y entera, salen con un 1,0 perfecto y encabezan la lista. O
89        # sea que el plan B de "lo mas popular" recomendaba lo mas ignorado.
90        popular = interaction_matrix.groupby('content_id').agg(
91            espectadores=('user_id', 'nunique'),
92            nota_media=('score', 'mean'),
93        ).reset_index()
94        popular = popular[~popular['content_id'].isin(seen)]
95        # Un minimo de espectadores antes de ordenar por nota, para que la nota
96        # signifique algo.
97        popular = popular[popular['espectadores'] >= 10]
98        popular = popular.sort_values(
99            ['espectadores', 'nota_media'], ascending=False
100        )
101        resultado = popular.head(n_recommendations).merge(content_df, on='content_id')
102        # Y va ETIQUETADO. Una recomendacion popular no es una recomendacion
103        # personalizada, y la pantalla tiene que poder decirlo: "lo mas visto en
104        # StreamVibe" en vez de "para ti".
105        resultado['origen'] = 'popular'
106        return resultado
107
108    # Contenido visto por usuarios similares pero NO por el target
109    similar_content = interaction_matrix[
110        interaction_matrix['user_id'].isin(similar_users)
111        & ~interaction_matrix['content_id'].isin(seen)
112    ]
113
114    # Rankear por score promedio ponderado
115    recommendations = similar_content.groupby('content_id').agg(
116        rec_score=('score', 'mean'),
117        similar_viewers=('user_id', 'nunique'),
118    ).reset_index()
119
120    # Ponderar por número de usuarios similares que lo vieron
121    recommendations['final_score'] = (
122        recommendations['rec_score'] * np.log1p(recommendations['similar_viewers'])
123    )
124
125    recommendations = recommendations.sort_values('final_score', ascending=False)
126    top_recs = recommendations.head(n_recommendations)
127
128    resultado = top_recs.merge(
129        content_df[['content_id', 'title', 'genre_primary', 'content_type']],
130        on='content_id',
131    )
132    resultado['origen'] = 'personalizada'
133    return resultado

Filtrado colaborativo entre personas. Cubre el 71,8% de los usuarios, y el resto cae al plan B.

1-- "Quien vio A tambien vio B", calculado una vez para todo el catalogo.
2CREATE OR REPLACE TABLE gold_titulos_parecidos AS
3WITH vistos AS (
4    SELECT DISTINCT user_id, content_id
5    FROM silver_playbacks
6    WHERE is_meaningful AND user_id IS NOT NULL
7),
8espectadores AS (
9    SELECT content_id, COUNT(*) AS n FROM vistos GROUP BY 1
10),
11total AS (SELECT COUNT(DISTINCT user_id) AS n FROM vistos),
12pares AS (
13    SELECT a.content_id AS origen, b.content_id AS parecido, COUNT(*) AS covistos
14    FROM vistos a JOIN vistos b
15         ON a.user_id = b.user_id AND a.content_id <> b.content_id
16    GROUP BY 1, 2
17)
18SELECT p.origen, p.parecido, p.covistos,
19       -- LIFT: las coincidencias observadas divididas por las que saldrian POR
20       -- AZAR. Sin esto, el titulo mas visto de la plataforma aparece emparejado
21       -- con todo, porque coincide con todo el mundo por pura frecuencia. El lift
22       -- pregunta otra cosa: ¿coinciden MAS de lo que les tocaria?
23       ROUND(p.covistos * (SELECT n FROM total)
24             / (eo.n * 1.0 * ep.n), 2) AS lift
25FROM pares p
26JOIN espectadores eo ON eo.content_id = p.origen
27JOIN espectadores ep ON ep.content_id = p.parecido
28-- Un minimo de coincidencias: con una sola, el lift es ruido.
29WHERE p.covistos >= 2
30ORDER BY p.origen, lift DESC;
31
32-- Y recomendar es ahora una consulta, no un bucle:
33WITH mios AS (
34    SELECT DISTINCT content_id FROM silver_playbacks
35    WHERE user_id = 'usr_00042' AND is_meaningful
36)
37SELECT t.parecido AS content_id, c.title, c.genre_primary,
38       SUM(t.lift) AS afinidad, COUNT(*) AS veces_sugerido
39FROM gold_titulos_parecidos t
40JOIN mios m           ON m.content_id = t.origen
41JOIN content c        ON c.content_id = t.parecido
42WHERE t.parecido NOT IN (SELECT content_id FROM mios)
43GROUP BY 1, 2, 3
44ORDER BY afinidad DESC
45LIMIT 10;

Parecido entre títulos: 26.494 pares con al menos dos coincidencias, y cobertura del 100%.

1# Riesgo de baja, con los umbrales a la escala de los datos que hay.
2#
3# El umbral de inactividad NO se elige por intuicion. Con 21 dias de historico,
4# "mas de 21 dias sin entrar" no lo cumple nadie y la senal no vale para nada.
5# Se mira la distribucion primero: aqui hay 602 usuarios con mas de 7 dias sin
6# aparecer y 189 con mas de 14, asi que los tramos van ahi.
7
8UMBRALES_INACTIVIDAD = (7, 3)     # dias: mucho / algo
9UMBRALES_DIAS_ACTIVOS = (2, 5)    # dias distintos con actividad
10
11
12def riesgo_de_baja(usuario: dict, ultimo_dia) -> dict:
13    """Puntua el riesgo de 0 a 90. Devuelve tambien que no se ha podido medir."""
14    puntos = 0
15    senales = []
16    sin_medir = []
17
18    # 1. Inactividad. La referencia es el ultimo dia CON DATOS, no hoy.
19    ultima = usuario.get('ultima_actividad')
20    if ultima is None or pd.isna(ultima):
21        # Un dato que falta NO suma puntos. La version ingenua ponia
22        # days_ago = user_metrics.get('last_play_days_ago', 0), o sea que a quien
23        # le faltaba el dato le asignaba CERO dias de inactividad -- el mejor
24        # valor posible. Asi, un hueco protegia al usuario de la alarma.
25        sin_medir.append('inactividad')
26    else:
27        dias = (ultimo_dia - ultima).days
28        if dias > UMBRALES_INACTIVIDAD[0]:
29            puntos += 40
30            senales.append(('inactividad_alta', f'{dias} dias sin entrar'))
31        elif dias > UMBRALES_INACTIVIDAD[1]:
32            puntos += 20
33            senales.append(('inactividad_media', f'{dias} dias sin entrar'))
34
35    # 2. Frecuencia.
36    dias_activos = usuario.get('dias_activos')
37    if dias_activos is None or pd.isna(dias_activos):
38        sin_medir.append('frecuencia')
39    elif dias_activos <= UMBRALES_DIAS_ACTIVOS[0]:
40        puntos += 30
41        senales.append(('frecuencia_baja', f'solo {dias_activos} dias activos'))
42    elif dias_activos <= UMBRALES_DIAS_ACTIVOS[1]:
43        puntos += 15
44        senales.append(('frecuencia_media', f'{dias_activos} dias activos'))
45
46    # 3. Profundidad.
47    pct_visto = usuario.get('pct_visto')
48    if pct_visto is None or pd.isna(pct_visto):
49        sin_medir.append('profundidad')
50    elif pct_visto < 0.60:
51        puntos += 20
52        senales.append(('abandona_pronto', f've el {pct_visto:.0%} de lo que empieza'))
53    elif pct_visto < 0.80:
54        puntos += 10
55        senales.append(('profundidad_media', f've el {pct_visto:.0%}'))
56
57    # Si no se ha podido medir NADA, el usuario no tiene riesgo: tiene un hueco.
58    # Devolver 0 seria decir "esta bien", y no lo sabemos.
59    nivel = None if len(sin_medir) == 3 else (
60        'alto' if puntos >= 50 else 'medio' if puntos >= 25 else 'bajo'
61    )
62
63    return {
64        'puntos': puntos,
65        'nivel': nivel,
66        # El TIPO de senal va separado del texto. Si se guarda la frase entera
67        # ("14 dias sin entrar"), contar las senales mas comunes devuelve mil
68        # senales distintas con una aparicion cada una, porque el numero cambia
69        # en cada usuario. Con el tipo aparte, agrupa.
70        'senales': [t for t, _ in senales],
71        'detalle': [d for _, d in senales],
72        'sin_medir': sin_medir,
73    }

Los umbrales salen de la distribución, y un dato que falta no suma puntos.

1-- VALIDACION RETROSPECTIVA. Se corta el historico en una fecha, se puntua con
2-- lo que se sabia ENTONCES, y se mira lo que paso DESPUES.
3WITH hoy AS (SELECT MAX(event_date) AS f FROM silver_playbacks),
4corte AS (SELECT (SELECT f FROM hoy) - 7 AS c),
5-- 1. Lo que se sabia en el corte. Ni un dato posterior entra aqui: si se cuela,
6--    el modelo "predice" con informacion del futuro y acierta siempre.
7antes AS (
8    SELECT user_id,
9           MAX(event_date)            AS ultima,
10           COUNT(DISTINCT event_date) AS dias_activos,
11           AVG(completion_ratio)      AS pct_visto
12    FROM silver_playbacks
13    WHERE is_meaningful AND user_id IS NOT NULL
14      AND event_date <= (SELECT c FROM corte)
15    GROUP BY user_id
16),
17puntuada AS (
18    SELECT a.*,
19           (CASE WHEN DATE_DIFF('day', a.ultima, (SELECT c FROM corte)) > 7 THEN 40
20                 WHEN DATE_DIFF('day', a.ultima, (SELECT c FROM corte)) > 3 THEN 20
21                 ELSE 0 END
22          + CASE WHEN a.dias_activos <= 2 THEN 30
23                 WHEN a.dias_activos <= 5 THEN 15 ELSE 0 END
24          + CASE WHEN a.pct_visto < 0.60 THEN 20
25                 WHEN a.pct_visto < 0.80 THEN 10 ELSE 0 END) AS riesgo
26    FROM antes a
27),
28-- 2. Lo que paso despues.
29despues AS (
30    SELECT DISTINCT user_id FROM silver_playbacks
31    WHERE is_meaningful AND event_date > (SELECT c FROM corte)
32      -- OJO: sin este IS NOT NULL, el NOT IN de abajo NO FUNCIONA. Un NOT IN
33      -- contra un conjunto que contiene un nulo nunca es verdadero, asi que
34      -- "se_fue" sale falso para TODO EL MUNDO y la validacion dice que no se
35      -- fue nadie. No da error: da un cero.
36      AND user_id IS NOT NULL
37)
38SELECT COUNT(*)                                        AS evaluados,
39       COUNT(*) FILTER (WHERE riesgo >= 50)            AS marcados,
40       COUNT(*) FILTER (WHERE se_fue)                  AS se_fueron,
41       -- De los que marcamos, cuantos se fueron de verdad.
42       ROUND(100.0 * COUNT(*) FILTER (WHERE riesgo >= 50 AND se_fue)
43             / NULLIF(COUNT(*) FILTER (WHERE riesgo >= 50), 0), 1) AS acierto_pct,
44       -- De los que se fueron, a cuantos habiamos marcado.
45       ROUND(100.0 * COUNT(*) FILTER (WHERE riesgo >= 50 AND se_fue)
46             / NULLIF(COUNT(*) FILTER (WHERE se_fue), 0), 1)       AS cobertura_pct,
47       -- Y LO QUE HAY QUE MIRAR ANTES QUE NADA: cuantos se fueron en total.
48       ROUND(100.0 * COUNT(*) FILTER (WHERE se_fue) / COUNT(*), 1) AS tasa_base_pct
49FROM (SELECT p.*, p.user_id NOT IN (SELECT user_id FROM despues) AS se_fue
50      FROM puntuada p);
51
52-- evaluados 988 | marcados 287 | se_fueron 670
53-- acierto 95,8 % | cobertura 41,0 % | tasa base 67,8 %

La validación que convierte una opinión con puntos en un modelo. Mira las tres cifras juntas.