Newsletter topic click and unsubscribe analytics dashboard

# Newsletter stats

## Purpose

This project ranks newsletter topics by click-through and shows when subscribers unsubscribe, by weekday and hour. The user is a beginner solo analyst, so keep explanations plain and steps small.

## Stack

- Python 3.12, pandas, matplotlib, pytest, ruff.
- No database and no web server. Data lives in CSV files under `data/raw/` (real exports, never committed) and `data/sample/` (fake data for tests and checks).
- Ask before adding a database, a web server, or any new dependency.

## Commands

- Create the virtual environment: `python -m venv .venv`
- Activate it (macOS/Linux): `source .venv/bin/activate` (Windows: `.venv\Scripts\activate`)
- Install dependencies: `pip install -r requirements.txt`
- Run the analysis and regenerate reports: `python -m newsletter_stats.report`
- Run tests: `pytest -q`
- Lint: `ruff check .`

## Layout

- `src/newsletter_stats/` holds the package. `parse.py` extracts topics, `clean.py` parses dates, `report.py` builds outputs.
- `tests/` holds pytest files. Each parser needs at least one messy-input test.
- `reports/` holds generated outputs (`topics.csv`, `unsubscribes.png`, the markdown report).
- `data/sample/` holds fake campaign and event rows with hashed subscriber IDs.
- `data/raw/` holds real exports. It is gitignored.

## Data rules

- Never commit subscriber emails, names, or any personal field. Never print or paste raw exports into chat or output.
- Use hashed subscriber IDs in every output file, chart, and fixture.
- Raw exports go in `data/raw/`, and that folder stays out of git.
- Click rate is unique clickers divided by delivered. Unsubscribe rate is unsubscribes divided by delivered, per send.
- Topics must have at least 200 delivered before they can be ranked.

## Topic parsing

- Each send has a topic tag in its campaign name.
- Parse topics only in `parse()` inside `src/newsletter_stats/parse.py`. Do not parse topic strings anywhere else, including tests, reports, or notebooks.

## Timezone basis

- Unsubscribe timing uses the send timestamp in UTC. If the subscriber has a local timezone field, convert to that timezone. Otherwise keep UTC.
- Every chart title must state the basis, for example "Unsubscribes by weekday and hour (subscriber local time)" or "(UTC, no timezone data)".

## Gotchas

- ESP exports mix date formats. Always parse with an explicit format string in `clean.py`.
- On an unknown date format, raise an error that names the value and the column. Do not guess, and do not silently drop rows.
- Check row counts before and after each cleaning step, and report any drop.
- Campaign and event IDs must match across both CSVs. Report orphan rows instead of ignoring them.

## Review

@REVIEW.md

## Working style

- Make small changes and run `ruff check .` and `pytest -q` after each one.
- Explain each change in plain language in the final message. The user is new to coding, so define any term the first time you use it.
- When a request is unclear, ask one question before writing code.

{
  "model": "sonnet",
  "permissions": {
    "defaultMode": "acceptEdits",
    "allow": [
      "Bash(pytest:*)",
      "Bash(ruff:*)",
      "Bash(python -m newsletter_stats.report:*)",
      "Bash(git status:*)",
      "Bash(git diff:*)"
    ],
    "deny": [
      "Bash(rm -rf:*)",
      "Bash(git push:*)",
      "Bash(curl:*)",
      "Read(./.env)"
    ],
    "ask": [
      "Bash(git commit:*)"
    ]
  }
}

---
name: verify
description: Run after any code change in this project to check lint, tests, and a sample report run before calling the work done.
---

# Verify

Use this skill to prove a change works. Run the steps in order and stop at the first failure.

## Steps

1. **Lint.** Run `ruff check .`
2. **Tests.** Run `pytest -q`
3. **Sample report.** Run `python -m newsletter_stats.report` against the sample data in `data/sample/`. If the report has no way to read that folder yet, stop and tell the user the report needs an input option before verification can pass.
   - Confirm `reports/topics.csv` exists and is not empty.
   - Confirm `reports/unsubscribes.png` exists and is not empty.
4. **Report.** Give a pass or fail for each step in plain language. On failure, show only the first error line and the file it points to. Do not paste the full output.

## Rules

