majiayu000 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 |
|---|---|---|---|
| newsletter@service.com | 45 | 12% | Rarely read |
| updates@useful.com | 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]