Statistiques et tracking

English version
← Retour au sommaire

---

Vue d'ensemble

Le système de statistiques de Racines est entièrement anonyme — aucune donnée utilisateur personnelle n'est collectée. Les événements sont agrégés par jour et par langue.

Deux tables stockent les données :

Table	Contenu
statistics	Événements d'usage (vues cartes, lectures audio, déverrouillages)
quiz_sessions	Sessions de révision QCM (scores, catégories, abandons)

---

Table statistics

Schéma

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,
  count       INTEGER DEFAULT 1,
  event_date  DATE NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(language_id, card_id, item_id, event_type, event_date)
);

Comportement clé : Les événements sont agrégés par jour via un upsert :

INSERT INTO statistics (language_id, card_id, item_id, event_type, count, event_date)
VALUES ($1, $2, $3, $4, 1, CURRENT_DATE)
ON CONFLICT (language_id, card_id, item_id, event_type, event_date)
DO UPDATE SET count = statistics.count + 1;

Pas de ligne par clic — 1 ligne par (langue, carte, item, type, jour), incrémentée.

Types d'événements

event_type	Déclencheur	card_id	item_id
unlock_language	Joueur déverrouille une langue	null	null
view_card	Joueur affiche une carte (mot ou phrase)	requis	null
play_audio	Joueur lit un audio	requis	requis

---

API de tracking

POST /api/statistics/track

Endpoint public (aucune authentification). Appelé automatiquement par le frontend.

// Déclenchements dans le code :

// unlock_language → LanguageContext.unlockLanguage()
await fetch('/api/statistics/track', {
  method: 'POST',
  body: JSON.stringify({ eventType: 'unlock_language', languageId }),
});

// view_card → pages /[language]/words/[n] et /[language]/phrases/[n]
trackEvent({ eventType: 'view_card', languageId, cardId });

// play_audio → AudioPlayer.handlePlay()
trackEvent({ eventType: 'play_audio', languageId, cardId, itemId });

Body :

{
  "eventType": "play_audio",
  "languageId": "uuid",
  "cardId": "uuid",
  "itemId": "uuid"
}

Réponse :

{ "success": true, "count": 42 }

count = nombre cumulé d'occurrences pour ce jour.

---

Gestion offline des statistiques

File d'attente IndexedDB

Quand le réseau est indisponible, les événements sont mis en file d'attente dans IndexedDB :

// Store : statistics-queue (racines-db v3)
{
  id: autoIncrement,
  timestamp: Date.now(),
  eventType: "play_audio",
  languageId: "uuid",
  cardId: "uuid",
  itemId: "uuid"
}

Synchronisation automatique

// src/components/ServiceWorkerRegistration.tsx

// Tentative au montage du composant (au chargement de l'app)
flushStatisticsQueue();

// Également déclenchée à la reconnexion réseau
window.addEventListener("online", () => {
  flushStatisticsQueue();
});

flushStatisticsQueue() : 1. Lit tous les événements de la file IndexedDB 2. Les envoie un par un vers /api/statistics/track 3. Supprime les événements envoyés avec succès 4. Conserve les échecs pour la prochaine tentative

---

Dashboard admin

Endpoint GET /api/statistics/by-language

Agrège les événements par langue, avec support du mode comparaison :

# Période principale
GET /api/statistics/by-language?from=2026-01-01&to=2026-01-31

# Avec période de comparaison
GET /api/statistics/by-language?from=2026-01-01&to=2026-01-31&compareFrom=2025-12-01&compareTo=2025-12-31

Réponse avec comparaison :

{
  "success": true,
  "stats": [
    {
      "language_id": "uuid",
      "language_name": "Pular",
      "language_code": "pul",
      "unlock_count": 245,
      "view_count": 1830,
      "audio_count": 956
    }
  ],
  "compareStats": [
    {
      "language_name": "Pular",
      "unlock_count": 180,
      "view_count": 1420,
      "audio_count": 720
    }
  ]
}

Endpoint GET /api/statistics/top-cards

Cartes les plus consultées sur une période :

GET /api/statistics/top-cards?languageId=uuid&limit=10&from=2026-01-01&to=2026-01-31

---

Requêtes SQL utiles

Vue globale par langue (toutes périodes)

SELECT
  l.name AS langue,
  SUM(CASE WHEN s.event_type = 'unlock_language' THEN s.count ELSE 0 END) AS deverrouillages,
  SUM(CASE WHEN s.event_type = 'view_card' THEN s.count ELSE 0 END) AS vues_cartes,
  SUM(CASE WHEN s.event_type = 'play_audio' THEN s.count ELSE 0 END) AS lectures_audio
FROM statistics s
JOIN languages l ON s.language_id = l.id
GROUP BY l.id, l.name
ORDER BY deverrouillages DESC;

Activité quotidienne (30 derniers jours)

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

Top 10 des cartes les plus vues

