Jump to content
Sign in to follow this  
equilla

Supertracker Development

Recommended Posts

At someone else's suggestion, I have set up this development thread for Supertracker. The idea is that questions/suggestions/feedback regarding future requirements and improvements for Supertracker should be posted and discussed here.

 

If you have an issue with getting the current edition of Supertracker to work, please post your problem to the Supertracker Support Thread, which you can find here: http://forums.oscommerce.com/index.php?act=ST&f=7&t=166141

 

OK, so this would be a suitable juncture at which to reveal what is in the version which I am about to upload (I needed to upload a new one to sort out proper copyright/licence text in the files, etc anyway):-

 

 

1. Improvement to the appearance/layout of the admin screen (probably still needs more work though)

 

2. Info about number of rows in Supertracker database table displayed in admin screen, with the ability to delete the oldest x rows (x is entered into a text box).

 

3. Basic reporting information screen for number of hits referred through PPC campaigns - stats at both campaign level and keyword level possible. This has to be configured both in Supertracker and, of course, within your PPC provider's control panel.

 

4. Summary report breaking down the % of overall visitors by country.

 

5. I have now switched the ip to country conversion on the catalog side, rather than the admin side. The IP address of a visitor really only needs to be evaluated once, and can then be stored in the database alongside their other data. Converting the IP address to give country information represents a large, unnecessary processing overhead, particularly when running new reports such as summary of % of visitors by country (see above) which can time out using the current methods with database tables that have over about 15,000 rows. For existing users the new download package includes an update script which sorts out aligning their databases to cope with the new structure for this, and the stuff which follows below.

 

6. Added tracking for which categories have been viewed. This might be useful information because at the moment an exit page shown as "index.php" does not necessarily mean the customer left on the front page of the site, but may have been viewing product category listings. Also, it may come in handy for finding categories that are not often viewed by customers.

 

7. Added tracking for what was in the customers cart when they left (if the added to cart). Not sure if this is strictly necessary - it may just add to the torture when seeing what sales have not been converted, but it's there now!

 

I may soon also add an option to stop the system recording hits from your own IP address.

 

For the longer term, I have some other ideas of things that need to be improved, but I'd be interested to hear what people think about this one. What I have in mind is a wizard/report thingy that takes some of the manual assessment out of the results generated by Supertracker. We have potentially a lot of data here to make sense of, and perhaps for the less experienced person some of the significance of it can be missed. What I have in mind is a one-click process that analyses everything and generates plain text advice based on the result (which would include looking at other stuff as well as the Supertracker data). For example, you could imaging the following kinds of output:

 

- 40% of visitors who added products to their cart left at the page login.php. If you are selling products which are of a highly personal nature, perhaps you need to consider adding the PWA contribution....

 

- 90% of customers do not add products to their carts. As your product descriptions are an average of 30 characters in length, perhaps they do not have confidence in what they are buying. You should consider providing more detailed product descriptions.

 

- Your overall conversion rate for PPC campaigns is 3%. On average, PPC referrals result in 2 clicks on the site. Perhaps you are receiving referrals from people who are looking for a different kind of store. You should consider revising your trigger keywords for your PPC campaigns.

 

Well, the above may not be polished, but you get the idea - Thoughts...?

Share this post


Link to post
Share on other sites

Now that I've had a little time to play with the supertracker I must say it is very useful! Especially the referers info.

 

Here are a few suggestions for future improvements I've come up with:

- You might pull the small image height and width from the config as not everyone's images are 100 x 100 and it can get quite distorted. (I realize you problably did 100 x 100 as not everyone uses small and large images so its a tought call, but just a suggestion.)

- Its nice that you can see what page they quit on but if you could say click on the product_info.php exit page and view a list of the product pages or the product page titles along with the people that left at that page. This would be endlessly useful in identifying product, picture, or description problems.

- A percent of the total in addition to the # of people would be very useful.

 

Just a suggestions, do with them what you want....and great work again!


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Hello Mark,

 

Many thanks for developping SUPERTRACKER contribution.

 

While it has great features, I find it difficult to put it for real use.

For example since I installed it last month I have more tha 16000 exit

pages. I don't know why 16000 customers didn't buy anything.

 

What would be nice is to have a PATH feature to the last ten customer

menu as well as the sales made and exit without sales menus.

This way you get an idea of customer behaviour.

 

This feature is available in a couple of web based commercial site

and log based analysers which I found too complicated or inaccurate.

(bsides being expensive)

 

In user tracking you can expand the sessions but need to do manually

and must keep an eye for loading carts.

 

+++++++++++++++

I found a similar problem with key words with several thousand entries. It will be

better if these can be sorted by number so you know the most common

key words at one glance.

 

