Page View / Unique Page View Query

Support & Bugs
1

hi can i know what is the query for the PIWIK under this link ?

yourdomain.com/stats/index.php?module=CoreHome&action=index&date=yesterday&period=day&idSite=2#module=Actions&action=indexPageUrls&date=yesterday&period=day&idSite=2

i am trying to do a more specific filtering across the Actions/Page in a custom script to my own admin panel using piwik

here is my code which i had come up with to fetch but it dosent seem to fetch the correct data.

Is there anyone that can help to give some advise on this ?

SELECT idaction_url, count(distinct idvisit) as pageview, count(distinct idvisitor) as uniquepageview, AVG(time_spent_ref_action) as timespent FROM piwik_log_link_visit_action WHERE DATE( CONVERT_TZ( server_time, ‘+00:00’, ‘+08:00’ ) ) = ‘2011-09-07’ AND idsite=2 GROUP BY idaction_url

SELECT idaction_name as idaction, sum(time_spent_ref_action) as timespent FROM piwik_log_link_visit_action AS log_link_visit_action LEFT JOIN piwik_log_visit as piwik_log_visit ON (piwik_log_visit.idvisit = log_link_visit_action.idvisit) WHERE server_time >= “2011-09-07” AND server_time <= “2011-09-08” AND log_link_visit_action.idsite = 2 AND time_spent_ref_action > 0 AND time_spent_ref_action > 0 AND idaction_name > 0 GROUP BY idaction_name, idaction

i have been trying on these 2 codes but no luck…

2

What are you trying to do? you should use the API for it if you can: http://piwik.org/docs/analytics-api/reference/

3

how do i make use of the api? i am trying to do a customised screen just to show the filtered stuffs

like eg.

there will be home, about us, contact us, and articles,

we only need to track the articles and get the header name

because currently it is only showing the id name so i will need to recode and make it to read it in another db to fetch the title name

please advise

4

yes i have same quistion…

5

In most cases, you do not need to go in the database to get information.

The Piwik Analytics API has a lot of features, you should really check out this page : http://piwik.org/docs/analytics-api/reference/

As an example, if you need to get the titles of your pages, you should look at the Actions module : http://piwik.org/docs/analytics-api/reference/#Actions and maybe use Actions.getPageTitle or another method which is suitable to your use case.

How you make API calls really depend on which programming environment you are using.