Statistics and analytics

Version française
← Back to index

---

Tracked event types

The application automatically records 3 types of user events:

Type (event_type)	Triggered when	What it measures
unlock_language	A player unlocks a language	Popularity / sales per language
view_card	A player views a card	Engagement per card
play_audio	A player listens to audio	Recording usage

---

Structure of the statistics table

CREATE TABLE statistics (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
  card_id     UUID REFERENCES cards(id) ON DELETE CASCADE,
  item_id     UUID REFERENCES items(id) ON DELETE CASCADE,
  event_type  VARCHAR(50) NOT NULL,  -- 'unlock_language', 'view_card', 'play_audio'
  count       INTEGER DEFAULT 1,
  event_date  DATE NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

Uniqueness key: (language_id, card_id, item_id, event_type, event_date) — events are aggregated per day (upsert with count increment).

---

Structure of the quiz_sessions table

CREATE TABLE quiz_sessions (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id      UUID REFERENCES languages(id) ON DELETE CASCADE,
  category_slug    VARCHAR(255) NOT NULL,
  category_name    VARCHAR(255),
  total_questions  INTEGER NOT NULL,
  score            INTEGER,
  started_at       TIMESTAMPTZ DEFAULT NOW(),
  completed_at     TIMESTAMPTZ,
  created_at       TIMESTAMPTZ DEFAULT NOW()
);

• A quiz_sessions record is created when a player starts a quiz
• score and completed_at are filled in when the player completes the quiz
• If completed_at is NULL, the quiz was abandoned

---

Viewing statistics in the dashboard

Most analyses are accessible directly in the dashboard. For advanced analyses, you can access the PostgreSQL database directly.

---

Useful SQL queries

Total number of unlocks

SELECT COUNT(*) AS total_unlocks
FROM statistics
WHERE event_type = 'unlock_language';

Unlocks by language (given period)

SELECT l.name, SUM(s.count) AS unlocks
FROM statistics s
JOIN languages l ON s.language_id = l.id
WHERE s.event_type = 'unlock_language'
  AND s.event_date >= '2026-01-01'
  AND s.event_date <= '2026-05-31'
GROUP BY l.name
ORDER BY unlocks DESC;

Top 10 most viewed cards (all languages)

SELECT c.card_number, c.type, l.name, SUM(s.count) AS views
FROM statistics s
JOIN cards c ON s.card_id = c.id
JOIN languages l ON c.language_id = l.id
WHERE s.event_type = 'view_card'
GROUP BY c.card_number, c.type, l.name
ORDER BY views DESC
LIMIT 10;

Most listened-to audio for a language

SELECT i.original_text, i.translation, SUM(s.count) AS plays
FROM statistics s
JOIN items i ON s.item_id = i.id
JOIN cards c ON i.card_id = c.id
WHERE s.event_type = 'play_audio'
  AND c.language_id = 'LANGUAGE_UUID'
GROUP BY i.original_text, i.translation
ORDER BY plays DESC
LIMIT 10;

Daily activity over 30 days

SELECT event_date, event_type, SUM(count) AS total
FROM statistics
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY event_date, event_type
ORDER BY event_date DESC;

Quiz performance — completion rate

SELECT
  l.name AS language,
  qs.category_name,
  COUNT(*) AS sessions_started,
  COUNT(qs.completed_at) AS sessions_completed,
  ROUND(COUNT(qs.completed_at)::numeric / COUNT(*)::numeric * 100, 1) AS completion_rate,
  ROUND(AVG(qs.score::numeric / qs.total_questions::numeric * 100), 1) AS avg_score_pct
FROM quiz_sessions qs
JOIN languages l ON qs.language_id = l.id
GROUP BY l.name, qs.category_name
ORDER BY l.name, qs.category_name;

---

Offline statistics

Offline players accumulate events in a local queue (IndexedDB). These events are synchronised automatically when the connection is restored.

Impact: - Real-time statistics may be slightly delayed for offline users - Data is always eventually consistent (no data loss) - There is no way to force synchronisation from the admin side

---

Exporting statistics

Via pg_dump (full export)

# On the server or via SSH tunnel
pg_dump -h localhost -U postgres -d racines \
  --table=statistics --table=quiz_sessions \
  -F c -f stats_backup.dump

Via psql (CSV export)

psql -h localhost -U postgres -d racines -c \
  "\COPY (SELECT * FROM statistics WHERE event_date >= '2026-01-01') \
  TO '/tmp/stats_2026.csv' WITH CSV HEADER;"

Via the admin API

The GET /api/admin/statistics endpoint returns aggregated statistics as JSON:

GET /api/admin/statistics?period=30d&language_id=[UUID]

There is no native CSV export in the graphical interface. For regular exports, set up a cron script on the server side.

---

Next steps

• 👉 Dashboard
• 👉 Language management