Youtube-Analyst-Runbook

YouTube Analyst Runbook

Version: v3 — 2026-04-28

Operational playbook for the live pipeline. Assumes the setup skill has already run (6 MCPs connected, OAuth tokens on disk, secrets.env populated, Ads Manager wired up). Covers discovery → library scan → packaging analysis → matched-pair tests → findings reports → honest interpretation → maintenance → forward-track routing.

When to use

• Scanning / adding a new channel to the analysis set: "scan @handle", "add peer channel X"
• Rerunning packaging/matched-pair after new videos, a fresh ads CSV, or a code change to features
• Interpreting findings before writing a report ("how should I read d=+1.35?", "is this result real?")
• Weekly maintenance: OAuth re-auth (7-day Testing-mode expiry), fresh Google Ads CSV export + ingest, quota check

Out of scope: first-time Google Cloud / Ads Manager / API key bootstrap → use Youtube-Analyzer-Setup-Skill.

Paths you'll use a lot

~/openclaw-mcp-servers/youtube-data/          MCP + scripts/ dir
~/openclaw-mcp-servers/youtube-data/.venv/    venv for running scripts
~/openclaw-output/youtube-analyst/             all outputs
  ├─ week-1/<market>/<channel>/library.xlsx    one per scanned channel
  ├─ week-2/                                   packaging + matched-pair reports
  ├─ thumbnails/<channel-slug>/<video_id>.jpg  downloaded thumbnails
  ├─ ads-exports/                              Google Ads UI CSV dumps (Danmar only)
  ├─ roi/                                      per-video ROI joins
  └─ config/danmar_collab_video_ids.txt        optional whitelist, one video_id per line

Playbook spec (source of truth for the full pipeline design): /home/dmmdea/AI Ecosystem/OpenClaw/docs/superpowers/specs/2026-04-21-youtube-expert-analyst-playbook.md

Run scripts with the youtube-data venv

Every script must be invoked with the youtube-data MCP's venv (has pandas, openpyxl, cv2, google-api-python-client + the editable openclaw_shared). From ~/openclaw-mcp-servers/youtube-data/:

cd ~/openclaw-mcp-servers/youtube-data
./.venv/bin/python -m scripts.<script_name>

-m scripts.<name> (not ./.venv/bin/python scripts/<name>.py) is the correct invocation — scripts now import the sibling scripts._danmar_ads_helper module, and the -m form sets package context.

Quota & rate-limit discipline

Two distinct limits are in play; conflate them at your peril.

1. YouTube Data API quota — global per-project, 10k units/day. Track via openclaw-quota MCP (wired into every Data API tool). Always check before a bulk run:

./.venv/bin/python -c "from openclaw_shared.quota_budget import QuotaBudget; print(QuotaBudget('youtube_data').status())"

Costs: channel_info 1 unit, channel_videos ⌈N/50⌉, videos_batch_stats ⌈N/50⌉. A single library scan ≈ 50 units; 8 channels ≈ 400 units — well under daily cap.

2. YouTube caption-endpoint IP rate limit — a per-IP throttle on timedtext (captions), unrelated to Data API quota. Symptoms when tripped:

• youtube-transcript-api raises IpBlocked
• yt-dlp returns HTTP Error 429: Too Many Requests even with --impersonate chrome (curl_cffi)

Notes:

• Single-video fetches during normal use are fine. ~10 back-to-back bulk pulls was enough to trip it on Dell 2026-04-21.
• yt-dlp's info-JSON endpoints (metadata, search) are NOT affected — only timedtext/captions.
• Reset window: 1–6 h, per-IP.

Mitigations for any bulk transcript job (transcript-feature track, recency cross-checks, peer-cluster expansion):

• Space requests ≥ 30 s apart
• Route through a mobile hotspot or VPN for fast bulk passes
• Wait it out (1–6 h) if you've already tripped it

The youtube MCP (~/openclaw-mcp-servers/youtube/) uses the same two transports, so it surfaces the same error — there's no "use a different MCP" escape hatch.

Workflow 1 — Scan a new channel

cd ~/openclaw-mcp-servers/youtube-data
./.venv/bin/python -m scripts.library_scan @ChannelHandle
# OR a channel_id:
./.venv/bin/python -m scripts.library_scan UCxxxxxxxxxxxxxxxxxxxxxx

