Querying data directly from tables


(Pablo Solano) #1

Hi,

We have been using Piwik/Matomo for the last two years and now we need to report some data that we can’t get using the out of the box widgets.

For example:

  1. We want all search keywords with date and userids

|datetime1|search termA|userid1|
|datetime2|search termB|userid1|
|datetime3|search termA|userid2|

  1. All file downloade; were file name is an EventAction (I guess type =11)

|datetime1|filenameA|userid1|
|datetime2|filenameB|userid1|
|datetime3|filenameC|userid1|
|datetime4|filenameB|userid2|
|datetime5|filenameC|userid3|

Is there a way we can have an SQL statement that combines all this information in one single report? Maybe:

|datetime|actiontype|detail|userid|

For example:

|2018-25 2:00:00 PM|search|macro|userA|
|2018-25 2:01:05 PM|search|creating a macro|userA|
|2018-25 2:05:06 PM|visit|Page Title A|userC|
|2018-25 2:06:06 PM|visit|Page Title B|userC|
|2018-25 2:25:00 PM|search|Feedback|userC|
|2018-25 2:25:00 PM|EventAction|User Guide.pdf|userB|
|2018-25 2:25:00 PM|EventAction|Readme.txt|userB|

We are running 3.6 version.