Can you track events by querying the database?

General questions
1

I have put events on the website that I would like to track using raw data by accessing the piwik database.

From what I understand, piwik_log_action seems to to be the table storing events and I should expect the details of the event to be in piwik_log_link_visit_action. But I’m not able to find event category/action that would relate to the event in these tables. Are they stored in one of the BLOB’s? Not sure.

Please help.

Thanks,
Achin

2

Hi,

Is there any reason why you want to modify the database directly which could break any data instead of using the reporting API to send the events to piwik?
https://developer.piwik.org/api-reference/tracking-api#optional-event-tracking-info

3

Thanks for the reply.

Don’t want to modify the database, just read it. Ideally want to write SQL queries that will let me make any kind of report related to traffic or events. Going ahead, we want to put events on product impressions too (not just clicks, so we expect the db size to increase very quickly) and so was trying to check if I could directly query the db to extract insights.

I saw there is a reporting API too but haven’t tried it yet. Querying an API for large amounts of data might be slower than firing a SQL query I presume.

4

Hi,

I’d recommend you to use the Reporting API then as I can’t guarantee backwards compatibility on the database structure.
https://developer.piwik.org/api-reference/reporting-api

But If you wan’t to query the Database, this may help:
https://developer.piwik.org/guides/persistence-and-the-mysql-backend

5

Hi Achin!

Did you come to a conclusion with this issue? I am with the same problem… I want to see event category/action related to the events in piwik.

I only see:

  • idaction_name: the ID of the page title action type for this action (integer)
  • idaction_name_ref: the ID of the page title action type for the previous action in the visit (integer)
6

Hi @Adriana, hi @achin.jindal ,

I have the same issue and was wondering if and how did you resolve it? Any ideas & tips are very much appreciated.

Thanks, Eve

7

I had the same issue and copying the format from this matomo faq page ended up being the answer for me: https://matomo.org/faq/how-to/how-do-i-write-sql-queries-to-select-visitors-list-of-pageviews-searches-events-in-the-matomo-database/#sql-query-to-select-all-event-names-where-the-event-action-contains-search