Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Support Thread for Supertracker Contribution


equilla

Recommended Posts

Hello there. I just installed this contribution which is a fabulous idea by the way! But my countries seem to be reporting wrong... I visited myself from Toronto and I showed up as finland.. and Inktomi was by and reported as Japan.. when I clicked on the link that goes to showmyip.com, it showed that inktomi was from the USA... I think I installed correctly, and downloaded the binary from maxmind... any ideas?

 

Thank you!

Robert

Link to comment
Share on other sites

I've seen that problem from time to time, there have been a few times my IP showed up as being in Mexico, and another time it was some country I'd never heard of. You can't always trust it, it does get screwy on occassion as IPs around the world resolve and DNS servers update. Not really an issue with the contrib, I don't beleive, it's just the way it is :)

Link to comment
Share on other sites

Hi,

 

I'm just wondering why no one else has reported this problem as there is an obvious issue here. The cart_contents field is set to NOT NULL and has no default value set. As this is not set when a new visitor arrives you get this problem. Perhaps MySQL on your system is set to be less tolerant of such errors than the rest of us, or perhaps MySQL on other systems is taking the default as "empty string".

 

The solution I believe would be to use phpmyadmin change this field to allow NULL values. You may also need to make this change for other fields, such as products_viewed and categories_viewed.

 

If you could report back on this - once we have the definite solution I can adjust the contrib accordingly in the next build.

 

Any more info on this? I just installed and noticed no problems.....is this something I should worry about? How to correct it.....?

 

Thanks

Link to comment
Share on other sites

Excellent contrib!! the only downside for me is that I can now see how many folk fill their cart and then abandon it later on!!! Oh well.

 

Anyway my question is this, I notice that if someone has added a product to the cart I see this

 

Customers Cart : (value=0) :

 

I'm presuming that this is the total amount in value of products in their cart? Why would this be 0. Everything else works so I'm not sure if I've messed up or not.

 

Any ideas.

 

Sarah

Link to comment
Share on other sites

Following on from my post on Nov 26th about SEO problems with SuperTracker I have made a small change that seems to have solved my problem.

 

The problem I was having was that clicks made on the category or product_info pages were not being recorded because the SEO seemed to be hiding the true URL. This was affecting stats on the 'Products Viewed Report' and also on the 'Last 10 Visitors' report where the thumbnails were not being shown for each product plus viewed categories were not being listed.

 

The following change appears to have sorted this problem:

 

In catalog/includes/classes/supertracker.php

 

change the line (in 2 places):

$current_page=$_SERVER['PHP_SELF'];

 

to

$current_page=$_SERVER['SCRIPT_FILENAME'];

 

This now seems to give the true filename and not the SEO version of it and clicks on products and categories are being recorded and displayed in the results.

 

I really don't know if this was necessary due to the way I have configured SEO or the way my particular server operates but hopefully it will be of use to someone else.

--

Ed

Link to comment
Share on other sites

Hi,

 

no question about it: great contribution!

 

i just have one simple (?) problem:

froogle - Total Referrals 2

froogle : Empfehlungen, Durchschnittliche Zeit: mins, Durchschnittliche Klickanzahl:

 

As you can see, there are 2 referrers, but no stats on average time and clicks. Why is this?

 

Okay, i my tables habe pre-words, as suggested by multi-shop/multi-cart contribs. So i changed the sql-statements of this contrib to:

pre_orders, pre_customers etc (as an example). i did this for all "select"-statements. The statements dealing with the supertracker table i left as they were.

 

Are the any "patchwork"-statements, which i didn't see? or what might be the reason for this the problem?

 

Any help appreciated

Greetz

erdm?nnchen

 

 

p.s. everything else works pretty well!

Link to comment
Share on other sites

Hi,

 

no question about it: great contribution!

 

i just have one simple (?) problem:

As you can see, there are 2 referrers, but no stats on average time and clicks. Why is this?

 