- Never print, paste, or summarize anything from `data/raw/`. Real exports contain subscriber data.
- Use only `data/sample/` for the sample report run.
- Do not fix code inside this skill. Report the failure and let the user or the main session decide the fix.
- If a step cannot run because a command is missing, say which command and suggest the install step from `INSTALL.md`.

## Example result

```
Lint: pass
Tests: pass (12 tests)
Sample report: pass (topics.csv and unsubscribes.png exist and are non-empty)
```

If a step fails:

```
Tests: fail
First error: tests/test_parse.py:14, ValueError: unknown date format
```

# Install and run your first goal

This guide walks you through setting up the newsletter stats project. You do not need coding experience. Follow the steps in order.

## What you will need

- A computer running macOS, Linux, or Windows.
- A Claude subscription or an Anthropic API key.
- Your email provider's campaign and event CSV exports (you can add these later).

## Step 1: Install Claude Code

Run the official installer in your terminal.

- macOS or Linux: `curl -fsSL https://claude.ai/install.sh | bash`
- Windows (PowerShell): `irm https://claude.ai/install.ps1 | iex`

Close the terminal, open a new one, and check the version:

```
claude --version
```

The number must be 2.1.296 or newer. If it is older, run the installer again.

## Step 2: Install Python 3.12

1. Go to python.org and download Python 3.12.
2. Run the installer. On Windows, tick "Add python.exe to PATH".
3. Check the install:

```
python --version
```

It should print `Python 3.12.x`.

## Step 3: Create the project folder

In a terminal, run:

```
mkdir newsletter-stats
cd newsletter-stats
git init
```

Copy these into the folder, keeping the names exactly as they are:

- CLAUDE.md
- SPEC.md
- PLAN.md
- REVIEW.md
- INSTALL.md
- the `.claude` folder

## Step 4: Start Claude Code

From inside the project folder, run:

```
claude
```

Accept the trust prompt when it appears. Trust means Claude may read and edit files in this folder.

Only run `/init` if CLAUDE.md is missing. If you copied the file in Step 3, keep it as it is.

## Step 5: Install the two plugins

Inside Claude Code, type:

```
/plugin
```

Add these two plugins:

- `code-review@claude-plugins-official` runs the reviewer that checks your changes against REVIEW.md.
- `commit-commands@claude-plugins-official` gives clean commit steps, so you do not have to write git messages by hand.

After installing, type `/code-review` to confirm it is listed.

## Step 6: Start Phase 1

The kickoff prompt is in PLAN.md. It begins with `/goal`, so pasting it as your first message sets the goal and Claude starts working.

Phase 1 creates the project skeleton: the requirements file, the gitignore, sample data, and one passing test. Claude stops when the check passes or after 25 turns.

When it finishes, read its final message. It explains what changed in plain language.

## Step 7: Clear the session before Phase 2

Type:

```
/clear
```

This empties the conversation so the next phase starts fresh. Then open PLAN.md, copy the `/goal` prompt for Phase 2, and paste it.

Repeat the same pattern for each phase: finish the phase, run `/clear`, then paste the next phase's goal.

## What /goal does

`/goal` gives Claude a finish line. After each turn, a small fast model checks whether the condition you wrote has been met, using only what Claude has shown in the conversation. Claude keeps working until the check passes or it reaches the turn limit in the goal.

To stop a goal early, type:

```
/goal clear
```

## Step 8: Add real data (optional, later)

When you are ready to use your own numbers:

1. Export your campaign CSV and your event CSV from your email provider.
2. Save both files into `data/raw/`. Create the folder if it does not exist.
3. Do not move these files into any other folder.

The `data/raw/` folder is listed in `.gitignore`, so git will not track these files. Never paste their contents into Claude or into a commit.

## If something goes wrong

- If `claude --version` shows an old version, run the installer again and open a new terminal.
- If `python --version` shows an older version, reinstall Python 3.12 and check the PATH option.
- If a goal stops before the check passes, read the final message. It lists what is still missing. You can paste that list into a new goal after `/clear`.

# Build plan

This plan builds the newsletter stats tool in five phases. Each phase is one Claude Code session and ends with a `/goal` prompt.

## Rules for every phase

