Docs/Skills and code/src/

src/analysis/within-channel-performance-report.js

Code127 lines9.0 KBsrc/

import fs from "node:fs";
import path from "node:path";
import { latestWithinChannelRun } from "./within-channel-performance-store.js";

function csvCell(value) {
  if (value === null || value === undefined) return "";
  const text = typeof value === "string" ? value : String(value);
  return /[",\r\n]/.test(text) ? `"${text.replaceAll('"', '""')}"` : text;
}

function writeCsv(filePath, rows, columns) {
  const header = columns ?? [...new Set(rows.flatMap(row => Object.keys(row)))];
  fs.writeFileSync(filePath, [header.join(","), ...rows.map(row => header.map(key => csvCell(row[key])).join(","))].join("\n") + "\n", "utf8");
}

function safeStamp(value) {
  return String(value).replaceAll(":", "-").replaceAll(".", "-").replace(/[^A-Za-z0-9_-]/g, "_");
}

const percent = value => value == null ? "n/a" : `${(value * 100).toFixed(1)}%`;
const integer = value => value == null ? "n/a" : Number(value).toLocaleString("en-US");

export function writeWithinChannelPerformanceReports(db, root = process.cwd(), runId = null) {
  const run = runId
    ? db.prepare(`SELECT * FROM within_channel_analysis_runs WHERE run_id=?`).get(runId)
    : latestWithinChannelRun(db);
  if (!run) throw new Error("No within-channel analysis run found. Run npm run within-channel:analyze first.");

  const dir = path.join(root, "reports", "within-channel-performance", safeStamp(run.run_id));
  fs.mkdirSync(dir, { recursive: true });
  const profiles = db.prepare(`SELECT * FROM within_channel_channel_profiles WHERE run_id=? ORDER BY outlier_candidate_count DESC, median_views DESC, channel_name COLLATE NOCASE`).all(run.run_id);
  const outliers = db.prepare(`SELECT * FROM within_channel_video_outliers WHERE run_id=? ORDER BY
    CASE WHEN lifetime_outlier_label LIKE 'strong_outlier%' THEN 0 WHEN lifetime_outlier_label LIKE 'potential_outlier%' THEN 1 ELSE 2 END,
    lifetime_log_robust_z DESC, lifetime_relative_ratio DESC, view_count DESC`).all(run.run_id);
  const benchmarks = db.prepare(`SELECT * FROM within_channel_age_cohort_benchmarks WHERE run_id=? ORDER BY CASE age_cohort WHEN '0_7d' THEN 1 WHEN '8_30d' THEN 2 WHEN '31_90d' THEN 3 WHEN '91_365d' THEN 4 WHEN '1_3y' THEN 5 WHEN '3y_plus' THEN 6 ELSE 7 END`).all(run.run_id);
  const queueEvidence = db.prepare(`SELECT * FROM within_channel_queue_evidence WHERE run_id=? ORDER BY queue_name, channel_name COLLATE NOCASE`).all(run.run_id);

  writeCsv(path.join(dir, "channel-profiles.csv"), profiles);
  writeCsv(path.join(dir, "video-outliers.csv"), outliers);
  writeCsv(path.join(dir, "age-cohort-benchmarks.csv"), benchmarks);
  writeCsv(path.join(dir, "queue-evidence-observational.csv"), queueEvidence);

  const countBy = (rows, key) => Object.fromEntries([...new Set(rows.map(row => row[key]))].sort().map(value => [value, rows.filter(row => row[key] === value).length]));
  const summary = {
    run_id: run.run_id,
    generated_at: run.generated_at,
    analysis_version: run.analysis_version,
    channels_analyzed: profiles.length,
    linked_unique_channel_video_pairs: run.linked_unique_channel_video_pairs,
    eligible_video_records: run.eligible_video_records,
    reliable_dated_video_records: run.reliable_dated_video_records,
    profiles_by_sample_assessment: countBy(profiles, "sample_assessment"),
    lifetime_outlier_labels: countBy(outliers, "lifetime_outlier_label"),
    age_adjusted_outlier_labels: countBy(outliers, "age_adjusted_outlier_label"),
    age_cohort_benchmarks: benchmarks,
    queue_evidence_rows: queueEvidence.length,
    queue_evidence_is_observational_only: queueEvidence.every(row => row.integration_status === "observational_only" && Number(row.score_effect) === 0),
    existing_queue_scores_modified: false,
    report_directory: dir
  };
  fs.writeFileSync(path.join(dir, "summary.json"), JSON.stringify(summary, null, 2) + "\n", "utf8");

  const strongestVideos = outliers.filter(row => row.view_count != null && (String(row.lifetime_outlier_label).startsWith("strong_outlier") || String(row.lifetime_outlier_label).startsWith("potential_outlier"))).slice(0, 30);
  const bestProfiles = [...profiles].filter(row => row.eligible_view_count >= 5).sort((a, b) => (b.strong_outlier_count - a.strong_outlier_count) || (b.outlier_candidate_count - a.outlier_candidate_count) || ((b.median_views ?? -1) - (a.median_views ?? -1))).slice(0, 25);
  const md = [
    "# Within-channel video performance analysis",
    "",
    `- **Run:** \`${run.run_id}\``,
    `- **Generated:** ${run.generated_at}`,
    `- **Analysis version:** \`${run.analysis_version}\``,
    `- **Channels analysed:** ${integer(profiles.length)}`,
    `- **Unique channel-video pairs:** ${integer(run.linked_unique_channel_video_pairs)}`,
    `- **Videos with usable view counts:** ${integer(run.eligible_video_records)}`,
    `- **Videos with reliable dates and usable views:** ${integer(run.reliable_dated_video_records)}`,
    `- **Queue evidence rows:** ${integer(queueEvidence.length)} (five per channel when all channels are present)`,
    "",
    "## Important interpretation limits",
    "",
    "- Relationship rows were deduplicated by `(channel_url, video_id)`; a relationship row is not treated as an independent upload.",
    "- Lifetime views and age-adjusted views-per-day are separate signals. Neither proves current momentum or causation.",
    "- Age-adjusted comparisons use only dates whose stored precision is exact and whose normalization method is not relative/unrecognized. Approximate dates remain available as raw metadata but are excluded from age-adjusted outlier labels.",
    "- Robust outlier labels are exploratory flags. They are not claims of viral causation or guaranteed future performance.",
    "- Channel sample bands are engineering guidance, not universal statistical thresholds.",
    "- Queue evidence is stored separately and marked `observational_only`; **no existing queue score, rank, or score history was changed**.",
    "",
    "## Channel sample coverage",
    "",
    "| Sample assessment | Channels |",
    "|---|---:|",
    ...Object.entries(summary.profiles_by_sample_assessment).map(([label, count]) => `| ${label} | ${integer(count)} |`),
    "",
    "## Channel profiles worth inspecting",
    "",
    "| Channel | Videos with views | Median views | P90 views | Top 5 share | Reach-threshold share | Lifetime candidates | Reliable dates |",
    "|---|---:|---:|---:|---:|---:|---:|---:|",
    ...bestProfiles.map(row => `| ${String(row.channel_name ?? row.channel_url).replaceAll("|", "\\|")} | ${integer(row.eligible_view_count)} | ${integer(row.median_views)} | ${integer(row.p90_views)} | ${percent(row.top_5_view_share)} | ${percent(row.reach_threshold_share)} | ${integer(row.outlier_candidate_count)} | ${integer(row.reliable_dated_view_count)} |`),
    "",
    "## Highest-ranked lifetime outlier candidates",
    "",
    "| Channel | Video | Views | Channel median | Relative ratio | Robust z | Label | Age-adjusted label |",
    "|---|---|---:|---:|---:|---:|---|---|",
    ...strongestVideos.map(row => `| ${String(row.channel_name ?? row.channel_url).replaceAll("|", "\\|")} | [${String(row.title ?? row.video_id).replaceAll("|", "\\|")}](${row.url || `https://www.youtube.com/watch?v=${row.video_id}`}) | ${integer(row.view_count)} | ${integer(row.channel_median_views)} | ${row.lifetime_relative_ratio == null ? "n/a" : `${Number(row.lifetime_relative_ratio).toFixed(2)}×`} | ${row.lifetime_log_robust_z == null ? "n/a" : Number(row.lifetime_log_robust_z).toFixed(2)} | ${row.lifetime_outlier_label} | ${row.age_adjusted_outlier_label} |`),
    "",
    "## Age-cohort reference population",
    "",
    "| Age cohort | Videos | Median views/day | P90 views/day | Status |",
    "|---|---:|---:|---:|---|",
    ...benchmarks.map(row => `| ${row.age_cohort} | ${integer(row.eligible_video_count)} | ${integer(row.median_views_per_day)} | ${integer(row.p90_views_per_day)} | ${row.benchmark_status} |`),
    "",
    "## Files",
    "",
    "- `channel-profiles.csv`: one row per channel, including sample size, distribution, concentration, coverage, and date reliability.",
    "- `video-outliers.csv`: one row per unique channel-video pair, with separate lifetime and age-adjusted signals.",
    "- `age-cohort-benchmarks.csv`: descriptive views-per-day baselines for broad video-age cohorts.",
    "- `queue-evidence-observational.csv`: queue-specific candidate evidence, explicitly not used to change scores.",
    "- `summary.json`: machine-readable run and coverage summary.",
    "",
    "## Re-run guidance",
    "",
    "Run `npm run within-channel:analyze` to create a new immutable analysis run. Run `npm run within-channel:report` to regenerate reports from the latest run without recalculating metrics.",
    ""
  ].join("\n");
  fs.writeFileSync(path.join(dir, "summary.md"), md, "utf8");
  console.log("[within-channel:report] Reports written", { runId: run.run_id, directory: dir, profiles: profiles.length, videoRows: outliers.length, queueEvidenceRows: queueEvidence.length });
  return { runId: run.run_id, directory: dir, profileCount: profiles.length, outlierRows: outliers.length, queueEvidenceRows: queueEvidence.length, summary };
}

Shown as it is in the repository at build time (2026-11-03); the home directory appears as <home>. If this page and the file differ, the file is right. Back to the catalogue.