ingest-substack-csv

$npx mdskill add lyndonkl/claude/ingest-substack-csv

Validate and load Substack CSV exports when automation fails.

  • Handles manual weekly stats extraction for growth analysis.
  • Integrates with Substack exports and corpus storage systems.
  • Executes schema checks before processing subscriber data.
  • Outputs typed WeekExport objects to the stats directory.

SKILL.md

.github/skills/ingest-substack-csvView on GitHub ↗
---
name: ingest-substack-csv
description: Loads and validates a weekly Substack CSV stats export for the substacker Growth Analyst. Reconciles header against expected-columns schema, parses post rows + subscriber aggregates, moves file into corpus/stats/ on success, emits schema-warning stub on header drift. Never reads subscriber emails row-by-row — aggregates only. FALLBACK path when fetch-substack-stats (Chrome automation) is unavailable. Use when a CSV appears in inbox/substack-stats/, when Chrome is not logged in, or when the writer prefers manual export. Trigger keywords: CSV, Substack export, stats export, schema validation, subscriber data, manual export, CSV fallback.
---

# Ingest Substack CSV

## Workflow

```
Per weekly CSV:
- [ ] Step 1: Read header row; diff against .schema-expected.json
- [ ] Step 2: If load-bearing column missing, halt + emit schema-warning stub
- [ ] Step 3: Parse post rows (load-bearing: views, opens, open_rate, clicks, sent, post_date)
- [ ] Step 4: Parse subscribers aggregate (total, free/paid distribution, activity tiers) — NEVER row-by-row emails
- [ ] Step 5: Emit typed WeekExport object
- [ ] Step 6: On success, move CSV into corpus/stats/YYYY-WW.csv
```

## Expected schema

Posts CSV columns (load-bearing in bold):
**id, slug, title, post_date, views, opens, open_rate, clicks, click_through_rate, sent**, delivered, signups_within_1_day, subscriptions_within_1_day, unsubscribes_within_1_day, signups, subscribes, shares, estimated_value, engagement_rate, reaction_count, comment_count

Subscribers CSV (handled separately, never ingested row-by-row): email, created_at, subscription_type, activity_tier, email_opens_last_30_days, email_opens_last_7_days.

## Schema drift

If a load-bearing column is missing:
- Write `ops/growth-analyst/YYYY-WW-schema-warning.md` with the header diff.
- Halt the pipeline.
- Prompt the writer: "Substack may have changed its export format. Review the diff; update `.schema-expected.json` if confirmed."

New columns (additions) are accepted silently. Removals of non-load-bearing columns produce a soft note but don't halt.

## Output: WeekExport

```python
{
  "subscribers_end": int,
  "delta_subscribers": int,
  "posts": [ {slug, title, post_date, views, opens, open_rate, clicks, sent, ...} ],
  "sends_this_week": int,
  "free_subs": int,
  "paid_subs": int,
  "activity_tier_distribution": {active, at-risk, churned}
}
```

## Guardrails

1. Never read subscriber-CSV row-by-row. Aggregate reductions only.
2. Halt on load-bearing column loss; stub the warning.
3. New columns → accept silently; note in data-caveats later.
4. Subscriber CSV is deleted from `inbox/` after 7 days (privacy posture).
5. Tolerate naming variants (underscore vs hyphen vs missing date) — warn, don't hard-fail.

More from lyndonkl/claude

