# YouTube CSV Analytics Learning Loop for Creator Dashboards

Use this reference when a creator/publishing dashboard needs to turn manual YouTube Studio CSV exports into actionable product insights before OAuth/API automation is ready.

## Durable Pattern

Prioritize the creator decision loop before provider integrations:
1. Import CSV analytics snapshots manually.
2. Normalize provider metrics into backend tables.
3. Surface KPI pulse, rankings, trends, and insights in the dashboard/review flow.
4. Map external analytics posts to internal prepared packages/topics.
5. Only then automate YouTube/TikTok APIs.

This keeps product learning moving while OAuth, upload APIs, and platform review remain blocked or intentionally deferred.

## Supported YouTube CSV Shapes

### `Gesamtwerte.csv`

Daily aggregate export:

```csv
Datum,Aufrufe
2026-05-08,0
```

Implementation notes:
- Parse with `utf-8-sig`.
- Date is ISO date.
- `Aufrufe` is integer.
- Store one normalized daily aggregate row per date.
- Date range comes from min/max dates.

### `Tabellendaten.csv`

Video table export:

```csv
Videos,Videotitel,Veröffentlichungszeitpunkt des Videos,Dauer,Aufrufe,Wiedergabezeit (Stunden),Abonnenten,Impressionen,Klickrate der Impressionen (%)
Gesamt,Gesamt,,,329,0.31,-1,439,1.59
abc123,"Title, with comma","May 31, 2026",0:31,120,0.12,2,180,3.20
```

Implementation notes:
- Use Python `csv.DictReader`; never naive `split(',')` because titles can contain commas, quotes, and emoji.
- English publication dates such as `May 31, 2026` must be quoted in fixtures or parsed from real CSV quoting.
- Treat `Videos == Gesamt` as an aggregate/warning row, not an external post.
- Keep rows with empty views/watchtime but impressions as partial snapshots.
- Create/update `ExternalPost` by provider + external video id.
- Store one `AnalyticsPostSnapshot` per import batch/video id.

### `Diagrammdaten.csv`

Chart export may be header-only:

```csv
Datum,Videos,Videotitel,Veröffentlichungszeitpunkt des Videos,Dauer,Aufrufe
```

Implementation notes:
- Header-only files are valid empty imports; return a warning, not an error.
- Do not let empty chart CSVs abort a multi-file snapshot batch.

## Backend Model Shape

Prefer normalized tables rather than giving raw JSON to the frontend:
- `AnalyticsImportBatch`: provider/account/source/snapshot_at/date range/status/warnings/errors.
- `AnalyticsImportFile`: file name/hash/type/row counts/import status/warnings/errors.
- `ExternalPost`: provider video id, title, published_at, duration, optional linked video asset/topic/series.
- `AnalyticsDailyAggregate`: date + views + raw_json.
- `AnalyticsPostSnapshot`: snapshot metrics, derived fields, deltas and trend.

Existing raw `metrics_json` models can remain for compatibility, but frontend APIs should return normalized fields.

## Parser Functions Worth Keeping

- `detect_csv_type(headers)`
- `parse_number(value)`
- `parse_percent(value)`
- `parse_duration_seconds(value)`
- `parse_youtube_date(value)`
- `parse_youtube_daily_totals_csv(text)`
- `parse_youtube_video_table_csv(text)`
- `parse_youtube_chart_csv(text)`

## Snapshot + Delta Rules

Each import is a snapshot. For repeated YouTube video ids:
- Skip exact duplicate file hashes within already imported files.
- If a later import has changed data, create a new snapshot.
- Compute deltas versus previous snapshot:
  - views_delta / views_delta_pct
  - views_velocity_per_day
  - watch_time_delta
  - impressions_delta
  - subscriber_delta_change
  - ctr_change
  - retention_proxy_change
  - days_since_previous_snapshot
  - trend: rising/stable/slowing

Be careful with Python naive vs timezone-aware datetimes in delta math; normalize to UTC or strip consistently before subtraction.

## Creator Insights Rules

Simple median-based rules are useful before full ML:
- Winner: views above median and retention above median.
- Packaging opportunity: retention good but views low.
- Hook/CTR problem: impressions high but CTR low.
- Retention problem: views good but retention/avg view duration weak.
- Subscriber converter: subscribers per 1000 views above median.
- Topic opportunity: topic has few videos but above-average performance.
- Negative trend: latest snapshot strongly slows versus previous snapshot.

Show insights not only on Analytics Overview but also on the package Review page as "Learning Context".

## Frontend UX Pattern

Analytics page tabs:
- Overview
- Import
- Videos
- Trends
- Topics
- Insights
- Import History

Required components:
- Import wizard with multi-file input/drag-drop, type detection preview, import summary, warnings/errors.
- KPI cards from normalized overview endpoint.
- Daily views line chart from daily aggregates.
- Video ranking table with views, watch time, subs, impressions, CTR, avg view duration, retention proxy, deltas/trend.
- Bar charts for top videos by views/watch time/subs/retention.
- Scatter charts for retention-vs-views and CTR-vs-impressions.
- Import history with file counts, rows, warnings, status.
- Explicit "unlinked analytics post" state and manual mapping UI for ExternalPost -> prepared package/topic.

## Verification

Minimum gates:
- Backend tests for detection/parsing/empty CSV/Gesamt row/partial rows/duplicate hash/second snapshot/deltas/insights.
- Real multipart API import using all three CSV shapes.
- Frontend build.
- API smoke checks for analytics overview/videos/insights/imports.
- Browser snapshots for Analytics Overview, Trends, Videos, Import, and Review Learning Context.
- Visible-copy scan to avoid exposing internal automation names in creator-facing UI.

## Pitfalls

- Do not continue with YouTube/TikTok OAuth first when the user's goal is the learning loop; CSV snapshots unblock insights without provider auth.
- Do not report "import works" after only counting rows; verify normalized daily aggregates, external posts, snapshots, warnings, and visible charts.
- Do not discard partial video rows with empty views. Impression-only rows still matter for CTR/packaging analysis and mapping.
- Do not let video-table sums mismatch daily totals become fatal. Treat as warning because YouTube exports can differ by table scope/rounding.
- ECharts/Chart.js can inflate the main bundle. A green build with a large chunk warning is acceptable for MVP but document lazy-loading/code-splitting as follow-up.