Author Topic: Extracting data from tracked links thru PhpMyAdmin  (Read 53779 times)

KB

  • Posts: 23
    • View Profile
Extracting data from tracked links thru PhpMyAdmin
« on: January 31, 2005, 09:10:31 pm »
Hi

I want to extract the data of who has clicked thru a tracked link.
I assume PhpMyAdmin is the way but I can"t seem to get the data out
in a useable form to import back into listmail.
Any ideas?

Regards

Kevin

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • https://legacy.listmailpro.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #1 on: February 01, 2005, 05:35:49 pm »
I plan to improve existing and add new tracking soon.

Tracked hits are store in the lm_hits table.

There are 4 fields, id, uid, kid, and date.

The id field is an auto-incrementing ID which you don't need to worry about.  (may be removed soon to prevent limitations/slowdowns)

The uid field contains the users id from the lm_users table.

The kid field contains the id of the link from the lm_links table.

So, the data is all there.  What I need to know now is what data you are trying to extract?

Regards!
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

KB

  • Posts: 23
    • View Profile
data I want
« Reply #2 on: February 01, 2005, 06:39:59 pm »
Hi Dean

I want to be able to extract as much of the personal data as possible - name, lname, ip, email,site visited(Code is fine) in a format that i can reimport into a new list.

Whar would be really great is if tey could be deleted from the list they were is before automatically.

Regards

Kevin

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • https://legacy.listmailpro.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #3 on: February 02, 2005, 02:41:09 pm »
So, you'd like to extract the users who have clicked through to your tracked links so that you can build a highly responsive list, correct?

This would take a small custom script.

Do you want anyone who has clicked any link to be put on a single list?

Let me know and I'll help you out.

Regards
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

KB

  • Posts: 23
    • View Profile
List extraction
« Reply #4 on: February 02, 2005, 05:34:17 pm »
Hi Dean

Thanks for getting back on this. I would like the option to to split the lists according to site visited. IT would have to be linked to the code I give each link I guess.

Regards

kevin

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • https://legacy.listmailpro.com
Custom Code - Create List and Move All Users Who Clicked...
« Reply #5 on: February 03, 2005, 06:49:31 pm »
Kevin,

Copy the code below into a new file, e.g.: codespecial.php and upload it to your main ListMail folder.  Login to ListMail, then modify the URL in the address bar to browse to the custom script.

You'll be presented with a dropdown showing all of your links.  Choose the link you want to process and hit "Create List".  All users on all lists who have clicked this link will be moved to a new list (created with a List Name the same as the Link's Title).  If you run the process twice on the same link there will be a new list again with all users from all lists, so the OLD new list will still exist but have no users.

I hope this works as you like!

Note: Modified to work with v1.88
Last Updated: May 16, 2014
Code: [Select]
<?php
// process specified link code
// create a new list with all users who clicked code
// ** Be sure to place this script IN your ListMail directory

// v1.8 Note: List is configured with the Default HTML and Error Messages

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'){

$link mysql_connect($sqlhost,$sqluser,$sqlpass);
mysql_selectdb($sqldb);

if(
$_POST['submit']=='Copy'){ $copy 1$move ''$append '';}
if(
$_POST['submit']=='Move'){ $copy ''$move 1$append '';}
if(
$_POST['submit']=='Append'){ $copy ''$move ''$append 1; }

