Eppu-projekti — published data
==============================

Format:  RFC 4180 CSV, UTF-8, no BOM, LF line endings, comma delimiter.
Numbers: C locale (en_US_POSIX) -- decimal point, no thousands separator.
Dates:   ISO 8601 with offset, e.g. 2026-07-09T07:00:00+03:00.
Booleans: the strings True and False.
Empty:   an empty field means no value, never zero.

Data is pseudonymized: email addresses are replaced by opaque participant ids
and free-text fields are omitted. Each participant's full rating vector is
present, so rows are re-identifiable by somebody who already knows several of a
given person's ratings.

QUALIFICATION
-------------
A participant counts toward the published rankings only if they reviewed at
least 128 distinct songs. 252 of the 357 people who reviewed anything cleared
that bar; the `done` column in raatilaiset.csv marks them. Filter on
done = true to reproduce the website's figures.

Totals: 183 songs, 14 albums, 392 registered, 357 reviewed at least once,
252 qualified, 48778 current reviews, 45335 of them from qualified reviewers.

TABLE: biisit.csv — the song catalogue, one row per song (183 rows)
song_order         publication order, 1-183. The join key for everything (integer, primary key)
track_name         canonical song title (string)
album              album as released, including single and B-side provenance (string)
album_normalized   album with every single collapsed to "Single". GROUP BY THIS ONE (string)
year               release year of the recording, not of the album (integer)
date               the morning the song unlocked, 07:00 Helsinki (datetime)
song_length        seconds; absent for a few songs (integer)
fadeout            the recording ends in a fade rather than a cold stop (boolean)
album_cover        cover image filename, for reference only; images are not in this dump (string)
spotify            listening link (url)
apple              listening link (url)
youtube            listening link (url)
analysis           AI-written summary of what the song is about. Editorial color, not data (string)

TABLE: biisit-tulokset.csv — the song rankings (183 rows)
rank               1-183, by rating_avg descending. Ties break on count_10s
                   descending, then review_count descending, then song_order
                   ascending. One tie occurs: ranks 88 and 89 share
                   rating_avg 7.394 and are separated by count_10s (integer)
song_order         foreign key to biisit.csv (integer)
track_name         the song title (string)
album              album_normalized: every single collapsed to "Single" (string)
year               release year of the recording (integer)
rating_avg         mean of the qualified reviewers' ratings, 4.0-10.0 (float)
rating_sd          POPULATION standard deviation of those ratings, dividing by n
                   rather than n-1. Small means the panel agreed (float)
count_10s          how many reviewers gave it a full 10 (integer)
count_4s           how many gave it 4.0, the floor of the Finnish school scale.
                   Not a zero: a 4 is an actively failing grade (integer)
review_count       qualified reviewers who rated it (integer)
rating_music_avg   mean score for the composition, 1-5, or empty (float)
rating_music_n     how many people gave one. These four fields were OPTIONAL, so
                   their means sit on roughly two thirds of the reviews and each
                   carries its own denominator (integer)
rating_lyrics_avg  mean score for the words, 1-5, or empty (float)
rating_lyrics_n    (integer)
familiarity_avg    "I already knew this song", 1-5, or empty (float)
familiarity_n      (integer)
repeat_avg         "I would happily listen again", 1-5, or empty (float)
repeat_n           (integer)

The same numbers the website's song table shows. Everything here is computed
over the 252 qualified reviewers only.

TABLE: albumit.csv — album rankings (14 rows)
rank               1-14, by rating_avg (integer)
album              album_normalized. "Single" is excluded from this table (string)
year               earliest release year among the album's songs (integer)
rating_avg         mean of the album's songs' mean ratings (float)
rating_median_of_songs   median of those same song means (float)
song_count         songs on the album in this project (integer)
review_count       total qualified reviews across those songs (integer)
best_song_rank     the album's highest-placed song in the song ranking (integer)

TABLE: raatilaiset.csv — the participants (392 rows)
participant_id     opaque id, p001-p392. Assigned by a seeded shuffle, so the order
                   carries no information -- it is NOT registration order (string, primary key)
