Just a quick update today. I want to make myself a little most popular pages this week for the front page of this site. Now, I am using the lovely Piwik Analytics tool to monitor the site. So I first looked at the Piwik API. That should be the way to do it right? Not quite. The Actions plugin can give you either a list of URLs or a list of titles (with the getPageTitles or getPageUrls methods). No good for me. I want the title and the URL in the same data structure, so I can link stuff correctly. Googling round for "piwik popular pages" and such revealed nothing.
So I had to write a big fat nasty load of SQL to grab the data from the MySQL database. I guess you'll want to translate to whatever dialect you use if you need. I’m pretty sure that there may be a more efficient way to do this. Suggestions in the comments please :-) This query fetches the unique visits, hits page title and URL of the ten most popular pages on a particular site this week from my piwik database
SELECT COUNT( DISTINCT `idvisit` ) AS unique_visits,
COUNT( DISTINCT `idlink_va` ) AS hits,
(SELECT `name` FROM piwik_log_action WHERE idaction_url=idaction) AS url,
(SELECT `name` FROM piwik_log_action WHERE idaction_name=idaction) AS title
FROM `piwik_log_link_visit_action`
WHERE `server_time` >= '2011-07-04'
AND `server_time` <= '2011-07-10'
AND `idsite`=1
AND `idaction_name` IS NOT NULL
GROUP BY `idaction_name`
ORDER BY `unique_visits` DESC
LIMIT 10;