++++++++++++++++

 

Your feature to clean the database from time to time is indeed great.

However it will be nice to accumulate the most commopn key words

 

++++++++++++++++

 

As you are in the Uk I shall look forward to speak to you

some time. Please PM me

Share this post


Link to post
Share on other sites

Hello Mark,

 

its a really nice contribution.

I only miss that the contri don count clicks from my selfe.

In germany most have changing ip`s so it will be nice when the contri don count all click with the IP i have when i logged in admin.

 

you can see this feature at who`s online enhancement http://www.oscommerce.com/community/contributions,824

 

sorry for my bad english

greetings from Oktoberfest ;-)

Share this post


Link to post
Share on other sites

This is a great contribution. My suggestions are as follows.

 

1. Keyword report should have the order sorted to show most occurances at the top and descending.

 

2. Make the report window a fixed width. Many times referal links cause the page to be very wide and dificult to read. If the field could wrap that would look so much cleaner.

 

3. Put the numerical info, such as times, clicks, occurances, to the left of the links. It is much easier to read. (example, top referers has rank, refering url, number of hits. I think its much better as rank, number of hits, refering url.)

 

4. Make an additional report that will drop the session data from links and count them as the same referer. (example, yahoo product submit has http://shopping.yahoo.com/search;_ylc=X3oD...TQ5BHNlYwNzZWFy as the refering link, but each search adds a new sequence of data tot he end of the link. It would be more useful to see all these links condensed down to say http://shopping.yahoo.com/search and counted as the same referer.)

 

5. An admin page to set some options, such as the option to show links as described in #4 or in the current way. Enable/disable ppc, adjust image sizes for last 10 visitors, etc..

 

6. ppc roi data reports would be great. Perhaps an admin page to enter your profit margin, cost for advertising and then a report to show our ROI information. (I really need a way to track my yahoo ppc advertising :) )

 

Thats my 2 cents. Great job so far, thanks and keep up the good work.

Share this post


Link to post
Share on other sites

Absolutely love this contrib.

 

Is there any way we can get more summarized information about the users. My favorite is the last 10 users. I love being able to see what products people are viewing. I'd really like to see more summary info of this if possible. Also, I'd like to see a filtered view of the last 10 which filters those who have not viewed a product, cause I don't really care about those hits, I really wanna see who goes where and what the stats on a product are. But I love this tool Thanks!!!!!!!!!!!!!!!!!

Share this post


Link to post
Share on other sites

Great job on the new release. Still want a few things fixed up. Please fix the following. Make http referers wrap to the next line so the screen dont have to scroll side to side. also reorder the columns so that the referer is the right most column. it will make it easier to read the screens for the # of click, avg time spent, etc...

Also the menu at the top needs some reworking since the links do not respond well when clicked on, it moves the screen, then you need to reclick it to make it actually work. (on firefox)

Share this post


Link to post
Share on other sites
Great job on the new release. Still want a few things fixed up. Please fix the following. Make http referers wrap to the next line so the screen dont have to scroll side to side. also reorder the columns so that the referer is the right most column. it will make it easier to read the screens for the # of click, avg time spent, etc...

Also the menu at the top needs some reworking since the links do not respond well when clicked on, it moves the screen, then you need to reclick it to make it actually work. (on firefox)

 

and from the support thread:

 

And the last thing i can think of, is there anyway to make http referers that have search strings at the end crop them off. Referers such as ones from yahoo shopping have lots of search stings at the end that cause them to show up on their own lines, even though i have around 100 of them. This clutters the referers page and makes the data useless, since i have no idea how many people were from yahoo shopping unless i count them by hand. Now, i realize the importance of having the entire http referer, so what i suggest is make another page and have the cropped referers shown there. this may be a pain in the butt to do, so maybe have a config section that you can have referers matched to and if they match it would drop the rest of the referer and display what you typed in the config file. see below for examples

 

full http referer, whats shown with the contrib now.

http://shopping.yahoo.com/s:Clothing:06-Ca...6y464kjth6y4kuh (This line is much longer than displayed, it wont show up properly in this forum)

 

If it showed it this way on a seperate page, then all the referers would be show on one line.

http://shopping.yahoo.com

 

OK, some good points here, most of which I think I can accommodate. What I have in mind is the following:

 

1. To add a dropdown to the referrals and exit pages reports, similar to the new one on the Last 10 Visitors report (Show All, Bailed Carts). This will allowed some of the reports to be combined due to the new filtering capability.

 

2. To add a new configuration variable, which will allow a comma separated list of "spotlight referrers" to be entered. In your case this would include 'http://shopping.yahoo.com/search'. This will produce a new summary box at the top of the referrers report giving a total of referrals for all those matching the text string. This I think is the easiest way to do it as any other approach would leave to just too much looping through database queries (= sloooow report).

 

