mail-newsletter

name	mail-newsletter
description	Identify newsletters and mailing lists in email, show volume per sender, and help clean up subscriptions. Use when user wants to see what newsletters they're subscribed to, audit mailing lists, or identify email noise. Arguments: optional time range or "unsubscribe" to surface opt-out links.
disable-model-invocation	false
user-invocable	true
allowed-tools	Bash
metadata	{"openclaw":{"requires":{"bins":"[Truncated]"}}}

Mail Newsletter — Subscription and Mailing List Audit

Analysis: $ARGUMENTS (default: last 90 days)

Step 1: Detect newsletters via unsubscribe_type (most accurate)

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"
SINCE=$(($(date +%s) - 7776000))  # 90 days

sqlite3 "$DB" "
SELECT a.address, a.comment as name,
       m.unsubscribe_type,
       COUNT(*) as count,
       SUM(CASE WHEN m.read=1 THEN 1 ELSE 0 END) as read_count,
       ROUND(100.0 * SUM(m.read) / COUNT(*), 0) as read_pct,
       MAX(datetime(m.date_received,'unixepoch','localtime')) as latest
FROM messages m
JOIN addresses a  ON m.sender  = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE m.date_received >= ${SINCE}
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Trash%'
  AND mb.url NOT LIKE '%Sent%'
  AND m.unsubscribe_type > 0
GROUP BY a.address
ORDER BY count DESC;" 2>/dev/null

Step 2: Catch high-volume bulk senders without unsubscribe headers

sqlite3 "$DB" "
SELECT a.address, a.comment, COUNT(*) as cnt,
       ROUND(100.0 * SUM(m.read) / COUNT(*), 0) as read_pct
FROM messages m
JOIN addresses a ON m.sender = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE m.date_received >= ${SINCE}
  AND m.deleted=0
  AND m.automated_conversation = 2
  AND m.unsubscribe_type = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Sent%'
GROUP BY a.address
HAVING cnt >= 10
ORDER BY cnt DESC LIMIT 20;" 2>/dev/null

Step 3: Group by mailing list (list_id_hash)

To see distinct mailing lists (e.g. a company using multiple sender addresses):

sqlite3 "$DB" "
SELECT m.list_id_hash, COUNT(*) as cnt, COUNT(DISTINCT a.address) as sender_variants,
       MAX(a.comment) as name_sample
FROM messages m
JOIN addresses a ON m.sender = a.ROWID
WHERE m.date_received >= ${SINCE}
  AND m.deleted=0
  AND m.list_id_hash IS NOT NULL AND m.list_id_hash != 0
GROUP BY m.list_id_hash
ORDER BY cnt DESC LIMIT 20;" 2>/dev/null

Step 4: If "unsubscribe" requested, find opt-out links

For senders with low read rates (< 30%), find unsubscribe links in body:

python3 ~/.claude/skills/_mail-shared/parser.py <ROWID>

Look for unsubscribe URLs in the body text.

Step 5: Calculate noise impact

Total emails from newsletter/automated senders vs. total inbox volume = % noise.

Output Format

Newsletter/Mailing List Audit — [PERIOD]

Sender	Emails	Read Rate	Verdict
[e-mail]	45	12%	Rarely read
[e-mail]	28	89%	Engaged

Unsubscribe Candidates (high volume + low read rate): [list with unsubscribe links if available]

Total newsletter volume: X emails = Y% of inbox Estimated time saved if unsubscribed from low-engagement ones: [estimate]