Find which followups have the most removes

[sanj]

hi,
Is there any way of finding out which exact email from my autoresponder followups people are unsubcribing from.

It would be great if that info could be added to the email sent to anministrator when user unsubscribe.

It would be really helpful cos that way I can know which letters are working and which ones i need to change.

Dean I'm sure you've got a script somewhere that can do this right!

thanks
sanj

[DW]

Hi SanJ,

On the User Database page you can change the listing from "Active" to "Removed" users and then look at the Seq #s.  A simple query could count the Seq #s with the most removes...

What about this?

Code: [Select]

SELECT cseq, count(*) as cnt FROM lm_users WHERE list = '1' and cnf = '2' GROUP BY cseq ORDER BY cnt DESC
Regards

[sanj]

hi,
the only problem is that i don't keep unsubscribed users in the database.
isn't there a script that i can use and run it from a browser?
thanks
sanj

[DW]

SanJ,

Without the data in the database there is no information to analyze.  What could be done is an extra process added after removal takes place.  This process could insert the date, list number and seq # of users into a custom table as they are removed.  Then, a custom script or query could read this data.  It may be easier to turn "Keep removed users in database" on and then manually (or with a script) delete some or all removed users on demand or on a schedule.

[sanj]

hi,

i'll start keeping unsubscribe users in database.
Could you write me a little php script to use to run this query

Code: [Select]

SELECT cseq, count(*) as cnt FROM lm_users WHERE list = '1' and cnf = '2' GROUP BY cseq ORDER BY cnt DESC

so that i can just check from a browser

thanks
sanj

[DW]

Hi SanJ,

Thanks for your patience.  I went ahead and made this for you.

Updated: Nov 6, 2007

Code: [Select]

<?phpinclude('./config.php');include('./admin.php');// ListMail login requiredif(!islogged()){ header("Location: ./login.php?r=".urlencode(substr($_SERVER['REQUEST_URI'],strrpos($_SERVER['REQUEST_URI'],'/')+1))); exit;}echo "<h4>Which followups have the most all-time removes?</h4>Choose a list: ";// Loop lists with followups$fup_rows = mysql_query("select list from $ftable group by list order by list;");echo "<form method=post><select name=list>";while(list($flist)=@mysql_fetch_row($fup_rows)){ if($flist==0) continue; list($ltitle)=@mysql_fetch_row(mysql_query("select title from $ltable where listnum = '$flist';")); echo "<option value=$flist"; if($list==$flist) echo ' selected'; echo ">List $flist: $ltitle\n";}echo "</select> <input type=submit value=View><input type=hidden name=submit value=1></form>\n";if($submit){ echo "<hr size=1>\n"; // check counts $rows=mysql_query("SELECT cseq, count(*) as cnt FROM $utable WHERE list = '$list' and cnf = '2' GROUP BY cseq ORDER BY cnt DESC;"); if(@mysql_num_rows($rows)>0){  echo "<table border=1 cellpadding=3 cellspacing=0><tr><td>Count</td><td>Seq #</td><td>Subject</td>\n";  while(list($cseq,$cnt)=mysql_fetch_row($rows)){   list($ftitle)=mysql_fetch_row(mysql_query("select subject from $ftable where list = '$list' and seq = '$cseq';"));   echo "<tr><td>$cnt</td><td>$cseq</td><td>$ftitle</td></tr>\n";  }  echo "</table>\n"; } else {  echo "No results<br>"; }}?>

This should remain fairly accurate as all renumbering, followup additions, etc. should take effect for all users on a list regardless of status.

I can't guarantee I will be able to put the same effort in next time - I'm swamped with custom stuff and it's starting to affect LMP development again. :cry:

Regards

[sanj]

hi Dean,
Thanks a lot for this script.
You are the man!

sanj

p.s Do I have to change the list number everytime i run the script.
because with list=1 i get the same reults for all databases and with list =2 i get results for database 2 and so on.

Code: [Select]

$rows=mysql_query("SELECT cseq, count(*) as cnt FROM $utable WHERE list = '1' and cnf = '2' GROUP BY cseq ORDER BY cnt DESC;");

[DW]

Hi SanJ,

I made a mistake, change:

Code: [Select]

WHERE list = '1'
to

Code: [Select]

WHERE list = '$list'
Regards

[don1]

DW,

You are so good at helping so many people with their individual requests and customized scripts...so here is my question which has been asked before in different ways:

Can you incorporate most of these "custom scripts" you have written into the "ListMail" program?   I would imagine many of your users/clients could use these features or you could use the new features as "selling points".

BTW,  when can we expect the new upgrade   ...

Thanks for everything!!!!

Don

[DW]

Hi Don,

This particular request got a little out of hand for a free one. I have had thoughts about setting up a place where all such plugins can be viewed and downloaded and I do expect to do that at some point.  Up until a couple weeks ago when I became swamped with 5 server setups and 10 installations (I'll still be dealing with these early next week.. ) I was working away passionately on the LMP update.  I did make some major changes to the ListMail structure but now I'm not sure I like it.  I might redo it again....  Sorry it's taking so long but I'm STILL gaining valuable experience simplifying sites and programs even after all these years.  What I really need is a nice simple and modular design/structure that makes expansion easy. ListMail should basically be completely rewritten and then an upgrade process developed for backward compatibility...  I am completing a bunch of work I've been swamped with then will be deciding what to do about the update.

Regards

[don1]

DW,

Thanks for getting back!  The program just the way it is works really well for me...even though there are some features I would like added.

You have personally been so helpful to me and many others... I just want to thank you again and look forward to the updates!!


Don