Writes ~/openclaw-output/youtube-analyst/week-1/<slug>/library.xlsx with all videos + stats + outlier_score. Quota cost: 1 unit for channel resolve + ceil(N/50) units for the video list + ceil(N/50) for batched stats hydration. Check before running:

./.venv/bin/python -c "from openclaw_shared.quota_budget import QuotaBudget; print(QuotaBudget('youtube_data').status())"

Then download thumbnails (no API cost, CDN scrape):

./.venv/bin/python -m scripts.download_thumbnails @ChannelHandle <thumb_slug>

Workflow 2 — Add a peer channel to competitor analysis

1. Scan the channel (Workflow 1).
2. Add it to the CHANNEL_INFO dict in scripts/competitor_title_matched_pair.py with market + tier.
3. For packaging comparison, also add an entry to CHANNEL_SPECS in scripts/packaging_analysis.py.
4. Rerun Workflow 3.

New peer channels do NOT get ad-spend data — they keep raw outlier_score. This asymmetry is intentional; see feedback_cross_reference_ads_before_citing_outliers.md.

Workflow 3 — Rerun packaging + matched-pair after new data

Three scripts, always in this order (each reads artifacts from the previous):

cd ~/openclaw-mcp-servers/youtube-data
./.venv/bin/python -m scripts.title_matched_pair_analysis      # Danmar-only, organic score
./.venv/bin/python -m scripts.packaging_analysis               # Danmar recent vs peer outliers
./.venv/bin/python -m scripts.competitor_title_matched_pair    # Cross-channel d-matrix

Outputs land in ~/openclaw-output/youtube-analyst/week-2/:

• title_analysis.xlsx + FINDINGS.md (within-channel top10% vs bottom50%)
• packaging_analysis.xlsx + PACKAGING_FINDINGS.md (head-to-head vs peers, 56-feature vector)
• competitor_title_analysis.xlsx + COMPETITOR_FINDINGS.md (universal-vs-Danmar-specific effects)

Danmar uses organic_outlier_score; peers use raw outlier_score. Collab videos (title mentions @handle or contains "saludo especial"/"ft."/"colaboración", or listed in ~/openclaw-output/youtube-analyst/config/danmar_collab_video_ids.txt) are automatically dropped from Danmar cohorts.

Workflow 4 — Refresh ads CSV (weekly or before a re-analysis)