Okay, i my tables habe pre-words, as suggested by multi-shop/multi-cart contribs. So i changed the sql-statements of this contrib to:

pre_orders, pre_customers etc (as an example). i did this for all "select"-statements. The statements dealing with the supertracker table i left as they were.

 

Are the any "patchwork"-statements, which i didn't see? or what might be the reason for this the problem?

 

Any help appreciated

Greetz

erdm?nnchen

p.s. everything else works pretty well!

It's me once again. Really everthing works fine:

 

 

Zielseite: /nusslikoer-nocino-spadoni-flasche-p-14387.html?ref=go....

Letzte Zeit angezeigt: /product_info.php

Erster Klick: 09.12.2005 08:18:01

Letzter Klick: 09.12.2005 08:19:09

Gesamtzeit im Shop: 0hrs 1mins 8 seconds

Anzahl Klicks: 5

 

As you can see the Landing Page (Zielzeite) is recorded as well as the first click, last click, time on site and the amount of clicks. But in the ppc-section the stats on average time and clicks are still missing!

 

See you

erdm?nnchen

Link to comment
Share on other sites

Hi, i am vincenzo and i think this contr. is good. it works good if i stay in local. if i go in remote, if i call a report of supertacker, the sistem goes to catalog/admin/login.php and wants user and password. IT DOESN'T WORK.

what do you thik about this? any ideas?

 

vicenzo thank you

Link to comment
Share on other sites

Hi, i am vincenzo and i think this contr. is good. it works good if i stay in local. if i go in remote, if i call a report of supertacker, the sistem goes to catalog/admin/login.php and wants user and password. IT DOESN'T WORK.

what do you thik about this? any ideas?

 

vicenzo thank you

 

Hi Vicenzo,

 

this is an interesting one, and one I don't have the answer for at the moment. On my shop, this doesn't happen, but on my wife's store, it happens as soon as I select a Report type from the Supertracker screen. I get thrown back to the login screen; then I log into admin again and everything works fine.

 

One clue which I did notice - when I first log in the osCadminID (Session ID) is attached to all the URLs of the menu links on the left. The second time I log in they aren't. I mention this because this problem is clearly session related.

 

I've a feeling that the problem on my system relates to the way that some of the domains are parked as I've found it with other pages in admin before. The domain we are presently using to run the osCommerce store is a parked/pointed domain and is not the main one for the hosting account.

 

Sorry I can't be of more help at the moment - If I get a chance in the next few days I will run some more tests to see if I can narrow down the cause of the problem further.

 

Regards,

 

 

 

 

Mark

Link to comment
Share on other sites

Sorry I can't be of more help at the moment - If I get a chance in the next few days I will run some more tests to see if I can narrow down the cause of the problem further.

 

Regards,

Mark

 

 

thank you equilla, i am trying too.... help us

Link to comment
Share on other sites

any idea on this error when trying to delete rows

 

1064 - You have an error in your SQL syntax near 'ORDER by tracking_id ASC LIMIT 500' at line 1

 

DELETE from supertracker ORDER by tracking_id ASC LIMIT 500

Link to comment
Share on other sites

Hello,

 

I just installed this great contribution. I have a few problems with the reports in admin. I seem to be having some trouble with the table names.

 

For example, when I select the Product Viewed Report, I get the following error:

 

1146 - Table 'eyackey.products' doesn't exist

 

select p.products_id, pd.products_name from products as p, products_description as pd where p.products_id=pd.products_id and p.products_status='1'

 

For my installation, I believe the table should be eyackey.osc_products - All my osc tables have ?osc_? in front of them. Is there a define someplace that I need to change for the table names? I get similar errors for other reports where it is looking for a table.

 

Any help or suggestions would be greatly appreciated.

 

Ed

Link to comment
Share on other sites

Hello,

 

I just installed this great contribution. I have a few problems with the reports in admin. I seem to be having some trouble with the table names.

 

For example, when I select the Product Viewed Report, I get the following error:

 

