We’ve had this issue which even paid piwik support wasn’t able to shed any light into, so I’ll try my luck here.
We’re using 2 custom variables when recording a hit, as it happened, due to our mistake, Piwik recorded those custom variables with some requests only. It happened probably due to race condition. We fixed our tracking code and the custom variables started recording properly for all visitors. So far so good.
Now we wanted to update the previous hits with the custom variables so the data were correct retrospectively. A guy from support suggested this:
“For the past visits, you can update the custom variables directly in the database (in log_visit table) & drop the archive tables (archive_*) to force Piwik to re-process the data taking into account changes that you did in the log_visit table”
So I created this script that looks like this: (~18k records)
update piwik_log_visit set custom_var_k1 = ‘campaign’, custom_var_v1 = ‘309’, custom_var_k2=‘press_release’, custom_var_v2 = ‘578’ where idvisit=3019;
after running it (without errors or warnings) and deleting the archive tables (both numeric and blob) the visitor log indeed showed the custom variables properly for all visits - so that seems like it was fixed.
However the Visits Overview widget and any other widget suddenly started showing zero visits. When I apply the custom segment filter, they appear again, but without it, they’re zero.
So I have 2 questions:
- Does anyone have an idea why they were zeros?
- What is the correct way of adding the custom variables retrospectively?
EDIT: this issue is the same in old 1.x piwik and also when I upgraded to the newest 2.x version