if(
$move$txt 'moved'; elseif($copy$txt 'copied'; elseif($append$txt 'appended';

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

if(
$process_link){
 
// create new list with link ref code (if doesn't exist)

 
list($kref)=mysql_fetch_row(mysql_query("select refcode from $ktable where id = '".addslashes($process_link)."'"));

 if(
$append){ $newlist $list; echo "Target "; } else { $newlist new_list($kref); echo "New "; }
 echo 
"list num = $newlist<br>";

 
// get user hits for this link
 
$cmd "select uid from lm_hits where kid = '".addslashes($process_link)."' group by uid";
 
$rows mysql_query($cmd);
 echo 
"new list: $newlist<br>$txt users: ";
 if(@
mysql_num_rows($rows)>0){
  while(list(
$uid)=mysql_fetch_row($rows)){
   if(
$move){
    
// update the user with the new list
    
mysql_query("update lm_users set list = '$newlist' where id = '".addslashes($uid)."'");
   } elseif(
$copy || $append){
    
// get all user data and create a new user on the new list
    
list($fn,$ln,$em,$u1,$u2,$u3,$u4,$u5,$u6,$u7,$u8,$u9,$u10,$cn,$da,$ip,$ru,$htm)=mysql_fetch_row(mysql_query("select fname,lname,email,user1,user2,user3,user4,user5,user6,user7,user8,user9,user10,cnf,dateadd,ipaddr,refurl,htmail from lm_users where  id = '".addslashes($uid)."';"));
    
// create new uid
    
$uniq '';
    while(!
$uniq){
     
$newuid unique_id(7);
     if(@
mysql_num_rows(mysql_query("select id from $utable where uid = '$newuid'"))==0$uniq=1;
    }
    
// insert user if not already on new list
    
$trow mysql_query("select id from lm_users where email like '$em' and list = '$newlist'");
    if(@
mysql_num_rows($trow)==0){
     
mysql_query("insert into lm_users values('','$newuid','$newlist','".addslashes($fn)."','".addslashes($ln)."','".addslashes($em)."','".addslashes($u1)."','".addslashes($u2)."','".addslashes($u3)."','".addslashes($u4)."','".addslashes($u5)."','".addslashes($u6)."','".addslashes($u7)."','".addslashes($u8)."','".addslashes($u9)."','".addslashes($u10)."','1','','$cn','".addslashes($da)."','".addslashes($ip)."','".addslashes($ru)."','".addslashes($htm)."','0');") or die('user insert error..'.mysql_error());
    } else {
     echo 
"skip";
    }
   }
   echo 
"$uid "flush();
  }
 } else echo 
"process aborted: no tracked users found for selected link<br>";
 echo 
"<br><br>";
}
// show all links
$cmd "select id,refcode,title,http,hits from $ktable where 1 order by refcode";
$rows mysql_query($cmd);

echo 
"This script will create a new list containing all users from any list who have clicked through a certain link.  Users can be copied or moved from their old list.<br><br>";

if(@
mysql_num_rows($rows)>0){
 echo 
" Select link to process:<br><br><form method=post><input type=hidden name=process value=1><select name=process_link>";
 while(list(
$i,$r,$t,$h,$s)=mysql_fetch_row($rows)){
  echo 
"<option value=$i>$r - $h";
 }
 echo 
"</select><br><br><input name=submit type=submit value=\"Move\">&nbsp;<input name=submit type=submit value=\"Copy\">&nbsp;or&nbsp;<input name=submit type=submit value=\"Append\">&nbsp;to&nbsp;<select name=list>\n";
 
$lrows mysql_query("select listnum,title from $ltable where 1 order by listnum");
 while(list(
$li,$ti)=mysql_fetch_row($lrows)){
  echo 
"<option value=$li>$li$ti\n";
 }
 echo 
"</select></form>";
} else echo 
"no links found!<br>";

}

function 
new_list($refcode){
 global 
$link; global $ltable; global $vtable;

 
$cmd "select id,listnum from $ltable where title like '".addslashes($refcode)."' limit 1";
 
$result mysql_query($cmd);
 if(@
mysql_num_rows($result)>0){
  list(
$id,$nlnum)=mysql_fetch_row($result);
 } else {
  
$cmd "select id,listnum from $ltable where id >= '0' order by listnum desc limit 0,1";
  
$result mysql_query($cmd);

  while(list(
$id,$listnum) = @mysql_fetch_row($result)){
   
$nlnum $listnum+1;
   
$xmails explode(';',$adminmail); $adminmail $xmails[0];
   
$dom getdomain();
   
$cmd2 "insert into $ltable values('null','$nlnum','".addslashes($refcode)."','news@$dom','New List Newsletter','Welcome!','Welcome to the mailing list!','','','1','Please verify your subscription','Click the link below to verify your email address for subscription to the yoursite.com newsletter\n\n!confirm','','','0','remsubj','remmsg','','','','','','', '','','','','','','','','','','0;0;0;0;0','$adminmail','0;1','1','1','')";
   
mysql_query($cmd2) or die('list creation error: '.mysql_error());
  }
 }
 return 
$nlnum;
}
?>
« Last Edit: May 16, 2014, 05:37:14 am by DW »
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

KB

  • Posts: 23
    • View Profile
Yowsa
« Reply #6 on: February 03, 2005, 08:52:11 pm »
Hi Dean

Great - I'll have a bash at following your instructions.

Regards

Kevin

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • https://legacy.listmailpro.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #7 on: February 03, 2005, 10:39:59 pm »
I can modify the script to create the list with the link ref code if that's more useful.  I could also put in a check to see if a list named with that ref code exists and, if so, move users to the existing list instead of creating a new one.

Regards :)
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

KB

  • Posts: 23
    • View Profile
yabadabdo
« Reply #8 on: February 03, 2005, 11:52:37 pm »
Hi Dean

Yes please.

BGSWebDesign

  • Posts: 625
    • View Profile
    • http://www.bgswebdesign.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #9 on: February 04, 2005, 07:34:30 am »
Hi Dean,

WOw, that looks great, I suggest you call it targethit.php, or targetlink.php and add the modifications you suggested: very useful, and thanks ;)
Thanks,
-Brett
http://www.bgswebdesign.com/Contact-Us.php

*** I do custom List Mail Pro installations ***
Contact me through my website (above)

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • https://legacy.listmailpro.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #10 on: February 06, 2005, 03:58:35 pm »
Okay, I have made the changes (to the original post) but it's untested.  Let me know if you have any troubles.

Regards!
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

neil

  • Posts: 13
    • View Profile
    • http://www.MultipleStreamsOfProfit.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #11 on: February 08, 2005, 10:38:11 pm »
That looks great Dean!

I have wanted to know who clicked on my links for a long time, but
wasn't sure how to find out.  I thought I would get some kind of report,
but of course it never came so I thought no one ever clicked on them.

Thanks for fixing it so fast.

Kevin,

I'm glad you brought this topic up!

Neil



CONQUER the Internet! GRAB your piece of the Internet pie!
It's time someone gave you the training you need to launch your
Internet success story. Why settle for reading about how others
are making it big online? When you decide it's YOUR TURN,
I’ll reimburse your $24.95 Fast Track Fee to your
Paypal account as soon as you send me You account confirmation!

Signup here:
http://www.MultipleStreamsOfProfit.com/empower.html
CONQUER the Internet! GRAB your piece of the Internet pie!
Why settle for reading about how others are making it big
online? When you decide it's YOUR TURN,
I’ll reimburse your $24.95 Fast Track Fee!
http://www.MultipleStreamsOfProfit.com/empower.html

KB

  • Posts: 23
    • View Profile
Update on extraction process
« Reply #12 on: February 21, 2005, 04:08:59 am »
Hi Dean

My experiences with the extraction code you posted.

Loaded fine. Seems to work fine for two extractions - creates new list - and all details/records shown in new list. After two extracts, the records are shown as extracted but don't appear in the userdatabase - which is shown as empty. Then the codespecial page requests login, and then reverts to editlist and a loop is created. Puzzling.

Any suggestions?

Regards

Kevin

KB

  • Posts: 23
    • View Profile
Latest feedback on tracking/extraction
« Reply #13 on: March 14, 2005, 10:50:31 pm »
Open note and Dean

Worked around the idiocyincracy of the lists deleting themselves - if anyone wantd details just post request and I'll explain my experience.

However new factor

A large number of records are now represented with a ? instead of personal details. I've thought of two possible reasons. The link is being activated but the server response is too slow and the subscriber has left or the subscriber has software hiding his identity. The frequency of the ? is increasing to almost everyone.

Your thoughts/experiences

Regards

kevin

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • https://legacy.listmailpro.com
Extracting data from tracked links thru PhpMyAdmin
« Reply #14 on: March 15, 2005, 01:05:12 am »
Kevin,

Sorry, I didn't notice your other reply.  What was, and how did you fix, the problem you were experiencing?

I am not sure why ListMail isn't finding the user information.  It's possible the users unsubscribed or were otherwise removed.

The tracking is based on the user id field and the link id field.  The custom script should not cause a problem because it simply changes users' "list" values, keeping the same id and user data.  I'm not sure what is going on here.  :?
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting