Docs/Skills and code/skill ecosystem-rater
.claude/skills/ecosystem-rater/scripts/rate.mjs
What the file says about itself
Deterministic ecosystem rating from data already in an ecosystem database (plus optional probe-search results). node rate.mjs --db <data/x.db | ecosystem name> -> ecosystem-ratings/<eco>/metrics.json node rate.mjs --all -> every data/*.db,…
#!/usr/bin/env node
// Deterministic ecosystem rating from data already in an ecosystem database (plus optional probe-search results).
// node rate.mjs --db <data/x.db | ecosystem name> -> ecosystem-ratings/<eco>/metrics.json
// node rate.mjs --all -> every data/*.db, plus ecosystem-ratings/scorecard.md
// [--weights demand=2,room=1,...] overrides bands.json weights for the composite only
// Same inputs always give the same output: ages are measured at each row's own observation time, never "now".
import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import {
parseArgs, resolveDb, listDbs, ecosystemName, openDb, tableExists, outDir, readJson,
ageAtObservation, median, mean, round, OUT_ROOT, DAY_MS
} from "./lib.mjs";
const BANDS = readJson(path.join(path.dirname(fileURLToPath(import.meta.url)), "bands.json"));
const DIMENSIONS = ["demand", "room", "evergreen", "pressure", "monetization"];
const SHORT_MAX_SECONDS = 60;
// Minimum sample sizes; below these a metric is reported as insufficient and not scored.
const MIN = {
queries: 15, top100: 40, small: 20, young: 20, breakout: 20, backCatalogue: 30, growthVideos: 200,
cadence: 30, country: 30, core: 50
};
// ---------------------------------------------------------------- loading
function loadEcosystem(dbPath) {
const db = openDb(dbPath);
const eco = ecosystemName(dbPath);
// Relevance: the latest successful LLM qualification per channel. The rules table is stale in several DBs.
const cls = new Map();
if (tableExists(db, "channel_qualification_llm_results")) {
for (const r of db.prepare(`
SELECT r.channel_url, r.classification FROM channel_qualification_llm_results r
WHERE r.processing_status='succeeded' AND r.created_at = (
SELECT max(r2.created_at) FROM channel_qualification_llm_results r2
WHERE r2.channel_url=r.channel_url AND r2.processing_status='succeeded')`).all()) cls.set(r.channel_url, r.classification);
}
const channels = new Map();
for (const r of db.prepare(`
SELECT channel_url, subscriber_count subs, joined_date, country, observed_at FROM channel_metric_snapshots s
WHERE id = (SELECT max(id) FROM channel_metric_snapshots s2 WHERE s2.channel_url=s.channel_url)`).all()) {
const ageYears = r.joined_date ? (new Date(r.observed_at) - new Date(r.joined_date)) / (365.25 * DAY_MS) : null;
channels.set(r.channel_url, { ...r, ageYears: Number.isFinite(ageYears) ? ageYears : null });
}
const totalChannels = db.prepare("SELECT count(*) n FROM channels").get().n;
// Each video at its latest snapshot that carries a usable publish date.
const videos = new Map();
for (const v of db.prepare(`
SELECT v.video_id, v.channel_url, v.title, s.view_count views, s.published_at, s.published_at_text, s.observed_at,
COALESCE(s.duration_seconds, v.duration_seconds) dur
FROM videos v JOIN video_metric_snapshots s ON s.id = (
SELECT max(id) FROM video_metric_snapshots s2
WHERE s2.video_id=v.video_id AND (s2.published_at IS NOT NULL OR s2.published_at_text IS NOT NULL))`).all()) {
v.age = ageAtObservation(v.published_at, v.published_at_text, v.observed_at);
if (v.age != null) videos.set(v.video_id, v);
}
// Channel feeds: everything except the Popular sort (which over-represents hits). Unsorted is the default newest-first tab.
const feeds = new Map();
for (const r of db.prepare(`SELECT DISTINCT channel_url, video_id FROM channel_videos WHERE source NOT IN ('Popular','popular')`).all()) {
const v = videos.get(r.video_id);
if (!v || v.views == null) continue;
if (!feeds.has(r.channel_url)) feeds.set(r.channel_url, new Map());
feeds.get(r.channel_url).set(v.video_id, v);
}
// View growth between the first and last snapshot of the same video (only exists after repeat snapshots).
const growth = db.prepare(`
SELECT v.channel_url, f.view_count v0, l.view_count v1, f.observed_at o0, l.observed_at o1,
f.published_at p0, f.published_at_text t0
FROM videos v
JOIN video_metric_snapshots f ON f.id = (SELECT min(id) FROM video_metric_snapshots x WHERE x.video_id=v.video_id AND x.view_count IS NOT NULL)
JOIN video_metric_snapshots l ON l.id = (SELECT max(id) FROM video_metric_snapshots x WHERE x.video_id=v.video_id AND x.view_count IS NOT NULL)
WHERE f.id <> l.id AND julianday(l.observed_at) - julianday(f.observed_at) >= 14`).all();
const seedSearches = db.prepare(`
SELECT s.id, s.query FROM searches s
WHERE s.query NOT LIKE '[discovery-v2%' AND s.query NOT LIKE '%similar%' ORDER BY s.id`).all().map(s => ({
bucket: "seed", query: s.query,
items: db.prepare("SELECT video_id, position FROM video_searches WHERE search_id=? ORDER BY position").all(s.id)
.map(r => videos.get(r.video_id)).filter(Boolean)
}));
const observedRange = db.prepare("SELECT min(observed_at) a, max(observed_at) b FROM video_metric_snapshots").get();
db.close();
return { eco, dbPath, cls, channels, totalChannels, videos, feeds, growth, seedSearches, observedRange };
}
function loadProbes(eco) {
const dir = path.join(OUT_ROOT, eco);
if (!fs.existsSync(dir)) return null;
const files = fs.readdirSync(dir).filter(f => /^probe-results-.*\.json$/.test(f)).sort();
if (!files.length) return null;
const runs = files.map(f => readJson(path.join(dir, f)));
const versions = [...new Set(runs.map(r => r.queries_version))];
// Group the same query across probe days so each query counts once.
const byQuery = new Map();
for (const run of runs) for (const r of run.results) {
if (r.error) continue;
const items = r.items.map(it => ({ ...it, views: it.view_count, age: ageAtObservation(it.published_at, it.published_at_text, r.observed_at) }))
.filter(it => it.age != null);
if (!byQuery.has(r.query)) byQuery.set(r.query, { bucket: r.bucket, query: r.query, runs: [] });
byQuery.get(r.query).runs.push(items);
}
return { files, versions, queries: [...byQuery.values()] };
}
// ---------------------------------------------------------------- metrics
const isLong = v => !(v.dur != null && v.dur <= SHORT_MAX_SECONDS);
function bandScore(id, value) {
const spec = BANDS.metrics[id];
if (!spec || value == null) return null;
let score = 1;
spec.thresholds.forEach((t, i) => {
if (spec.direction === "higher" ? value >= t : value <= t) score = i + 2;
});
return score;
}
function metric(id, value, n, minN, extra = {}) {
const ok = value != null && n >= minN;
return { id, dimension: BANDS.metrics[id]?.dimension ?? "context", value: ok ? value : null, raw: value, n, min_n: minN,
status: ok ? "ok" : "insufficient", score: ok ? bandScore(id, value) : null, ...extra };
}
/** Per-query statistics over the top 10 results; a query probed on several days contributes the mean of its days. */
function searchMetrics(queries, source) {
const perQuery = queries.map(q => {
const runs = (q.runs ?? [q.items]).map(items => items.filter(it => it.position == null || it.position <= 10).slice(0, 10))
.filter(items => items.length >= 5);
if (!runs.length) return null;
return {
views: mean(runs.map(items => median(items.map(it => it.views)))),
old: mean(runs.map(items => items.filter(it => it.age > 730).length / items.length)),
fresh: mean(runs.map(items => items.filter(it => it.age <= 31).length / items.length))
};
}).filter(Boolean);
const n = perQuery.length;
return [
metric("search_views", round(median(perQuery.map(q => q.views)), 0), n, MIN.queries, { source }),
metric("search_old_share", round(mean(perQuery.map(q => q.old))), n, MIN.queries, { source }),
metric("search_fresh_share", round(mean(perQuery.map(q => q.fresh))), n, MIN.queries, { source })
];
}
function rate(data, probes) {
const { cls, channels, feeds } = data;
const core = [...cls].filter(([, c]) => c === "core").map(([u]) => u).sort();
const longFeed = u => [...(feeds.get(u)?.values() ?? [])].filter(isLong);
const subsOf = u => channels.get(u)?.subs;
// Per-channel median views of long-form videos aged 30-365 days (old enough to have settled, young enough to reflect today).
const chanMedian = new Map();
for (const u of core) {
const mid = longFeed(u).filter(v => v.age >= 30 && v.age <= 365);
if (mid.length >= 3) chanMedian.set(u, median(mid.map(v => v.views)));
}
// Fixed-size slice so results do not depend on how deep discovery went.
const top100 = core.filter(u => subsOf(u) != null).sort((a, b) => subsOf(b) - subsOf(a) || a.localeCompare(b)).slice(0, 100);
const top100Medians = top100.map(u => chanMedian.get(u)).filter(x => x != null);
const sortedDesc = [...top100Medians].sort((a, b) => b - a);
const coreSubs = core.map(subsOf).filter(Number.isFinite).sort((a, b) => b - a);
const small = core.filter(u => subsOf(u) >= 1000 && subsOf(u) < 100000);
const reach = small.filter(u => chanMedian.has(u)).map(u => chanMedian.get(u) / subsOf(u));
const young = core.filter(u => channels.get(u)?.ageYears != null && channels.get(u).ageYears <= 3 && subsOf(u) != null);
const breakout = small.map(u => {
const vs = longFeed(u).filter(v => v.age >= 30);
if (vs.length < 8) return null;
const m = median(vs.map(v => v.views));
return m > 0 ? vs.filter(v => v.views >= 5 * m).length / vs.length : null;
}).filter(x => x != null);
const backCat = core.map(u => {
const vs = longFeed(u).filter(v => v.views > 0);
const fresh = vs.filter(v => v.age >= 30 && v.age <= 180), old = vs.filter(v => v.age >= 365 && v.age <= 3 * 365);
return fresh.length >= 3 && old.length >= 3 ? Math.log10(median(old.map(v => v.views)) / median(fresh.map(v => v.views))) : null;
}).filter(x => x != null);
const coreSet = new Set(core);
let gainOld = 0, gainAll = 0, growthN = 0;
for (const g of data.growth) {
if (!coreSet.has(g.channel_url)) continue;
const age = ageAtObservation(g.p0, g.t0, g.o0);
const days = (new Date(g.o1) - new Date(g.o0)) / DAY_MS;
if (age == null || g.v1 < g.v0) continue;
const perMonth = (g.v1 - g.v0) / days * 30;
gainAll += perMonth; if (age > 365) gainOld += perMonth; growthN++;
}
const cadence = top100.map(u => {
const vs = [...(feeds.get(u)?.values() ?? [])];
return vs.length >= 5 ? vs.length / (Math.max(...vs.map(v => v.age)) / 30.4) : null;
}).filter(x => x != null);
const tier1 = new Set(BANDS.tier1_countries);
const withCountry = top100.filter(u => channels.get(u)?.country);
const search = probes?.queries?.length
? searchMetrics(probes.queries, `probe (${probes.files.length} run${probes.files.length > 1 ? "s" : ""}, version ${probes.versions.join("/")})`)
: searchMetrics(data.seedSearches, "UNSTANDARDIZED discovery seed searches");
const metrics = [
search[0],
metric("ceiling_subs", coreSubs[9] ?? null, coreSubs.length, MIN.core, { note: "subscribers of the 10th-largest core channel" }),
metric("top100_views", round(median(top100Medians), 0), top100Medians.length, MIN.top100),
metric("concentration_top10", round(sortedDesc.slice(0, 10).reduce((a, b) => a + b, 0) / sortedDesc.reduce((a, b) => a + b, 0)), sortedDesc.length, MIN.top100),
metric("small_reach", round(median(reach)), reach.length, MIN.small),
metric("young_success", round(young.filter(u => subsOf(u) >= 10000).length / young.length), young.length, MIN.young,
{ note: "survivorship-biased: only channels search surfaced" }),
search[1], search[2],
metric("back_catalogue_ratio", round(median(backCat)), backCat.length, MIN.backCatalogue,
{ note: "confounded by channel growth; needs mined catalogues" }),
metric("view_growth_back_share", growthN ? round(gainOld / gainAll) : null, growthN, MIN.growthVideos,
{ note: "needs a second snapshot >= 14 days after the first" }),
metric("cadence_top100", round(median(cadence), 2), cadence.length, MIN.cadence),
metric("tier1_country_share", round(withCountry.filter(u => tier1.has(channels.get(u).country)).length / withCountry.length),
withCountry.length, MIN.country, { note: "creator location, not audience location" })
];
// Context: descriptive, never scored.
const coreFeedAll = core.flatMap(u => [...(feeds.get(u)?.values() ?? [])]);
const newest = core.map(u => { const vs = [...(feeds.get(u)?.values() ?? [])]; return vs.length ? Math.min(...vs.map(v => v.age)) : null; }).filter(x => x != null);
const countries = {};
for (const u of core) { const c = channels.get(u)?.country ?? "unknown"; countries[c] = (countries[c] ?? 0) + 1; }
const nonLatin = t => { const letters = String(t ?? "").match(/\p{L}/gu) ?? []; return letters.length && letters.filter(ch => !/\p{Script=Latin}/u.test(ch)).length / letters.length > 0.3; };
const context = {
shorts_share: round(coreFeedAll.filter(v => !isLong(v)).length / coreFeedAll.length),
median_longform_minutes: round(median(coreFeedAll.filter(v => isLong(v) && v.dur).map(v => v.dur / 60)), 1),
dormant_share: round(newest.filter(a => a > 180).length / newest.length),
new_entrant_share: round(core.filter(u => channels.get(u)?.ageYears != null && channels.get(u).ageYears <= 2).length /
core.filter(u => channels.get(u)?.ageYears != null).length),
non_latin_title_share: round(coreFeedAll.filter(v => nonLatin(v.title)).length / coreFeedAll.length),
// Unscored: about 0.09-0.13 in every ecosystem measured so far, so it does not separate them.
breakout_rate: breakout.length >= MIN.breakout ? round(mean(breakout)) : null,
top_countries: Object.entries(countries).sort((a, b) => b[1] - a[1] || a[0].localeCompare(b[0])).slice(0, 5)
};
const dims = {};
for (const d of DIMENSIONS) {
const scored = metrics.filter(m => m.dimension === d && m.score != null);
const total = metrics.filter(m => m.dimension === d).length;
dims[d] = {
score: scored.length ? median(scored.map(m => m.score)) : null,
metrics_used: scored.length, metrics_defined: total,
confidence: !scored.length ? "none" : scored.length >= 3 || (scored.length === total && total <= 2) ? "normal" : "low"
};
}
const quality = {
channels_total: data.totalChannels,
llm_classified: cls.size,
core_channels: core.length,
core_with_feed: core.filter(u => feeds.has(u)).length,
core_with_subs: coreSubs.length,
search_source: search[0].source,
snapshot_window: data.observedRange,
flags: []
};
if (core.length < MIN.core) quality.flags.push(`only ${core.length} core channels`);
if (!probes) quality.flags.push("search metrics use unstandardized discovery seeds: run the probe for comparable demand/evergreen numbers");
if (probes?.versions.length > 1) quality.flags.push(`probe results mix query versions ${probes.versions.join(", ")}: archive the old runs`);
if (probes?.files.length === 1) quality.flags.push("single probe run: fresh-share can swing with the news cycle; probe again on another day");
if (cls.size / Math.max(1, data.totalChannels) < 0.6) quality.flags.push(`only ${round(cls.size / data.totalChannels, 2)} of channels have an LLM qualification`);
for (const m of metrics) if (m.status !== "ok") quality.flags.push(`${m.id}: insufficient (n=${m.n}, need ${m.min_n})`);
return { ecosystem: data.eco, db: path.relative(process.cwd(), data.dbPath).replace(/\\/g, "/"), bands_version: BANDS.version, quality, dimensions: dims, metrics, context };
}
/** Weighted mean of the scored dimensions. Partial when a weighted dimension could not be scored: not comparable to full ones. */
function composite(result, weights) {
let sum = 0, w = 0;
const missing = [];
for (const d of DIMENSIONS) {
if (!weights[d]) continue;
const s = result.dimensions[d].score;
if (s == null) { missing.push(d); continue; }
sum += s * weights[d]; w += weights[d];
}
return { value: w ? round(sum / w, 2) : null, partial: missing.length > 0, missing };
}
// ---------------------------------------------------------------- output
function scorecard(results, weights) {
const ecos = results.map(r => r.ecosystem);
const lines = [];
const row = cells => `| ${cells.join(" | ")} |`;
lines.push(`# Ecosystem scorecard`, "", `Bands: \`${BANDS.version}\`. Weights: ${DIMENSIONS.map(d => `${d} ${weights[d]}`).join(", ")}.`,
"Scores are 1-5 on fixed thresholds; 5 is always better for a new creator. `-` means not enough data to score.", "");
// Full composites rank above partial ones: a missing dimension is unknown, not average.
const ranked = [...results].sort((a, b) => (a.composite.partial - b.composite.partial) ||
((b.composite.value ?? -1) - (a.composite.value ?? -1)) || a.ecosystem.localeCompare(b.ecosystem));
lines.push("## Dimension scores", "", row(["Ecosystem", ...DIMENSIONS, "**Composite**", "Search source"]), row(Array(DIMENSIONS.length + 3).fill("---")));
for (const r of ranked) {
const c = r.composite;
lines.push(row([r.ecosystem, ...DIMENSIONS.map(d => { const x = r.dimensions[d]; return x.score == null ? "-" : `${x.score}${x.confidence === "low" ? " (low)" : ""}`; }),
c.value == null ? "-" : c.partial ? `${c.value}* (no ${c.missing.join(", ")})` : `**${c.value}**`,
r.quality.search_source.startsWith("probe") ? "probe" : "seeds (unstandardized)"]));
}
lines.push("", "`*` partial composite: ranked below complete ones because a missing dimension is unknown, not average.");
lines.push("", "## Metrics (raw value, [score], n)", "", row(["Metric", "Dimension", ...ecos]), row(Array(ecos.length + 2).fill("---")));
for (const id of Object.keys(BANDS.metrics)) {
lines.push(row([id, BANDS.metrics[id].dimension, ...results.map(r => {
const m = r.metrics.find(x => x.id === id);
return m.status === "ok" ? `${m.value} [${m.score}] n=${m.n}` : `- (n=${m.n})`;
})]));
}
lines.push("", "## Context (not scored)", "", row(["Signal", ...ecos]), row(Array(ecos.length + 1).fill("---")));
for (const k of ["shorts_share", "median_longform_minutes", "dormant_share", "new_entrant_share", "non_latin_title_share", "breakout_rate"]) {
lines.push(row([k, ...results.map(r => r.context[k] ?? "-")]));
}
lines.push(row(["top_countries", ...results.map(r => r.context.top_countries.map(([c, n]) => `${c} ${n}`).join(", "))]));
lines.push("", "## Data quality", "");
for (const r of results) {
const q = r.quality;
lines.push(`- **${r.ecosystem}**: ${q.core_channels} core of ${q.llm_classified} classified (${q.channels_total} channels); ` +
`snapshots ${String(q.snapshot_window.a).slice(0, 10)} to ${String(q.snapshot_window.b).slice(0, 10)}.` +
(q.flags.length ? ` Flags: ${q.flags.join("; ")}.` : ""));
}
return lines.join("\n") + "\n";
}
const args = parseArgs(process.argv.slice(2));
const weights = { ...BANDS.weights };
if (typeof args.weights === "string") for (const kv of args.weights.split(",")) { const [k, v] = kv.split("="); if (k in weights) weights[k] = Number(v); }
const dbPaths = args.all ? listDbs() : [resolveDb(args.db)];
const results = [];
for (const dbPath of dbPaths) {
const data = loadEcosystem(dbPath);
const result = rate(data, loadProbes(data.eco));
result.composite = composite(result, weights);
result.weights = weights;
fs.writeFileSync(path.join(outDir(data.eco), "metrics.json"), JSON.stringify(result, null, 2));
results.push(result);
const dimText = DIMENSIONS.map(d => `${d}=${result.dimensions[d].score ?? "-"}`).join(" ");
console.log(`[rate] ${data.eco.padEnd(16)} ${dimText} composite=${result.composite.value ?? "-"}${result.composite.partial ? "*" : ""} flags=${result.quality.flags.length}`);
}
if (args.all) {
fs.writeFileSync(path.join(OUT_ROOT, "scorecard.md"), scorecard(results, weights));
console.log(`[rate] wrote ${path.join(OUT_ROOT, "scorecard.md")}`);
}
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.