Día 3 (tarde) -- Modelo analítico de contenido

1-- Lo primero es el coste por titulo, porque no viene en el catalogo.
2-- Sale de la hoja que mantiene Javier a mano, y hay que limpiarla: importes en
3-- formato europeo, sufijos en los titulos, mayusculas y espacios de sobra.
4CREATE OR REPLACE VIEW coste_titulo AS
5WITH limpio AS (
6    SELECT TRIM(REPLACE(titulo, ' (temp 1-2)', ''))  AS titulo,
7           -- "1.565,32" -> 1565.32: primero fuera el punto de los miles, y
8           -- despues la coma decimal a punto. En ese orden, o se pierde todo.
9           TRY_CAST(REPLACE(REPLACE(coste_mensual_eur, '.', ''), ',', '.')
10                    AS DOUBLE)                        AS coste_eur
11    FROM costes_licencias
12    -- Los packs fuera de aqui: una fila que dice "Pack series BBC, incluye 5
13    -- titulos" no tiene un coste por titulo hasta que alguien decida como
14    -- repartirlo, y eso es una decision, no un calculo. Se tratan aparte.
15    WHERE titulo NOT LIKE 'Pack %'
16),
17por_clave AS (
18    SELECT LOWER(TRIM(titulo)) AS clave, MAX(coste_eur) AS coste_eur
19    FROM limpio GROUP BY 1
20)
21SELECT c.content_id, p.coste_eur
22FROM content c JOIN por_clave p ON LOWER(c.title) = p.clave;
23
24-- Y ahora la puntuacion de rendimiento por titulo.
25WITH hoy AS (
26    -- "Hoy" es el ultimo dia con datos, nunca CURRENT_DATE: el extracto esta
27    -- congelado en octubre de 2024, y anclado al reloj del ordenador esta
28    -- consulta devuelve cero filas y parece que la escribiste mal.
29    SELECT MAX(started_at) AS fecha FROM playbacks
30),
31por_titulo AS (
32    SELECT
33        c.content_id,
34        c.title,
35        c.content_type,
36        c.genre_primary,
37        c.duration_minutes,
38        t.coste_eur,
39        COUNT(p.playback_id)                          AS reproducciones,
40        COUNT(DISTINCT p.user_id)                     AS espectadores,
41        SUM(p.watch_seconds) / 3600.0                 AS horas,
42        -- El cociente de tiempos necesita la duracion EN SEGUNDOS. El catalogo
43        -- la da en minutos: sin el x60 el porcentaje sale 60 veces mas alto.
44        AVG(p.watch_seconds * 1.0
45            / NULLIF(c.duration_minutes * 60.0, 0)) * 100 AS pct_visto,
46        -- Denominador COUNT(p.playback_id), no COUNT(*): con COUNT(*) la fila
47        -- de un titulo que nadie ha visto cuenta 1 y sale un 0% de
48        -- finalizacion, que se lee como "nadie lo termina" cuando lo que pasa
49        -- es que nadie lo ha empezado.
50        SUM(CASE WHEN p.completed THEN 1 ELSE 0 END) * 100.0
51            / NULLIF(COUNT(p.playback_id), 0)         AS pct_terminado
52    FROM content c
53    LEFT JOIN coste_titulo t ON t.content_id = c.content_id
54    -- LAS CONDICIONES SOBRE LA TABLA DE LA DERECHA VAN EN EL ON.
55    -- Si la fecha se pone en el WHERE, el cruce por la izquierda deja de serlo:
56    -- los titulos sin reproducciones tienen NULL ahi, el NULL no cumple la
57    -- condicion y la fila desaparece sin avisar.
58    LEFT JOIN playbacks p
59           ON c.content_id = p.content_id
60          AND p.started_at >= (SELECT fecha FROM hoy) - INTERVAL '90 days'
61          AND p.is_meaningful
62    GROUP BY 1, 2, 3, 4, 5, 6
63),
64puntuada AS (
65    SELECT
66        pt.*,
67        pt.espectadores * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM playbacks
68                                 WHERE is_meaningful)      AS alcance,
69        -- Sin coste no hay retorno que calcular. Se deja a NULO, no a cero:
70        -- un cero en un factor que pesa el 30% manda el titulo al fondo de la
71        -- tabla, y la tabla se usa para decidir que no renovar. Un hueco en la
72        -- hoja de Javier no puede convertirse en una decision de no comprar.
73        CASE WHEN pt.coste_eur > 0
74             THEN pt.horas / pt.coste_eur * 1000 END       AS horas_por_1000eur
75    FROM por_titulo pt
76    WHERE pt.reproducciones >= 10   -- por debajo, el porcentaje es ruido
77)
78SELECT
79    content_id, title, content_type, genre_primary, duration_minutes,
80    espectadores,
81    ROUND(pct_visto, 1)        AS pct_visto,
82    ROUND(pct_terminado, 1)    AS pct_terminado,
83    ROUND(horas, 1)            AS horas,
84    ROUND(horas_por_1000eur, 2) AS horas_por_1000eur,
85    -- Sin los tres factores no hay nota. Inventarle una con los dos que si
86    -- tenemos tampoco vale: le daria una posicion en el ranking que no se ha
87    -- ganado, y como nunca podria salir al final, quedaria protegido de la
88    -- lista de no renovar. Las dos cosas son decisiones disfrazadas de calculo.
89    CASE WHEN horas_por_1000eur IS NULL THEN NULL ELSE ROUND(
90          LEAST(alcance / 0.05, 1.0)            * 30
91        + LEAST(pct_terminado / 80.0, 1.0)      * 40
92        + LEAST(horas_por_1000eur / 6.0, 1.0)   * 30
93    , 1) END AS puntuacion
94FROM puntuada
95ORDER BY puntuacion DESC NULLS LAST
96LIMIT 50;

