Actual Query for Actions > Page Titles


#1

Enabling debug doesn’t give the actual queries used to calculate the page views, unique page views, etc. How do I get those queries?

Thanks,
James Beauchamp


#2

To be more specific…

I need to replicate this report on JasperServer with additional data from Joomla. We have the extension installed which pushes the titles to Piwik and allows Piwik to show the hierarchy of the Joomla categories. Is there a way to see the raw queries before the archive is created or better yet can someone just give me the query used to create that view?

Thanks,
James


#3

Anyone???

Here are my queries:

CREATE TABLE IF NOT EXISTS unique_pageviews (
SELECT
llva.server_time,
llva.idvisit,
la_url.idaction AS article_url_id,
la_url.name AS article_url,
la_name.idaction AS article_title_id,
la_name.name AS article_title
FROM piwik_log_link_visit_action llva
LEFT JOIN piwik_log_action AS la_url
ON llva.idaction_url = la_url.idaction
LEFT JOIN piwik_log_action AS la_name
ON llva.idaction_name = la_name.idaction
WHERE llva.idsite = 1
AND llva.idaction_name IS NOT NULL
AND llva.server_time BETWEEN ‘2014-02-19 00:00:00’ AND '2014-02-19 23:59:59’
GROUP BY llva.idvisit, llva.idaction_url
ORDER BY idvisit);

SELECT SUM(sub.pageviews) AS total_unique_pageviews
FROM (SELECT
COUNT(*) AS pageviews
FROM unique_pageviews
WHERE article_title LIKE 'ELC/%'
GROUP BY article_url) AS sub;