Hi all, I am trying to understand the relationship in Matomo data from looking at the schema to the API responses.
I understand the uniqueness of a Visit, however when looking at the database schema for Actions and Events, it is a little less clear.
In the schema (https://developer.matomo.org/guides/database-schema), I see that the key on log_link_visit_action is id_link_va, but this does not come back in the API response.
I can also see that there is a unique constraint on log_link_visit_action for visit and time, indicating that this table is page visits, but I can also see that a user of our site hits one page and we trigger three events, we see one action and three event records, which is correct, but they have the same idpageview, but different actiondatetime values.
The context of the question is we periodically call the API, store the results and then upsert on the next call. The logic we currently have is convoluted and I’d like to streamline it, but without knowing the relationship from the database schema to the API response, I am missing details to make this happen.
Basically, I’d love to know what exactly do idpageview, pageid, pageidaction and pageview position from the API (i.e. Live.getLastVisitDetails) actually relate to in the schema (if anything). There is a query here that details the underlying joins from the raw database. https://matomo.org/faq/how-to/faq_24536/#2-direct-read-only-access-to-the-mysql-database-not-available-for-matomo-cloud
My thoughts here are that, within a unique visit:
- idpageview - the unique identifier for the pages visited
- pageviewposition - the pages the user has clicked on, if they visit five pages, this will have multiple records for the values 1-5, effectively just a partitioned visit order by idpageview (?)
- pageidaction - I don’t quite follow this one, it seems like if you log an action, it gets a new ID and all events inside the same idpageview get the same ID here (which kinda makes sense as they aren’t actions, but they aren’t null and our type = search records have 0)
- pageid - this is the one that in the API response seems unique, and I think might somehow be able to be considered a PK, but I cannot find any information on this
Basically, I think I am at the end of trying to find the answer in the documentation … I think I am just seeking clarification as to what pageid exactly relates to or how it is constructed as well as confirmation that my thoughts above are correct. I have suspicions, but I’d like clarification that this is the case rather than me making an assumption.
If I have missed this in the documentation, I am happy to receive any directions as to where this is.
Thanks in advance!!
