Email Unsubscribe Tracking

Merkmal
1

We send a triggered email via Discourse to all users when a new topic is posted by an admin in a specific section. I assume some users are unsubscribing from these triggered emails and/or changing their email preferences upon receiving the email.

Is there a way to track the number of unsubscribes?

1 „Gefällt mir“
2

You could use the data explorer plugin to search which users are not watching the category.

2 „Gefällt mir“
3

Good question, any thoughts here @sam?

4

Not at the moment but I think a report (and tracking) of where the atomic unsubscribe comes from is interesting

6 „Gefällt mir“
5

Hi again.

We were able to use the data explorer plugin to find the users who are no longer watching the category (thanks @pfaffman!). We would also like to find the users who unsubscribe by clicking on “To unsubscribe from these emails, click here” and then selecting “Don’t send me any mail from Squarespace Circle Forum”

Do you have any insight on the best way to query the results for the don’t send me any mail folks?

CC: @Emaddy

5 „Gefällt mir“
7

:man_zombie: I couldn’t find more recent discussion of this…

I would also find a report & tracking of atomic unsubscribes interesting.

I do have the Data Explorer query to list digest-unsubscribed users. But:

  1. Unsubscribe dates aren’t recorded, so it’s hard to spot a trend.
  2. Sometimes I’d like an idea of whether they’re new or old users, how active/inactive they’ve been, whether they’ve also requested no emails whatsoever, etc.
1 „Gefällt mir“
8

This seems like a legit new feature request, if there really is no way to see which email addresses (and how many) are unsubscribing as a result of receiving emails from a Discourse site. I will slide this over to Feature so it can be discussed.

1 „Gefällt mir“
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?