Event Tracking in the Database

General questions
1

I am tracking events / analytics on a website using Matomo. I have a separate software system, custom built in our company, that looks directly at the Matomo/Piwik database to get the event data and show raw numbers inside our customer web-based software.

I have discovered an issue where I lookup a specific Event Category on a specific day and see 177 records (events) that were saved in the database.

However, when I login to Matomo, when I lookup that specific Event Category for that specific day, only 116 events are reported.

I want to ensure that I am pulling the correct number of events that are actually happening into my other software.

Does anyone know…
What causes the number of events stored in the database to differ from that displayed in the software?
What specific fields inside the “piwik_log_link_visit_action” table are used for?

I am wondering if there are maybe duplicate events that are stripped out by the Matomo front-end, but I haven’t yet figured out to identify this if that is the case.

2

I found out what was going on, there were 2 issues.

  1. The “server_time” was displayed in UTC, while the events in the Matomo front-end were displayed in my timezone.
  2. There are rows in the “piwik_log_link_visit_action” table that have a “0” value, specifically the “idaction_url_ref” field.

When I adjust my query to remove these zero values, and offset the timezone properly. I can get my raw database query to match the analytics displayed in the Matomo front-end.