SELECT
  c.card_number,
  c.type,
  c.theme,
  l.name AS langue,
  SUM(s.count) AS vues
FROM statistics s
JOIN cards c ON s.card_id = c.id
JOIN languages l ON s.language_id = l.id
WHERE s.event_type = 'view_card'
  AND s.event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.card_number, c.type, c.theme, l.name
ORDER BY vues DESC
LIMIT 10;

Taux d'engagement audio (items écoutés vs vus)

SELECT
  l.name AS langue,
  SUM(CASE WHEN s.event_type = 'view_card' THEN s.count ELSE 0 END) AS vues,
  SUM(CASE WHEN s.event_type = 'play_audio' THEN s.count ELSE 0 END) AS audios,
  ROUND(
    SUM(CASE WHEN s.event_type = 'play_audio' THEN s.count ELSE 0 END)::numeric /
    NULLIF(SUM(CASE WHEN s.event_type = 'view_card' THEN s.count ELSE 0 END), 0) * 100, 1
  ) AS pct_engagement_audio
FROM statistics s
JOIN languages l ON s.language_id = l.id
GROUP BY l.id, l.name
ORDER BY pct_engagement_audio DESC NULLS LAST;

---

Statistiques Quiz

Table quiz_sessions

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,              -- null si abandonnée
  started_at       TIMESTAMPTZ DEFAULT NOW(),
  completed_at     TIMESTAMPTZ,         -- null si abandonnée
  created_at       TIMESTAMPTZ DEFAULT NOW()
);

Endpoint GET /api/admin/quiz/stats

# Stats quiz avec mode comparaison
GET /api/admin/quiz/stats?languageId=uuid&from=2026-01-01&compareFrom=2025-12-01&compareTo=2025-12-31

Requête SQL quiz globale

SELECT
  l.name AS langue,
  COUNT(*) AS sessions_total,
  COUNT(qs.completed_at) AS sessions_terminees,
  COUNT(*) - COUNT(qs.completed_at) AS sessions_abandonnees,
  ROUND(COUNT(qs.completed_at)::numeric / COUNT(*) * 100, 1) AS taux_completion_pct,
  ROUND(AVG(CASE WHEN qs.completed_at IS NOT NULL
    THEN qs.score::numeric / qs.total_questions * 100 END), 1) AS score_moyen_pct
FROM quiz_sessions qs
JOIN languages l ON qs.language_id = l.id
WHERE qs.started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY l.id, l.name
ORDER BY sessions_total DESC;

Top catégories par langue

SELECT
  qs.category_name,
  COUNT(*) AS sessions,
  COUNT(qs.completed_at) AS terminees,
  ROUND(AVG(CASE WHEN qs.completed_at IS NOT NULL
    THEN qs.score::numeric / qs.total_questions * 100 END), 1) AS score_moyen
FROM quiz_sessions qs
WHERE qs.language_id = $1
GROUP BY qs.category_name
ORDER BY sessions DESC
LIMIT 10;

---

Purge et maintenance

GET /api/admin/statistics

Compte les lignes avant purge :

curl "http://localhost:3000/api/admin/statistics?languageId=uuid&from=2025-01-01&to=2025-12-31" \
  -b cookies.txt
# → {"count": 45230}

DELETE /api/admin/statistics

Purge par filtre :

# Purger une langue sur une période
curl -X DELETE \
  "http://localhost:3000/api/admin/statistics?languageId=uuid&from=2025-01-01&to=2025-12-31" \
  -b cookies.txt
# → {"success": true, "deleted": 45230}

Purge totale :

# Header obligatoire pour éviter les suppressions accidentelles
curl -X DELETE http://localhost:3000/api/admin/statistics \
  -H "x-delete-all-confirmed: true" \
  -b cookies.txt
# → {"success": true, "deleted": 123456}

⚠️ Action irréversible. Les statistiques supprimées ne peuvent pas être récupérées.

---

Export des données

Les données de statistiques ne disposent pas d'un endpoint d'export CSV intégré. Pour exporter :

# Export CSV via psql
psql -h $PG_HOST -U $PG_USER -d $PG_DATABASE -c \
  "COPY (
    SELECT
      event_date, l.name AS langue, event_type,
      c.card_number, c.theme, SUM(s.count) AS total
    FROM statistics s
    JOIN languages l ON s.language_id = l.id
    LEFT JOIN cards c ON s.card_id = c.id
    GROUP BY event_date, l.name, event_type, c.card_number, c.theme
    ORDER BY event_date DESC
  ) TO STDOUT WITH CSV HEADER" > stats_export.csv

# Export JSON via API admin
curl "http://localhost:3000/api/statistics/by-language?from=2026-01-01&to=2026-12-31" \
  -b cookies.txt > stats_2026.json

---

Étapes suivantes

• 👉 Référence API — endpoints statistiques
• 👉 Base de données — tables statistics et quiz_sessions
• 👉 Module Révision QCM — table quiz_sessions