Author Topic: Sending cseq of original list during signup-extra (Read 4753 times)

boundless · « **on:** February 04, 2005, 02:59:19 am »

Something that would be extremely helpful as a marketer would be the ability to know at what point a person became a customer in the followup process.

For example:

A person signs up for our sample material (List 1) and are emailed a series of follow-up emails as a result.

When they become a customer, they are signed up on another list (List 2), and signup-extra runs, removing them from list 1.

If, along with the notification that they have been signed up for the new list, we are also told the "cseq" they were on on the original list (List 1), we would be able to see what email in our follow-up process might have prompted them to make the purchase.

This would be EXTREMELY valuable information and would allow us to fine-tune our follow-up, and perhaps shorten our conversion time considerably!

This is one of those items that I know would only take a line or two of SQL to do, but I'm just not savvy enough write that SQL without possibly screwing things up.

If Dean can't implement this any time soon, perhaps someone else on the list can suggest the line of modified code for the admin.php file or signup-extra??

Bob

BGSWebDesign · « **Reply #1 on:** February 04, 2005, 07:28:33 am »

Hi Bob,

That sounds like a great idea and something that would be useful to me as well, maybe we can work something up in this forum...

For starters see this post on how to extract table data to an Excel file for analysis:
http://listmailpro.com/forum/index.php?topic=29.0

From there you would do something similar, only use the lm_users table to extract the data from, you would need the cseq field as you mentioned, and the cdel (delay field), and the id, or the uid field to grab the unique records that match, order by the cseq field and the cdel (delay) field to get the order of when they leave one list.

I'll try to work up a sql query for it... has anyone else done this already?
DW - your input please?

Thanks,

DW · « **Reply #2 on:** February 08, 2005, 11:57:23 am »

This would be fairly simple to record.

In signup-xtra.php for list 2, before the user is removed from list 1 we could add a few statements to grab the value of cseq and cdel and put it into a custom field for the new entry on list 2.

After that, it will take a very small custom script to calculate the names of the followups and the number of users that were converted from each.

I'd be happy to help but I'm out for an hour or so. I'll see what I can do this afternoon/evening.

Regards!

boundless · « **Reply #3 on:** February 08, 2005, 01:25:22 pm »

Quote from: "DW"

I'd be happy to help but I'm out for an hour or so. I'll see what I can do this afternoon/evening.

Regards!

That would be OUTSTANDING! This kind of information is invaluable to marketers!

Bob

DW · « **Reply #4 on:** February 08, 2005, 11:28:39 pm »

Okay, let's see what we have here.

For signup-xtra.php:

Code: [Select]

<?php
$list1 = '1';
$list2 = '2';

if($list==$list2){
 $zrows = mysql_query("select id,cseq from $utable where list = '$list1' and email like '$email'",$link);
 if(@mysql_num_rows($zrows)>0){
  list($zid,$zseq)=mysql_fetch_row($zrows);
  mysql_query("delete from $utable where id = '$zid'");
  mysql_query("update $utable set user10 = '$zseq' where uid = '$uid';");
 }
}
?>

And the script to view the results, once there are some:

Code: [Select]

<?php
// ** Be sure to place this script IN your ListMail directory
// read tracked seq #s from custom field 10 list 2
// Login to ListMail and browse to this script

include("./config.php");
include("./admin.php");

$islogged = islogged();

ini_set("max_execution_time","0");

if($islogged){$isadmin='true';} else { adminheader('Not logged in','Not logged in','x');echo "<center>You must <a href=login.php>login</a>."; exit; }
if ($isadmin == 'true'){

echo "<html><body><a href=./>Back to ListMail</a><br><br>";

// go through followups for list2

$rows = mysql_query("select subject,seq from $ftable where list = '2' order by seq;");
if(@mysql_num_rows($rows)>0){
 echo "<table border=0 cellspacing=2 cellpadding=0><tr><td>Followup Seq & Subject</td><td>Upgrades</td></tr>";
 while(list($subj,$seq)=mysql_fetch_row($rows)){
  $urows = mysql_query("select count(*) from $utable where list = '2' and user10 = '$seq';");
  if(@mysql_num_rows($urows)>0){
   list($cnt)=mysql_fetch_row($urows);
  } else $cnt = '0';
  echo "<tr><td>$seq - $subj<br></td><td>$cnt<br></td></tr>"; 
 }
 echo "</table></body></html>";
}

}
?>

This is completely untested but I gave it a once-over.. :shock: Please let me know if you have any troubles.