Mail Newsletter

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 (listidhash)

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 (

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]

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [aashari](https://github.com/aashari)
- **Source:** [aashari/ai-agent-skills](https://github.com/aashari/ai-agent-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.