1. Dataset and Use Case

Points: 5 — Define a user persona, describe the analytics use case, and justify why the chosen transformations support it.

---

User Persona

Name: Alex, 26, HSLU student (part-time MSc in Applied Information and Data Science).

Situation: Alex works at an office near Zurich and drives in. Around 17:00 the same question shows up: leave now, or wait half an hour? Traffic in the city centre can swing a 10-minute drive into a 25-minute one, and rain makes it worse.

What Alex needs: a quick read on whether the route between two points is heavier or lighter than usual for the current hour, with the rain forecast already taken into account.

---

Use Case

Rush answers one question: "Should I leave the office now, or wait?"

Inputs:

Input	Source	When
Driving route between two points	OSRM public demo	on demand
Average count per counter, hour, dow, month	mart_traffic_baseline	precomputed
Weather coefficient per counter, hour, dow, precip bucket	mart_weather_effect	precomputed
Precipitation forecast at the destination	Open-Meteo forecast	on demand

Output:

{
  "route": {"duration_s": 590.1, "distance_m": 5313.3, "source": "osrm"},
  "precip_mm": 0.0,
  "precip_bucket": "none",
  "baseline_minutes": 9.835,
  "weather_multiplier": 0.994,
  "expected_minutes": 9.77,
  "counters_used": 15,
  "verdict": "fine to leave — usual conditions"
}

The verdict is just a band on the weather multiplier:

multiplier	verdict
≥ 1.10	wait — traffic is heavier than usual
≤ 0.90	leave now — lighter than usual
in between	fine to leave — usual conditions

---

Why no model

The first version tried to train a linear regression on counts. It never beat the rolling average by enough to justify the extra moving parts. The honest answer is: the historical average for the right hour, day-of-week and month already does most of the job, and weather adds a small but real second signal on top. So the marts are the model: two GROUP BY queries.

---

Why these transformations

flowchart TD
    R1["traffic_raw.hourly_counts"] --> S1["stg_traffic__counts<br/>(table, derived h/dow/m)"]
    S1 --> S2["stg_traffic__counters"]
    R2["weather_raw.hourly_history"] --> S3["stg_weather__history<br/>(precip_bucket)"]
    S1 --> M1["mart_traffic_baseline<br/>(avg per counter, h, dow, m)"]
    S1 --> M2["mart_weather_effect<br/>(coef per counter, h, dow, bucket)"]
    S3 --> M2
    M1 --> REC["recommend.py"]
    M2 --> REC

Step	Why
Derive hour_of_day, day_of_week, month_of_year on counts	grouping keys for the baseline
Cast LV95 to WGS84 (in Python, not SQL)	recommender needs lat/lon to match OSRM
Bucket precipitation (none / light / moderate / heavy)	4 readable buckets beat a continuous mm split
mart_traffic_baseline	the expected count per counter and hour
mart_weather_effect	how much that count moves under each bucket

Every transformation either lands a column the recommender reads, or shrinks 4M rows into a 100k-row lookup that the recommender can hit cheaply at request time.