Piwik/Matomo Archive: Why are there three records for the month period?

Question

I use a cron job to run the auto-archiving process for Matomo. It works great. I am analyzing some of the data and I noticed that when I filter the data in a numeric archive table for period 3 (which is the total number of visits for a given month), I get three records back for each site. However, I only expected to see one for each site e.g. the total number of visits to a site for a single month. Of those three records coming back for a single site, it appears that the oldest record in the query is the summation of the later two records and also the oldest record is the total visits to the site for a given month.

Here's an example of the query:

select * from piwik.piwik_archive_numeric_2019_10 as q
where q.name = 'nb_visits' and q.idsite = 1 and q.period = 3;

Does anyone know why there are three records being returned from a piwik_archive_numeric_<date> table for the month period (e.g. period 3).

Answer 1

joshmcode answer is correct, There are multiple archives in Matomo. there can be multiple archives for the same period and site, for example if you have multiple segments, or if multiple archives were processed at different times of the day, and the "outdated" ones may not yet be deleted. You can look at the idarchive field to see which archives it belongs to.

Answer 2

OP note: I am 85% sure this is correct. If anyone else has a better explanation, please post.

The solution is in the done value within the name column of the archive table. Basically, wherever there is a record where the name column = done, the corresponding idarchive is the associated correct key for other records. For example, if a row has done in the name column and the idarchive is 1234, then the correct sum_visit_length record for any given site is the one where idarchive is 1234, even if other values may exist for that site.

Here is how I used this in a more practical sense:

select * from piwik.piwik_archive_numeric_2019_10 as q
where q.period = 3 and q.name = 'nb_users' and q.idarchive in
                                                (select q.idarchive from 
                                                 piwik.piwik_archive_numeric_2019_10 
                                                 as q where q.name = 'done');

The above script finds all the idarchive values where the name column equals done and then the outer query only takes values from nb_users where the corresponding idarchive value is in the subquery.