Manual query to calculate Downloads


#1

Hello,

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?

The other option would be to re-generate all reports, after changing limits, as described here:
After the top 500 or top 1000 rows, Piwik automatically groups pages, keywords, websites, etc. under the label "Others"; How do I force Piwik to not limit the data? - Analytics Platform - Matomo
but I would like to try the manual way too, if possible.

(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!)

By the way, to disable “Others” Summarization, should we configure the value of “0” for all parameters described at: After the top 500 or top 1000 rows, Piwik automatically groups pages, keywords, websites, etc. under the label “Others”; How do I force Piwik to not limit the data? - Analytics Platform - Matomo ?

Please help.

Thanks,
Nick


(Kuba Bomba) #2

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.

See this line: piwik/ArchivingHelper.php at 2.16.0 · matomo-org/piwik · GitHub
In general, setting it to 0 should work. My recommendation is to set all the values to 100000 directly in the config file.


#3

Thank you for your kind assistance!

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/%';

Do you agree?

Thanks again,
Nick


(Kuba Bomba) #4

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.


#5

Thank you,

I confirmed that the two queries produce identical results.

All the best,
Nick