I have a vanilla deployment of Piwik tracking an Angular site that does not have anything other than the basic tracking code in the site. I have been asked to provide a visitor by day over the last year report for a particular URL. Specifically “/home.aspx#/newsletter”
I can find the URL in the “Actions” “Pages” report but it is too high level - somebody wants to slice it within Excel and Tableau. How can I query the database to extract the raw data?
Hi,
I am not sure to understand.
You have /home.aspx#/newsletter under Action -> Pages
but what do you mean by too high level, you mean it is too difficult for your final user to click on the export button?
When I click on the export button it only gives the stats for that summary display. E.g. If my date range is for the year 2017 and I navigate down “Actions” -> “Pages” -> “/home.aspx#/newsletter” I only get the option to open “Row Evolution”. Is there some other way to get at this data via the UI - I have searched…
They would like to be able to get really low level detail such as timestamps for each visit etc. As if you were parsing the server log files themselves. That’s why I have been trying to directly query the database.
Yes but it only gives me the same stats as you’d see on the page. In the screenshot above the CSV file appears to only contain the summary counts for each URL.
I’m trying to get all the individual records across a year for a specific URL. E.g. for the URL “prestations-analytics/index” I would like to be able to export every single visit to date for 2017.
Here is an example of what I have been trying to do. My ultimate goal is an exported table of data listing the data for each and every visit for the URL.
SELECT visit_first_action_time, name , COUNT( DISTINCT idvisitor ) AS unique_visitors, COUNT( idvisit) AS total_visits, SUM( visit_total_actions ) AS sum_actions
FROM piwik_log_visit
LEFT JOIN piwik_site ON piwik_log_visit.idsite = piwik_site.idsite
WHERE visit_first_action_time >= '2017-01-01’
AND visit_first_action_time <= '2017-08-30’
GROUP BY visit_first_action_time#piwik_log_visit.idsite
ORDER BY unique_visitors DESC
you grabbed my attention 
not sure, you are going to like my idea… but let’s give it a try.
As the Overview report can give you the details of several period(I just tried with a xml file and it works), you can create a segment with page contains or is, and then applied it and export it.
No?
Greatly appreciate the help!
I did the following.
Once the page reloaded
But the data was still the same high level stuff. CSV contents below.
Date,Visits,Actions,Maximum actions in one visit,Bounces,Total time spent by visitors (in seconds),Returning Visits,Actions by Returning Visits,Maximum actions in one returning visit,Bounce Rate for Returning Visits,Avg. Actions per Returning Visit,Avg. Duration of a Returning Visit (in sec),Conversions,Visits with Conversions,Revenue,Conversion Rate,nb_conversions_new_visit,nb_visits_converted_new_visit,revenue_new_visit,conversion_rate_new_visit,nb_conversions_returning_visit,nb_visits_converted_returning_visit,revenue_returning_visit,conversion_rate_returning_visit,Pageviews,Unique Pageviews,Downloads,Unique Downloads,Outlinks,Unique Outlinks,Searches,Unique Keywords,nb_hits_with_time_generation,Avg. generation time,Bounce Rate,Actions per Visit,Avg. Visit Duration (in seconds)
2017,158,2273,187,2,179693,153,2203,187,1%,14.4,19 min 27s,2,2,0,1.27%,1,1,0,20%,1,1,0,0.65%,481,158,0,0,0,0,0,0,481,0.08s,1%,14.4,18 min 57sI just changed the date from Year to date range “2017/01/01” to “2017/08/18” and the exported data seems to be there now!
Thank you very much for helping!
Yeah!!!
could you please mark the post as solved?
Good question 
I hardly ever open a topic, I think you can mark one of your answer as the solution within the icons like the heart, the link, the flag etc