- Start with `/clear`, then paste the phase's `/goal` prompt.
- The `/goal` check only sees what Claude has shown in the conversation. Ask Claude to paste the check command output.
- If a goal runs out of turns, read the report it gives you before retrying.
- Do not commit raw data. Commits happen only when you ask.

## Phase 1: project scaffold

Steps:

- Create `requirements.txt` with pandas, matplotlib, pytest, and ruff.
- Create `.gitignore` covering `.venv/` and `data/raw/`.
- Create `data/sample/` with 20 fake campaign rows and 100 fake event rows, using hashed subscriber IDs.
- Create `src/newsletter_stats/__init__.py` and one passing test in `tests/test_smoke.py`.

Goal prompt:

```
/goal Phase 1 complete: project scaffold exists with requirements.txt (pandas, matplotlib, pytest, ruff), .gitignore excluding .venv/ and data/raw/, data/sample/ with 20 fake campaign rows and 100 fake event rows using hashed subscriber IDs, src/newsletter_stats/__init__.py, and one passing test in tests/test_smoke.py. Check: run `pip install -r requirements.txt && pytest -q` and it exits 0 with at least one test collected, and paste the output. Constraints: install nothing beyond requirements.txt; create no real subscriber data; do not commit; change only requirements.txt, .gitignore, data/sample/, src/, and tests/. Turn bound: stop after 25 turns and report what is missing.
```

## Phase 2: parsing and cleaning

Steps:

- Write `src/newsletter_stats/parse.py` with the single function that extracts the topic from a campaign name.
- Write `src/newsletter_stats/clean.py` with date parsing that uses explicit format strings and raises on unknown formats.
- Write unit tests with messy name and date samples.

Goal prompt:

```
/goal Phase 2 complete: src/newsletter_stats/parse.py extracts the topic from campaign names in one function, src/newsletter_stats/clean.py parses dates with explicit format strings and raises ValueError on unknown formats, and tests/test_parse.py and tests/test_clean.py each include at least one messy-input case. Check: run `pytest -q` and it exits 0; paste the output. Constraints: no topic parsing outside parse.py; no date parsing without an explicit format; no new dependencies; no network calls; change only src/newsletter_stats/ and tests/; do not commit. Turn bound: stop after 25 turns and report what is missing.
```

## Phase 3: click rate by topic

Steps:

- Compute unique clickers and delivered per topic.
- Compute click rate as unique clickers divided by delivered.
- Rank only topics with at least 200 delivered.
- Save the result to `reports/topics.csv`.

Goal prompt:

```
/goal Phase 3 complete: running `python -m newsletter_stats.report` on data/sample/ writes reports/topics.csv with columns topic, delivered, unique_clickers, click_rate, ranked; click_rate equals unique clickers divided by delivered; only topics with delivered of at least 200 have ranked set to yes; a test in tests/ covers the 200 threshold using an in-test fixture. Check: run `pytest -q` and then the report command, exit 0 for both, and paste the output and the first five lines of reports/topics.csv. Constraints: use delivered as the only denominator; use the topic function from parse.py; no real data; change only src/newsletter_stats/, tests/, and reports/ output; do not commit. Turn bound: stop after 25 turns and report what is missing.
```

## Phase 4: unsubscribe charts and report

Steps:

- Compute unsubscribes per weekday and hour, overall and per campaign.
- Draw `reports/unsubscribes.png` (overall heatmap) and `reports/campaign_unsubscribes.png` (per campaign).
- Put the time basis (UTC or local) in every chart title.
- Write `reports/report.md` with the topic ranking and a plain-language summary of both charts.

Goal prompt:

```
/goal Phase 4 complete: running `python -m newsletter_stats.report` on data/sample/ writes reports/unsubscribes.png, reports/campaign_unsubscribes.png, and reports/report.md; both PNG files are non-empty; each chart title states the time basis (UTC or local timezone); unsubscribe rate uses delivered as the denominator. Check: run `pytest -q` and the report command, both exit 0; list the three output files with sizes; paste the output. Constraints: no subscriber emails or names in any output; no new dependencies; change only src/newsletter_stats/, tests/, and reports/; do not commit. Turn bound: stop after 25 turns and report what is missing.
```

## Phase 5: README and verification

Steps:

- Add a README section titled "Using your own data" that explains, in plain language, how to export the CSVs and where to put them.
- Run the verify skill (`/verify`) and fix any failure it reports.