La puntuación por título: alcance (30%), finalización (40%) y retorno por euro (30%).

1-- Lo que no se vuelve a comprar: licencia pagada y casi nadie viendolo.
2WITH hoy AS (SELECT MAX(started_at) AS fecha FROM playbacks)
3SELECT
4    c.content_id, c.title, c.genre_primary, c.duration_minutes,
5    t.coste_eur,
6    -- Marcado a proposito: un titulo sin coste registrado NO es un titulo
7    -- gratis. Es un titulo del que no sabemos lo que pagamos.
8    t.coste_eur IS NULL                      AS coste_sin_registrar,
9    COUNT(p.playback_id)                     AS reproducciones,
10    COUNT(DISTINCT p.user_id)                AS espectadores
11FROM content c
12LEFT JOIN coste_titulo t ON t.content_id = c.content_id
13LEFT JOIN playbacks p
14       ON c.content_id = p.content_id
15      AND p.started_at >= (SELECT fecha FROM hoy) - INTERVAL '90 days'
16      AND p.is_meaningful
17GROUP BY 1, 2, 3, 4, 5, 6
18HAVING COUNT(p.playback_id) < 10
19-- NULLS LAST a proposito: los titulos sin coste no deben encabezar una lista
20-- ordenada por coste. Van marcados y al final, no arriba.
21ORDER BY t.coste_eur DESC NULLS LAST;
22
23-- Lo que sale con los datos del caso:
24--   929 titulos con menos de 10 reproducciones en la ventana.
25--   De esos, 636 sin coste registrado.
26--   Los 293 que si tienen coste suman 764.666 EUR al mes.

La lista que libera presupuesto. 929 títulos, y 764.666 EUR al mes en los que sí tienen coste.

