Re: keyword reports don't add up - how can we synch the data?


I’m trying to work out how piwik totals some of it’s reports and why they might be incorrect in my system?

I have a site which in piwik says on the 4th October there were 22 searches for a particular keyword.
However if I search the db table piwik_log_visit I find that there are only 18 searches for this site and this keyword on this day.

When I look in piwik via the web interface, referrers, search engine & keywords report - I get one totals showing 22 visits in total for this keyword on this date.
When I look at the visitor log on that day I can ONLY find 18 searches on this day for that keyword.

I know that piwik makes totals for days, weeks etc and stores these in archive tables.
e.g. piwik_archive_numeric_2011_10 and piwik_archive_blob_2011_10
How can I find where this particular archive is stored and work out what is going wrong?

I think it may be related to the fact that we deleted some junk data from the piwik_log_visit table at some point. Now a few things don’t add up!

Is there anyway to re-run the archived totals so they reflect the data we may have deleted from appearing in the totals?

I don’t understand the relationship between piwik_archive_blob_2011_10 and other tables - what does it do and how is it brought in to reports.

BTW: We are good with db but I’d rather not get in to hacking around with piwik code.

Many thanks for any piece of advice however small on this subject.



Is it possible to delete the archive tables and then have them re-calculate for a previous month?

As an experiment I tried deleting (well renaming) the October archive tables.

When I went back in to the piwik web interface this seemed to trigger re-calculating SOME of the October figures.

BUT SOME have not been re-calculated and now my keyword report (for example) just says “There is no data for this report.”

when I examine the NEW piwik_archive_numeric_2011_10 it has 740 records.
when I examine the OLD piwik_archive_numeric_2011_10 it has 2993 records.

Clearly this is where my problem lies how can I get piwik to re-calculate and insert these totals for old (October) data?

Sorry if I am being naive just in a bit of a panic! :open_mouth:


See reprocess reports from logs