Query to retrieve Unsubscribers

[NYChris]

I thought some of you might find this helpful.
I was looking for a way to get a list of people that unsubscribed.
The info I wanted was the Email address, Date, and Reason (if any was given).
This is the query I came up with:

Code: Select all

SELECT
    phplist_user_user.email
    , phplist_user_user_history.date
    , phplist_user_blacklist_data.data
FROM
    mydtata_plst1.phplist_user_user_history
    INNER JOIN mydtata_plst1.phplist_user_user
        ON (phplist_user_user_history.userid = phplist_user_user.id)
    INNER JOIN mydtata_plst1.phplist_user_blacklist_data
        ON (phplist_user_user.email = phplist_user_blacklist_data.email)
WHERE phplist_user_user_history.detail LIKE 'Unsubscribed%'
ORDER BY phplist_user_user_history.date ASC;


Of course, you'll have to change "mydtata" in the table names to fit your tables.
It works well with humongous mailing lists.
Let me know if you find it useful or can improve on it.

[julienl]

Thanks for the query ! I was searching something like that last month ! I'm not a SQL expert so ... !

I don't know if phplist can do that : find the list(s) users have unsuscribed.

Why ? Because I must handle unsubscription manually in a special app with no link to phplist for now

[NYChris]

Mine has been modified like crazy so when people unsubscribe they wind up in a special list. And that's why this query doesn't work for me.
But it might for you...

Code: Select all

SELECT
    phplist_user_user.email
    , phplist_user_user_history.date
    , phplist_list.name
    , phplist_user_blacklist_data.data
    , phplist_user_user_history.detail
FROM
    mydtata_plst1.phplist_listuser
    INNER JOIN mydtata_plst1.phplist_list
        ON (phplist_listuser.listid = phplist_list.id)
    INNER JOIN mydtata_plst1.phplist_user_user
        ON (phplist_user_user.id = phplist_listuser.userid)
    INNER JOIN mydtata_plst1.phplist_user_user_history
        ON (phplist_user_user.id = phplist_user_user_history.userid)
    INNER JOIN mydtata_plst1.phplist_user_blacklist_data
        ON (phplist_user_blacklist_data.email = phplist_user_user.email)
WHERE phplist_user_user_history.detail LIKE 'Unsubscribed%' AND phplist_user_user_history.date >='2010-02-02%'
ORDER BY phplist_user_user_history.date ASC;


By the way, this one also allows you to set the date for your query.
In this case it's only going to show you results for people that unsubscribed after (and including) Feb. 2nd, 2010