1-- La version que parece bien y esta mal. Merece la pena escribirla primero,
2-- porque es la que sale sola y el error no da ningun aviso.
3SELECT
4    c.genre_primary,
5    ROUND(SUM(p.watch_seconds) / 3600.0
6          / SUM(t.coste_eur) * 1000, 3) AS horas_por_1000eur
7FROM content c
8JOIN playbacks p     ON c.content_id = p.content_id
9JOIN coste_titulo t  ON t.content_id = c.content_id
10GROUP BY 1
11ORDER BY horas_por_1000eur DESC;
12
13-- documentary 0,371   <-- primera
14-- animation   0,370
15-- drama       0,351
16-- comedy      0,341
17-- thriller    0,320
18-- horror      0,294
19-- romance     0,281
20-- sci-fi      0,271   <-- ultima
21--
22-- Ocho generos entre 0,271 y 0,371. Sospechosamente parecidos.
23
24-- EL PROBLEMA: despues del cruce hay una fila por REPRODUCCION, y el coste del
25-- titulo va repetido en todas. Asi que SUM(t.coste_eur) no es lo que costo el
26-- catalogo del genero: es el precio de cada titulo multiplicado por sus veces
27-- vistas, y sumado. Un titulo visto cinco mil veces aporta cinco mil veces su
28-- precio.
29--
30-- El efecto no es que el resultado quede raro: es que la metrica se vuelve CIEGA
31-- A LA POPULARIDAD. Numerador y denominador crecen los dos con las
32-- reproducciones, asi que se cancelan y lo que queda es, en la practica, las
33-- horas por reproduccion divididas por el coste medio por reproduccion.
34
35-- LA VERSION BUENA: una fila por titulo primero, el genero despues.
36WITH hoy AS (SELECT MAX(started_at) AS fecha FROM playbacks),
37por_titulo AS (
38    SELECT
39        c.genre_primary          AS genero,
40        c.content_id,
41        -- MAX y no SUM: el coste del titulo es uno, aunque la fila se repita.
42        MAX(t.coste_eur)         AS coste_eur,
43        COALESCE(SUM(p.watch_seconds), 0) / 3600.0 AS horas,
44        COUNT(p.playback_id)     AS reproducciones,
45        COUNT(DISTINCT p.user_id) AS espectadores
46    FROM content c
47    JOIN coste_titulo t ON t.content_id = c.content_id
48    -- Cruce por la IZQUIERDA y con la fecha en el ON: el coste del catalogo que
49    -- nadie ha visto tiene que seguir en el denominador. Si se cae, un genero con
50    -- mucho catalogo muerto sale mejor cuanto mas muerto este.
51    LEFT JOIN playbacks p
52           ON c.content_id = p.content_id
53          AND p.started_at >= (SELECT fecha FROM hoy) - INTERVAL '90 days'
54          AND p.is_meaningful
55    GROUP BY 1, 2
56)
57SELECT
58    genero,
59    COUNT(*)                                                AS titulos,
60    SUM(reproducciones)                                     AS reproducciones,
61    ROUND(SUM(horas), 1)                                    AS horas,
62    ROUND(SUM(horas) / NULLIF(SUM(coste_eur), 0) * 1000, 2)  AS horas_por_1000eur
63FROM por_titulo
64GROUP BY genero
65HAVING SUM(reproducciones) >= 100
66ORDER BY horas_por_1000eur DESC;
67
68-- thriller     5,64   <-- primera
69-- horror       5,33
70-- drama        5,14
71-- comedy       5,07
72-- romance      4,81
73-- documentary  4,26
74-- sci-fi       3,64
75-- animation    1,78   <-- ultima

La misma pregunta con las dos fórmulas. La mala no sólo aplana el ranking: lo invierte.

