Afternoon! For those of you following along with the development of our upcoming web analytics app, Cloud Control, you know that we are building it on top of the great open source analytics platform Piwik
. Piwik comes with a great standard user interface and an API to create widgets or access your data yourself.
One metric which wasn't imediately apparent on how to retrieve the data we waned was Page views
broken down by page
of the website. Here is our current version, for Piwik version 1.2 (Note: The database structure is known to flux with each major release to this may need tweaking if you are using this with a later version).
-- @idsite int the id of the site you want data on
-- @start timestamp where to start (YYYY-MM-DD HH:MM:SS)
-- @id int id of the page in the actions table
-- @views int number of pageviews for the page
-- @visits int number of unique visitors to the page
-- @entry int number of people who landed on the page
-- @page string the page title of the page
SELECT idaction_name as id,
COUNT(idlink_va) as views,
COUNT(DISTINCT idvisitor) as visits,
COUNT(IF(idaction_url_ref=0, 1, null)) as entry,
log_action.name as page
FROM log_link_visit_action, piwik_log_action
WHERE log_link_visit_action.idsite= ?
AND log_link_visit_action.idaction_name = log_action.idaction
AND server_time >= ?
GROUP BY idaction_name
ORDER BY pageviews DESC
To do: Link the cooresponding URL to the page title.