Goal prompt:

```
/goal Phase 5 complete: README.md has a section titled "Using your own data" that explains exporting campaigns.csv and events.csv into data/raw/ and running the report command; `/verify` reports every step as pass. Check: run `ruff check .` and `pytest -q`, then run `/verify`; all three exit 0 or pass; paste the verify summary. Constraints: no real exports committed or printed; plain-language wording for a first-time coder; change only README.md and code needed to pass checks; do not commit. Turn bound: stop after 25 turns and report what is missing.
```

## After Phase 5

- Read `reports/report.md` yourself and check the numbers against your ESP dashboard for one campaign.
- Ask Claude to explain any part of the code you do not understand.
- Commit only when you are ready. Ask Claude to prepare the commit message.

# Review checklist

This file is imported into CLAUDE.md with `@REVIEW.md`, so `/code-review` checks these items. Work through every item before committing.

## Privacy

- [ ] Subscriber IDs are hashed. No emails or names appear in outputs, fixtures, or test data.
- [ ] Sample data in `data/sample/` uses fake, hashed IDs only.
- [ ] No raw exports from `data/raw/` are committed, printed, or pasted into a report.

## Rates and ranking

- [ ] Click rate uses unique clickers divided by delivered.
- [ ] Unsubscribe rate uses unsubscribes divided by delivered for each send.
- [ ] Topics with fewer than 200 delivered are removed before ranking.

## Dates and timezones

- [ ] Date parsing uses an explicit format string.
- [ ] Unknown date formats raise an error. They are never guessed or silently skipped.
- [ ] Every chart title states the timezone basis (local or UTC).

## Tests

- [ ] Each parser has at least one test with a messy input.
- [ ] Tests run with `pytest -q` and pass.

## Secrets and files

- [ ] No API keys, tokens, or `.env` contents appear in any file.
- [ ] `data/raw/` and `.venv/` are listed in `.gitignore`.

# Newsletter stats spec

## Purpose

Answer two questions from exported email campaign data:

1. Which newsletter topics get the most clicks?
2. When do subscribers unsubscribe?

## Question 1: topic click rate

- Rank topics by click rate.
- Click rate = unique clickers divided by delivered.
- Only topics with at least 200 delivered are ranked. Topics below that minimum are left out of the ranking.

## Question 2: unsubscribe timing

- Show when unsubscribes cluster by weekday and hour.
- Show this per campaign and as one overall heatmap.
- Unsubscribe rate = unsubscribes divided by delivered, per send.
- Time basis: the send timestamp is UTC. If the export has a subscriber timezone field, convert to that local time. Otherwise use UTC.
- Every chart title states which time basis it uses.

## Inputs

Two CSV exports, placed in `data/raw/` (never committed):

- `campaigns.csv`: campaign_id, name, sent_at, delivered
- `events.csv`: campaign_id, subscriber_id, event_type, event_at

Notes:

- The topic is part of the campaign `name`, as a tag. One function (`src/newsletter_stats/parse.py`) reads it.
- ESP exports use mixed date formats. Each date is parsed with an explicit format. Unknown formats stop the run with an error.
- `subscriber_id` must already be hashed. No emails or names in any output.
- A timezone column is optional. Its presence is an open question.

## Outputs

One command regenerates everything: `python -m newsletter_stats.report`

- `reports/report.md`: one markdown report with the topic ranking and both charts described in plain language.
- `reports/topics.csv`: topic, delivered, unique clickers, click rate, ranked (yes or no).
- `reports/unsubscribes.png`: overall heatmap of unsubscribes by weekday and hour.
- `reports/campaign_unsubscribes.png`: unsubscribe rate per campaign, by weekday and hour.

## Definitions

- Delivered: messages the ESP reports as delivered for a campaign.
- Unique clickers: distinct subscriber_id values with a click event for a campaign.
- Click rate: unique clickers divided by delivered.
- Unsubscribe rate: unsubscribe events divided by delivered, per send.

## Out of scope for v1

- Automatic sync with an ESP API
- Sending email
- Predictions or forecasts
- Individual subscriber profiles

## Open questions for you

- Which email provider (ESP) do you use?
- Does the export include a subscriber timezone field?
- How many months of history do you have?