3. I also have an idea to add a text box on the products viewed page in which to enter a string to match on user agent. Why do we want to do this? Well, if you were to enter "googlebot" for instance, you can get a report on which of your products have recently been indexed by google's spider.

 

4. I am a firefox user too, so I know what you mean about those hyperlinks. I am thinking that I may well reduce this interface to a dropdown selector (or possibly 2) from which to select the desired report - the menu box at the moment is just too big!

 

5. I also think that some date filtering controls would be useful - get stats for today, yesterday, this week, last week, whatever.

 

 

Regards,

 

 

 

Mark

Share this post


Link to post
Share on other sites

Great contrib.... install was easy once I got past the two most common errors with application_top not having the function and having the geoip.dat file in the wrong place (instructions do say /admin/includes tho!)

 

I think it would be cool to be able to launch the reports in a javascript window.. resizeable, scrolling, and onload self.focus so a window cant get lots.... maybe not the default action but an option.... just the solid report with no clutter from the other page elements.

 

I am amazed at how fast Supertracker works in both the public pages and the admin. Not much of a performance hit for site visitors at all....

 

Thanks for a great peice of work... your knowledge is impressive.

Share this post


Link to post
Share on other sites

I would like the ability to turn off BOT monitoring - or at least separate it out. when I click on "last 10 visitors" most often 8 of these are bots. I'd rather see what the humans clicked on - and see bot info someplace else. Same for top products viewed.

Share this post


Link to post
Share on other sites
I would like the ability to turn off BOT monitoring - or at least separate it out. when I click on "last 10 visitors" most often 8 of these are bots. I'd rather see what the humans clicked on - and see bot info someplace else. Same for top products viewed.

 

ill second that

Share this post


Link to post
Share on other sites

PERFORMANCE IMPROVEMENTS

(posted already to the normal support but thought that it was better on development)

 

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.

 

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).

 

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

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

 

Let me know your opinions and if you use it and see the performance differerence


Jorge

Share this post


Link to post
Share on other sites

Hands down, this is one of the most important contributions I've installed on my shop!

 

Here are some possible suggestions for improving this already great contribbution:

 

-This module does nothing to track sales conversions. It would be very beneficial to be able to do this for both PPC campaigns and organic search engine traffic. To take this one step further, I would like to also be able to measure the rate of visitors that actually create accounts or sign up for newsletters based on the PPC campaigns/keywords/etc.

 

-It would also be great if this module would be able to keep track of click history (the history of what pages a person visited).

 

Please let me know what you guys think!! Are these ideas feasible?

Share this post


Link to post
Share on other sites

i relaesed 3.2 version :

 

- changed hard coded englsih by define in the language file

- cosmetic changes for strong caracteres

- few langauge correction

- commented the support links to gain palce in the admin webpage

-> the several languages need to finished to translate


MS2

Share this post


Link to post
Share on other sites

Conversion Rates:

 

Below is my code to figure out the conversion rates for v3.2. Please let me know what you think and if it's accurate.

 

Look for this line in /admin/supertracker.php (Around line 528):

echo '<tr><td>' . $keyword_friendly_name . ' : ' . $ppc_key_refs . TABLE_TEXT_REFERRALS_AVERAGE_TIME  . $ppc_row['ave_time'] . TABLE_TEXT_MINS_AVERAGE_CLICKS  . $ppc_row['ave_clicks'] . '</td></tr>';

BEFORE that line ADD:

				  // conversion data
			  $ppc_key_p_sql = "SELECT COUNT(*) as conversions FROM supertracker where landing_page like '%ref=" . $ref_code . "&keyw=" . $keyword_code . "%' and completed_purchase='true'";
			  $ppc_key_p_array = tep_db_fetch_array(tep_db_query($ppc_key_p_sql));

			  if ($ppc_key_p_array['conversions'] == 0)
				  $conversion_rate = 0;
			  else
				  $conversion_rate = ($ppc_key_p_array['conversions']/$ppc_key_refs)*100;

Now, Change the line:

echo '<tr><td>' . $keyword_friendly_name . ' : ' . $ppc_key_refs . TABLE_TEXT_REFERRALS_AVERAGE_TIME  . $ppc_row['ave_time'] . TABLE_TEXT_MINS_AVERAGE_CLICKS  . $ppc_row['ave_clicks'] . '</td></tr>';

To:

echo '<tr><td>' . $keyword_friendly_name . ' : ' . $ppc_key_refs . TABLE_TEXT_REFERRALS_AVERAGE_TIME  . $ppc_row['ave_time'] . TABLE_TEXT_MINS_AVERAGE_CLICKS  . $ppc_row['ave_clicks'] . ', #Purchased: ' . $ppc_key_p_array['conversions'] . ', Conversion Rate: ' . number_format($conversion_rate,2) . '%</td></tr>';