1-- Dos columnas con la MISMA forma, que es lo que hace la comparacion honesta:
2-- el mismo conjunto de usuarios y la misma clase de ventana de 7 dias.
3--   la primera cuenta las ventanas que arrancan tras ver ESTE genero
4--   la segunda, las que arrancan tras CUALQUIER reproduccion de esos mismos
5--   usuarios -- o sea, la linea de base de lo activos que ya son
6WITH actividad AS (
7    SELECT DISTINCT user_id, event_date FROM playbacks WHERE is_meaningful
8),
9vistas AS (
10    SELECT p.user_id, c.genre_primary AS genero, p.event_date
11    FROM playbacks p JOIN content c USING (content_id)
12    WHERE p.is_meaningful
13),
14tras_genero AS (
15    SELECT v.genero,
16           COUNT(DISTINCT v.user_id) AS usuarios,
17           COUNT(DISTINCT CASE WHEN EXISTS (
18               SELECT 1 FROM actividad a
19               WHERE a.user_id = v.user_id
20                 AND a.event_date BETWEEN v.event_date + 1 AND v.event_date + 7
21           ) THEN v.user_id END) AS vuelven
22    FROM vistas v GROUP BY 1
23),
24linea_base AS (
25    -- Los MISMOS usuarios, pero la ventana arranca tras cualquier cosa que vieran.
26    SELECT v.genero,
27           COUNT(DISTINCT p.user_id) AS usuarios,
28           COUNT(DISTINCT CASE WHEN EXISTS (
29               SELECT 1 FROM actividad a
30               WHERE a.user_id = p.user_id
31                 AND a.event_date BETWEEN p.event_date + 1 AND p.event_date + 7
32           ) THEN p.user_id END) AS vuelven
33    FROM (SELECT DISTINCT genero, user_id FROM vistas) v
34    JOIN playbacks p ON p.user_id = v.user_id AND p.is_meaningful
35    GROUP BY 1
36)
37SELECT g.genero, g.usuarios,
38       ROUND(g.vuelven * 100.0 / g.usuarios, 1) AS tras_este_genero,
39       ROUND(b.vuelven * 100.0 / b.usuarios, 1) AS tras_cualquier_cosa,
40       ROUND(g.vuelven * 100.0 / g.usuarios
41             - b.vuelven * 100.0 / b.usuarios, 1) AS diferencia
42FROM tras_genero g JOIN linea_base b USING (genero)
43ORDER BY tras_este_genero DESC;
44
45-- genero        usuarios  tras_este_genero  tras_cualquier_cosa  diferencia
46-- drama             1397              74,3                 80,5        -6,2
47-- documentary       1262              70,1                 80,3       -10,1
48-- thriller          1146              68,1                 79,8       -11,8
49-- comedy            1095              67,9                 79,5       -11,6
50-- sci-fi             922              66,4                 80,7       -14,3
51-- romance            538              61,0                 82,0       -21,0
52-- horror             562              61,0                 79,9       -18,9
53-- animation          341              60,7                 82,1       -21,4

La misma métrica y su línea de base. La columna de la diferencia es la única que dice algo.

1-- Rendimiento por franja de duracion. Mismo patron que el genero: primero el
2-- titulo, despues la franja.
3--
4-- Y OJO CON LOS CORTES. El catalogo da la duracion en MINUTOS. Si se copian los
5-- cortes en segundos y solo se cambia el nombre de la columna, TODOS los titulos
6-- caen en el primer tramo -- una pelicula de 140 minutos es menor que 1.200 -- la
7-- tabla sale con una fila y el analisis desaparece sin dar ningun error.
8WITH hoy AS (SELECT MAX(started_at) AS fecha FROM playbacks),
9por_titulo AS (
10    SELECT
11        CASE
12            WHEN c.duration_minutes <  20 THEN '< 20 min (corto)'
13            WHEN c.duration_minutes <  45 THEN '20-45 min (episodio)'
14            WHEN c.duration_minutes <  90 THEN '45-90 min (medio)'
15            WHEN c.duration_minutes < 120 THEN '90-120 min (pelicula)'
16            ELSE '> 120 min (largo)'
17        END                       AS franja,
18        c.content_id,
19        MAX(t.coste_eur)          AS coste_eur,
20        COALESCE(SUM(p.watch_seconds), 0) / 3600.0 AS horas,
21        COUNT(p.playback_id)      AS reproducciones,
22        COUNT(DISTINCT p.user_id) AS espectadores,
23        -- Porcentaje visto medio de ESTE titulo. El x60 convierte la duracion a
24        -- segundos; sin el, el ratio sale 60 veces mas alto.
25        AVG(p.watch_seconds * 1.0
26            / NULLIF(c.duration_minutes * 60.0, 0)) * 100 AS pct_visto
27    FROM content c
28    JOIN coste_titulo t ON t.content_id = c.content_id
29    LEFT JOIN playbacks p
30           ON c.content_id = p.content_id
31          AND p.started_at >= (SELECT fecha FROM hoy) - INTERVAL '90 days'
32          AND p.is_meaningful
33    GROUP BY 1, 2
34)
35SELECT
36    franja,
37    COUNT(*)            AS titulos,
38    SUM(reproducciones) AS reproducciones,
39    -- Empuja hacia LO CORTO: la duracion esta en el denominador.
40    ROUND(AVG(pct_visto), 1)                                 AS pct_visto,
41    -- Empuja hacia LO LARGO: las horas vistas crecen con el metraje.
42    ROUND(SUM(horas) / NULLIF(SUM(coste_eur), 0) * 1000, 2)   AS horas_por_1000eur,
43    -- NO depende del metraje. Es el arbitro cuando las dos de arriba se pelean.
44    ROUND(SUM(espectadores) / NULLIF(SUM(coste_eur), 0) * 1000, 2)
45                                                             AS espectadores_por_1000eur,
46    -- Y esta es la que de verdad distingue un formato de otro.
47    ROUND(SUM(reproducciones) * 1.0 / NULLIF(SUM(espectadores), 0), 2)
48                                                             AS sesiones_por_espectador
49FROM por_titulo
50GROUP BY franja
51HAVING SUM(reproducciones) >= 100
52ORDER BY espectadores_por_1000eur DESC;
53
54-- franja                 titulos  repro  pct_visto  h/1000EUR  esp/1000EUR  ses/esp
55-- 20-45 min (episodio)        59   1167       90,6       4,20         4,75     1,58
56-- > 120 min (largo)           57    719       69,2       6,99         4,65     1,02
57-- 90-120 min (pelicula)       78   1052       77,3       5,83         4,39     1,01
58-- < 20 min (corto)            27    298       93,2       0,77         4,14     1,01
59-- 45-90 min (medio)          159   1907       83,2       4,31         3,89     1,22

