Créa·Ka — annuaire public cross-plateforme des créateurs de contenu québécois (crea-ka.com)
Python 73.6%
HTML 13.2%
TypeScript 6%
JavaScript 4.5%
CSS 1.7%
Dockerfile 0.6%
1# ==============================================================================2# Author: Simon-Pierre Boucher <contact@spboucher.ai>3# File: creaka/stats.py4# Desc: Tableau de bord analytique /api/stats/dashboard (contrat ka-stats5# SPEC.md v2) + rapport PDF Groupe-KA (/api/stats/report via kapdf.py,6# 5 modes). AUCUNE stat inventée : tout est calculé depuis creators/7# accounts/sync_log (first_seen converti en date locale, cache 5 min).8# v2 : sparklines KPI, jauges de complétude, multi-courbes par9# plateforme, ajouts empilés par source, distributions (audiences,10# comptes/créateur, confiance), heatmap horaire 7×24, 5 tableaux,11# records enrichis.12# ==============================================================================13from __future__ import annotations1415import json16import sqlite317import time18from datetime import date, datetime, timedelta, timezone19from pathlib import Path20from zoneinfo import ZoneInfo2122TZ = ZoneInfo("America/Toronto")23ROOT = Path(__file__).resolve().parent.parent24ECO_PATH = ROOT / "frontend" / "src" / "ka" / "ecosystem.json"2526CACHE_TTL = 300 # ≥ 5 min par période (SPEC §2)27_CACHE: dict[tuple, tuple[float, dict]] = {}2829PERIOD_DAYS = {"auj": 1, "7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365}30PERIOD_LABELS = {31 "auj": "aujourd'hui", "7j": "7 jours", "30j": "30 jours", "3m": "3 mois",32 "6m": "6 mois", "12m": "12 mois", "annee": "année en cours",33 "tout": "toute la période",34}3536PLAT_LBL = {37 "instagram": "Instagram", "tiktok": "TikTok", "youtube": "YouTube",38 "twitch": "Twitch", "kick": "Kick", "x": "X (Twitter)",39 "facebook": "Facebook", "snapchat": "Snapchat", "substack": "Substack",40 "patreon": "Patreon", "onlyfans": "OnlyFans", "linkedin": "LinkedIn",41 "threads": "Threads", "podcast": "Balado", "site-web": "Site web",42 "spotify": "Spotify", "discord": "Discord", "fansly": "Fansly",43 "mym": "MYM", "autre": "Autre",44}45NICHE_LBL = {46 "humour": "Humour", "mode": "Mode", "beaute": "Beauté",47 "lifestyle": "Lifestyle", "famille-parentalite": "Famille & parentalité",48 "cuisine": "Cuisine", "gaming": "Gaming", "tech": "Tech",49 "sport-fitness": "Sport & fitness", "plein-air": "Plein air",50 "voyage": "Voyage", "musique": "Musique", "arts": "Arts", "danse": "Danse",51 "education": "Éducation", "finance-affaires": "Finance & affaires",52 "sante-mieux-etre": "Santé & mieux-être", "bouffe-resto": "Bouffe & resto",53 "actualite-opinion": "Actualité & opinion", "autre": "Autre",54}55TYPE_LBL = {56 "youtubeur": "Youtubeur", "influenceur": "Influenceur",57 "streamer": "Streamer", "podcasteur": "Podcasteur",58 "humoriste": "Humoriste", "createur-tiktok": "Créateur TikTok",59 "createur-ecrit": "Créateur écrit", "musicien": "Musicien",60 "artiste": "Artiste", "autre": "Autre",61}62# bornes réelles de normalize.audience_tier (§6.3)63TIER_LBL = [("nano", "Nano (< 10 k)"), ("micro", "Micro (10 k – 100 k)"),64 ("macro", "Macro (100 k – 1 M)"), ("mega", "Méga (1 M et +)")]65# tranches d'audience (distribution) — bornes en abonnés cumulés connus66REACH_BINS = [(0, 1_000, "< 1 k"), (1_000, 10_000, "1 k – 10 k"),67 (10_000, 50_000, "10 k – 50 k"), (50_000, 100_000, "50 k – 100 k"),68 (100_000, 500_000, "100 k – 500 k"),69 (500_000, 1_000_000, "500 k – 1 M"),70 (1_000_000, None, "1 M et +")]717273def site_info() -> dict:74 """Identité Créa-Ka pour le PDF, lue dans ka/ecosystem.json (source commune)."""75 try:76 eco = json.loads(ECO_PATH.read_text(encoding="utf-8"))77 s = next(x for x in eco["sites"] if x["id"] == "crea-ka")78 return {"wordmark": s["wordmark"], "accent": s["accent"],79 "domain": s["domain"], "tagline": s.get("tagline", "")}80 except Exception:81 return {"wordmark": "Créa·Ka", "accent": "#7048e8",82 "domain": "www.crea-ka.com",83 "tagline": "Les créateurs d'ici, tous leurs liens"}848586# --- utilitaires -----------------------------------------------------------------8788def _local_dt(iso: str) -> datetime | None:89 """ISO-8601 UTC (…Z) → datetime local (America/Toronto)."""90 if not iso:91 return None92 try:93 dt = datetime.strptime(iso[:19], "%Y-%m-%dT%H:%M:%S")94 return dt.replace(tzinfo=timezone.utc).astimezone(TZ)95 except ValueError:96 return None979899def _local_date(iso: str) -> date | None:100 dt = _local_dt(iso)101 if dt:102 return dt.date()103 try:104 return date.fromisoformat((iso or "")[:10])105 except ValueError:106 return None107108109def _parse_date(s: str) -> date | None:110 try:111 return date.fromisoformat(s.strip()[:10])112 except (ValueError, AttributeError):113 return None114115116def _pct(cur: float, prev: float) -> float | None:117 """Variation vs période précédente ; None si la base est nulle (pas de faux %)."""118 if not prev:119 return None120 return round(100.0 * (cur - prev) / prev, 1)121122123def _delta(cur: float, prev: float) -> dict:124 p = _pct(cur, prev)125 if p is None:126 return {"delta_pct": None}127 return {"delta_pct": p, "direction": "up" if p >= 0 else "down"}128129130def _fr_int(n: int) -> str:131 return f"{int(n):,}".replace(",", " ")132133134def _spark(points: list[dict], keep: int = 20) -> list[dict]:135 """Sous-échantillonne une série pour la mini-tendance des KPI (≤ keep pts)."""136 if len(points) <= keep:137 return points138 step = (len(points) - 1) / (keep - 1)139 return [points[round(i * step)] for i in range(keep)]140141142# --- construction du tableau de bord ----------------------------------------------143144def _resolve_period(period: str, d_from: str, d_to: str,145 min_day: date | None) -> tuple[date, date, str]:146 today = datetime.now(TZ).date()147 f, t = _parse_date(d_from), _parse_date(d_to)148 if f and t:149 if t < f:150 f, t = t, f151 return f, t, f"du {f.isoformat()} au {t.isoformat()}"152 if period == "annee":153 return date(today.year, 1, 1), today, f"année {today.year}"154 if period == "tout":155 # depuis la première fiche (plancher 30 j pour des courbes lisibles)156 start = min(min_day or today, today - timedelta(days=29))157 return start, today, PERIOD_LABELS["tout"]158 days = PERIOD_DAYS.get(period, 30)159 label = PERIOD_LABELS.get(period, PERIOD_LABELS["30j"])160 return today - timedelta(days=days - 1), today, label161162163def _build(con: sqlite3.Connection, period: str, d_from: str, d_to: str) -> dict:164 # fiches actives (mineurs & opt-out exclus, comme partout dans l'API)165 creators = con.execute(166 "SELECT id, display_name, first_seen, niches, audience_tier, region, "167 "city, bio, creator_type, total_reach, primary_platform, "168 "json_extract(doc,'$.source') AS src, "169 "json_extract(doc,'$.avatar_url') IS NOT NULL AS has_avatar "170 "FROM creators WHERE status='active' AND is_minor=0").fetchall()171 n_active = len(creators)172 dts_seen = [_local_dt(r["first_seen"]) for r in creators]173 days_seen = [d.date() for d in dts_seen if d]174 min_day = min(days_seen) if days_seen else None175 p_from, p_to, p_label = _resolve_period(period, d_from, d_to, min_day)176 span = (p_to - p_from).days + 1177 prev_to = p_from - timedelta(days=1)178 prev_from = prev_to - timedelta(days=span - 1)179180 # ajouts par jour (toute l'historique) — sert séries, heatmap, records181 adds_by_day: dict[date, int] = {}182 for d in days_seen:183 adds_by_day[d] = adds_by_day.get(d, 0) + 1184185 def added_between(a: date, b: date) -> int:186 return sum(v for d, v in adds_by_day.items() if a <= d <= b)187188 def total_until(d: date) -> int:189 return sum(v for dd, v in adds_by_day.items() if dd <= d)190191 # comptes reliés par plateforme (comptes « à vérifier » exclus, §12.1) +192 # agrégats abonnés/vérifiés pour le tableau plateformes193 plat_rows = con.execute(194 "SELECT a.platform, COUNT(*) c, "195 "SUM(CASE WHEN a.verified=1 THEN 1 ELSE 0 END) nverif, "196 "SUM(COALESCE(a.followers,0)) fol, "197 "COUNT(a.followers) nfol "198 "FROM accounts a JOIN creators c2 ON c2.id=a.creator_id "199 "WHERE a.needs_review=0 AND c2.status='active' AND c2.is_minor=0 "200 "GROUP BY a.platform ORDER BY c DESC").fetchall()201 n_accounts = sum(r["c"] for r in plat_rows)202 n_verified = sum(r["nverif"] or 0 for r in plat_rows)203204 # comptes par créateur (multi-plateforme, distribution, record)205 acc_per_creator = con.execute(206 "SELECT a.creator_id, c2.display_name, COUNT(DISTINCT a.platform) np "207 "FROM accounts a JOIN creators c2 ON c2.id=a.creator_id "208 "WHERE a.needs_review=0 AND c2.status='active' AND c2.is_minor=0 "209 "GROUP BY a.creator_id").fetchall()210 n_multi = sum(1 for r in acc_per_creator if r["np"] >= 2)211212 # confiance des rattachements (distribution)213 conf_rows = con.execute(214 "SELECT a.confidence FROM accounts a JOIN creators c2 ON c2.id=a.creator_id "215 "WHERE a.needs_review=0 AND c2.status='active' AND c2.is_minor=0").fetchall()216217 # niches (multivaluées) — global + ajouts sur la période218 niche_total: dict[str, int] = {}219 niche_period: dict[str, int] = {}220 tier_total: dict[str, int] = {}221 tier_period: dict[str, int] = {}222 type_total: dict[str, int] = {}223 region_total: dict[str, int] = {}224 city_total: dict[str, int] = {}225 src_total: dict[str, int] = {}226 # ajouts par jour et par source (empilé) + par plateforme principale227 src_day: dict[tuple, int] = {}228 plat_day: dict[tuple, int] = {}229 reach_day: dict[date, int] = {}230 hour_cells: dict[tuple, int] = {}231 for r, dt_loc in zip(creators, dts_seen):232 d = dt_loc.date() if dt_loc else None233 in_p = bool(d and p_from <= d <= p_to)234 for n in (r["niches"] or "").split(","):235 if not n:236 continue237 niche_total[n] = niche_total.get(n, 0) + 1238 if in_p:239 niche_period[n] = niche_period.get(n, 0) + 1240 tier_total[r["audience_tier"]] = tier_total.get(r["audience_tier"], 0) + 1241 if in_p:242 tier_period[r["audience_tier"]] = tier_period.get(r["audience_tier"], 0) + 1243 if r["creator_type"]:244 type_total[r["creator_type"]] = type_total.get(r["creator_type"], 0) + 1245 if r["region"]:246 region_total[r["region"]] = region_total.get(r["region"], 0) + 1247 if r["city"]:248 city_total[r["city"]] = city_total.get(r["city"], 0) + 1249 if r["src"]:250 src_total[r["src"]] = src_total.get(r["src"], 0) + 1251 if d:252 src_day[(d, r["src"])] = src_day.get((d, r["src"]), 0) + 1253 if d and r["primary_platform"]:254 plat_day[(d, r["primary_platform"])] = \255 plat_day.get((d, r["primary_platform"]), 0) + 1256 if d and r["total_reach"]:257 reach_day[d] = reach_day.get(d, 0) + r["total_reach"]258 if dt_loc:259 key = (dt_loc.weekday(), dt_loc.hour) # 0=lun … 6=dim (SPEC)260 hour_cells[key] = hour_cells.get(key, 0) + 1261262 # ---- KPI (deltas seulement quand ils sont réellement calculables) ----263 added_cur = added_between(p_from, p_to)264 added_prev = added_between(prev_from, prev_to)265 total_end = total_until(p_to)266 total_start = total_until(prev_to)267 day_axis = [p_from + timedelta(days=i) for i in range(span)]268 pts_added = [{"t": d.isoformat(), "v": adds_by_day.get(d, 0)} for d in day_axis]269 running = total_until(p_from - timedelta(days=1))270 pts_cumul = []271 for d in day_axis:272 running += adds_by_day.get(d, 0)273 pts_cumul.append({"t": d.isoformat(), "v": running})274 reach = sum(r["total_reach"] for r in creators if r["total_reach"])275276 kpis = [277 {"id": "creators", "label": "Créateurs au répertoire", "value": total_end,278 "unit": "", **_delta(total_end, total_start), "spark": _spark(pts_cumul)},279 {"id": "accounts", "label": "Comptes publics reliés", "value": n_accounts,280 "unit": "", "delta_pct": None},281 {"id": "added", "label": "Créateurs ajoutés sur la période",282 "value": added_cur, "unit": "", **_delta(added_cur, added_prev),283 "spark": _spark(pts_added)},284 {"id": "platforms", "label": "Plateformes couvertes",285 "value": len(plat_rows), "unit": "", "delta_pct": None},286 {"id": "multi", "label": "Créateurs multi-plateformes", "value": n_multi,287 "unit": "", "delta_pct": None},288 {"id": "verified", "label": "Comptes vérifiés (badge)",289 "value": n_verified, "unit": "", "delta_pct": None},290 {"id": "niches", "label": "Niches couvertes", "value": len(niche_total),291 "unit": "", "delta_pct": None},292 {"id": "regions", "label": "Régions représentées",293 "value": len(region_total), "unit": "", "delta_pct": None},294 ]295 if reach:296 kpis.append({"id": "reach", "label": "Portée cumulée connue",297 "value": reach, "unit": "abonnés", "delta_pct": None})298 if n_active:299 kpis.append({"id": "acc_avg", "label": "Comptes reliés par créateur (moy.)",300 "value": round(n_accounts / n_active, 2), "unit": "",301 "delta_pct": None})302303 # ---- jauges : couvertures & complétude (calculées, pas estimées) ----304 def _cov(n: int) -> float:305 return round(100.0 * n / n_active, 1) if n_active else 0.0306 n_bio = sum(1 for r in creators if r["bio"])307 n_loc = sum(1 for r in creators if r["region"] or r["city"])308 n_reach = sum(1 for r in creators if r["total_reach"])309 n_avatar = sum(1 for r in creators if r["has_avatar"])310 # complétude moyenne d'une fiche = moyenne des 4 champs clés remplis311 completeness = round((_cov(n_bio) + _cov(n_loc) + _cov(n_reach)312 + _cov(n_avatar)) / 4, 1) if n_active else 0.0313 gauges = [314 {"id": "multi", "label": "Créateurs multi-plateformes (2 comptes et +)",315 "value": _cov(n_multi), "max": 100, "unit": "%"},316 {"id": "bio", "label": "Fiches avec biographie", "value": _cov(n_bio),317 "max": 100, "unit": "%"},318 {"id": "loc", "label": "Fiches avec ville ou région déclarée",319 "value": _cov(n_loc), "max": 100, "unit": "%"},320 {"id": "reach", "label": "Fiches avec audience connue",321 "value": _cov(n_reach), "max": 100, "unit": "%"},322 {"id": "complete", "label": "Complétude moyenne des fiches "323 "(bio, lieu, audience, photo)", "value": completeness,324 "max": 100, "unit": "%"},325 ]326327 # ---- séries quotidiennes ----328 cmp_added = [{"t": (prev_from + timedelta(days=i)).isoformat(),329 "v": adds_by_day.get(prev_from + timedelta(days=i), 0)}330 for i in range(span)]331 series = [332 {"id": "added", "title": "Créateurs ajoutés par jour", "unit": "créateurs",333 "kind": "line", "points": pts_added,334 **({"compare": cmp_added} if any(c["v"] for c in cmp_added) else {})},335 {"id": "cumul", "title": "Taille cumulative du répertoire",336 "unit": "créateurs", "kind": "area", "points": pts_cumul},337 ]338 # portée cumulée découverte (somme des audiences connues des fiches ajoutées)339 if reach_day:340 run_r = sum(v for dd, v in reach_day.items() if dd < p_from)341 pts_reach = []342 for d in day_axis:343 run_r += reach_day.get(d, 0)344 pts_reach.append({"t": d.isoformat(), "v": run_r})345 if any(p["v"] for p in pts_reach):346 series.append({"id": "reach_cumul",347 "title": "Portée cumulée découverte (audiences connues)",348 "unit": "abonnés", "kind": "area", "points": pts_reach})349 # journaux de sync : fiches ajoutées + mises à jour par les connecteurs350 sync_day: dict[date, int] = {}351 for r in con.execute("SELECT ts, COALESCE(added,0)+COALESCE(updated,0) n "352 "FROM sync_log").fetchall():353 d = _local_date(r["ts"])354 if d:355 sync_day[d] = sync_day.get(d, 0) + r["n"]356 if sync_day:357 pts_sync = [{"t": d.isoformat(), "v": sync_day.get(d, 0)} for d in day_axis]358 if any(p["v"] for p in pts_sync):359 series.append({"id": "sync",360 "title": "Fiches ajoutées ou mises à jour par les "361 "connecteurs (journaux de sync)",362 "unit": "fiches", "kind": "bar", "points": pts_sync})363364 # ---- multi-courbes : croissance par plateforme principale (top 4) ----365 plat_totals: dict[str, int] = {}366 for (d, pl), v in plat_day.items():367 plat_totals[pl] = plat_totals.get(pl, 0) + v368 top_plats = [p for p, _ in sorted(plat_totals.items(), key=lambda x: -x[1])[:4]]369 multiseries = []370 if top_plats:371 mseries = []372 for pl in top_plats:373 run = sum(v for (dd, p2), v in plat_day.items()374 if p2 == pl and dd < p_from)375 pts = []376 for d in day_axis:377 run += plat_day.get((d, pl), 0)378 pts.append({"t": d.isoformat(), "v": run})379 mseries.append({"label": PLAT_LBL.get(pl, pl), "points": pts})380 if any(pt["v"] for s in mseries for pt in s["points"]):381 multiseries.append({382 "id": "plat_growth",383 "title": "Croissance du répertoire par plateforme principale (top 4)",384 "unit": "créateurs", "series": mseries})385386 # ---- empilé : ajouts par source de découverte (top 5 + autres) ----387 stacked = []388 if src_day:389 top_src = [s for s, _ in sorted(src_total.items(), key=lambda x: -x[1])[:5]]390 keys = top_src + ["Autres"]391 pts = []392 for d in day_axis:393 vals = [src_day.get((d, s), 0) for s in top_src]394 other = sum(v for (dd, s), v in src_day.items()395 if dd == d and s not in top_src)396 pts.append({"t": d.isoformat(), "values": vals + [other]})397 if any(v for pt in pts for v in pt["values"]):398 stacked.append({"id": "sources",399 "title": "Créateurs ajoutés par source de découverte",400 "unit": "créateurs", "keys": keys, "points": pts})401402 # ---- répartitions (deltas = croissance réelle du stock sur la période) ----403 def _growth(total: int, added: int) -> dict:404 base = total - added405 p = _pct(total, base) if added else None406 if p is None:407 return {}408 return {"delta_pct": p}409410 breakdowns = [411 {"id": "platforms", "title": "Comptes reliés par plateforme",412 "kind": "donut",413 "items": [{"label": PLAT_LBL.get(r["platform"], r["platform"]),414 "value": r["c"]} for r in plat_rows]},415 {"id": "tiers", "title": "Créateurs par taille d'audience", "kind": "bar",416 "items": [{"label": lbl, "value": tier_total.get(t, 0),417 **_growth(tier_total.get(t, 0), tier_period.get(t, 0))}418 for t, lbl in TIER_LBL if tier_total.get(t)]},419 {"id": "niches", "title": "Top niches", "kind": "bar",420 "items": [{"label": NICHE_LBL.get(n, n), "value": v,421 **_growth(v, niche_period.get(n, 0))}422 for n, v in sorted(niche_total.items(), key=lambda x: -x[1])[:12]]},423 ]424 if type_total:425 breakdowns.append(426 {"id": "types", "title": "Par type de créateur", "kind": "bar",427 "items": [{"label": TYPE_LBL.get(t, t), "value": v}428 for t, v in sorted(type_total.items(), key=lambda x: -x[1])[:10]]})429430 # ---- distributions ----431 distributions = []432 reach_vals = [r["total_reach"] for r in creators if r["total_reach"]]433 if reach_vals:434 bins = []435 for lo, hi, lbl in REACH_BINS:436 n = sum(1 for v in reach_vals if v >= lo and (hi is None or v < hi))437 bins.append({"label": lbl, "value": n})438 distributions.append({"id": "audiences",439 "title": "Distribution des audiences connues "440 "(abonnés cumulés)",441 "unit": "créateurs", "bins": bins})442 if acc_per_creator:443 counts: dict[str, int] = {}444 for r in acc_per_creator:445 k = "5 et +" if r["np"] >= 5 else str(r["np"])446 counts[k] = counts.get(k, 0) + 1447 order = ["1", "2", "3", "4", "5 et +"]448 bins = [{"label": f"{k} compte{'s' if k != '1' else ''}",449 "value": counts[k]} for k in order if counts.get(k)]450 distributions.append({"id": "acc_per_creator",451 "title": "Comptes reliés par créateur",452 "unit": "créateurs", "bins": bins})453 if conf_rows:454 conf_bins = [(0.0, 0.6, "< 60 %"), (0.6, 0.7, "60 – 70 %"),455 (0.7, 0.8, "70 – 80 %"), (0.8, 0.9, "80 – 90 %"),456 (0.9, 1.01, "90 – 100 %")]457 bins = []458 for lo, hi, lbl in conf_bins:459 n = sum(1 for r in conf_rows if lo <= (r["confidence"] or 0) < hi)460 bins.append({"label": lbl, "value": n})461 if any(b["value"] for b in bins):462 distributions.append({"id": "confidence",463 "title": "Confiance du rattachement des comptes",464 "unit": "comptes", "bins": bins})465466 # ---- géographie ----467 geo = None468 if region_total:469 geo = {"title": "Par région déclarée (quand le créateur la rend publique)",470 "items": [{"label": k, "value": v} for k, v in471 sorted(region_total.items(), key=lambda x: -x[1])]}472473 # ---- heatmaps : calendrier (ajouts/jour) + horaire 7×24 (découvertes) ----474 heatmap = {"title": "Ajouts au répertoire",475 "cells": [{"date": d.isoformat(), "value": v}476 for d, v in sorted(adds_by_day.items())]}477 hourly = None478 if hour_cells:479 hourly = {"title": "Découvertes de créateurs par jour et heure "480 "(toute l'historique)",481 "cells": [{"dow": k[0], "hour": k[1], "value": v}482 for k, v in sorted(hour_cells.items())]}483484 # ---- tableaux ----485 top = sorted((r for r in creators if r["total_reach"]),486 key=lambda r: -r["total_reach"])[:100]487 tables = []488 if top:489 tables.append({490 "id": "top_creators", "title": "Top créateurs par audience connue",491 "columns": ["Créateur", "Taille", "Plateforme principale",492 "Abonnés cumulés", "Niches"],493 "rows": [[r["display_name"],494 dict(TIER_LBL).get(r["audience_tier"], r["audience_tier"]),495 PLAT_LBL.get(r["primary_platform"], r["primary_platform"]),496 r["total_reach"],497 ", ".join(NICHE_LBL.get(n, n)498 for n in (r["niches"] or "").split(",")[:2] if n)]499 for r in top]})500 if plat_rows:501 tables.append({502 "id": "platforms", "title": "Répartition par plateforme",503 "columns": ["Plateforme", "Comptes reliés", "Vérifiés",504 "Abonnés cumulés", "Audience moyenne / compte"],505 "rows": [[PLAT_LBL.get(r["platform"], r["platform"]), r["c"],506 r["nverif"] or 0, r["fol"] or 0,507 int(round((r["fol"] or 0) / r["nfol"])) if r["nfol"] else "—"]508 for r in plat_rows]})509 tables.append({510 "id": "niches", "title": "Répartition par niche",511 "columns": ["Niche", "Créateurs", "Ajoutés sur la période", "Part"],512 "rows": [[NICHE_LBL.get(n, n), v, niche_period.get(n, 0),513 f"{100 * v / max(1, n_active):.1f} %".replace(".", ",")]514 for n, v in sorted(niche_total.items(), key=lambda x: -x[1])]})515 # connecteurs & dernière synchro (journaux réels)516 sync_rows = con.execute(517 "SELECT source, COUNT(*) runs, SUM(COALESCE(added,0)) a, "518 "SUM(COALESCE(updated,0)) u, SUM(COALESCE(errors,0)) e, MAX(ts) last "519 "FROM sync_log GROUP BY source ORDER BY a DESC").fetchall()520 if sync_rows:521 def _fmt_ts(ts: str) -> str:522 dt = _local_dt(ts)523 return dt.strftime("%Y-%m-%d %H:%M") if dt else (ts or "")[:16]524 tables.append({525 "id": "connectors", "title": "Connecteurs & dernière synchronisation",526 "columns": ["Connecteur", "Synchros", "Fiches ajoutées",527 "Mises à jour", "Erreurs", "Dernière synchro (HE)"],528 "rows": [[r["source"], r["runs"], r["a"], r["u"], r["e"],529 _fmt_ts(r["last"])] for r in sync_rows]})530 if city_total:531 n_city = sum(city_total.values())532 tables.append({533 "id": "cities", "title": "Créateurs par ville déclarée",534 "columns": ["Ville", "Créateurs", "Part des fiches localisées"],535 "rows": [[c, v,536 f"{100 * v / max(1, n_city):.1f} %".replace(".", ",")]537 for c, v in sorted(city_total.items(), key=lambda x: -x[1])[:50]]})538539 # ---- records & faits marquants (générés depuis les données) ----540 records = []541 in_period = {d: v for d, v in adds_by_day.items() if p_from <= d <= p_to}542 if in_period:543 best = max(in_period.items(), key=lambda x: x[1])544 records.append({"label": "Jour record d'ajouts (période)",545 "value": f"{_fr_int(best[1])} créateurs",546 "date": best[0].isoformat()})547 records.append({"label": "Moyenne d'ajouts par jour (période)",548 "value": f"{added_cur / span:.1f} créateurs".replace(".", ",")})549 if adds_by_day:550 best_all = max(adds_by_day.items(), key=lambda x: x[1])551 if not in_period or best_all[0] not in in_period:552 records.append({"label": "Jour record d'ajouts (toute l'historique)",553 "value": f"{_fr_int(best_all[1])} créateurs",554 "date": best_all[0].isoformat()})555 if niche_period:556 bn = max(niche_period.items(), key=lambda x: x[1])557 records.append({"label": "Niche la plus dynamique (ajouts sur la période)",558 "value": f"{NICHE_LBL.get(bn[0], bn[0])} — {_fr_int(bn[1])}"})559 if plat_rows:560 records.append({"label": "Plateforme la plus reliée",561 "value": f"{PLAT_LBL.get(plat_rows[0]['platform'], plat_rows[0]['platform'])}"562 f" — {_fr_int(plat_rows[0]['c'])} comptes"})563 if top:564 records.append({"label": "Plus grande portée connue",565 "value": f"{top[0]['display_name']} — "566 f"{_fr_int(top[0]['total_reach'])} abonnés"})567 if acc_per_creator:568 bm = max(acc_per_creator, key=lambda r: r["np"])569 if bm["np"] >= 2:570 records.append({"label": "Créateur le plus multi-plateforme",571 "value": f"{bm['display_name']} — {bm['np']} plateformes"})572 if src_total:573 bs = max(src_total.items(), key=lambda x: x[1])574 records.append({"label": "Source de découverte la plus productive",575 "value": f"{bs[0]} — {_fr_int(bs[1])} créateurs"})576 if region_total:577 br = max(region_total.items(), key=lambda x: x[1])578 records.append({"label": "Région la plus représentée (déclarée)",579 "value": f"{br[0]} — {_fr_int(br[1])} créateurs"})580 if min_day:581 records.append({"label": "Première fiche au répertoire",582 "value": "ouverture du répertoire",583 "date": min_day.isoformat()})584 last_sync = con.execute(585 "SELECT ts FROM sync_log ORDER BY id DESC LIMIT 1").fetchone()586 if last_sync and last_sync["ts"]:587 dt = _local_dt(last_sync["ts"])588 if dt:589 records.append({"label": "Dernière synchronisation des connecteurs",590 "value": dt.strftime("%H:%M (heure de l'Est)"),591 "date": dt.date().isoformat()})592593 out = {594 "updated": datetime.now(TZ).isoformat(timespec="seconds"),595 "period": {"from": p_from.isoformat(), "to": p_to.isoformat(),596 "label": p_label},597 "kpis": kpis,598 "gauges": gauges,599 "series": series,600 "breakdowns": breakdowns,601 "heatmap": heatmap,602 "tables": tables,603 "records": records[:12],604 }605 if multiseries:606 out["multiseries"] = multiseries607 if stacked:608 out["stacked"] = stacked609 if distributions:610 out["distributions"] = distributions611 if geo:612 out["geo"] = geo613 if hourly:614 out["hourly"] = hourly615 return out616617618def dashboard(con: sqlite3.Connection, period: str = "30j",619 date_from: str = "", date_to: str = "") -> dict:620 """Point d'entrée avec cache mémoire (TTL 5 min par combinaison de période)."""621 key = (period, date_from, date_to)622 now = time.time()623 hit = _CACHE.get(key)624 if hit and now - hit[0] < CACHE_TTL:625 return hit[1]626 data = _build(con, period, date_from, date_to)627 if len(_CACHE) > 64: # borne dure (plages personnalisées illimitées)628 _CACHE.clear()629 _CACHE[key] = (now, data)630 return data631632633# --- insights par créateur (fiche « légendaire ») ------------------------------634635def creator_insights(con: sqlite3.Connection, doc: dict) -> dict:636 """Insights d'une fiche créateur : croissance (snapshots §13-14),637 engagement pondéré, rythme de publication et Ka Score composite /100.638639 AUCUNE stat inventée : tout provient des comptes rattachés (accounts.metrics640 remplis par les acteurs Apify) et des snapshots quotidiens. Un champ absent641 reste absent — pas d'estimation.642 """643 import math644645 from .db import follower_history646647 platforms = doc.get("platforms") or []648 reach = doc.get("total_reach") or sum(649 p.get("followers") or 0 for p in platforms) or 0650651 # croissance : série totale quotidienne (report dernière valeur connue)652 hist = follower_history(con, doc["id"], days=95)653 total = hist["total"]654655 def growth(days: int) -> dict | None:656 if len(total) < 2:657 return None658 last = total[-1]659 cutoff = (date.today() - timedelta(days=days)).isoformat()660 base = next((p for p in total if p["day"] >= cutoff), None)661 if base is None or base["day"] == last["day"] or not base["followers"]:662 return None663 delta = last["followers"] - base["followers"]664 return {"since": base["day"], "delta": delta,665 "pct": round(100 * delta / base["followers"], 2)}666667 # engagement moyen pondéré par l'audience de chaque plateforme668 weighted = [(p["metrics"].get("engagement_rate_pct"), p.get("followers") or 1)669 for p in platforms670 if isinstance(p.get("metrics"), dict)671 and p["metrics"].get("engagement_rate_pct") is not None]672 engagement = (round(sum(e * w for e, w in weighted)673 / sum(w for _, w in weighted), 2)674 if weighted else None)675676 # rythme de publication : somme des cadences hebdo déclarées par plateforme677 rates = [p["metrics"].get(k) for p in platforms678 if isinstance(p.get("metrics"), dict)679 for k in ("posts_per_week", "videos_per_week", "tweets_per_week")680 if isinstance(p["metrics"].get(k), (int, float))]681 pubs_week = round(sum(rates), 1) if rates else None682683 # dernière activité publique connue, toutes plateformes confondues684 last_dates = [str(p["metrics"].get(k)) for p in platforms685 if isinstance(p.get("metrics"), dict)686 for k in ("last_post_at", "last_video_at", "last_tweet_at",687 "last_broadcast_at", "last_video_published")688 if p["metrics"].get(k)]689 last_activity = max(last_dates) if last_dates else None690691 is_verified = any(p.get("verified") for p in platforms)692 live_now = any(isinstance(p.get("metrics"), dict)693 and p["metrics"].get("is_live_now") for p in platforms)694695 # Ka Score /100 : audience 40 (log), engagement 25, présence 20, rythme 10,696 # vérification 5 — comparable d'un créateur à l'autre, jamais inventé :697 # une composante inconnue vaut simplement 0.698 parts = {699 "audience": round(40 * min(1.0, math.log10(max(reach, 1)) / 7), 1),700 "engagement": round(25 * min(1.0, (engagement or 0) / 10), 1),701 "presence": round(20 * min(1.0, len(platforms) / 5), 1),702 "rythme": round(10 * min(1.0, (pubs_week or 0) / 3), 1),703 "verification": 5.0 if is_verified else 0.0,704 }705 top = max(platforms, key=lambda p: p.get("followers") or 0, default=None)706 return {707 "ka_score": round(sum(parts.values()), 1),708 "ka_score_parts": parts,709 "total_reach": reach or None,710 "growth_7d": growth(7),711 "growth_30d": growth(30),712 "avg_engagement_pct": engagement,713 "publications_per_week": pubs_week,714 "last_activity": last_activity,715 "platforms_count": len(platforms),716 "is_verified_somewhere": is_verified,717 "is_live_now": live_now,718 "top_platform": (top or {}).get("platform"),719 "history": hist,720 }721