Sorry if this has been asked before but I went through about 20 pages of posts without finding a solution…
Basically I need to calculate cumulative visitor statistics for all sites within my piwik network (200+ websites). Specifically I’m looking for percentage of mobile vs desktop users.
The multisite plugin doesn’t really show any relevant stats, just total page views and visits.
I realize I can add a second tracking link to all my sites that will be used for the “global” view, however I’d like to calculate this stat on the data I already gathered for 2012.
I tried looking at the database directly, however I’m having trouble figuring out the short codes for each browser version (i.e. what defines a mobile visitor or a desktop visitor?). I’m not opposed to manually running this stat in mysql, I just haven’t found any documentation on the DB structure along with what the column values actually mean.
SELECT COUNT(idvisit), SUM(IF(config_os=‘WI8’ OR config_os=‘WI7’ OR config_os=‘WVI’ OR config_os=‘WS3’ OR config_os=‘WXP’ OR config_os=‘W2K’ OR config_os=‘WNT’ OR config_os=‘WME’ OR config_os=‘W98’ OR config_os=‘MAC’ OR config_os=‘LIN’ OR config_os=‘WII’ OR config_os=‘PS3’ OR config_os=‘SOS’ OR config_os=‘NBS’ OR config_os=‘BSD’ OR config_os=‘UNK’,1,0)) AS DESKTOP, SUM(IF(config_os=‘IPD’ OR config_os=‘IPA’ OR config_os=‘IPH’ OR config_os=‘AND’ OR config_os=‘WPH’ OR config_os=‘WMO’ OR config_os=‘WCE’ OR config_os=‘MAE’ OR config_os=‘WOS’ OR config_os=‘BLB’ OR config_os=‘QNX’ OR config_os=‘SYM’ OR config_os=‘WP7’,1,0)) AS MOBILE FROM piwik_log_visit WHERE config_os IS NOT NULL
I used the UserAgentParser.php to classify desktop vs mobile. My lists above are not complete, I just used the values that were present in our database (i.e. we didn’t have any Nintendo DS hits so that’s not in the mobile list above).