CODEWITHSUNDEEP
mysqlfacebookmatomo
jcubic
jcubic

Reputation: 66650

How to change Piwik visit urls in MySQL database?

I have this issue my previous post had lot of visits from facebook and they started to add query string to their outbound urls (eg. ?fbclid=IwAR26j_D60TXYnGASbu27ABBdZduNInguL4mp_nK7eqxm6UklZEpWt8jkZM4) so in my stats each visit from Facebook is unique url (some of them have more then one visit so maybe they are shares). I've added redirect for future visitors:

RewriteCond %{QUERY_STRING} fbclid=.+
RewriteRule (.*) /$1?fbclid= [R=302,L]

but I also want to change the urls in Piwik database (only one last post), but I have trouble to find where urls are saved, I've visited the post from incognito mode (because I have cookie set to not track my visits) so I have one url without value.

Piwik have support, but it's for pay subscription for businesses (I have free version), they also have GitHub issues but those are for bugs, so I don't know where to ask this besides StackOverflow.

What I've tried this:

mysql> select * from piwik_log_action where name = 'jcubic.pl/2018/10/pytania-rekrutacyjne-css.html?fbclid='
# this is post after redirect
+----------+----------------------------------------------------------+------------+------+------------+
| idaction | name                                                     | hash       | type | url_prefix |
+----------+----------------------------------------------------------+------------+------+------------+
| 2246     | jcubic.pl/2018/10/pytania-rekrutacyjne-css.html?fbclid= | 4170874330 | 1    | 2          |
+----------+----------------------------------------------------------+------------+------+------------+


mysql> update piwik_log_link_visit_action
   set idaction_url = 2246
   where idaction_url in (select idaction
                          from piwik_log_action
                          where name like '%pytania-rekrutacyjne-css.html?fbclid=%' and name <> 'jcubic.pl/2018/10/pytania-rekrutacyjne-css.html?fbclid=')

the query was successful adn the adaction_url get updated for all records. But when I've refreshed the Piwki report, I still get pages with fbclid query string with value.

Does anyone have knowledge about Piwik (old version 3.0.0 before it got renamed to matomo) and know how to change the urls in DB?

Upvotes: 0

Views: 212

Answers (1)

jcubic
jcubic

Reputation: 66650

It seems that my Piwik installation have delete old logs enabled and Piwk is creating Archive based on logs for Report. They are in its own table with data as blob and according to FAQ you should not re-build the archive if you have delete old logs enabled.

But just in case if you don't have this enabled, here are queries I've executed that should update all data and DB and then you can try to re-process Archive from logs as in FAQ.

Here are queries I've executed:

update piwik_log_visit set visit_exit_idaction_url = 2246
where visit_exit_idaction_url in (select idaction 
                                  from piwik_log_action 
                                  where name like '%pytania-rekrutacyjne-css.html?fbclid=%' and name <> 'jcubic.pl/2018/10/pytania-rekrutacyjne-css.html?fbclid=')

update piwik_log_visit set visit_entry_idaction_url = 2246
where visit_entry_idaction_url in (select idaction 
                                   from piwik_log_action 
                                   where name like '%pytania-rekrutacyjne-css.html?fbclid=%' and name <> 'jcubic.pl/2018/10/pytania-rekrutacyjne-css.html?fbclid=')

Also in archive.org there are still docs for DB schema

Upvotes: 0

Related Questions