I think I found something, maybe piwik is changing the idsite during the archive.
for each day we have 14 values, one of those is the “done”.
for all problematic sites with no daily values, the next or previous id were counted twice.
but both counts for the next site has same values, it did not put the previous id counts in the next one.
id 62 with problem and 61 counted twice
mysql> select * from piwik_archive_numeric_2012_05 where idsite=62 and date1=“2012-05-20” ;
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
| idarchive | name | idsite | date1 | date2 | period | ts_archived | value |
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
| 8422 | done | 62 | 2012-05-20 | 2012-05-20 | 1 | 2012-05-21 04:37:17 | 1 |
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
1 row in set (0.00 sec)
mysql> select count() from piwik_archive_numeric_2012_05 where idsite=61 and date1=“2012-05-20” group by idsite;
±---------+
| count() |
±---------+
| 27 |
±---------+
1 row in set (0.00 sec)
id 15 problematic and 16 counted twice
mysql> select * from piwik_archive_numeric_2012_05 where idsite=15 and date1=“2012-05-20” ;
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
| idarchive | name | idsite | date1 | date2 | period | ts_archived | value |
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
| 8334 | done | 15 | 2012-05-20 | 2012-05-20 | 1 | 2012-05-21 04:31:08 | 1 |
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
1 row in set (0.00 sec)
mysql> select count() from piwik_archive_numeric_2012_05 where idsite=16 and date1=“2012-05-20” ;
±---------+
| count() |
±---------+
| 27 |
±---------+
1 row in set (0.00 sec)
id 5 problematic, id 4 counted twice
mysql> select * from piwik_archive_numeric_2012_05 where idsite=5 and date1=“2012-05-19” ;
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
| idarchive | name | idsite | date1 | date2 | period | ts_archived | value |
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
| 1439 | done | 5 | 2012-05-19 | 2012-05-19 | 1 | 2012-05-20 04:11:37 | 1 |
±----------±-----±-------±-----------±-----------±-------±--------------------±------+
1 row in set (0.00 sec)
mysql> select count() from piwik_archive_numeric_2012_05 where idsite=6 and date1=“2012-05-19” ;
±---------+
| count() |
±---------+
| 27 |
±---------+
1 row in set (0.00 sec)
now take a look in the ts_archive dates from one of the double counted ids:
mysql> select name,ts_archived from piwik_archive_numeric_2012_05 where idsite=61 and date1=“2012-05-20” ;
±------------------------------±--------------------+
| name | ts_archived |
±------------------------------±--------------------+
| bounce_count | 2012-05-21 04:35:42 |
| max_actions | 2012-05-21 04:35:42 |
| nb_actions | 2012-05-21 04:35:42 |
| nb_uniq_visitors | 2012-05-21 04:35:42 |
| nb_visits | 2012-05-21 04:35:42 |
| sum_visit_length | 2012-05-21 04:35:42 |
| UserCountry_distinctCountries | 2012-05-21 04:36:26 |
| bounce_count_returning | 2012-05-21 04:36:27 |
| max_actions_returning | 2012-05-21 04:36:27 |
| nb_actions_returning | 2012-05-21 04:36:27 |
| nb_uniq_visitors_returning | 2012-05-21 04:36:27 |
| nb_visits_returning | 2012-05-21 04:36:27 |
| sum_visit_length_returning | 2012-05-21 04:36:27 |
| bounce_count | 2012-05-21 05:00:42 |
| max_actions | 2012-05-21 05:00:42 |
| nb_actions | 2012-05-21 05:00:42 |
| nb_uniq_visitors | 2012-05-21 05:00:42 |
| nb_visits | 2012-05-21 05:00:42 |
| sum_visit_length | 2012-05-21 05:00:42 |
| bounce_count_returning | 2012-05-21 05:01:09 |
| done | 2012-05-21 05:01:09 |
| max_actions_returning | 2012-05-21 05:01:09 |
| nb_actions_returning | 2012-05-21 05:01:09 |
| nb_uniq_visitors_returning | 2012-05-21 05:01:09 |
| nb_visits_returning | 2012-05-21 05:01:09 |
| sum_visit_length_returning | 2012-05-21 05:01:09 |
| UserCountry_distinctCountries | 2012-05-21 05:01:09 |
±------------------------------±--------------------+
27 rows in set (0.00 sec)
the first block of values is close to the problematic id
mysql> select name,ts_archived from piwik_archive_numeric_2012_05 where idsite=62 and date1=“2012-05-20” ;
±-----±--------------------+
| name | ts_archived |
±-----±--------------------+
| done | 2012-05-21 04:37:17 |
±-----±--------------------+
1 row in set (0.00 sec)
take a look it ordered by ts_archived for all websites:
mysql> select idsite,date1,date2,name,ts_archived from piwik_archive_numeric_2012_05 where ts_archived > “2012-05-21 04:36:00” and ts_archived < “2012-05-21 04:37:28” ;
±-------±-----------±-----------±------------------------------±--------------------+
| idsite | date1 | date2 | name | ts_archived |
±-------±-----------±-----------±------------------------------±--------------------+
| 61 | 2012-05-20 | 2012-05-20 | UserCountry_distinctCountries | 2012-05-21 04:36:26 |
| 61 | 2012-05-20 | 2012-05-20 | bounce_count_returning | 2012-05-21 04:36:27 |
| 61 | 2012-05-20 | 2012-05-20 | max_actions_returning | 2012-05-21 04:36:27 |
| 61 | 2012-05-20 | 2012-05-20 | nb_actions_returning | 2012-05-21 04:36:27 |
| 61 | 2012-05-20 | 2012-05-20 | nb_uniq_visitors_returning | 2012-05-21 04:36:27 |
| 61 | 2012-05-20 | 2012-05-20 | nb_visits_returning | 2012-05-21 04:36:27 |
| 61 | 2012-05-20 | 2012-05-20 | sum_visit_length_returning | 2012-05-21 04:36:27 |
| 62 | 2012-05-20 | 2012-05-20 | done | 2012-05-21 04:37:17 |
| 65 | 2012-05-20 | 2012-05-20 | bounce_count | 2012-05-21 04:37:26 |
| 65 | 2012-05-20 | 2012-05-20 | max_actions | 2012-05-21 04:37:26 |
| 65 | 2012-05-20 | 2012-05-20 | nb_actions | 2012-05-21 04:37:26 |
| 65 | 2012-05-20 | 2012-05-20 | nb_uniq_visitors | 2012-05-21 04:37:26 |
| 65 | 2012-05-20 | 2012-05-20 | nb_visits | 2012-05-21 04:37:26 |
| 65 | 2012-05-20 | 2012-05-20 | sum_visit_length | 2012-05-21 04:37:26 |
| 67 | 2012-05-20 | 2012-05-20 | done | 2012-05-21 04:37:26 |
| 69 | 2012-05-20 | 2012-05-20 | bounce_count | 2012-05-21 04:37:27 |
| 69 | 2012-05-20 | 2012-05-20 | max_actions | 2012-05-21 04:37:27 |
| 69 | 2012-05-20 | 2012-05-20 | nb_actions | 2012-05-21 04:37:27 |
| 69 | 2012-05-20 | 2012-05-20 | nb_uniq_visitors | 2012-05-21 04:37:27 |
| 69 | 2012-05-20 | 2012-05-20 | nb_visits | 2012-05-21 04:37:27 |
| 69 | 2012-05-20 | 2012-05-20 | sum_visit_length | 2012-05-21 04:37:27 |
| 71 | 2012-05-20 | 2012-05-20 | bounce_count | 2012-05-21 04:37:27 |
| 71 | 2012-05-20 | 2012-05-20 | max_actions | 2012-05-21 04:37:27 |
| 71 | 2012-05-20 | 2012-05-20 | nb_actions | 2012-05-21 04:37:27 |
| 71 | 2012-05-20 | 2012-05-20 | nb_uniq_visitors | 2012-05-21 04:37:27 |
| 71 | 2012-05-20 | 2012-05-20 | nb_visits | 2012-05-21 04:37:27 |
| 71 | 2012-05-20 | 2012-05-20 | sum_visit_length | 2012-05-21 04:37:27 |
±-------±-----------±-----------±------------------------------±--------------------+
27 rows in set (0.01 sec)
I wonder if it would be counted twice for the week, month and year for the doubled Id.
I hope it helps to find the cause and fix it 