For Mac Mail users, summarises emails in your sent mailbox

sent-emails-summariser.scpt

-- Designed for Mac Mail users, this AppleScript program will search through the SENT items of your chosen mailbox
-- It is designed to help you (for example) if you are transitioning to a CRM system
-- run it by typing osascript sent-emails-summariser.scpt on the command line

-- It will produce summary csv file that shows:
-- How many emails were sent to each email address
-- How many emails were sent in total to all emails with the same domain ending
-- A domain rank whereby emails to domains ending .ac.uk, etc, rank higher

-- once you have the csv file, sort it in Excel e.g. by ascending DomainRank, descending DomainCount, Domain a-z, Count descending, Email a-z.


-- Handler to add leading zero
on addLeadingZero(num)
	if num < 10 then
		return "0" & num
	else
		return num
	end if
end addLeadingZero


-- Get the index in a list
on indexOf(theList, theItem)
	repeat with i from 1 to count of theList
		if item i of theList is theItem then
			return i
		end if
	end repeat
	return 0 -- Return 0 if not found
end indexOf


-- Get a datetime

set currentDate to current date
set theYear to year of currentDate as number
set theMonth to month of currentDate as number
set theDay to day of currentDate as number
set theHours to hours of currentDate as number
set theMinutes to minutes of currentDate as number
set theSeconds to seconds of currentDate as number

-- Add leading zeros to month, day, hours, minutes, and seconds if they are less than 10
set theMonth to my addLeadingZero(theMonth)
set theDay to my addLeadingZero(theDay)
set theHours to my addLeadingZero(theHours)
set theMinutes to my addLeadingZero(theMinutes)
set theSeconds to my addLeadingZero(theSeconds)

-- Construct the timestamp
set timestamp to (theYear & theMonth & theDay & "-" & theHours & theMinutes & theSeconds) as string


-- Only list emails once
set uniqueEmails to {}
set uniqueEmailNames to {}
set uniqueEmailDates to {}
set uniqueEmailDomains to {}
set uniqueEmailCounts to {}


-- Keep track of domains
set uniqueDomains to {}
set uniqueDomainCounts to {}


-- ignore generic emails
global genericEmailPatterns
set genericEmailPatterns to {"info", "support", "unsubscribe", "contact", "customer", "enquir", "help", "order", "booking"}

on isGenericEmail(email)
	global genericEmailPatterns
	repeat with genericEmailPattern in genericEmailPatterns
		if email contains genericEmailPattern then
			return 1
		end if
	end repeat
	return 0
end isGenericEmail

-- ignore common personal domains
global commonPersonalDomainPatterns
set commonPersonalDomainPatterns to {"hotmail", "gmail", "btconnect", "googlemail", "btinternet", "me.com", "yahoo", "outlook", "googlemail", "icloud", "blueyonder", "mac.com", "aol.com"}

on isCommonPersonalDomain(domain)
	global commonPersonalDomainPatterns
	repeat with commonPersonalDomainPattern in commonPersonalDomainPatterns
		if domain contains commonPersonalDomainPattern then
			return 1
		end if
	end repeat
	return 0
end isCommonPersonalDomain


-- classify other domains
global usefulDomainPatterns
set usefulDomainPatterns to {".ac.uk", ".ac.", ".ac", ".edu.", ".edu", ".org.uk", ".org", ".gov.uk", ".gov"}

on domainRank(domain)
	global usefulDomainPatterns
	set i to 1
	repeat with usefulDomainPattern in usefulDomainPatterns
		if domain contains usefulDomainPattern then
			return i
		end if
		set i to i + 1
	end repeat
	return i
end domainRank

tell application "Mail"
	set accountList to name of every account
end tell

-- Ask the user to pick an account
if (count of accountList) > 1 then
	set chosenAccount to choose from list accountList with prompt "Please select an account:" default items {item 1 of accountList}
else
	set chosenAccount to accountList
end if

-- Check if the user made a choice
if chosenAccount is not false then
	set chosenAccountName to item 1 of chosenAccount
else
	return "No account selected - exiting"
end if

log "Processing the 'Sent' mailbox of " & chosenAccountName & "..."

tell application "Mail"
	set sentMessages to messages of mailbox "Sent" of account chosenAccountName -- Replace with your account name
	set messageCount to count of sentMessages
	set processedCount to 0
	
	-- Set initial progress information
	set my progress total steps to messageCount as integer
	set my progress completed steps to 0
	set my progress description to "Processing Emails..."
	set my progress additional description to "Preparing to process."
	
	repeat with i from 1 to messageCount
		set aMessage to item i of sentMessages
		repeat with aRecipient in recipients of aMessage
			set theName to name of aRecipient
			set theAddress to address of aRecipient
			set theDate to date sent of aMessage
			set yearString to year of theDate as string
			set monthString to my addLeadingZero(month of theDate as integer)
			set dayString to my addLeadingZero(day of theDate as integer)
			set dateString to yearString & "-" & monthString & "-" & dayString
			
			
			-- Extract the domain from the email address
			set AppleScript's text item delimiters to "@"
			set emailParts to text items of theAddress
			set domain to item 2 of emailParts as string
			set AppleScript's text item delimiters to {""} -- Reset delimiters
			
			set isCommon to my isCommonPersonalDomain(domain)
			set isGeneric to my isGenericEmail(theAddress)
			
			if (isCommon is 0 and isGeneric is 0) then
				if (uniqueEmails contains theAddress) then
					set theIndex to my indexOf(uniqueEmails, theAddress)
					set currentCount to item theIndex of uniqueEmailCounts
					set newCount to currentCount + 1
					set item theIndex of uniqueEmailCounts to newCount
					
					set theIndex to my indexOf(uniqueDomains, domain)
					set currentCount to item theIndex of uniqueDomainCounts
					set newCount to currentCount + 1
					set item theIndex of uniqueDomainCounts to newCount
					
				else
					set end of uniqueEmails to theAddress
					set end of uniqueEmailNames to theName
					set end of uniqueEmailDates to dateString
					set end of uniqueEmailDomains to domain
					set end of uniqueEmailCounts to 1
					
					if (uniqueDomains contains domain) then
						set theIndex to my indexOf(uniqueDomains, domain)
						set currentCount to item theIndex of uniqueDomainCounts
						set newCount to currentCount + 1
						set item theIndex of uniqueDomainCounts to newCount
					else
						set end of uniqueDomains to domain
						set end of uniqueDomainCounts to 1
					end if
				end if
			end if
		end repeat
		
		-- Increment processed count and output progress
		set processedCount to processedCount + 1
		if processedCount mod 100 is 0 then
			set percent to (processedCount * 100) div messageCount
			log processedCount & " processed (" & percent & "%)..."
		end if
		
		set my progress additional description to "Processed email " & processedCount & " of " & messageCount
		set my progress completed steps to processedCount
	end repeat
end tell

-- Reset the progress information
set my progress total steps to 0
set my progress completed steps to 0
set my progress description to "Saving to Desktop"
set my progress additional description to ""


-- CSV Header
set header to "Domain,Email,Name,LatestDate,Count,DomainCount,DomainRank
"



-- Write the output to a file on the Desktop

-- Create the filename with timestamp
set filename to "sent-emails-overview-" & timestamp & ".csv"
set desktopPath to path to desktop folder as string
set filePath to desktopPath & filename
try
	set fileRef to open for access file filePath with write permission
	write header to fileRef
	set output to ""
	
	
	set recordCount to count of uniqueEmails
	
	repeat with i from 1 to recordCount
		set domain to item i of uniqueEmailDomains
		set domainIndex to my indexOf(uniqueDomains, domain)
		set domainCount to item domainIndex of uniqueDomainCounts
		
		if domainCount > 1 then
			set emailCount to item i of uniqueEmailCounts
			set theAddress to item i of uniqueEmails
			
			set theName to item i of uniqueEmailNames
			set dateString to item i of uniqueEmailDates
			set theDomainRank to domainRank(domain)
			
			
			set myRecord to domain & "," & theAddress & ",\"" & theName & "\"," & dateString & "," & emailCount & "," & domainCount & "," & theDomainRank & "
"
			
			write myRecord to fileRef
		end if
		
	end repeat
	
	
	
	close access fileRef
on error errorMessage number errorNumber
	close access file filePath
	log "An error occurred: " & errorMessage & " (Error code: " & errorNumber & ")"
end try

-- Output final message count
log "Total processed: " & messageCount

set my progress description to ""

return "Done"