Removing historical query string parameters

Support & Bugs
1

Hi all

I’m currently running Matomo 4.10.x and I’ve been tasked with removing a certain query string parameter from a given URL in historical data within Matomo and would like some guidance on approach.

I’ve updated the Exclusions settings for the website in Matomo, invalidated the reports and re-ran the core:archive command.

Other than writing some code to go through the reports and remove these values from the DataTables, is there a better, more efficient way?

Thanks!

2

This will just allow you to track without query string parameters (for new tracking only)…

The only way is to:

  1. Backup the database
  2. For each similar URL, and for each action type in the log_action table, select the ID of the good one, and update, in log_link_visit_action the idaction_url, idaction_url_ref, idaction_name, idaction_name_ref from the bad ones to the good one… If there is never good rows (with URL without query string), select one and update its name by removing the query string…
  3. (?) Remove bad rows in the log_action table
3

Thanks for your reply; I’m a bit confused though. Here’s what I did already (working on a local copy of the db):

UPDATE log_action SET name=REPLACE(name, "paramName=old", "paramName=new"), 
hash=CRC32(REPLACE(name, "paramName=old", "paramName=new")) 
WHERE type = 1 AND name LIKE "%paramName=old%"

This updates the records in the log_action correctly, but when I refresh the Matomo report I still see the old URL.

If I understand your suggestion correctly, you’re saying to create new rows in log_action and then update the references to these in log_link_visit_action.

Am I correct in understanding your suggestion?

4

Oh yes, you are right, I forgot to add the last step: after updating the database, you have to invalidate the past data and run again the archiving.

Another point, if you just update the values of log_action (and not update log_link_visit_action), I think you will see some duplicate page URLs in your reports (but with different values)

5

Thanks for that. Turns out I wasn’t seeing changes because I was updating the wrong database :man_facepalming:

Where would you expect to see duplicate values if I don’t update log_link_visit_action?

It’d be really helpful to know where to check.

And thanks again for your help - I owe you a beer :slight_smile: