[fix] Problems with Click Tracking size

Solutions for other advanced phplisters

[fix] Problems with Click Tracking size

Postby Guest » 5:51pm, Tue 05 Aug, 2008

Hi everybody!

Sorry for my english, I'm from Brazil.

I use the phplist version 2.10.5 with more than 500.000 users, I sent
something like 100.000 messages daily.

I customize all my phplist, thats includes some files like
/admin/index.php, /admin/sendlib.php, /admin/processqueue.php,
/admin/processbounces.php, among others importants files.

Today, after my 3º message by phplist, I see my database almost crashing,
my table linktrack has more than 3.000.000 lines, my server is working only
to process the requests of the linktrack feature.

I read on forum that the new version, 2.11.3, work fine with larges
databases and linktrack feature on, and I try to find a documentation of
the changes of this new feature, because, as I said, I customize importants
files and if I just upload the new version I will lose all my work.

There is some documentation on how to upload the new feature of linktrack
just changing some codes and adding new tables, without rewriting any
files?

Can anyone say me what files has changed in the version 2.11.3?

Thank you very much!
Guest
 

use linktrack_userclick table to create statistic

Postby Guest » 10:34am, Sun 23 Nov, 2008

http://mantis.phplist.com/view.php?id=6473

1. Problem: php_linktrack table is more than 500mb

2. Goal: try to use "php_linktrack_userclick" table (<3mb> 500mb)

message statistic picture link: Image [^]

3. Change: I just upload a modified version of mclick.php file (message click statistics page), please check the file I uploaded. The main change is the following sql script ---
Code: Select all
    $req = Sql_Query(sprintf('
        select m.id messageid,
                subject,
                uc.users,
                uc.linkcount,
                uc.totalclicks,
                if (ul.hasLinkTrack is null, 0, 1) hasLinkTrack,
                um.views views,
                um.total total
        from %s m
        Left join (
            SELECT messageid, count(distinct userid) users, count(distinct date) totalclicks, count(distinct linkid) linkcount
            FROM %s
            group by messageid) uc on uc.messageid = m.id
        Left join (SELECT messageid, 1 as hasLinkTrack
            from %s
            group by messageid) ul on ul.messageid = m.id
        Left join (SELECT messageid,
            count(viewed) as views,
            count(status) as total
            from %s
            group by messageid) um on um.messageid = m.id
        Where 1 = 1 %s
        order by m.id desc limit 20
    ',
    $GLOBALS['tables']['message'],
    $GLOBALS['tables']['linktrack_userclick'],
    $GLOBALS['tables']['linktrack'],
    $GLOBALS['tables']['usermessage'],
    $subselect));


4. Result: the mclick.php (message click statistics page) load much fast than before and I can even truncate or delete records from phplist_linktrack. For example: run delete sql script in phpmyadmin or mysql console

delete from `phplist_linktrack` where messageid < 60;
OPTIMIZE TABLE phplist_linktrack;

in my case, remove 350mb data from phplist_linktrack
and I will still be able to see "message click statistics page" mclick.php

PS. delete records from phplist_linktrack will cause you to lose some "url click statistics" data. Make sure you don't want the OLD "url click statistics" data
Guest
 

Re: [fix] Problems with Click Tracking size

Postby FabioLima » 12:49am, Wed 22 Jul, 2009

Hei "Guests" from Brazil.
If you see it e-mail me:

fabio.lima.corujao@gmail.com

I'm from Brazil too, and need more contacts about PHPList in my neighborhood.
FabioLima
PL Nut
 
Posts: 18
Joined: 7:14pm, Fri 12 Jun, 2009

Re: [fix] Problems with Click Tracking size

Postby MiThEuS » 3:03pm, Mon 04 Jan, 2010

Hi all,

I'm looking for a statistics click solution and I found this topic. Anybody could help? I don't know to use this module and I'm a little lost.

I'm brazilian too. My e-mail is: michel@lafanet.com.br

thks
MiThEuS
phpLister
 
Posts: 6
Joined: 12:43am, Mon 26 Oct, 2009

Re: [fix] Problems with Click Tracking size/ clicks not recorded

Postby popovici » 6:36am, Thu 22 Apr, 2010

phplinktrack table was 200 MB and I deleted all records where no clicks were recorded on. After that I sent a new message, and now in the /admin/?page=messages I can't see any clicks anymore. I've done no changes in the config.php file.

Any idea what might have happened?

Moreover, I've checked the links created by the system and the redirects don't work at all (example type of link: http://www.mydomain.ro/newsletter/lt.ph ... cfVA%3D%3D)
popovici
PL Nut
 
Posts: 25
Joined: 7:40am, Sat 28 Mar, 2009


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 0 guests