Can someone please suggest a manual query to be run against the (MySQL) db, to calculate exact number of downloads for a particular website, from a particular location (e.g. /this/file/location/) during a particular date/time range?
(Edit: This would still not be the same, because someone would have to add all entries in the report manually, so a manual query is what we would actually need in this case!)
select * from piwik_log_action where type = 3 and name like '%/this/file/location/%';
Review the results to find the appropriate file and idaction.
select count(*) from piwik_log_link_visit_action where idsite = 1 and server_time > '2016-01-01 00:00:00' and server_time <= '2016-12-31 23:59:59' and idaction_url = 123;
idsite - your site ID, in my case 1 server_time - time range, here I selected the whole year 2016 in UTC timezone idaction - action ID of the file (selected with the previous query), in my case 123
Of course, you can build one query with a subquery to get the idaction_url.
If we try to combine the two queries to produce a single one (by using a JOIN), I believe we could use (example for January 2016):
select count(*)
from piwik_log_link_visit_action a, piwik_log_action b
where a.idsite = 1
and a.server_time > '2016-01-01 00:00:00'
and a.server_time <= '2016-01-31 23:59:59'
and a.idaction_url = b.idaction
and b.type = 3
and b.name like '%/this/file/location/%';
Yes, this should work. Alternatively, you can also use something like that:
select count(*)
from piwik_log_link_visit_action llva
join piwik_log_action la
on llva.idaction_url = la.idaction
where llva.idsite = 1
and llva.server_time > '2016-01-01 00:00:00'
and llva.server_time <= '2016-01-31 23:59:59'
and la.type = 3
and la.name like '%/this/file/location/%';
Always remember to run explain select before executing to confirm that the query is using a proper MySQL index.