metrics: stand up the weekly funnel job (FRE-202) #3

agent-cto · 2026-10-31T09:23:51-04:00

agent-cto commented

2026-10-31 09:23:51 -04:00

marketing/metrics/weekly.md has been a placeholder since the marketing tree landed. This adds the producer: one command reads the live analytics and licence databases and rewrites the file with the staged funnel, the campaign breakdown, and the one thing that moved.

src/server/weekly-metrics.ts — queries + renderer, taking both connections as arguments (so tests run them against in-memory SQLite, like backfill-downloads.test.ts). Campaign grouping matches getAnalyticsSummary.topCampaigns exactly (top 10, untagged excluded).
scripts/weekly-metrics.ts — CLI: bun --env-file=.env scripts/weekly-metrics.ts (bun run metrics:weekly), options --days, --now, --out, --stdout. Idempotent.
marketing/metrics/weekly.md — Week 0 report, 2026-10-22 16:00 → 2026-10-29 16:00 UTC, generated, never hand-edited.
marketing/README.md — how the report is produced.

Test: bun test src/server/weekly-metrics.test.ts → 12 pass, 0 fail, 23 expect() calls.

The report states the four things the instrumentation cannot yet tell it (all-loopback addresses, one download label covering click and served, checkout start unrecorded, no host dimension); those are routed as FRE-366.

Task: FRE-202.

`marketing/metrics/weekly.md` has been a placeholder since the marketing tree landed. This adds the producer: one command reads the live analytics and licence databases and rewrites the file with the staged funnel, the campaign breakdown, and the one thing that moved. - `src/server/weekly-metrics.ts` — queries + renderer, taking both connections as arguments (so tests run them against in-memory SQLite, like `backfill-downloads.test.ts`). Campaign grouping matches `getAnalyticsSummary.topCampaigns` exactly (top 10, untagged excluded). - `scripts/weekly-metrics.ts` — CLI: `bun --env-file=.env scripts/weekly-metrics.ts` (`bun run metrics:weekly`), options `--days`, `--now`, `--out`, `--stdout`. Idempotent. - `marketing/metrics/weekly.md` — Week 0 report, 2026-10-22 16:00 → 2026-10-29 16:00 UTC, generated, never hand-edited. - `marketing/README.md` — how the report is produced. Test: `bun test src/server/weekly-metrics.test.ts` → 12 pass, 0 fail, 23 expect() calls. The report states the four things the instrumentation cannot yet tell it (all-loopback addresses, one download label covering click and served, checkout start unrecorded, no host dimension); those are routed as FRE-366. Task: FRE-202.

agent-cto added 1 commit 2026-10-31 09:23:51 -04:00

metrics: stand up the weekly funnel job (FRE-202) 89f56fc78a

`marketing/metrics/weekly.md` has been a placeholder since the marketing tree
landed — it promised a funnel and the one thing that moved, and nothing
produced it. This adds the producer: one command reads the live databases and
rewrites the file.

`scripts/weekly-metrics.ts` writes `marketing/metrics/weekly.md` from
`VisitorAnalytics` (visits, downloads, campaigns) and the-nook's `trials`,
`licenses`, `activations` (the paid funnel). It needs four env vars —
TURSO_DB_URL/TOKEN and NOOK_DB_URL/TOKEN — and imports no server env, so the
job runs from a checkout that has no Stripe or Apple secrets.
`src/server/weekly-metrics.ts` holds the queries and the renderer, taking both
connections as arguments; the tests run them against an in-memory SQLite shaped
like production, the same way the backfill suite does.

The campaign breakdown groups exactly as `getAnalyticsSummary.topCampaigns`
does (top 10, untagged excluded), so the weekly read and /analytics cannot
drift into different label sets.

The first real report says more about the instrumentation than about demand,
and the renderer computes that rather than asserting it:

- every addressed row the table has ever held (12,035 of 12,048) is loopback,
  so "unique visitors" is one host and per-channel reach cannot be read;
- the landing door and the file route both write `/api/downloads/thenook`, so
  "download click" and "DMG served" are one label — one served click writes two
  rows, a failed click one, a Sparkle update one;
- `POST /api/the-nook/checkout` records nothing, so checkout start is only a
  page-view proxy;
- `VisitorAnalytics` has no host dimension, so the Nook's `/` is freno.me's `/`.

Those three gaps are reported beside the numbers, not papered over. Week 0
(2026-10-22 → 2026-10-29): visit 61, download 156, trial 2, checkout 0,
purchase 0, activation 0 — and the one thing that moved is the download stage,
0 → 156, because the label was introduced that week.

Tests: `bun test src/server/weekly-metrics.test.ts` — 12 pass, 0 fail. The full
suite's 15 failures are pre-existing: this checkout's .env lacks
NOOK_LICENSE_PRIVATE_KEY, NOOK_STRIPE_SK/WEBHOOK/PRICE and LINEAGE_JWT_SECRET,
and every failure is that env validation.

Scripts/ is gitignored, so `scripts/weekly-metrics.ts` was added with `-f`, the
way the existing tracked scripts are.

FRE-202

Mike merged commit c7071b0e32 into master

2026-11-04 10:58:12 -05:00

Mike referenced this issue from a commit

2026-11-04 10:58:14 -05:00

Merge pull request 'metrics: stand up the weekly funnel job (FRE-202)' (#3) from agent-cto/freno-dev:freno-dev/FRE-202-weekly-metrics into master

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Mike/freno-dev#3

Allow edits from maintainers