Query to retrieve Unsubscribers

Solutions for other advanced phplisters

Query to retrieve Unsubscribers

Postby NYChris » 4:50pm, Mon 25 Jan, 2010

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.
NYChris
PL Geek
 
Posts: 59
Joined: 4:55pm, Mon 29 Jun, 2009

Re: Query to retrieve Unsubscribers

Postby julienl » 2:04pm, Mon 01 Feb, 2010

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 :(
julienl
PL Geek
 
Posts: 58
Joined: 8:20am, Wed 22 Jul, 2009
Location: France

Re: Query to retrieve Unsubscribers

Postby NYChris » 3:40pm, Mon 08 Feb, 2010

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
NYChris
PL Geek
 
Posts: 59
Joined: 4:55pm, Mon 29 Jun, 2009


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 3 guests