# Email Unsubscribe Tracking

**URL:** <https://meta.discourse.org/t/email-unsubscribe-tracking/89979>\
**Category:** Feature\
**Tags:** email\
**Created:** [June 15, 2018, 3:32pm UTC](https://meta.discourse.org/t/email-unsubscribe-tracking/89979 "2018-06-15T15:32:16Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

**Author:** ![ToddZ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/toddz/32/328350_2.png) [@ToddZ](https://meta.discourse.org/u/ToddZ)\
**Post date:** [October 15, 2025, 12:50am UTC](https://meta.discourse.org/t/email-unsubscribe-tracking/89979/9 "2025-10-15T00:50:58Z")

</div>

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:

```sql
-- 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_](https://global.discourse-cdn.com/meta/original/4X/0/e/2/0e24344a32277840c423731e5ef06aef7d8f53a3.gif)

…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?

---

_[View the full topic](https://meta.discourse.org/t/email-unsubscribe-tracking/89979)._