Author Topic: Importing Hit Stats Into Excel (Read 12867 times)

Vertex42 · « **on:** May 03, 2004, 12:24:50 am »

I have found it useful to occasionally import the link tracking statistics (number of hits) into Excel to look at trends over time. What you do with the info once it is in Excel is up to you, of course.

Dean helped me write a slick PHP script that could work directly with an Excel Web Query. (See the link in my signature below for info about Excel web queries).

Here is the script, with the file named hits_wq.php. You should modify thepassword (in the first line of "The Meat" section) to help provide a little security.

Code: [Select]


<?php/* ======================================== Script for displaying hits from ListMailPro(c) 2004 Vertex42 LLCThis file is placed in a directory called "Links", but you can put it wherever you like, as long as you edit the $configfile and $adminfile variables to point to the ListMail scripts.The URL to access this script will be:http://yourdomain.com/Links/hits_wq.php?p=thepassword========================================== *//* ======  STUFF TO EDIT MANUALLY  =============  */$configfile = "../ListMail/config.php";$adminfile = "../ListMail/admin.php";/* ======  THE MEAT  ======== */if ( $p == "thepassword" ) {  // continue} else {  echo "<html><head></head><body>Cannot Access Hit Statistics</body></html>";  exit;}include($configfile); // grabs info to connect to databaseinclude($adminfile); // grabs info to connect to database$link = mysql_connect($sqlhost, $sqluser, $sqlpass) or die('can\'t connect to MySQL, did you provide the user/pass/db in the commandline?');mysql_selectdb($sqldb) or die('can\'t select database, does it exist?');echo "   <table width=100% border=0 cellspacing=0 cellpadding=2><tr class=bg2>  <td>RefCode</td>  <td>Title</td>  <td>URL</td>  <td>Hits</td></tr>\n";// sort by field$ordby = "refcode";$rows = mysql_query("select refcode,title,http,hits from $ktable where 1 order by $ordby");while(list($ref,$title,$http,$hits)=mysql_fetch_row($rows)) {	echo "        <tr>		  <td>$ref<br></td>		  <td>$title<br></td>		  <td><a href=\"$http\">$http</a><br></td>       <td>$hits<br></td>       </tr>\n";}echo "   </table>";?>



The .IQY file for the Excel web query is listed next. You will need to modify the URL to link to the above script. Just save this short bit of text as filename.iqy and then open Excel and go to Data > Get External Data > Run Saved Query ... and open your saved .iqy file.

In some cases, you can just double-click on the .iqy file and it will open up Excel and import the data automatically.

Code: [Select]

WEB
1
http://www.yourdomain.com/Links/hits_wq.php?p=thepassword

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False


You ought to try this just to see how cool Excel web queries can be.      You could always just copy and paste into Excel, but that takes the fun out.

BGSWebDesign · « **Reply #1 on:** May 03, 2004, 04:39:48 pm »

Hi,

FANTASTIC, I love it :lol:

terriz · « **Reply #2 on:** May 30, 2005, 05:36:00 pm »

Dean--any chance of getting this built-in to LMPro? The script is a little daunting for us non-programmers :cry:

andrew · « **Reply #3 on:** August 11, 2005, 06:24:55 pm »

First, I want to say to Vertex42, this is one of the most useful and practical options created. BRILLIANT!!!....and nice job Dean too!!

I think I have everything set up properly, but when I run the query,
I receive this error in Excel after running the query:

Could not select database MySQL Error: No Database Selectedcan't select database, does it exist?

Any suggestions???

Would be VERY MUCH APPRECIATED!!!

Thanks,
Andrew Golden
Long time LM User

Vertex42 · « **Reply #4 on:** August 11, 2005, 09:28:47 pm »

andrew,

Can you view the results in your web browser by navigating to your hits_wq.php page? This sounds like a problem with the php script - not necessarily with the web query. Have you correctly edited the paths to the config.php and admin.php files?

andrew · « **Reply #5 on:** August 12, 2005, 09:36:34 am »

Hi Vertex,

Actually, when I try to bring up the scrip in my browser, I receive the
"Cannot Access Hit Statistics" message. So I agree, it sounds like it's an issue with the script and my customizing it.

My LM files are in a directory called "M3"

Here is how i've set things up, and I am sure it's my doing something wrong.

_______________________________________

The URL to access this script will be:
http://yourdomain.com/M3/Links/hits_wq.php?p=I-Put-My-Password-Here
========================================== */

/* ====== STUFF TO EDIT MANUALLY ============= */
$configfile = "http://mydomain.com/M3/Links/config.php";
$adminfile = "http://mydomain.com/M3/Links/admin.php";

/* ====== THE MEAT ======== */
if ( $p == "I-Put-My-Password-Here" ) {
// continue

_______________________________________

Does it look like I've made a mistake somewhere?

Thank you so very much for you help, I really appeciate it.

Regards,
Andrew Golden

Vertex42 · « **Reply #6 on:** August 12, 2005, 04:58:10 pm »

Andrew,

The only way to receive the "Cannot Access Hit Statistics" message is to have the passwords not match. Note that this password isn't the same as the password in config.php. It is actually a fairly superficial form of security.

Try getting rid of (or commenting out) the if statement the follows directly after /* ... THE MEAT ... */ and see if the rest of the script works.

andrew · « **Reply #7 on:** August 12, 2005, 06:09:02 pm »

It's a good think I am not trying to make a living as a programmer.

Thanks for your help.

I know that I am close, while I am not sure how to "Comment Out" the
password section , if you mean delete the entire line, then I can do that, but when trying again, I receive a line 22 error message.

If I leave the password in there, then make sure that it matches the passoword from the top line as well as the web query, I receive this error:

Could not select database MySQL Error: No Database Selectedcan't select database, does it exist?

I am sorry I am not better at PHP, but I sure appeciate your help.
Thanks so much,

Andrew Golden
Long Time LM User

Vertex42 · « **Reply #8 on:** August 12, 2005, 06:49:19 pm »

Andrew,

It looks like the problem is with the line

Code: [Select]

mysql_selectdb($sqldb)

However, I'm don't know how to fix it. You might try replacing $sqldb with the actual value (found in the config.php script).

andrew · « **Reply #9 on:** August 12, 2005, 07:20:22 pm »

Thank you, I'll give that a run....
Then probably ask DW for help.

Appreciate your time and thanks for the intro to Excel Web Queries.

Andrew

andrew · « **Reply #10 on:** August 12, 2005, 09:56:45 pm »

I know this isn't that hard, but I just can't figure it out.
Anychance you could help me with it. Just point me in the right direction?

I tried copying the script as is, adjusting per my posts above,
but even when I move the hits-wq.php file into my LM directory (M3)
I still am receiving the feabled error message:

Could not select database MySQL Error: No Database Selectedcan't select database, does it exist?

I know I can get this to work and it would be a major time saver for me.
Appreicate your help.

Andrew

DW · « **Reply #11 on:** August 13, 2005, 01:51:59 am »

andrew,

place the hits_wq.php file in your ListMail directory

where you have this:

Code: [Select]

$configfile = "http://mydomain.com/M3/Links/config.php";
$adminfile = "http://mydomain.com/M3/Links/admin.php";

try changing it to this:

Code: [Select]

$configfile = "./config.php";
$adminfile = "./admin.php";

Browse the the script http://domain.com/M3/Links/hits_wq.php to verify that it works. If it doesn't, make sure the SQL data in config.php is correct and ListMail works without a database error.

Hope that helps!

andrew · « **Reply #12 on:** August 13, 2005, 03:14:01 pm »

DW-

That did the trick!! Victory!!
Thank you very much for your help/patience.

Andrew

richardwing · « **Reply #13 on:** July 24, 2006, 12:55:58 am »

Thank you I really like this....

I wish there was a way to collect all the Tracked Link Hits userstats data of who clicked on the tracking links. anyone modified this script to do that yet?

That way we can keep stats for a sort time and download each months stats and then compare or analyse on a monthly basis.

I would love to know if someone can modify this to collect that data as well.

The script worked for me exactly as the instructions said.

I have been playing around and I just did a copy from the tracked links page and pasted into an excel sheet. It worked great.

If there was a way to extrack or list subscriber details like referal url and date added and unsubscribed etc then I could create a database that I can querry by users and how active each are etc....

It would really be cool if someone can help persue some sort of data extraction similar to this script but to extract more or all of the user details.

I am willing to pay a small fee for a simple script such as this to extract all this data at once.

Let me know if someone is interested in throwing this script together.

Thanks again,
Richard Wing

BGSWebDesign · « **Reply #14 on:** July 24, 2006, 06:12:05 am »

Hi Richard,

Quote

It would really be cool if someone can help persue some sort of data extraction similar to this script but to extract more or all of the user details.

I am willing to pay a small fee for a simple script such as this to extract all this data at once.

Let me know if someone is interested in throwing this script together.

Actually, there's a LOT more than that to be done, I've posted very long threads about this, details on Clicks->Sales and many other things, you can find these in my postings, here's a list:
Link Tracking: http://listmailpro.com/forum/index.php?topic=1001.0
Split Testing: http://listmailpro.com/forum/index.php?topic=455.0
Statistics: http://listmailpro.com/forum/index.php?topic=1223.0

Please add your comments, I hope DW CAN get some of these features in soon, but I've asked for them nearly 1 year ago...

Quote

Let me know if someone is interested in throwing this script together.

I don't think it's possible, MUCH of what you ask for is not available, and needs to be added to LMP so that the level of details available can be increased... please DW, do something???

ListMailPRO Email Marketing Software Forums

Author Topic: Importing Hit Stats Into Excel (Read 12867 times)

Vertex42

Importing Hit Stats Into Excel

BGSWebDesign

GREAT!

terriz

Importing Hit Stats Into Excel

andrew

Excel Cannot Conenct to MySql Database

Vertex42

Importing Hit Stats Into Excel

andrew

Cannot Import into Excel

Vertex42

Importing Hit Stats Into Excel

andrew

It's a good thing...

Vertex42

Importing Hit Stats Into Excel

andrew

Importing Hit Stats Into Excel

andrew

Hey DW

DW

Importing Hit Stats Into Excel

andrew

Importing Hit Stats Into Excel

richardwing

Importing Hit Stats Into Excel

BGSWebDesign

Importing Hit Stats Into Excel