Event report doesn't aggregate within category/action if there are many event 'name' values

[samjf]

The event report found in Behavior > Events does not correctly aggregate the count of events if there are too many event names. This is evident on certain reports Category/Action evolutions which suddenly have a drop in events for no reason. Upon investigation in the database it was found these records had been 'wrapped' into the other without having their values aggregated in the category and action levels of the drill down. This causes somewhat unreliable reports which are dependent on having the total name values less than 50001 to have an accurate report of actions and category counts.

My current understanding of what occurs is as follows:

• Ultimately it appears all the remaining records are wrapped up into the other row.
• In the archive query, data is grouped into DB rows by event category, action, name and then order by summed visits
  • e.g. the following row: MY_CATEGORY, MY_ACTION, ACTION ABC123, 2
• the ordered list is given a counter based on the sorting like
  • 50001, MY_CATEGORY, MY_ACTION, ACTION ABC123, 2
  • everything above 50,000 is wrapped into an other record
  • 50001, mtm_ranking_query_others, mtm_ranking_query_others, mtm_ranking_query_others, (summed visits of other)

This is wrapped into other without any regard to if the Category or action will be visible in the report which essentially removes the event aggregation of those events.

Context

This was evident in the archiving of a large Matomo instance that collects many events. The events have a somewhat unique event name which means they don't necessarily re-occur often.

Expected Behavior

Event category and event action evolution should show the aggregated totals for visits each day without being affected by large amount of events.

Current Behavior

The evolution of both category and action can be seen to have a significant drop when a large amount of events have been recorded.

Possible Solution

• archive the event category report separately so that it is not grouped using eventName .
• It’ll require a bit of PHP logic and SQL change but … add a WITH ROLLUP modifier to the GROUP BY of category, action, name. This should add an extra row for each category/action combo with a NULL for name. We’d have to add another derived table in order to sort it as rollup/orderby don’t like each other. Then downstream in the logic we can:
  • minus all non rollup values from the rollup row.
  • split that into an other value within the action drill down.
• Make it clear event names cannot be too unique. This would seem hard to enforce compared to what most would consider the expected behavior of the report.

Steps to Reproduce (for Bugs)

1. Track a large amount of events so that the amount of records exceed the configured archiving_ranking_query_row_limit
2. Ensure the subject Event Category/Action has enough visits to a certain event name to be within the displayed within the table as a named row.
3. Ensure the subject Event Category/Action has event names which have a low visit count so that they are beyond the archiving_ranking_query_row_limit count when sorted.
4. Observe that the summation of the vists do not include the records from step 3.

Your Environment

• Matomo Version: 4.14.3-b2
• PHP Version: 8.0
• Server Operating System: Linux
• Additionally installed plugins: Premium plugins
• Browser: n/a
• Operating System: n/a

[bx80]

Thanks for an excellent write up @samjf :+1:

Refs: L3-440