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

DW · « **Reply #30 on:** September 25, 2005, 02:44:54 pm »

I've updated the code to include a Move/Copy option. Let me know if you have any troubles.

Regards

BGSWebDesign · « **Reply #31 on:** September 25, 2005, 07:44:32 pm »

Hi DW,

Great, I loaded it up but have a problem, and I found a bug.

Here's the bug:
This code:


echo "This script will create a new list containing all users from any list who have clicked through a certain link.  Users are _$txt_ from their old list.<br><br>";

Needs to be like this:

Code: [Select]


echo "This script will create a new list containing all users from any list who have clicked through a certain link.  Users are _" . $txt . "_ from their old list.<br><br>";

I also moved this line up to the top as follows:

Code: [Select]


$move = false; 
$copy = true; 
if($move) $txt = 'moved'; elseif($copy) $txt = 'copied';

I keep getting this:

Code: [Select]


(new) list num = 33
new list: 33
copied users: problem inserting..

Every time I run it, there are NO users in the new list, and the old list is the same size, have you tested this on a sample list/hits?

Also a suggestion, up at the top since you are moving/copying things around I suggest you add this piece (as it was listed in the Excel query page to also grab hits) add it right between include admin.php and $islogged as indicated:

Code: [Select]


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

/* ======  THE MEAT  ======== */ 
if ( $p == "password-of-your-choice" ) { 
  // continue 
} else { 
  echo "<html><head></head><body>Cannot Access Extract Hits</body></html>"; 
  exit; 
} 

$islogged = islogged();

Then the program can be called as follows:

Code: [Select]


http://yourdomain.com/mail/extracthits.php?p=password-of-your-choice

This adds a bit of security since users records are being copied/moved, etc...

Can you please do a test, on why I'm getting

Code: [Select]

copied users: problem inserting..

BGSWebDesign · « **Reply #32 on:** September 27, 2005, 01:46:50 pm »

Hi,

bump....

Can anyone tell me why this returns error every time (see my previous message), has anyone else been able to get it to work?

DW · « **Reply #33 on:** September 28, 2005, 01:55:06 am »

I had fixed those issues but forgot to re-update the post. The "problem inserting.. " error was debugged by adding the following:

...or die('problem inserting..'.mysql_error());

There was a quote typo near the seq value.

I also added buttons for Move / Copy.

There is some security. You must be logged in to ListMail to use the script.

Regards

BGSWebDesign · « **Reply #34 on:** December 08, 2005, 03:40:44 pm »

Hi DW,

There's ONE MORE Additiona needed here, the ability to APPEND the records to an already existing list. Here's why, think about it, I have 50-60 lists, I want to just keep ADDING my interested people who clicked to the list that I have already COPIED them to.

Right now I have to Save to new list, then Export to txt file, then go to old list, then Import txt file, ( A REAL PAIN :oops: )

Better way, I can choose Move, Copy, or Append, if I choose Append, I am asked, WHICH list to append to - from a drop-down list, I choose the list, click Go, and it Appends the records (*ignoring duplicates) to the already created list... make sense?

How about a quick change addition to this code, it shoudln't take long...

DW · « **Reply #35 on:** December 09, 2005, 12:08:36 am »

Brett,

You got it!

Grab the new code.

mike2 · « **Reply #36 on:** January 13, 2006, 07:06:59 am »

I really love this code and feature!

I've really started using it and it works well. One problem I'm running into now is that I have around 10 tracked links / lists that I want to continue to append to when someone clicks the link.

The problem is doing 10 or so every day is getting cumbersome. If there was some way to link the "tracked links" and "lists" together and have one page that does them all at once or better yet get processed right before daily mail runs would be NICE!

I'd be willing to Donate a small amount to make this feature available, I bet there are a couple others... Any interest in this?

DW · « **Reply #37 on:** January 14, 2006, 01:19:25 am »

I wonder if this is possible with the new "Custom Query" option in User Selection. MySQL 4+, I believe, is required.

Here's a way to send to all users on a list who are not on another list:
http://listmailpro.com/forum/index.php?topic=906.msg3899#msg3899

Let's see...

The hits table stores the numeric user and link ids.. that gets a bit tricky. It would be easiest to take the link id from within LMP interface. Ahh, it's not displayed..

You can get the id by checking the "View Source" of the Link Tracking page. Each link has the following entry which shows the numeric link id...

<input type=hidden name=saveid value=1>

The resulting custom query might be:

Code: [Select]

SELECT *
 FROM lm_users a
 WHERE a.list = '2'
 AND cnf = '1'
 AND EXISTS (
  SELECT *
  FROM lm_hits b
  WHERE a.id = b.uid
  AND b.kid = '1'
 );

I could make it easier to obtain the link ID from the ListMail interface.

To show the linkid you could modify link.php, this line:

Code: [Select]

<input type=hidden name=saveid value=$kid>
to:

Code: [Select]

<input type=hidden name=saveid value=$kid>$kid
How about this quick fix?

Regards

BGSWebDesign · « **Reply #38 on:** January 16, 2006, 08:21:55 am »

Hi DW,

Quote

Here's a way to send to all users on a list who are not on another list

