Email Unsubscribe Tracking

Contribute Feature
9

I’ve expanded that query to kind of help with my point #2 – I can extrapolate and figure who were probably the most recent unsubscribers:

-- list users with negative email preferences and include their TL, touch dates, and Cheers
SELECT u.ID        "User ID" 
       ,u.USERNAME "Username" 
       ,u.trust_level "TL"
-- the following line optionally includes the all-time Cheers score from the Gamifaction plugin. See second join below.
       ,di.gamification_score "Cheers"
       ,CAST (u.first_seen_at AS DATE) "Joined"
       ,CAST (u.last_seen_at AS DATE) "Seen"
       ,CAST (u.last_emailed_at AS DATE) "Mailed"
        ,CASE uo.email_digests 
          WHEN 't' THEN 'Yes' 
          WHEN 'f' THEN 'NO' 
          ELSE 'Not set' 
        END        "Digests?"
        ,CASE uo.email_level
          WHEN 0 THEN 'NEVER'
          WHEN 1 THEN 'Away'
          WHEN 2 THEN 'Always'
        END        "Email lev"
        ,CASE uo.email_messages_level
          WHEN 0 THEN 'NEVER'
          WHEN 1 THEN 'Away'
          WHEN 2 THEN 'Always'
        END        "Email msg lev"
FROM   USERS u 
       LEFT OUTER JOIN USER_OPTIONS uo 
                    ON u.ID = uo.USER_ID
-- this second join includes the all-time Cheers score from the Gamifaction plugin
       LEFT OUTER JOIN directory_items di 
                    ON u.ID = di.USER_ID AND di.period_type = 1
-- use the following SELECT statement to list users receiving no Topic activity emails
WHERE  uo.email_digests = 'f' OR (uo.email_level = 0)
-- or use the following SELECT statement to list users receiving no Topic activity emails AND no Message activity emails
-- WHERE  uo.email_digests = 'f' OR (uo.email_level = 0 AND uo.email_messages_level = 0)
   AND u.ID <> -1
ORDER BY u.last_emailed_at DESC

noemail_query_results_
noemail_query_results_1058×560 52.5 KB

…but without some dates and details about unsubscribe requests, it can’t be a clear picture.

Basically what I’m looking for is: what kind of email prompted an unsubscribe; did they unsubscribe from the Digest or from everything; was an unsubscriber an active long-time user who perhaps lost interest, or were they an inactive TL0 who wasn’t really that interested to begin with?