Tres métricas y tres sesgos. La cuarta columna es la que contesta la pregunta.

1-- Tabla gold: rendimiento por titulo. Se recalcula cada noche, despues de que
2-- la capa silver este actualizada, y Sofia la consulta cuando quiera sin
3-- escribir un solo cruce.
4CREATE OR REPLACE TABLE gold_content_performance AS
5WITH hoy AS (SELECT MAX(started_at) AS fecha FROM playbacks)
6SELECT
7    c.content_id,
8    c.title,
9    c.content_type,
10    c.genre_primary,
11    c.duration_minutes,
12    c.release_year,
13    c.is_original,
14    t.coste_eur,
15    t.coste_eur IS NULL                        AS coste_sin_registrar,
16    COUNT(p.playback_id)                       AS reproducciones_90d,
17    COUNT(DISTINCT p.user_id)                  AS espectadores_90d,
18    ROUND(SUM(p.watch_seconds) / 3600.0, 2)    AS horas_90d,
19    ROUND(AVG(p.watch_seconds) / 60.0, 1)      AS minutos_por_sesion,
20    -- Denominador COUNT(p.playback_id) y no COUNT(*): un titulo sin
21    -- reproducciones tiene que salir a NULO, no a 0%.
22    ROUND(SUM(CASE WHEN p.completed THEN 1 ELSE 0 END) * 100.0
23          / NULLIF(COUNT(p.playback_id), 0), 1) AS pct_terminado,
24    ROUND(AVG(p.watch_seconds * 1.0
25              / NULLIF(c.duration_minutes * 60.0, 0)) * 100, 1) AS pct_visto,
26    -- Sin coste, el retorno se queda a nulo. Un cero seria una afirmacion.
27    CASE WHEN t.coste_eur > 0
28         THEN ROUND(SUM(p.watch_seconds) / 3600.0 / t.coste_eur * 1000, 2)
29    END                                        AS horas_por_1000eur,
30    -- Cuando se calculo esta foto. Va dentro de la tabla, porque una tabla
31    -- materializada sin fecha de calculo es una tabla en la que nadie sabe si
32    -- puede confiar.
33    CURRENT_TIMESTAMP                          AS calculada_el
34FROM content c
35LEFT JOIN coste_titulo t ON t.content_id = c.content_id
36LEFT JOIN playbacks p
37       ON c.content_id = p.content_id
38      AND p.started_at >= (SELECT fecha FROM hoy) - INTERVAL '90 days'
39      AND p.is_meaningful
40GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9;

La tabla que Sofía consulta sola. Incluye el catálogo entero, también lo que nadie ve.