OJS Statistics Queries - Metrics Table - OJS 3.1.2

Hi Everyone,

I’m in need of some guidance on how to properly write the correct query from the metrics table for download stats for articles site-wide, during 2017 - 2018, split by top ten countries.

So far I’ve gotten as far as getting a sum of metric for a certain time period for assoc_type=259 which I’m not sure is correct for article/file downloads. I have checked the old archived wiki for stats framework and have confused myself. Could someone please explain the components of a query such as this example from this thread for user view count. How do the columns assoc_id, assoc_type, context_id and metric_type affect the query? And why are we using SUM() over COUNT()?

SELECT SUM(metric) AS metric FROM metrics WHERE assoc_id = ‘1983’ AND assoc_type = 257 AND context_id = ‘1’ AND metric_type = ‘ojs::counter’ LIMIT 5000;

Our Application.inc.php has these defined constants:

  • define(‘ASSOC_TYPE_ARTICLE’, ASSOC_TYPE_SUBMISSION); // DEPRECATED but needed by filter framework
  • define(‘ASSOC_TYPE_JOURNAL’, 0x0000100); // 256
  • define(‘ASSOC_TYPE_ISSUE’, 0x0000103); //259
  • define(‘ASSOC_TYPE_ISSUE_GALLEY’, 0x0000105); //261
  • define(‘CONTEXT_JOURNAL’, 1);

Query I’ve got up to this point:

mysql> select sum(metric) from metrics where assoc_type=259 and (MONTH BETWEEN 201705 AND 201804) ;
| sum(metric) |
| 297876 |
1 row in set (6.75 sec)

Thank you so much for reading, appreciate any assistance you can provide!