Display pages for full year with a lot of data

(Albert Jankowski) #1

I have paths on my site called /my-account/[slug]. I would like to get the list of all these pages for the full year. The problem is that I have about 35,000 unique page views.

I did the following:

When I try to view the page and view All in the pager instead of 100 I get the following error:

Oops… there was a problem during the request. Maybe the server had a temporary issue, or maybe you requested a report with too much data. Please try again. If this error occurs repeatedly please contact your Matomo administrator for assistance.

Is there any suggestion on how I can this data.

(Peterbo) #2

You can use a segment for that: Page URL “contains” /my-account/. Increase the max_execution_time for this heavy request since the server needs some time to calculate that stuff: Segmentation - Compare segments of visitors - Analytics Platform - Matomo

(Albert Jankowski) #3

I was not able to get it to work.

I ended up just running a custom query to get what I wanted:

SELECT pla.name, plv.custom_var_v1, plv.custom_var_v4, pllva.server_time as visit_time
FROM piwik_log_visit plv
LEFT JOIN piwik_log_link_visit_action pllva ON pllva.idvisit = plv.idvisit
LEFT JOIN piwik_log_action pla ON pla.idaction = pllva.idaction_url
WHERE pla.name LIKE '%my-path%'
AND plv.custom_var_v2 LIKE '%My Role%'
AND pla.type = 1
ORDER by plv.idvisit DESC