I have a Piwik instance in which only data for 1 website is tracked. This website has 2 different groups of users that visit (mostly) completely different pages. One group has to log in. I want to track both groups in a seperate website in Matomo and implementing that for any future visits is not a problem. But I want to try to also split up the historical data for those who had to log in and add it to the new website with idSite=2. Has anyone here ever tried this?
So far my idea to approach this is to
first find all the id’s of the actions i want to split off in the log_action table,
then find the id’s of the visits that these actions belong to in log_link_visit_action,
then use the visit ids to find all the rows in log_link_visit_action that belong to these visits (since some rows may contain action id’s that are part of the public website)
then just change the idSite in the rows thus identified in log_visit and log_link_visit_action.
Would this be enough or am I missing something important?
currently im running into issues because, in my case, visitors to my site have often visited both of the subdomains that I am trying to separate in the same visit. So if I copy over the entire visit ( “then find the id’s of the visits that these actions belong to in log_link_visit_action,”) then it will end up bringing over data that i don’t want. I think the only way to do this successfully is to duplicate the visit across both websites and associate the actions from the subdomain I am moving with the copy while keeping the other actions associated with the original
Update: i have gotten this method to work. here are the steps i took: note this is only if you want to separate actions from the same visit, if you want to move the whole visit then changing the idsite for the relevant visits and actions in the log_visit, log_action and log_link_visit_action tables should be sufficient
Start with a fresh matomo install
track some visits to a localhost/test.html file and a localhost/test2.html which both go to the same idsite in matomo.
go into the database and find the idaction for the actions you want to break off (in the log_action table)
search the idaction_url column in the log_link_visit_action table for the idaction you found in step 3. these are the actions that you want to move to the new idsite
create a new idsite (by creating a new website through the matomo UI). i just did this here because i forgot to do it earlier
find the idvisit for the actions that you want to move (from step 3)
find these idvisit's in the log_visit table. these are the visits that encapsulate the actions that you want to move and may include other actions that you might not want to move
duplicate each idvisit that you found in the last step, make sure that the duplicates get a new idvisit and that you change the idsite of these new visits to the idsite of the new website you created earlier
now that you have a new idvisit that is associated with the new site, you can take the actions that you want to migrate (that you found in step 4; from the log_link_visit_action table) and change their idsite and idvisit to match your dupicated visit. this will essentially move it to the new siteID in matomo.
The side effect of this whole process is that you end up with duplicated visit data for the migrated visits across the two sites, which may have other unintended consequences in terms of data in reports potentially not being correct, but at least you retain the actions that you are trying to move.
NOTE: i am not a matomo daveloper and am in no way familiar with how matomo works internally. all of the information here is stuff that i found on the developer documentation and/or learned myself after looking into the process that you originally posted
Thanks for your updates! This will be helpful when I finally get a database dump to run some tests on myself. I’ll make sure to post an update myself once I’ve wrangled with this.
I assumed that since idaction_url and idaction_url_ref reference keys in a table that doesn’t contain idvisit that I could just ignore those, are you saying that is not the case? Or did some issue with those tables crop up because you duplicated the visits you wanted to track under a different site ID?
I havent tried this method with actual data yet so I havent had a chance to evaluate whether there are issues with this method.
the method i described above is not a complete solution to splitting the records of an idsite as i only really focused on the logged visits and actions. I would consult the official matomo developers database documentation (which should be linked to above somewhere) for more info on how the database works since all my information is either derived from there or from my own experimentation