1146 - Table 'eyackey.products' doesn't exist

 

select p.products_id, pd.products_name from products as p, products_description as pd where p.products_id=pd.products_id and p.products_status='1'

 

For my installation, I believe the table should be eyackey.osc_products - All my osc tables have ?osc_? in front of them. Is there a define someplace that I need to change for the table names? I get similar errors for other reports where it is looking for a table.

 

Any help or suggestions would be greatly appreciated.

 

Ed

 

Hi Ed,

 

Ah yes, you have spotted a shortcoming in my code. Of course, when I first wrote this, it was just for me, so I didn't take too much care to be compliant with the osCommerce coding standards. I should have used TABLES_PRODUCTS, etc, instead of hard-coding "products" into the queries. Basically, the queries in the supertracker.php files need to be edited to do this in order to make it work on your installation.

 

Regards,

 

 

 

 

Mark

Link to comment
Share on other sites

Hi Ed,

 

Ah yes, you have spotted a shortcoming in my code. Of course, when I first wrote this, it was just for me, so I didn't take too much care to be compliant with the osCommerce coding standards. I should have used TABLES_PRODUCTS, etc, instead of hard-coding "products" into the queries. Basically, the queries in the supertracker.php files need to be edited to do this in order to make it work on your installation.

 

Regards,

Mark

 

 

Mark,

 

Thank you for he quick reply. I changed the queries in admin/supertracker.php and everything looks great.

 

- Ed

Link to comment
Share on other sites

Hi,

 

I installed decided to install the contribution (in an already modified shop) and in about 15 minutes it was working!!! Great :-) Very easy to install.

 

One thing though. I'm getting as referrers my own shop, for example I get things like:

http://www.aporvino.com/comprar_vino/conditions.php

 

Is this standard behaviour? I don't find very useful to know about my internal referrals...

 

And then one doubt about now it works. When a registered customer returns, do new rows get added into the supertracker table or they are "accumulated".

 

Thanks.

 

Jorge.

Jorge

Link to comment
Share on other sites

PERFORMANCE IMPROVEMENTS

 

Hi,

 

I've been looking at possible performance improvements for the contribution and I found the possible candidates:

 

- 1st Add an index on table supertracker field customer_id

This should help as the query that is executed every single time and always is a select on customer_id trying to identify if there is an existing session or not. Without the index we are doing a table scan every single time.

 

- 2nd Change the order of the query about IP and browser string:

doing the query first on the ip and then on query string will allow us to add an index on IP.

 

- 3rd Add an index on IP

 

- 4th Add an index on cart_total and last_click.

In this case an index on cart_total could have very little granularity so it will be better to add an index to both columns.

 

- 5th Remove the "*" from three queries trying to find a session

Currently there is a "select * from" which returns all the fields from the table when in fact the only data required is to check the number of rows to know if there a session or not.

And improvement would be to change the three queries to "select 1 from" this just a "1" if there is a match, and no row if there is no match.

 

An additional advantage of this is that most probably to run the three above queries MySQL will not even access the table and use instead the index tree which is much faster.

 

In resume this is what I propose:

 

Chage:

$query = "select * from supertracker where customer_id ='" . $customer_id . "' and last_click > '" . $thirty_mins_ago . "'";

To:

$query = "select 1 from supertracker where customer_id ='" . $customer_id . "' and last_click > '" . $thirty_mins_ago . "'";

 

Change:

$query = "select * from supertracker where browser_string ='" . $browser_string . "' and ip_address like '" . $ip_start . "%' and last_click > '" . $thirty_mins_ago . "'";

To:

$query = "select 1 from supertracker where ip_address like '" . $ip_start . "%' and last_click > '" . $thirty_mins_ago . "'" and browser_string ='" . $browser_string . "';

 

Change:

$query = "select * from supertracker where cart_total ='" . $cart->show_total() . "' and last_click > '" . $thirty_mins_ago . "'";

To:

