---
name: mail-stats-092dd744c310
description: >-
  Email volume statistics, trends, and patterns. Show daily/weekly/monthly volume, peak periods, read rates, and account breakdowns. Use when user asks about email statistics, how much email they get, trends, or wants analytics on their inbox. Arguments: optional time range or specific metric like "by day", "by account", "unread rate".
disable-model-invocation: false
user-invocable: true
allowed-tools: Bash
metadata:
  openclaw:
    requires:
      bins: [sqlite3]
---

# Mail Stats — Email Analytics

Analysis: **$ARGUMENTS** (default: last 30 days overview)

## Volume by day (last 30 days)
```bash
DB="$HOME/Library/Mail/V10/MailData/Envelope Index"
SINCE=$(($(date +%s) - 2592000))

sqlite3 "$DB" "
SELECT date(datetime(m.date_received,'unixepoch','localtime')) as day,
       COUNT(*) as total,
       SUM(CASE WHEN m.read=0 THEN 1 ELSE 0 END) as unread
FROM messages m
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 day ORDER BY day;" 2>/dev/null
```

## Volume by week (last 12 weeks)
```bash
sqlite3 "$DB" "
SELECT strftime('%Y-W%W', datetime(m.date_received,'unixepoch','localtime')) as week,
       COUNT(*) as total
FROM messages m
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE m.date_received >= (strftime('%s','now') - 7257600)
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Sent%'
GROUP BY week ORDER BY week;" 2>/dev/null
```

## Volume by hour of day (when do emails arrive?)
```bash
sqlite3 "$DB" "
SELECT strftime('%H', datetime(m.date_received,'unixepoch','localtime')) as hour,
       COUNT(*) as cnt
FROM messages m
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 hour ORDER BY hour;" 2>/dev/null
```

## By account
```bash
sqlite3 "$DB" "
SELECT mb.url,
       COUNT(*) as total,
       SUM(CASE WHEN m.read=0 THEN 1 ELSE 0 END) as unread,
       ROUND(100.0 * SUM(m.read) / COUNT(*), 1) as read_rate_pct
FROM messages m
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 substr(mb.url, 1, instr(mb.url || '/', '/', 8))
ORDER BY total DESC;" 2>/dev/null
```

## All-time totals (fast path via mailboxes)
```bash
# total_count is pre-maintained per mailbox — no COUNT(*) needed for totals
sqlite3 "$DB" "
SELECT SUM(total_count) as total_indexed,
       SUM(unread_count) as reported_unread
FROM mailboxes
WHERE url NOT LIKE '%Spam%' AND url NOT LIKE '%Trash%' AND url NOT LIKE '%Sent%';" 2>/dev/null

# Note: unread_count is accurate for EWS/Exchange accounts only; IMAP always shows 0.
# For accurate total unread, use:
sqlite3 "$DB" "
SELECT COUNT(*) as total_unread
FROM messages m
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE m.deleted=0 AND m.read=0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Sent%';" 2>/dev/null

sqlite3 "$DB" "
SELECT SUM(CASE WHEN m.flagged=1 THEN 1 ELSE 0 END) as total_flagged,
       MIN(datetime(m.date_received,'unixepoch','localtime')) as oldest,
       MAX(datetime(m.date_received,'unixepoch','localtime')) as newest
FROM messages m WHERE m.deleted=0;" 2>/dev/null
```

## Output Format
**Email Statistics**

All-time: X total messages across Y accounts, oldest: [date]

Last 30 days:
- Average per day: N
- Busiest day: [date] (N emails)
- Quietest: [date]
- Peak hour: [hour] UTC+offset
- Read rate: X%
- Overall unread: N

Daily volume chart (ASCII or table).
Per-account breakdown.
Trends: increasing/decreasing compared to previous period if calculable.


---
Source attribution: aashari
Original project: https://github.com/aashari/ai-agent-skills/blob/42445bdfaaf6d2dddcd74ff5c3e6fd82c4287867/skills/apple-mail/mail-stats/SKILL.md
License: MIT
License information: https://opensource.org/license/mit
Original upstream instructions; downloaded from Mega AI Hub.

## Upstream license notice

MIT License

Copyright (c) 2026 Ashari Tech

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.



# Mega AI Hub resource metadata

Creator / publisher: aashari
Original project: https://github.com/aashari/ai-agent-skills/blob/42445bdfaaf6d2dddcd74ff5c3e6fd82c4287867/skills/apple-mail/mail-stats/SKILL.md
Attribution basis: Source repository record

Fields of work: Marketing, Data & Analytics
Roles: Email Marketer, Marketing Analyst, Data Analyst
Specialties: API, Database
Tools: SQLite, Bash
Country applicability: Country not specified
Classification basis: Source category, role and specialization labels
Instruction SHA-256: 26c6b06bb20958d23f21549864d8dbaf77223a60ca6a94588a1afb3ccf616265