3 (Three) Level Event Reports

[rufinus]

We use the event tracking a lot.
We have

• Category
• Actions
• Names

In Piwik we can't display all 3 of them, only 2 are shown.
For example:
if you look at the categories you see all actions of this category -> missing name
if you look at the names you see all actions of this name -> missing category
if you look at the actions, you see all names of this action -> missing category

What we need is to group the results by this third layer.

The only way to get around this, is with segment filters by, for example a specific category, then i get all names and actions for this category. the problem is, there is an infinite number of categories in many different sites.

Is there a way to get this grouping in to the backend? (it would be enough to have this in the API)

To illustrate our use case see the following images:

TIA

[mattab]

Hi @rufinus thank you for the suggestion. I agree that it would be really great to be able to see the 3 levels of Event data at once. Maybe we will do this later with a new Custom Reports feature: #4776

[mgonera]

This is rather important to me @mattab ;)

[mattab]

@mgonera do you have some more info/ context?

[mgonera]

@mattab Basically demand is to be able to review all of Events data levels in one view. Furthermore, user would like to have data about Events enhanced with data about Goals triggered within visit containing such an event and so on.

[mattab]

Maybe we could create a new report, that aggregates all 3 dimensions, into one table:

• the table row labels would be something like EventCategory - EventAction - EventName
• there would be no sub-table.

What do you think @mgonera @rufinus

[mgonera]

@mattab in my opinion such approach doesn't fulfill the need behind the issue. Hierarchy is key feature of this report. User's desire is to have 3 level structure.

[mattab]

ideally for this we need Custom Reports feature: #4776

[alemonteiro]

Hi, we really need this feature, so I'm taking a shot at it.

I was able to create a new record name to store the full hierarchy and return with the new API method Events.getEventsTree

Changes made:
Handling archiving of Events Full Tree (Category -> Action -> Name)
Adding API.Events.getEventsTree method
Adding $dataTreeLevel to DataArray to handle 3 levels hierarchy

Events.getEventsTree response example here

Is this an acceptable implementation approach?

[tsteur]

I haven't checked in detail but could work. You would likely need to limit the number of rows quite a bit for each level in these reports otherwise you end up with say up to 1000 x 1000 x 1000 rows = and this would not only take forever to process but also take a lot of storage. So you'd want more like 50 x 50 x 50 or something (restrict to 50 rows on each level).

The problem is maybe also that sometimes people want it in a different order, eg Category -> Name -> Action causing even more rows / storage / processing time. We'd need to see how this impacts archiving / storage etc.

With Custom reports there would be already a more flexible solution there and it would be only processed when this data is actually needed. Just in case you're not aware of it.

[alemonteiro]

I haven't checked in detail but could work. You would likely need to limit the number of rows quite a bit for each level in these reports otherwise you end up with say up to 1000 x 1000 x 1000 rows = and this would not only take forever to process but also take a lot of storage. So you'd want more like 50 x 50 x 50 or something (restrict to 50 rows on each level).

How can I limit it? Since the Events plugin capped the returned rows at 10 on each report, and I didn't see that limitation inside of the plugin, I assumed the limit was built in inside Archive. I just took a second look and wasn't able to find it, if you could point me in the direction it'll be very helpful.

The problem is maybe also that sometimes people want it in a different order, eg Category -> Name -> Action causing even more rows / storage / processing time. We'd need to see how this impacts archiving / storage etc.

Agreed. In terms of resources I think the best option would be to create configurations for the user to decide which hierarchies they want. In my case I need only two of the seven (including my new one) record names being archived at the moment.

With Custom reports there would be already a more flexible solution there and it would be only processed when this data is actually needed. Just in case you're not aware of it.

Thanks for reminding me. It is an viable option that I'm discussing with my product manager.

[tsteur]

How can I limit it?

It's a bit complicated to explain and this part of the archiving isn't really documented. So there is a concept ranking query in the archiving that can limit the query to a certain number of rows. Then additionally, when we serialise the table to store it in the DB here: https://github.com/matomo-org/matomo/blob/3.13.4/plugins/Events/Archiver.php#L223 different limits would need to be used for reports with 3 levels. Eg $dataTable->getSerialized($firstLevel = 30, $secondAndThirdLevelRows = 50, ...)

You find the ranking query here: https://github.com/matomo-org/matomo/blob/3.13.4/plugins/Events/Archiver.php#L179
Adjusting this part in the 3 level report is probably not really needed, but it can make things faster since a lot less aggregating will need to be done in PHP.

[alemonteiro]

Thanks for the explanation.

As I understand it, the same configuration for second row is already being applied recursively to the third level on $subTable->getSerialized($maximumRowsInSubDataTable, $maximumRowsInSubDataTable, $columnToSortByBeforeTruncation, $aSerializedDataTable);.
Is it necessary to create an third configuration for this case? Something like datatable_archiving_maximum_rows_subsubtable_events?
Or have two discting configurations for two level and three level archiving? Something like datatable_archiving_maximum_rows_3_level_rows_events and datatable_archiving_maximum_rows_3_level_rows_subtable_events ?

About the ranking query, I didn't fully understand it. Events Archiver insertDayReports() and aggregateDataTableRecords()uses the configurationdatatable_archiving_maximum_rows_eventsanddatatable_archiving_maximum_rows_subtable_events. But aggregateDayEvents()callsArchivingHelper::getRankingQueryLimit()which usesdatatable_archiving_maximum_rows_actionsanddatatable_archiving_maximum_rows_subtable_actions`.

So, if rows_actions config is bigger than rows_events, some rows will be fetched but not archived? Those rows would be archived in the next archiving session?
And in the other case, if row_events is bigger, it would be disregarded and capped by rows_actions value?
Is this interpretation correct?

[tsteur]

Is it necessary to create an third configuration for this case?

The value for 2nd level will be also applied for all lower levels so it shouldn't be needed to add another setting for this. It's just important to not pick a high value for the 2nd level.

About the ranking query, I didn't fully understand it.

I would say this is likely a bug. It should use the event row levels not the action row levels I would say.

So, if rows_actions config is bigger than rows_events, some rows will be fetched but not archived?

They would be still archived but within PHP they would be grouped into some Others row.

And in the other case, if row_events is bigger, it would be disregarded and capped by rows_actions value?

Yes indeed I would say.

[mattab]

Fyi another feedback by email from a user who didn't see what he expected:

Its such a clunky design - all I want to see are the categories, the actions in that category and then all the names and values.