I’m looking into an issue which seems to have been occurring for a while. Partly due to our website links not following a convention.
When I look at our entry pages for example there is some names which are duplicated(see screenshot below). I have now put in place a piece of javascript to ensure we are only logging in lowercase.
However how can I amend all previous logs to ensure they are all the same? Is there any tables which I could look at and replace the URLs manually to lower case?
My understanding is the old logs are archived and the URL is stored in the ‘value’ column of the matomo_archive_blob_2022_05 table for example? I don’t think I can change this easily, is there any query which to force all the previous URLS to lower?
Fist, I think you’ll have to update some raw data in the MySQL database.
Before any manual update, don’t forget to backup the database…
I suggest you search the IDs of wrong data in table log_action, column name. Get also the IDs of equivalence in the same table for good values.
Then in table log_link_visit_action update the wrong log_action ids by the good ones in columns:
idaction_url : the ID of the URL action type for this action
idaction_url_ref : the ID of the URL action type for the previous action in the visit
idaction_name : the ID of the page title action type for this action
idaction_name_ref : the ID of the page title action type for the previous action in the visit
