View Issue Details

IDProjectCategoryView StatusLast Update
0016757phplist applicationStatisticspublic25-03-13 11:26
Reporterduncanc 
PrioritynormalSeveritymajorReproducibilityhave not tried
Status resolvedResolutionfixed 
Product Version2.11.7 
Target VersionFixed in Version2.11.8 
Summary0016757: Click tracking conversion created incorrect totals on linktrack_uml_click
DescriptionProblem in SVN trunk.

The process to populate the new link tracking tables does not appear to be setting some linktrack_uml_click columns correctly.

The current linktrack_userclick table has one row for each click of a link by a user. So a user/linkid/messageid combination can have multiple rows.
The conversion process seems to expect only one row for each combination, line 83 is reading only the first row of several possible.

The effect is that the htmlclicked and textclicked columns in linktrack_uml_click have values of only 0 or 1, instead of the total number of html or text link clicks. Additionally these totals are inconsistent with the clicked column.
TagsNo tags attached.

Activities

duncanc

25-03-13 10:05

developer  

convertstats.php (4,806 bytes)

duncanc

25-03-13 10:05

developer   ~0051976

On closer look there is also a problem with the firstclick and latestclick fields on the linktrack_ml table.

One row on linktrack_ml will correspond to many rows on linktrack, so firstclick needs to be the earliest value from those rows, and latestclick needs to be the latest value from those rows.

The fields are updated for each row read from linktrack, so in effect the firstclick and latestclick values from final row read from linktrack are used.

I have modified convertstats.php to fix these issues.
- added a loop to process all rows in linktrack_userclick, instead of only the first
- calculate the earliest and latest click times for linktrack_ml
- changed some of the sql to reduce the number of queries executed

After converting the linktrack tables, I get the same detailed results through my statistics plugin on the 2.10.18 release and on trunk, and also with the figures shown by similar pages in core phplist.

The modified convertstats.php is attached.

michiel

25-03-13 11:25

manager   ~0051977

brilliant!, thanks. added to the code