Piwik Get Pageviews & Visits by Page

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 & Visits 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).
--  Parameters: 
--   @idsite    int          the id of the site you want data on 
--   @start     timestamp    where to start (YYYY-MM-DD HH:MM:SS) 
--   Returns  
--   @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.
Small dog web development studio

View All Our Posts

Web application developers in western Washington, creating user centric web solutions for businesses of all shapes and sizes.

Our projects include ecommerce, event organization, user driven web applications and more.

Small Dog Studios, LLC. Small Dog Studios News