Quote:
Originally Posted by ruff
What is the best way to accurately count hits? Surely not Alexa. Google admits their analytics are not accurate. Extreme Tracking does not allow porn. Is Stats Counter all that's left?
|
For sites under 100K a day Piwik.org
Set up a Piwik stats domain or subdomain on a VPS or dedicated (depending on your volume). I think it will install on some virtual servers but it may be too database use intensive.
Piwik is javascript triggered so as to the accuracy? Supposedly, all IPs are listed and you can directly connect to the MySQL database and query detail.
Code:
select
inet_ntoa(conv(hex(Country_code.location_ip), 16, 10)) AS Source_IP,
LVA.idlink_va as VisitorActionCode,
LVA.idvisit,
LVA.idsite,
URL.name as URL,
Page_Action.name as Page_Action,
Reffering_URL.name as Reffering_URL,
Reffering_Page_Action.name as Reffering_Page_Action,
Country_code.location_country as Country_code,
Country_code.visit_last_action_time as TimeLastVisit
from
piwik_log_link_visit_action LVA,
piwik_log_action URL,
piwik_log_action Page_Action,
piwik_log_action Reffering_URL,
piwik_log_action Reffering_Page_Action,
piwik_log_visit Country_code
where LVA.idaction_url = URL.idaction
AND LVA.idaction_name = Page_Action.idaction
AND LVA.idaction_url_ref = Reffering_URL.idaction
AND LVA.idaction_name_ref = Reffering_Page_Action.idaction
AND URL.name LIKE '?%'
AND Country_code.location_country ='?'
AND Page_Action.name LIKE '%?%'
AND Page_Action.name LIKE '%?%'
AND Page_Action.name NOT LIKE '%?%'
AND Page_Action.name NOT LIKE '%?%'
AND Page_Action.name NOT LIKE '%?%'
AND Page_Action.name NOT LIKE '%?%'
AND Reffering_URL.name NOT LIKE '%?%'
AND Reffering_URL.name NOT LIKE '%?%'
AND Reffering_URL.name NOT LIKE '%?%'
AND Country_code.visit_last_action_time > 2014-05-09
AND LVA.idsite ='?'
Limit 50;
Probably not the best SQL but
Code:
mysql> SELECT visit_total_actions,location_country, inet_ntoa(conv(hex(location_ip), 16, 10)) AS IP, referer_type, referer_name, referer_url FROM [DATABASE NAME].piwik_log_visit WHERE referer_url LIKE '%?=%' AND location_country = 'us' ORDER BY visit_total_actions DESC limit 200;
=>
200 rows in set (1 min 15.73 sec)
I probably searched 10 million records on a huge MySQL server
I tried on a vps stats server I have (a small database) today;
=>
200 rows in set (0.50 sec)
Code:
Welcome to the MySQL monitor. Commands end with ; or \g.
SSH or Terminal rocks if you know how to use it