SkillDescription
abstraction-concrete-examplesBuilds structured abstraction ladders that translate high-level principles into concrete, actionable examples across 3-5 levels. Bridges communication gaps, reveals hidden assumptions, and tests whether abstract ideas work in practice. Use when explaining concepts at different expertise levels, moving between abstract principles and concrete implementation, identifying edge cases by testing ideas against scenarios, designing layered documentation, decomposing complex problems into actionable steps, or bridging strategy-execution gaps.
academic-letter-architectGuides the creation of evidence-based academic recommendation letters, reference letters, and award nominations that combine concrete examples, meaningful comparisons, and genuine enthusiasm. Use when writing recommendation letters for students, postdocs, or colleagues, or when user mentions recommendation letter, reference, nomination, letter of support, endorsement, or needs help with strong advocacy and comparative statements.
adr-architectureDocuments significant architectural and technical decisions with full context, alternatives considered, trade-offs analyzed, and consequences understood. Creates a decision trail that helps teams understand why decisions were made. Use when choosing between technology options, making infrastructure decisions, establishing standards, migrating systems, or when user mentions ADR, architecture decision, technical decision record, or decision documentation.
adverse-selection-priorProduces a Bayesian prior probability that an offered transaction is +EV for the recipient, given that the counterparty chose to propose it. Applies Akerlof market-for-lemons logic -- if they offered it, they believe it is +EV for them, so the prior that it is +EV for us is materially below 50%. Reusable across trade evaluation, waiver drops (another team dropping a player is also adverse selection), job-offer analysis, M&A, and any "someone offered me this" situation. Use when you receive an unsolicited trade/offer/proposal, analyzing incoming trade prior, evaluating why a counterparty proposed a deal, or when user mentions adverse selection, market for lemons, why did they offer this, incoming trade prior, they proposed it, Bayesian adjustment on received offer.
alignment-values-north-starCreates actionable alignment frameworks that give teams a shared North Star (direction), values (guardrails), and decision tenets (behavioral standards). Enables autonomous decision-making while maintaining organizational coherence. Use when starting new teams, scaling organizations, defining culture, establishing product vision, resolving misalignment, creating strategic clarity, or when user mentions North Star, team values, mission, principles, guardrails, decision framework, or cultural alignment.
analogy-weight-checkFor every analogy in a substacker draft, verifies it carries mechanical weight — the analogy does real work explaining the mechanism, not merely decorates it. Cross-references analogy-catalog.md for novelty (is this analogy reused from a prior post?) and domain fit (biology > organizational > sports preferred; physics/military disfavored). Use whenever an analogy appears in the draft. Trigger keywords: analogy weight, decorative, mechanical weight, reused analogy, catalog check, metaphor check.
answer-uncomfortable-questionTakes one strategic question about substacker ("should we launch paid?", "is this section dead?", "are we writing for the wrong audience?") and produces the mandatory evidence + reasoning + downside triad plus a recommendation. Used 3 times per Growth Strategist review. Trigger keywords: uncomfortable question, strategic question, evidence reasoning downside, triad.
attribute-performanceFor each substacker post that materially over- or under-performs the rolling baseline (|z| ≥ 1.0), produces a plain-English attribution paragraph with calibrated confidence (high / medium / low / unexplained). Considers subject-line effect, topic zeitgeist, external share, day-of-week, length effect, and audience-notes signals. Labels unexplained outliers explicitly rather than fabricating a story. Use after compute-baseline when outlier posts exist. Trigger keywords: attribution, why did this post work, outlier explanation, performance analysis.
auction-first-price-shadingComputes the optimal shaded bid for a first-price sealed-bid auction given a true private value, an estimate of the number of competing bidders N, and a value-distribution assumption. Implements the `(N-1)/N` equilibrium shading rule for uniform private values, adjusts for log-normal or empirical value distributions, layers a risk-aversion adjustment, and caps output against the bidder's remaining budget. Domain-neutral auction theory reusable across fantasy sports (baseball FAAB, NBA/NHL waiver auctions), prediction-market limit sizing, sealed procurement bids, and any blind-bid context. Use when user mentions "first-price auction bid", "sealed bid shading", "(N-1)/N", "FAAB bid amount", "auction shading", "optimal bid first-price", "bid for sealed-bid", "blind bid sizing", or when downstream logic needs a principled shade factor rather than an ad-hoc heuristic.
auction-winners-curse-haircutApplies a Bayesian haircut to a bid valuation for common-value auctions where winning is itself evidence the bidder over-estimated. Takes a raw valuation, a value-type classification (common_value / private_value / mixed), the number of informed bidders N, and a signal-dispersion estimate, and returns an adjusted valuation. Domain-neutral and reusable across fantasy FAAB, prediction markets, M&A bids, ad-auction budgets, and any generic bidding context. Use when user mentions "winner's curse", "common value auction", "valuation haircut", "adverse valuation", "Bayesian bid adjustment", or "over-paying in auction".