1. In Google Ads UI (https://ads.google.com), switch to the Manager account (143-213-9099), drill into 212-310-0176.
2. Export fresh CSVs for each report type — Campaigns, Ads, Ad groups, Asset groups, Promotions (from YouTube Studio). Leave default column sets.
3. Drop all CSVs into ~/openclaw-output/youtube-analyst/ads-exports/ (replace prior files).
4. When Basic access lands, the MCP path (ads_video_performance, ads_daily_spend, etc.) replaces the CSV path — analyst-side scripts don't need changes since the schema is identical.

Trigger a rerun of top5_with_ads.py + Workflow 3 after new CSVs land — the organic outlier scores and per-video ROI change with fresh data.

When Google Ads Basic access lands (email approval pending to dmmdea@hotmail.com), Workflow 4's CSV step is replaced by a live API path — see Track A in ~/openclaw-output/youtube-analyst/CONTINUATION_NON_HAILO.md for the cutover procedure (smoke test, reconcile against CSV totals, swap top5_with_ads.py + per_video_roi_generator.py to live-query, keep CSV as cached fallback).

Workflow 5 — Weekly OAuth re-auth (7-day Testing-mode expiry)

Symptom that it's time: any youtube-analytics or google-ads tool returns invalid_grant.

/home/dmmdea/openclaw-mcp-servers/youtube-analytics/.venv/bin/python \
  /home/dmmdea/openclaw-mcp-servers/youtube-analytics/scripts/oauth_auth.py

Critical gotcha: at the Google account-picker, pick the brand account that owns the YouTube channel, NOT the personal Google account. Wrong selection silently queries the wrong channel.

Fix the 7-day cycle permanently by moving the Cloud Console OAuth consent screen from Testing to Production (requires Google brand verification).

Workflow 6 — Interpreting findings (statistical-discipline contract)

Every claim in a report must carry its confidence. These rules are non-negotiable:

1. SAMPLE_FLOOR = 30 per cohort. If either top or bottom cohort has fewer than 30 videos, the result is tagged HYPOTHESIS ONLY, n<30. Never promote a hypothesis-only finding to a confirmed conclusion in a report. Danmar currently has 68 scored videos → top cohort = 6. All Danmar-internal findings are hypothesis-only until the library grows past ~300.
2. Cohen's d thresholds: |d|<0.2 negligible, 0.2–0.5 small, 0.5–0.8 medium, 0.8+ large. Report the size, don't just say "there's an effect."
3. CI excludes zero = the effect direction is stable across bootstrap resamples (higher confidence). CI crosses zero = directionally uncertain.
4. Cross-reference ads before citing outlier wins on Danmar. Raw outlier_score on a promoted video conflates packaging quality with paid boost. Always also report ad_views_claimed and ad_spend_usd for any specific video called out as "working well". See feedback_cross_reference_ads_before_citing_outliers.md.
5. Asymmetric framing. Danmar findings are filtered through organic_outlier_score + collab-drop. Peer findings are raw. State this asymmetry in every report that compares the two.

Workflow 7 — Quick recency cross-checks

Before writing advice based on historical patterns, verify the pattern still holds in recent content — creators self-correct. Example: in Week 1 analysis, Danmar's Colombia-peso pricing was flagged as a weakness; by the time of Week 2 it had already dropped from 15% to 0% in recent titles.

./.venv/bin/python -m scripts.recent_content_analysis     # last 30 videos only

If the historical weakness has already been fixed, drop it from the recommendations.

Workflow 8 — Refreshing a specific subset

• Just thumbnails, no stats refresh: ./.venv/bin/python -m scripts.download_thumbnails @handle <slug>
• Just the Danmar top-5 with ads snapshot: ./.venv/bin/python -m scripts.top5_with_ads
• Revenue-weighted report (market CPM-weighted findings): ./.venv/bin/python -m scripts.revenue_weighted_report
• Resolve a batch of candidate handles → channel_ids: ./.venv/bin/python -m scripts.resolve_candidates

Workflow 9 — Hailo-accelerated features (when device present)

Packaging analysis automatically picks up Hailo for richer thumbnail features (extra CLIP embeddings, OCR entity extraction) when /dev/hailo0 exists. No manual switch — openclaw_shared/features/thumbnail.py calls extract_thumbnail_features(path, backend=maybe_hailo_backend()), which returns a Hailo backend if available and None otherwise. The OpenCV-only path remains the deterministic fallback; both paths emit the same dict schema, so downstream matched-pair stats don't care which produced the features.

Verify Hailo health before a bulk run that's expected to use it:

ls /dev/hailo*                          # expect /dev/hailo0
hailortcli fw-control identify          # expect Board=Hailo-8, Firmware 4.23.0

If either fails, packaging silently falls back to OpenCV — that's correct behaviour, but you'll want to know: PACKAGING_FINDINGS.md will show fewer feature columns than a Hailo-enabled run, and any week-3-hailo/ deliverables will skip the Hailo-only columns.

Hailo runtime, DKMS driver, HEF management, kernel-patch lifecycle, content-addressed Vision Cache, and the OCR-quality pipeline are out of scope for this skill. They live in the sibling repo dmmdea/hailo-youtube-stack-mcp (Hailo-Stack-Skill, hailo-vision MCP, openclaw_shared.cache.VisionCache, DKMS patch). Do not reach into ~/openclaw-mcp-servers/hailo-vision/ or /usr/src/hailo_pci-*/ from analyst scripts — go through maybe_hailo_backend() (and optionally pass a cache=VisionCache(...) for the 1060× speedup on repeat scans).

Workflow 10 — Writing a findings report

Each analysis emits a structured FINDINGS.md (or PACKAGING_FINDINGS.md / COMPETITOR_FINDINGS.md) alongside its xlsx workbook. Format must satisfy the statistical-discipline contract from Workflow 6 plus the playbook spec § 7. Don't free-form: deviating breaks the user's downstream Excel/Looker links and erodes trust in the cohort labels.

Required sections (in order)

1. Title + run metadata. Deliverable name · channel(s) analysed · date range · cohort sizes · script path that generated it.
2. Confidence summary at top. A short block stating: (a) % of claims that carry effect-size + CI, (b) minimum cohort size n_min, (c) algorithm-snapshot date with the playbook §7 drift disclaimer ("Generated 2026-Qx; YouTube algorithm changes quarterly; re-validate before applying to next quarter"). The drift disclaimer goes at the top, not buried in a footer — readers must see it before any finding.
3. Headline finding. ONE sentence with effect direction + size + CI + sample size. Example: "In the recent-30-vs-Danmar-back-catalogue cohort (n_top=8, n_bottom=34), titles with year-mention 2026 carry a +0.62 d advantage on organic_outlier_score (95% CI 0.18–1.06, HYPOTHESIS ONLY n<30 in top cohort)."
4. Findings table. One row per claim. Columns: feature · cohort A vs cohort B · n_A · n_B · Cohen's d · 95% CI · effect-size verbal label (negligible / small / medium / large per Workflow 6 §2) · HYPOTHESIS ONLY flag if either n<30 · short prose explaining the direction.
5. Asymmetric-framing block when comparing Danmar to peers: state explicitly that Danmar uses organic_outlier_score + collab-drop while peers use raw outlier_score. Reference feedback_cross_reference_ads_before_citing_outliers.md.
6. Caveats list. Specific samples-too-small calls, anomalous channels excluded, ads-spend-promoted videos filtered, content-trajectory drift since the snapshot, etc.
7. Recommendations (only if the data supports them) — each with effect size + CI + a backtestable hypothesis. A/B-able via YouTube Test & Compare wherever possible.

Mandatory rules

• No "score X out of 100". Emit feature vectors, not scores. Uncalibrated scoring is astrology — see playbook § 0.
• No "do X to go viral". Recommendations are testable, not prescriptive.
• HYPOTHESIS ONLY cannot be promoted in the same report. If a finding is HYPOTHESIS in the headline, it must remain HYPOTHESIS in the recommendations.
• Every numeric claim cites its sample size. No "trends suggest" prose without n.
• Cross-reference ads on Danmar specifics. Any specific Danmar video called out as "working well" must report ad_views_claimed + ad_spend_usd alongside outlier_score (per feedback_cross_reference_ads_before_citing_outliers.md).
• Don't rename existing column headers in the xlsx workbook (per Operational discipline). Recommendations live in markdown; the workbook is reference data only — additive columns OK, renames break the user's downstream links.
• Recency cross-check before publishing — Workflow 7 — verify the claimed weakness/strength is still in recent content before citing it.

Skeleton

Use the most-recent FINDINGS.md from ~/openclaw-output/youtube-analyst/week-2/ as the canonical template. The structure has stabilized over weeks 1–2 and downstream tools (Looker, the user's eyeballs) expect it. Match section order, table columns, and the HYPOTHESIS ONLY placement exactly.

Operational discipline

Invariants that hold across every run, every track, every report:

• Standalone-first. Don't introduce cluster, DB, or external-service dependencies. If a track needs external data, fetch once and cache to ~/openclaw-output/youtube-analyst/cache/<source>/. Every analysis must be reproducible on a single node.
• Checkpoint to disk. Anything multi-hour writes intermediate state under ~/openclaw-output/youtube-analyst/<workflow>/ so an interruption (kernel update, OAuth expiry, network blip) doesn't lose work. Subsequent runs check markers (phase_N_done.marker) before redoing expensive steps.
• Quota first. Call openclaw-quota before any YouTube/Brave op above single-call cost. If the budget is short, abort early — never half-run a bulk job and leave artifacts in inconsistent state.
• Quality mode is default. Local optimizes for quality; overnight runs are expected. Don't pessimize accuracy to fit a 10-minute window.
• Don't regress W1/W2 deliverables. Existing sheets and column names in library.xlsx, title_analysis.xlsx, packaging_analysis.xlsx, competitor_title_analysis.xlsx, per_video_roi.xlsx are reference points the user has built downstream Excel/Looker links against. Add columns; never rename them.
• Re-auth weekly while OAuth is in Testing. See Workflow 5. The 7-day refresh-token expiry is a hard floor; schedule a reminder.

When to ask the user before acting

• Adding peers whose market/role isn't obvious from their handle
• Expanding the peer cluster beyond 20 channels (statistical noise vs cost trade-off)
• Any change to sheet/column naming conventions
• Cross-platform expansion (Meta, TikTok) — scope is weeks, not hours
• Shipping any skill update to GitHub (Drive → verify → GitHub → memory pipeline)
• When a re-run flips the sign of a previously validated finding — the data may be right, but verify before publishing

Active tracks

Forward-roadmap of follow-on work. Each track is self-contained — none strictly blocks another. Priority and effort live in ~/openclaw-output/youtube-analyst/SKILL_PLAN.md; per-track invariants in ~/openclaw-output/youtube-analyst/CONTINUATION_NON_HAILO.md.

• Track A — Google Ads Basic-access live migration. Replace CSV ingest with live API. Blocked on Google approval email to dmmdea@hotmail.com. Cutover steps in CONTINUATION § Track A; pointer also in Workflow 4.
• Track B — Collab video detection. is_collab flag + filter in matched-pair (different experiment type from organic packaging). New module openclaw_shared/features/video_type.py. ~1 day.
• Track C — Transcript / hook / topic analysis (= playbook Week 3). Topic tags + hook category + sentiment + words-per-minute from existing transcripts at ~/openclaw-output/transcripts/. New module openclaw_shared/features/transcript.py. Rate-limit aware per the Quota & rate-limit discipline section above — bulk transcript pulls trip the per-IP caption-endpoint throttle.
• Track D — Weekly re-scan automation. weekly_rescan.py + diff vs last week + telegram notification. Sunday 04:00 systemd timer. ~1 day.
• Track E — Peer cluster expansion 7 → 15-20. Stronger matched-pair stats. Candidate handles in project_user_channel_danmar_auto_reviews.md. Ask user before adding peers whose market/role isn't obvious.
• Track F — Skill feedback-upgrade loop. Adversarial regression check on W1/W2 invariants weekly (Cohen's d sign-stability, SAMPLE_FLOOR, Danmar-vs-peer asymmetry, Venezuela audience share floor).
• Track H — OpenClaw integration surfacing. Wire analyst sub-agents to Sonnet conductor via ~/.claude/scripts/openclaw-subagents-mcp.js so Telegram can trigger scans/analyses.
• Track G — Cross-platform (Meta / TikTok / X / LinkedIn). Deferred per playbook §9 + §12. Phase BA-8 using shared infra. Ask user first — scope is weeks per platform.

Trigger-phrase routing: "weekly rescan" / "drift report" → D · "expand peer cluster" / "add new peers" → E · "build transcript features" / "hook analysis" → C · "collab detection" / "filter collabs" → B · "regression check" / "invariant audit" → F · "wire to telegram" / "expose to conductor" → H.

Troubleshooting

Related memory files (read these before publishing any finding)

Doctrine — HOW to operate:

• feedback_quality_over_time_local_ecosystem.md — quality-mode default, overnight runs expected
• feedback_standalone_first_design.md — every capability must run on a single host
• feedback_cross_reference_ads_before_citing_outliers.md — the #1 analytical rule
• feedback_learn_and_evolve.md — extract invariants when porting; re-validate, don't blind-replicate
• feedback_skill_shipping_protocol.md — Drive → verify → GitHub → memory ship cycle (mandatory)

Skill cross-reference:

• reference_youtube_analyst_skills.md — the 2-skill layout (Setup + Runbook), Drive + GitHub locations

MCP references:

• reference_openclaw_quota_mcp.md — shared quota tracker (MUST respect)
• reference_openclaw_youtube_data_mcp.md — Data API tools + quota costs
• reference_openclaw_youtube_analytics_mcp.md — owner-side Analytics MCP + OAuth lifecycle
• reference_openclaw_google_ads_mcp.md — ad-spend MCP + CSV ingest fallback
• reference_openclaw_packaging_analysis.md — feature extractors + matched-pair stats module
• reference_brave_search_mcp.md — web discovery endpoints + costs
• reference_youtube_mcp.md — yt-dlp transcripts + metadata

Project context:

• project_user_channel_danmar_auto_reviews.md — the subject channel (WHY of everything); revenue + audience facts; biggest known packaging gaps
• project_openclaw_install.md — infrastructure state

Active plan (this skill's roadmap):

• ~/openclaw-output/youtube-analyst/SKILL_PLAN.md — maintenance backlog (M1-M8) + feature roadmap (F1-F10)
• ~/openclaw-output/youtube-analyst/CONTINUATION_NON_HAILO.md — the 8 tracks A-H with implementation notes

Sibling skill

Youtube-Analyzer-Setup-Skill handles one-time bootstrap (Cloud Console APIs, OAuth consent flow, Ads Manager wrapper, developer token, MCP registrations). If any of those is missing, run it first.