Move stats from site #1 to site #2

Support & Bugs
1

Running Piwik 1.7 I discovered that the stats from site.domain.com was actually being tracked as site2.domain.com because the wrong tracking code was attached to the actual site.

site.domain.com has the site ID 23 in Piwik
site2.domain.com has the site ID 22 in Piwik

I figured that I could fix this in the database by running a few queries, so I ran these queries on a copy of the database:


update `piwik_archive_numeric_2012_02`
set idsite = 23
where idsite = 22



update `piwik_archive_blob_2012_02`
set idsite = 23
where idsite = 22



update `piwik_log_visit`
set idsite = 23
where idsite = 22

I also ran the archive.php script after this.

While the site2.domain.com was empty with zero visits after these queries, site.domain.com (id 23) actually lost visits according to the Piwik admin interface. What did I do wrong or miss?

2

You missed other tables piwik_log_*

You can also recreate reports from the logs: How to - Analytics Platform - Matomo

3

[quote=matt]
You missed other tables piwik_log_*

You can also recreate reports from the logs: How to - Analytics Platform - Matomo

I did this:

  1. Ran the query on piwik_log_* tables.
  2. Added always_archive_data_day=1 and always_archive_data_period=1 to the config file.
  3. Deleted all piwik_archive_* tables in my Piwik Mysql Database
  4. Re-loaded the Piwik UI.

The result: New empty piwik_archive_* tables created.

What now?

4
  1. remove always_archive_data_day=1 and always_archive_data_period=1 from the config file.
  2. assuming you are NOT using Delete logs (otherwise you have no logs so you can’t re-process reports)
  3. delete all tables piwik_archive_XXX that you wish to re-create (blog AND numeric)
  4. load Piwik, it should create table piwk_archive_numeric_xx ?
5

I am not deleting any logs, (there are data ranging way back still in the database).

Just did exactly that, the piwk_archive_numeric_* tables are empty.

6

Enable “browser trigger archiging” in Piwik > Settings > general settings?

7

Tried that. The result: empty tables.

8

I forgot to say: edit the ts_created in the piwik_website table, to set to the start date that you have data for this website

9

The piwik_website table does not exist. Should I create one?

10

Sorry, I meant piwik_site then update the ts_created field using phpmyadmin for example.
Then delete all archive tables,
run the cron script or access UI
it should work?

11

Thanks! I first updated to 1.7.1 before doing anything.

Then checked that these look as they should, which they seem to do:
[attachment 530 Skrmavbild2012-02-26kl.18.37.58.png]

Then I deleted all piwik_archive_* tables.

Then I set Piwik settings Allow Piwik archiving to trigger when reports are viewed from the browser to Yes.

Then I clicked on the Piwik logo to go to the front page. I reloaded the database tables and saw four new piwik_archive_* tables, only tables for *_2012_01 and *_2012_02, nothing for 2011.

After this I ran


/usr/bin/php5 www//misc/cron/archive.php --url=http://

in the SSH terminal, but this changed nothing.

The end result: The changes for site #1 to site #2 got through and all visits that was erroneously collected/reported is now visible for the right site. However, one problem still exists: Logs from before january are missing.

12

Most of the ts_created are in January. To process data before, change the ts_created to earlier date (as early as your logs go)

13

I tried that, I changed them all to october (the month and date of the first site), went through all the above steps but it did not help.

I also tried this:


/usr/bin/php5 www//misc/cron/archive.php --url=http:// --force-all-websites --force-timeout-for-periods=1

Did not help.

14

Are you sure you have logs for 2011?

try this query:


select count(*), DATE( visit_last_action_time) as datel,idsite from piwik_log_visit group by datel,idsite order by datel asc,idsite asc
15

1 2011-10-17 10
1 2011-10-17 16
9 2011-10-17 23
2 2011-10-18 6
53 2011-10-18 23
4 2011-10-19 6
1 2011-10-19 8
1 2011-10-19 18
39 2011-10-19 23
21 2011-10-20 6
5 2011-10-20 8
31 2011-10-20 23

…and so on. 528 rows.

16

OK so it should definitely work :wink: delete all archive tables, check ts_created in piwik_site for ALL sites, then run archive.php

17

I think we got everything now (not entirely confident though, but what we have will do). However… There is a related problem with displaying the archived data where some archived visits are only visible if I select date range or a specific day, all other display options hide the visits. Look at this video for an example: Private video on Vimeo (use password piwik).

18

If the problem happens only for the “current” week/month AND if you are using archive.php auto archiving (How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo ) then it is a known bug that will be fixed in the next release

But if you have the issue for past periods as well, or if you are not using archive.php, then let me know

19

That is possible. Will wait until 1.7.2 and test again. Thanks!