Visitors with most pageviews stats


#1

Hello,

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:

  1. Selecting a particular site page (e.g. from a dropdown with site pages), could display the same stats but for the particular page only.
  2. 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?

All the best,
Nick


#2

Any advice on this, please?


(Matthieu Aubry) #3

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/


#4

I have opened the following feature request for this: http://forum.piwik.org/read.php?3,119086

Anyone who agrees this functionality is a real need may say so on the above thread, so it might be added in a future version!

Thanks,
Nick


#5

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!


(Matthieu Aubry) #6

@nick1200 see this FAQ to display IPs addresses nicely: How do I select IP addresses or Visitor ID directly from the database? - Analytics Platform - Matomo


#7

Thanks Matt,

I had seen/tried it already, but it didn’t work on my case, I am afraid… :frowning:

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


(Matthieu Aubry) #8

Does it not work for IPv6 only?

if so Can you try to replace INET_NTOA with INET6_NTOA ?


#9

Tried it too; I get:


ERROR 1305 (42000): FUNCTION piwik.inet6_ntoa does not exist


#10

I also upgraded to mysql 5.5.39 today; same behavior.


(Matthieu Aubry) #11

Umh ok… feel free to create an issue in our issue tracker if you care about this :slight_smile: Issues · matomo-org/piwik · GitHub