$query = "select 1 from supertracker where cart_total ='" . $cart->show_total() . "' and last_click > '" . $thirty_mins_ago . "'";

 

The add the following indexes:

 

ALTER TABLE `supertracker` ADD INDEX ( `customer_id` )

ALTER TABLE `supertracker` ADD INDEX ( `ip_address` )

ALTER TABLE `supertracker` ADD INDEX ( `cart_total` , `last_click` )

 

Let me know your thoughts. I'll implement it right now :-)

Jorge

Link to comment
Share on other sites

It's running :-) and I think it feels faster!

 

By the way, the correct second query I used was:

$query = "select 1 from supertracker where ip_address like '" . $ip_start . "%' and last_click > '" . $thirty_mins_ago . "' and browser_string ='" . $browser_string . "'";

 

 

If anyone wants to learn more about performance, here is a good reference:

http://www.scit.wlv.ac.uk/appdocs/mysql/ma...Performance.htm

Jorge

Link to comment
Share on other sites

Just in case someone is interested. This is what I finally did to improve performance. Now it has been runing for the whole day and reporting well (the previous optimisations where not working completely well).

 

The are of the code that changed is: (headed with comments the exact lines that change)

 

//First thing to try is customer_id, if they are signed in

 

//JORGE20051213: Lets define the possible queries to identify the session

//It's important that this queries use the indexes and ideally without needing to access the table (query only the index tree)

//This queries are executed for every single access.

$query_array[0] = "from supertracker where customer_id ='" . $customer_id . "' and last_click > '" . $thirty_mins_ago . "'";

//I have also changed the query to match the full IP address as it was generating false matches from users on the same ISP (it was confusing me (fix IP) with a proxied IP!)

$query_array[1] = "from supertracker where ip_address like '" . $ip_address . "%' and last_click > '" . $thirty_mins_ago . "' and browser_string ='" . $browser_string . "'";

$query_array[2] = "from supertracker where cart_total ='" . $cart->show_total() . "' and last_click > '" . $thirty_mins_ago . "'";

$query_sucess = -1;

//JORGE20051213: END

 

if (isset($customer_id)) {

//JORGE20051213: Now don't retrieve any values, we just need the number of rows)

$query = "select 1 " . $query_array[0];

//JORGE20051213: END

$result = tep_db_query($query);

if (tep_db_num_rows ($result) > 0) {

$existing_session = true;

//JORGE20051213: Store which query was sucessful

$query_sucess = 0;

//JORGE20051213: END

}

}

 

//Next, we try this: compare first 2 parts of the IP address (Class B), and the browser

//Identification String, which give us a good chance of locating the details for a given user. I reckon

//that the chances of having more than 1 user within a 30 minute window with identical values

//is pretty small, so hopefully this will work and should be more reliable than using Session IDs....

 

if (!$existing_session) {

//JORGE20051213: Now don't retrieve any values, we just need the number of rows)

$query = "select 1 " . $query_array[1];

//JORGE20051213: END

$result = tep_db_query($query);

if (tep_db_num_rows ($result) > 0) {

$existing_session = true;

//JORGE20051213: Store which query was sucessful

$query_sucess = 1;

//JORGE20051213: END

}

}

 

//If that didn't work, and we have something in the cart, we can use that to try and find the

//record instead. Obviously, people with things in their cart don't just appear from nowhere!

if (!$existing_session) {

if ($cart->count_contents()>0) {

//JORGE20051213: Now don't retrieve any values, we just need the number of rows)

$query = "select 1 " . $query_array[2];

//JORGE20051213: END

$result = tep_db_query($query);

if (tep_db_num_rows ($result) > 0) {

$existing_session = true;

//JORGE20051213: Store which query was sucessful

$query_sucess = 2;

//JORGE20051213: END

}

}

}

 

//Having worked out if we have a new or existing user session lets record some details....!

