I was wondering whether there is a way to display a list of the top X (e.g. 10 or 50 or whatever) clients (visitors) based on No. of pageviews.
The top client would be the one who (for the specified period) had the greatest number of pageviews.
The list would include the client domain name (if available), the IP address, the No. of pageviews.
To further expand on it:
Selecting a particular site page (e.g. from a dropdown with site pages), could display the same stats but for the particular page only.
Clicking on a client on the list could display the top X site pages visited by that client (again for the specified period), based on the No. of pageviews.
If such info is not available, I would welcome such an addition, and I believe it would be handy to most admins.
If it is, could you please guide me on how to do it?
Hi Nick, such functionnality is possible to do with Piwik! Maybe it requires some custom development. if you have some budget, feel free to get in touch at: http://piwik.org/consulting/
Until a page with relevant information is added in Piwik, here is a manual solution, querying directly the db:
// LIST TOP 20 CLIENTS (VISITORS) IN PAGEVIEW ORDER
// FOR A PARTICULAR TIME PERIOD
SELECT hex(location_ip) as IP, SUM(visit_total_actions) as pviews
FROM piwik_log_visit
WHERE ((visit_first_action_time>='2014-08-20 00:00:00' AND visit_first_action_time<='2014-08-20 00:08:00')
OR (visit_last_action_time>='2014-08-20 00:00:00' AND visit_last_action_time<='2014-08-20 00:08:00'))
GROUP BY IP
ORDER BY pviews DESC
LIMIT 20;
----------------------------------------
SAMPLE OUTPUT:
----------------------------------------
IP pviews
----------------------------------------
00000000000000000000FFFF5E40A690 111
00000000000000000000FFFF05367982 31
2A0206B80000180C00000000B29AFF81 18
...
----------------------------------------
// LIST VISIT IDs FOR A PARTICULAR TIME PERIOD AND FOR A PARTICULAR CLIENT IP ADDRESS
SELECT idvisit as visit_ID
FROM piwik_log_visit
WHERE ((visit_first_action_time>='2014-08-20 00:00:00' AND visit_first_action_time<='2014-08-20 00:08:00')
OR (visit_last_action_time>='2014-08-20 00:00:00' AND visit_last_action_time<='2014-08-20 00:08:00'))
AND location_ip = UNHEX('00000000000000000000FFFF5E40A690')
ORDER BY visit_ID ASC;
--------------------------------------------
SAMPLE OUTPUT:
--------------------------------------------
visit_ID
--------------------------------------------
2042228
2053821
...
--------------------------------------------
// LIST URLs VISITED BY THAT CLIENT IN THAT PERIOD, INCL. NUMBER OF HITS
SELECT a.idaction as URL_ID, count(b.idaction_url) as visits, a.name as URL
FROM piwik_log_action a, piwik_log_link_visit_action b
WHERE a.idaction=b.idaction_url
AND b.idvisit in (2042228,2053821)
GROUP BY URL_ID
ORDER BY visits DESC;
-----------------------------------------------
SAMPLE OUTPUT:
-----------------------------------------------
URL_ID visits URL
-----------------------------------------------
27845 8 www.example.com/en/index.php
...
-----------------------------------------------
Location IPs can be interpreted as follows:
2A0206B80000180C00000000B29AFF81 is an IPv6 address:
2A02:06B8:0000:180C:0000:0000:B29A:FF81 or:
2A02:06B8:0000:180C::B29A:FF81
00000000000000000000FFFF5E40A690 is an IPv4 address:
STRIP '00000000000000000000FFFF' AND WE GET:
5E.40.A6.90 in HEX or 94.64.166.144 in DEC (human readable) format.
Any suggestions for correcting/improving/amending the above will be welcome and appreciated!
I had seen/tried it already, but it didn’t work on my case, I am afraid…
For example:
mysql> SELECT inet_ntoa(conv(hex(location_ip), 16, 10)) as ip, SUM(visit_total_actions) as pviews
-> FROM piwik_log_visit
-> WHERE ((visit_first_action_time>='2014-08-20 00:00:00' AND visit_first_action_time<='2014-08-20 00:08:00')
-> OR (visit_last_action_time>='2014-08-20 00:00:00' AND visit_last_action_time<='2014-08-20 00:08:00'))
-> GROUP BY IP
-> ORDER BY pviews DESC
-> LIMIT 20;
+------+--------+
| ip | pviews |
+------+--------+
| NULL | 233 |
+------+--------+
1 row in set (1.82 sec)
Any advice in resolving this IP display issue will be welcome.
Note: I am using mysql 5.5.34 (on CentOS 6.5 x86_64).
