mail-subscriptions

Mail Subscriptions — Recurring Charge Audit

Scanning: $ARGUMENTS (default: last 13 months)

Step 1: Find all subscription/renewal emails

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"
SINCE=$(($(date +%s) - 34214400))  # 13 months = 396 days

sqlite3 "$DB" "
SELECT datetime(m.date_received,'unixepoch','localtime') as dt,
       s.subject, a.address as sender, a.comment as name,
       mb.url, m.ROWID
FROM messages m
JOIN subjects  s  ON m.subject = s.ROWID
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 (
    s.subject LIKE '%subscription%'
    OR s.subject LIKE '%renewal%'
    OR s.subject LIKE '%renew%'
    OR s.subject LIKE '%auto-renew%'
    OR s.subject LIKE '%billing%'
    OR s.subject LIKE '%monthly%'
    OR s.subject LIKE '%annual%'
    OR s.subject LIKE '%your plan%'
    OR s.subject LIKE '%membership%'
    OR s.subject LIKE '%your invoice%'
    OR s.subject LIKE '%receipt for%'
    OR s.subject LIKE '%charged%'
    OR s.subject LIKE '%continued%'
    OR s.subject LIKE '%successfully renewed%'
  )
ORDER BY m.date_received DESC;" 2>/dev/null

Step 2: Group by sender/service to find recurring patterns

sqlite3 "$DB" "
SELECT a.address, a.comment, COUNT(*) as occurrences,
       MIN(datetime(m.date_received,'unixepoch','localtime')) as first_charge,
       MAX(datetime(m.date_received,'unixepoch','localtime')) as last_charge
FROM messages m
JOIN subjects  s  ON m.subject = s.ROWID
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 (s.subject LIKE '%subscription%' OR s.subject LIKE '%renewal%'
       OR s.subject LIKE '%receipt%' OR s.subject LIKE '%invoice%'
       OR s.subject LIKE '%billing%' OR s.subject LIKE '%charged%')
GROUP BY a.address
HAVING COUNT(*) >= 1
ORDER BY occurrences DESC;" 2>/dev/null