Agent skill
mail-top-senders
Show who sends the most email, communication frequency analysis, and relationship mapping. Use when user asks who emails them most, top contacts, communication patterns, or wants to understand their email social graph. Arguments: optional time range (default: last 90 days), account filter, or "humans only" to exclude automated senders.
Install this agent skill to your Project
npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/other/other/mail-top-senders
Metadata
Additional technical details for this skill
- openclaw
-
{ "requires": { "bins": [ "sqlite3" ] } }
SKILL.md
Mail Top Senders — Communication Frequency Analysis
Analysis period: $ARGUMENTS (default: last 90 days)
Step 1: All senders ranked by volume
DB="$HOME/Library/Mail/V10/MailData/Envelope Index"
SINCE=$(($(date +%s) - 7776000)) # 90 days
sqlite3 "$DB" "
SELECT a.address, a.comment as name,
COUNT(*) as total,
SUM(CASE WHEN m.read=0 THEN 1 ELSE 0 END) as unread,
MIN(datetime(m.date_received,'unixepoch','localtime')) as first,
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%'
GROUP BY a.address
ORDER BY total DESC
LIMIT 50;" 2>/dev/null
Step 2: Separate humans from automated senders
Use automated_conversation and unsubscribe_type columns (more reliable than address-pattern matching):
automated_conversation = 0+unsubscribe_type = 0→ real humansautomated_conversation = 1→ transactional (Jira, Slack, alerts)automated_conversation = 2ORunsubscribe_type > 0→ bulk/newsletters (noise)
# Human senders only (automated_conversation = 0, no unsubscribe header)
sqlite3 "$DB" "
SELECT a.address, a.comment as name, COUNT(*) as cnt
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 '%Sent%'
AND m.automated_conversation = 0
AND m.unsubscribe_type = 0
GROUP BY a.address ORDER BY cnt DESC LIMIT 20;" 2>/dev/null
Step 3: Domain-level analysis
sqlite3 "$DB" "
SELECT substr(a.address, instr(a.address,'@')+1) as domain,
COUNT(*) as cnt
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%'
GROUP BY domain ORDER BY cnt DESC LIMIT 20;" 2>/dev/null
Step 4: Thread participation (two-way communication)
Find senders you also replied to — true relationships vs. one-way communication:
sqlite3 "$DB" "
SELECT a.address, COUNT(*) as received
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 '%Sent%'
GROUP BY a.address
ORDER BY received DESC LIMIT 30;" 2>/dev/null
Output Format
Top Email Relationships — [PERIOD]
Most Frequent Human Contacts:
| Rank | Name | Address | Emails Received | Unread |
|---|
Top Automated Senders (noise):
| Service | Count | Type |
|---|
Top Domains:
| Domain | Count |
|---|
Insight: note anyone with high unread rate (you receive a lot but don't read → de-prioritize subscription?). Note anyone with very recent last email who you haven't read → potential missed message.
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
majiayu000/claude-skill-registry
agent-ops-spec
Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.
majiayu000/claude-skill-registry
agent-ops-state
Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.
majiayu000/claude-skill-registry
agent-ops-spec
Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.
majiayu000/claude-skill-registry
agent-ops-testing
Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.
majiayu000/claude-skill-registry
agent-ops-testing
Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.
majiayu000/claude-skill-registry
agent-ops-state
Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.
Didn't find tool you were looking for?