hi can i know what is the query for the PIWIK under this link ?
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…