if ($existing_session) {

//JORGE20051213: Now obtain all the data from the query we know is sucessful to identify the session

//This way we access the table only when we know that we have identified a session and not always

$query = "select * " . $query_array[$query_sucess];

$result = tep_db_query($query);

//JORGE20051213: END

 

//Existing tracked session, so just update relevant existing details

Jorge

Link to comment
Share on other sites

The last idea posted in the developement thread I thought was good, anybody have thoughts on that? Basically about removing bot crawls from the "last ten visitors" section so that you only see real peoples browsing habits.

 

Does anybody know how to do this? The bot crawls really do need to be eliminated or put someplace else - as they fill up a huge percentage of my database, and my looking at last 10 visits a waste of time as it's usually 8 bots and 2 people!

 

Looking for a fix...

Link to comment
Share on other sites

Does anybody know how to do this? The bot crawls really do need to be eliminated or put someplace else - as they fill up a huge percentage of my database, and my looking at last 10 visits a waste of time as it's usually 8 bots and 2 people!

 

Looking for a fix...

 

Eh? It's already in there!

 

Have alook at catalog/includes/classes/supertracker.php

 

and the very first function around line 24 allows you to exclude bots. This is what mine looks like:

 

//// **** CONFIGURATION SECTION  **** ////

  //Comma Separate List of IPs which should not be recorded, for instance, your own PCs IP address, or 
 //that of your server if you are using Cron Jobs, etc
  $excluded_ips = '65.54.188.64';

// list is: MSNBOT

 

 

Just add more ip address separated by commas.

 

Ed

Link to comment
Share on other sites

Eh? It's already in there!

 

Have alook at catalog/includes/classes/supertracker.php

 

and the very first function around line 24 allows you to exclude bots. This is what mine looks like:

 

//// **** CONFIGURATION SECTION  **** ////

  //Comma Separate List of IPs which should not be recorded, for instance, your own PCs IP address, or 
 //that of your server if you are using Cron Jobs, etc
  $excluded_ips = '65.54.188.64';

// list is: MSNBOT

Just add more ip address separated by commas.

 

Ed

But it will be easier to exclude by agent "googlebot", etc as there are many IP addresses from which bots run and keeping them up to date can be time consuming.

 

I'm planning to code the exclusion by agent but I don't think I'll be able to do it b4 xmas.

Jorge

Link to comment
Share on other sites

Hey Mark,

 

You are awesome,

 

Its working now :thumbsup:

You really helped me the way i never expected. I will go thru now all the features and may disturb you again if i need any help,

 

As of now i just need to know how to change path of Flag images in the code ??

as default its taking admin/images/uk.gif ... but i think its more feasible if its

admin/images/flags/uk.gif

 

Once again, Thank you

 

Cheers

Naved

==========

 

 

Hi everyone, i just found this contribution today and test it out myself. The installtion was successful with no problem, but then for some reason i can't connect to my site anymore, it keeps giving an error. An error says like this

 

"1364 - Field 'cart_contents' doesn't have a default value

 

INSERT INTO `supertracker` (`ip_address`, `browser_string`, `country_code`, `country_name`, `referrer`,`referrer_query_string`,`landing_page`,`exit_page`,`time_arrived`,`last_click`) VALUES ('81.1.106.167','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)','gb', 'United Kingdom', 'http://doan-production.com/doan-e-shop/admin/supertracker.php', '','/doan-e-shop/','/doan-e-shop/index.php','2005-12-16 01:22:12','2005-12-16 01:22:12')

 

[TEP STOP]"

 

Could you please tell me where did go wrong? Since i don't really know. Should i put some values in that cart_contents perhaps via phpadmin, because i can't go to my site anymore.

 

Doan Shop

 

Any helps are appreciated. Please hope you can help me.

 

Best regards

 

 

Kevin Doan

Link to comment
Share on other sites

I had that exact problem on the previous page of this thread, I had to go into phpmyadmin and allow null values for that field in the database. Fixed it right up.

Edited by doctorstupid
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...