Scottie Wilcoxson (no advertising)

Share this post


Link to post
Share on other sites

GotTeamProductions could you explain us what is your conversion rate is for !


MS2

Share this post


Link to post
Share on other sites
GotTeamProductions could you explain us what is your conversion rate is for !

Hi,

 

looks like this calculates the percentage of people coming from each referrer who completed a purchase, which is known as sales conversion rate.

 

What also might be interesting is to set an "average cost per lead" parameter for each campaign and then to calculate how much the hits cost versus the value of the sales achieved.

 

In fact, this area could really do with some work in general - many thanks to GotTeamProductions for spurring us on! :D

 

Regards,

 

 

 

Mark

Share this post


Link to post
Share on other sites

equilla , would you insert GotTeamProductions tweak in the next release , and generally, would you insert the propositions taht are revelant and works in the next releases ?


MS2

Share this post


Link to post
Share on other sites
Hi,

 

looks like this calculates the percentage of people coming from each referrer who completed a purchase, which is known as sales conversion rate.

 

What also might be interesting is to set an "average cost per lead" parameter for each campaign and then to calculate how much the hits cost versus the value of the sales achieved.

 

In fact, this area could really do with some work in general - many thanks to GotTeamProductions for spurring us on! :D

 

Regards,

Mark

You're welcome! Supertracker is an awesome contribution, an essential for all OSC stores. You could add a field that would calculate the $$$/lead. Google has a web service, you could possibly interface with it and do that way as well.


Scottie Wilcoxson (no advertising)

Share this post


Link to post
Share on other sites

Thanks to everyone who helped making that contribution. It's one of the most useful ones I have installed in my store. However, I have one suggestion that could make it a bit easier. Right now when I check Products Viewed Report I see all products, even the ones that haven't been visited so far. But what (hypothetically) if I have 10000 products in my store and only 100 are being visited. Wouldn't it be easier to just show those products that have been viewed so far with number of views and skip those with 0 visits?

 

Also, another great thing would be being able to trace every step of customers with list of all pages in order of visiting them.

 

I'm already looking forward to seeing new version with all extra features mentiones in that thread. If you guys are interested in having more languages I can translate it to Polish.

Share this post


Link to post
Share on other sites

I'm not sure if this could be implemented into this fine contribution or if it laready exists in another contribution (if so, I haven't found it), but could we get a report that simply states the number of customers the store generated in a given day? (customer being defined by a site visitor--not someone registering an account or necessarily making a purchase)

 

Also, I was wondering when we purge old data lines do the counters (products viewed, countries, search terms, referrals, etc.) either start at zero or at least just count the data lines that haven't been purged or does it keep adding up numbers including the data you already purged?

Share this post


Link to post
Share on other sites

This is a great contrib which has been of much value to our company. However something I think would make it even more great is to add a from date and a to date. (For example this could be of great use for a store owner to see if a commersial or certain campaign made an impact on the sales)

 

Thanks and keep up the good work!

Share this post


Link to post
Share on other sites

i was wondering were i add the following line into dreamweaver and what file:

 

CREATE TABLE `supertracker` (

`tracking_id` bigint(20) NOT NULL auto_increment,

`ip_address` varchar(15) NOT NULL default '',

`browser_string` varchar(255) NOT NULL default '',

`country_code` char(2) NOT NULL default '',

`country_name` varchar(100) NOT NULL default '',

`customer_id` int(11) NOT NULL default '0',

`order_id` int(11) NOT NULL default '0',

`referrer` varchar(255) NOT NULL default '',

`referrer_query_string` varchar(255) NOT NULL default '',

`landing_page` varchar(255) NOT NULL default '',

`exit_page` varchar(100) NOT NULL default '',

`time_arrived` datetime NOT NULL default '0000-00-00 00:00:00',

`last_click` datetime NOT NULL default '0000-00-00 00:00:00',

`num_clicks` int(11) NOT NULL default '1',

`added_cart` varchar(5) NOT NULL default 'false',

`completed_purchase` varchar(5) NOT NULL default 'false',

`categories_viewed` varchar(255) NOT NULL default '',

`products_viewed` varchar(255) NOT NULL default '',

`cart_contents` mediumtext NOT NULL,

`cart_total` int(11) NOT NULL default '0',

PRIMARY KEY (`tracking_id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

Share this post


Link to post
Share on other sites

plus how do you do:

 

You will also need to add this new box in Administrator -> File Access from the admin menu

 

in cre-loaded 6.20 standard

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×