Salta el contingut

Projecte 2 — TurisTIC

Camp Valor
Empresa TurisTIC (SaaS de gestió de reserves, seu a Blanes)
Sector Software com a servei (SaaS) per al sector turístic
Nivell Júnior+ — el teu segon encàrrec: entendre el producte a través de les dades
Durada estimada 6 hores
Blocs relacionats Bloc 2 (relacional, window functions), Bloc 4 (arquitectura de dades)

Context

TurisTIC és una plataforma SaaS creada per un equip de Blanes que ven programari de gestió de reserves a cases de turisme rural, hotels petits i apartaments turístics de tota la Costa Brava. Cada acció de l'usuari dins l'aplicació (iniciar sessió, crear una reserva, cancel·lar-la, enviar un missatge a un client) genera un event. L'equip de producte et demana ajuda per entendre com es comporten els clients: quants es queden fent servir l'eina mesos després de donar-se d'alta, i quins allotjaments l'aprofiten més.

El model de dades: events, no taules fixes

A diferència del Rebost de Blanes (Projecte 1), aquí les dades no viuen en taules comandes/clients clàssiques, sinó en un flux d'events amb aquesta forma:

{"event": "signup", "id_allotjament": 4021, "timestamp": "2025-03-02T10:14:00Z"}
{"event": "booking_created", "id_allotjament": 4021, "id_reserva": 88213, "timestamp": "2025-03-05T09:02:11Z"}
{"event": "booking_cancelled", "id_allotjament": 4021, "id_reserva": 88213, "timestamp": "2025-03-06T18:40:03Z"}
{"event": "login", "id_allotjament": 4022, "timestamp": "2025-03-02T11:00:00Z"}

Aquests events es carreguen a una taula relacional events(id_event, id_allotjament, tipus_event, id_reserva, timestamp) per poder-los consultar amb SQL.

Datasets

Fitxer Contingut Files
allotjaments.csv Dimensió d'allotjaments: id_allotjament, nom, comarca 380
events.jsonl Flux d'events (signup, login, booking_created, booking_cancelled), un JSON per línia 1.944

Dia 1 — Explorar el flux d'events

Tasca: amb una consulta SQL, calcula quants events de cada tipus_event hi ha al total del dataset, i quin percentatge de les reserves creades (booking_created) acaben sent cancel·lades (booking_cancelled) en els 7 dies següents a la creació.

Dia 2 — Anàlisi de cohorts amb window functions

L'equip de producte vol saber: dels allotjaments que es van donar d'alta (signup) cada mes, quants segueixen actius (han generat com a mínim un event) 1, 3 i 6 mesos després?

WITH alta_per_allotjament AS (
    SELECT id_allotjament, DATE_TRUNC('month', MIN(timestamp)) AS mes_alta
    FROM events
    WHERE tipus_event = 'signup'
    GROUP BY id_allotjament
),
activitat_mensual AS (
    SELECT id_allotjament, DATE_TRUNC('month', timestamp) AS mes_activitat
    FROM events
    GROUP BY id_allotjament, DATE_TRUNC('month', timestamp)
)
SELECT
    a.mes_alta,
    DATE_PART('month', AGE(act.mes_activitat, a.mes_alta)) AS mesos_despres,
    COUNT(DISTINCT act.id_allotjament) AS allotjaments_actius
FROM alta_per_allotjament a
JOIN activitat_mensual act ON a.id_allotjament = act.id_allotjament
GROUP BY a.mes_alta, mesos_despres
ORDER BY a.mes_alta, mesos_despres;

Tasca: executa (o adapta) aquesta consulta i construeix una taula de cohorts (mesos des de l'alta en columnes, mes d'alta en files) amb el percentatge d'allotjaments actius a cada punt. Utilitza una window function (LAG, FIRST_VALUE o similar) per calcular, per a cada cohort, el percentatge respecte al total inicial d'aquell mes (no el nombre absolut).

Dia 3 — Ranking d'allotjaments amb window functions

El comercial de TurisTIC vol una llista dels 5 allotjaments amb més reserves creades a cada comarca de la Costa Brava (la Selva, el Baix Empordà, l'Alt Empordà), per prioritzar quins visitar presencialment.

SELECT *
FROM (
    SELECT
        comarca,
        id_allotjament,
        COUNT(*) AS total_reserves,
        RANK() OVER (PARTITION BY comarca ORDER BY COUNT(*) DESC) AS posicio
    FROM events e
    JOIN allotjaments a ON e.id_allotjament = a.id_allotjament
    WHERE tipus_event = 'booking_created'
    GROUP BY comarca, id_allotjament
) rankings
WHERE posicio <= 5;

Tasca: explica amb les teves paraules la diferència entre RANK(), DENSE_RANK() i ROW_NUMBER() en aquest context, i indica quina faries servir si dos allotjaments empatessin en nombre de reserves i per què.

Dia 4 — Del flux d'events a un magatzem de dades

L'aplicació creix i consultar directament la taula events en producció comença a alentir el rendiment de l'aplicació. El teu cap et demana proposar una arquitectura per separar l'anàlisi de la producció.

Tasca: dissenya (en un diagrama i 4-5 línies d'explicació) una arquitectura amb una zona raw (els events tal com arriben), una zona silver (events nets i tipats) i una zona gold amb una taula de mètriques de retenció ja precalculada per al dashboard de producte, seguint el patró de zones vist al Bloc 4.

Lliurament

  • Consulta del Dia 1 (recompte d'events i taxa de cancel·lació).
  • Taula de cohorts del Dia 2, amb la consulta SQL utilitzada.
  • Consulta de ranking del Dia 3 i explicació RANK vs DENSE_RANK vs ROW_NUMBER.
  • Diagrama d'arquitectura raw/silver/gold del Dia 4.

Mòdul M5074 Sistemes de Big Data | Institut Sa Palomera (Blanes) | Curs CEIABD 2026-2027