I think what he's looking for instead is having this added to Dailymail (as an option possibly), so that it AUTOMATICALLY moves the clicked links over to the other lists...

I like that idea, but only if I can get reliable Statistics on the lists after the move such as How Many Clicks happened between these dates, etc, etc...

mike2 · « **Reply #39 on:** January 16, 2006, 09:39:52 am »

Quote from: "webshaman"

I think what he's looking for instead is having this added to Dailymail (as an option possibly), so that it AUTOMATICALLY moves the clicked links over to the other lists...

I like that idea, but only if I can get reliable Statistics on the lists after the move such as How Many Clicks happened between these dates, etc, etc...

Yes I am looking for them to be appended to the list automatically each day. My followups on the lists would then take care of the emailing to them.

Sorry if I made my previous post a bit confusing and unclear. I simply want to be able to append them automatically each day, so I don't have to manually run down 10 diffirent tracked links into 10 diffirent lists, which will grow and grow even bigger.

Stats would be good, IE. How many clicks(people) were moved/copied/appended each time. But for me This isn't real necessary at the moment.

This would take a bit of manual work at first (IE, we'd have to go somewhere and say ok, Tracked Link "!link_ma010106" Is tied to "List 4: Followup - http://mjracejr.3500weekly.hop.clickbank.net" and users should be appended, etc, etc for each link to auto append into a new list. But once you set each one up, it just appends any new clicks daily to each list and my followups would then go out.

mike2 · « **Reply #40 on:** January 20, 2006, 02:28:00 pm »

Bump > any news on this?

BGSWebDesign · « **Reply #41 on:** March 24, 2006, 06:27:44 am »

Hi DW,

Ok, back to this script, I've noticed something very strange, I ran it 'Append' mode on several lists I have, the first list, I keep getting something bizarre!

If I run it in 'Append', it says added one and shows the ID number of the new one added, BUT, the actual count when I click on User Database does NOT increase, it stays the same?

Secondly, if I then choose to run it in 'Copy' mode it shows me a different ID number, but again does NOT increase the number of users in the Users Database...

Next
-----------
I also see this sometimes:

Code: [Select]

appended users: skip0

How well is this tested, are you certain it works correctly?

Also, it would help tremendously if the Selected Lists for BOTh the From and To list would STAY Selected after clicking, so I could move right on to the next one, instead of having to hunt through the entire Selection list every time...

DW · « **Reply #42 on:** May 17, 2006, 07:12:51 pm »

Script updated for v1.85+ [click here]

BGSWebDesign · « **Reply #43 on:** May 17, 2006, 10:16:35 pm »

Hi,

Ok, I hate to be picky but I noticed this... when I do an Append into a table when the number of clicks is showing as: 2 clicks (in Link Tracking), I notice only 1 number shows up when I click Append:

Code: [Select]

Target list num = 87
new list: 87
appended users: 302555

When I then go to User Database for that list - it is only increased by 1?

Since there are 2 clicks showing, it seems it is leaving one out, or the number of clicks showing is off by 1, though it seems it's the other way around - this script is MISSING ONE of the clicks???

Weird, have you fully tested this, try it with 1 or 2 clicks, you'll see what I do, I'm sure...

richardwing · « **Reply #44 on:** July 24, 2006, 03:03:01 am »

I really love this thread.... This kind of sorting by clicked tracking link is very powerful and I am not sure if people really understand this....

Imagine if you set up a marketing campaign to promote xyz product... if you had an auto sort by clicked tracking code then we can move the user to a folowup list sequence that will personalise the email to them as if we know that they checked out the product and followup according.

An automated feature would be cool to allow for this additional copy to new list on tracked link click.

You could take this one step further and set rules for the amount of times a link is clicked to copy/move to a list. if they click on it 1-4 times copy to one list or 5 + copy to another list. and remove from the the 1-4 times list. etc....

we could then see if they have made a purchase from us and if not maybe we can push them over the fence with an offer they can't refuse or ask them if they have some questions they need answered or clarified.

I hope that this feature will be developed further and added into the system as a standard feature.

Richard Wing

ListMailPRO Email Marketing Software Forums

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

DW

Extracting data from tracked links thru PhpMyAdmin

BGSWebDesign

Extracting data from tracked links thru PhpMyAdmin

BGSWebDesign

Every time I run it, there are NO users in the new list, and

DW

Extracting data from tracked links thru PhpMyAdmin

BGSWebDesign

Extracting data from tracked links thru PhpMyAdmin

DW

Extracting data from tracked links thru PhpMyAdmin

mike2

Extracting data from tracked links thru PhpMyAdmin

DW

Extracting data from tracked links thru PhpMyAdmin

BGSWebDesign

Extracting data from tracked links thru PhpMyAdmin

mike2

Extracting data from tracked links thru PhpMyAdmin

mike2

Extracting data from tracked links thru PhpMyAdmin

BGSWebDesign

problem appending...

DW

Extracting data from tracked links thru PhpMyAdmin

BGSWebDesign

Extracting data from tracked links thru PhpMyAdmin

richardwing

Extracting data from tracked links thru PhpMyAdmin