created            registration time (datetime)
done               reviewed 128+ distinct songs, so counts toward the rankings (boolean)
gender             Male / Female / Other. "Other" is 3 people; treat with care (string)
decade             birth decade, 1950s-2000s (string)
city               Helsinki area / Tampere / Turku / Oulu / Finland urban / Finland rural / Other (string)
urban_rural        urban / rural, a coarser cut of the same answer (string)
format             preferred listening format (string)
plays_music        plays music themselves: yes / no / sometimes (string)
works_in_music     works in the music industry: yes / no / sometimes (string)
artist_relationship        how well they knew the catalogue going in, 1-5 (integer)
music_relationship         how central music is to their life, 1-5 (integer)
listening_frequency        how often they listened to Eppu before, 1-5. PAIRS WITH aft_ (integer)
statement_lyrics_band      agreement: "above all a lyrics band", 1-5. PAIRS WITH aft_ (integer)
statement_greatest_band    agreement: "greatest band in the universe", 1-5. PAIRS WITH aft_ (integer)
statement_album_cuts       agreement: "the best songs hide on the albums", 1-5. PAIRS WITH aft_ (integer)
favourite_era              which era they liked best (string)
live_gigs_seen             none / once / 2-5 / 6-10 / over 10 (string)
first_contact              how they first heard the band (string)
hope_*                     six booleans: why they joined. Multi-select, flattened (boolean)
project_endurance          how far they predicted they would get. Compare against done (string)
participation_frequency    predicted rhythm: daily / weekly / monthly / once (string)
winner_prediction          which song they thought would win, at registration.
                           Lookup: matches biisit.csv.track_name exactly (string)
album_winner_prediction    which album they thought would win, at registration.
                           Lookup: matches biisit.csv.album_normalized (string)
google_group               member of the project's email list (boolean)

The aft_ prefix marks the post-project survey, answered by 217 of
the 392 participants. AN ABSENT aft_ VALUE MEANS THE PERSON DID NOT
TAKE THE SURVEY. It does not mean zero and must not be averaged as one.

aft_submitted_at           when they answered (datetime)
aft_nps                    "how likely are you to recommend a project like this", 0-10 (integer)
aft_nps_bucket             detractor 0-6 / passive 7-8 / promoter 9-10 (string)
aft_winner_prediction      winner prediction after hearing everything. Lookup:
                           matches biisit.csv.track_name exactly (string)
aft_album_winner_prediction  same, for albums. Lookup: matches
                           biisit.csv.album_normalized exactly (string)
aft_discovery              biggest find of the project; empty if they picked
                           "none". Lookup: matches biisit.csv.track_name (string)
aft_hate_song              the song they learned to hate; empty if they picked
                           "none". Lookup: matches biisit.csv.track_name (string)
aft_listening_frequency    PAIRS WITH listening_frequency (integer)
aft_statement_*            PAIR WITH the matching statement_* fields (integer)
aft_statement_listened_fully  did they listen to every song fully before rating, 1-5 (integer)
aft_reviewed_as_expected   did their ratings match their own expectations, 1-5 (integer)
aft_influenced_by_others   did the group discussion sway them, 1-5 (integer)
aft_app_worked             satisfaction with the app itself, 1-5 (integer)
aft_would_join_again       1 never again ... 5 absolutely (integer)
aft_daily_dose             was one song a day too little / just right / too much (string)
aft_eppu_day               stated intent for the wrap-up party (string)
aft_channel_*              four booleans: which channels they followed (boolean)

PAIRED FIELDS: the four statement/listening questions were asked with an identical
option set at registration and again afterwards, so aft_x minus x is a real
before/after delta. Average each side over the people who answered BOTH, not over
whoever answered each one -- a handful answered one side only, and an unpaired
comparison silently compares two different populations.

TABLE: arviot.csv — the reviews, one row per participant-song pair (48778 rows)
song_order         foreign key to biisit.csv (integer)
participant_id     foreign key to raatilaiset.csv (string)
rating             the headline score, 4.0-10.0 in 0.25 steps. Finnish school
                   grading, so 4 is the floor rather than 0 (float)
rating_music       separate score for the composition, 1-5. Optional: present on
                   about 65% of reviews (integer)
rating_lyrics      separate score for the words, 1-5. Optional, same coverage (integer)
familiarity        "I already knew this song", 1-5. Optional, about 67% (integer)
repeat             "I would happily listen again", 1-5. Optional, about 67% (integer)
time               when the current version of the review was written (datetime)
edit_count         how many times the review was revised after first being written.
                   0 means it was written once and never changed (integer)

One row per participant-song pair: the CURRENT review, not the edit history. In
the database this table is append-only and a revision writes a new row rather
than updating the old one, so the raw table holds 56314 rows for 48778 pairs.
Those extra rows are a per-person behavioral trace, so they are collapsed here
to the current opinion with edit_count beside it.

TABLE: tekijat.csv — who wrote what (408 rows)
song_order         foreign key to biisit.csv (integer)
contributor        the writer's name. These are the band and their collaborators,
                   public figures in this context, not project participants (string)
contribution_type  music or lyrics (string)

A song usually has one of each, sometimes several.