wiki:PiwikServer

Version 34 (modified by chris, 4 years ago) (diff)

--

Piwik Web Stats

When upgrading the Piwik servers please first do it on the dev version and then if it all goes OK do it on the live site, see ticket:418 for an example of the steps this involves.

Upgrades:

Plugins

Do Not Track

Upgraded to version 0.3 on ticket:414

Live Server

The live install at https://stats.transitionnetwork.org/ on the NewLiveServer, the install was done on ticket:160.

Install

cd /web
cd stats.transitionnetwork.org/
wget http://piwik.org/latest.zip
unzip latest.zip
cd piwik
chown -R www-data:www-data tmp/
chown -R www-data:www-data config/
chown -R www-data:www-data themes/

Apache

Create stats.transitionnetwork.org.conf in cd /etc/apache2/sites-available/:

<VirtualHost *:80>
  ServerName stats.transitionnetwork.org
  ServerAlias stats.transitionnetwork.org.webarch.net
  ServerSignature Off
  UseCanonicalName On

  ErrorLog /var/log/apache2/stats.transitionnetwork.org_error.log
  ErrorLog /var/log/apache2/error.log
  SetEnvIf Remote_Addr "127\.0\.0\.1" loopback
  SetEnvIf Remote_Addr "kiwi\.transitionnetwork\.org" loopback
  CustomLog /var/log/apache2/stats.transitionnetwork.org_access.log combined env=!loopback
  CustomLog /var/log/apache2/access.log combined env=!loopback

  Redirect / https://stats.transitionnetwork.org/
</VirtualHost>

<VirtualHost *:443>
  ServerName stats.transitionnetwork.org
  ServerAlias stats.transitionnetwork.org.webarch.net
  ServerSignature Off
  UseCanonicalName On

  LogLevel warn
  ErrorLog /var/log/apache2/stats.transitionnetwork.org_ssl_error.log
  ErrorLog /var/log/apache2/error.log
  SetEnvIf Remote_Addr "127\.0\.0\.1" loopback
  SetEnvIf Remote_Addr "kiwi\.transitionnetwork\.org" loopback
  CustomLog /var/log/apache2/stats.transitionnetwork.org_ssl_access.log combined env=!loopback
  CustomLog /var/log/apache2/access.log combined env=!loopback

  SSLEngine on
  SSLCipherSuite HIGH
  SSLProtocol all -SSLv2
  SSLCertificateFile      /etc/ssl/transitionnetwork.org/transitionnetwork.org.pem
  SSLCertificateChainFile /etc/ssl/transitionnetwork.org/gandi.pem

  DocumentRoot "/web/stats.transitionnetwork.org/piwik"
  <Directory "/web/stats.transitionnetwork.org/piwik">
    DirectoryIndex index.php
    Options -Indexes
    AddType 'image/x-icon' .ico
    AddHandler php5-script .php
    AddType text/html .php
    SSLOptions +StdEnvVars
    AllowOverride None
    order allow,deny
    allow from all
  </Directory>
  <Directory "/web/stats.transitionnetwork.org/piwik/tmp">
    Options Indexes
    RemoveHandler .php
    AllowOverride None
    order allow,deny
    allow from all
  </Directory>
  <Location /config>
    Order deny,allow
    Deny from all
    Allow from 127.0.0.1
  </Location>

  <FilesMatch "\.(cgi|shtml|phtml|php)$">
    SSLOptions +StdEnvVars
  </FilesMatch>
  <Directory /usr/lib/cgi-bin>
    SSLOptions +StdEnvVars
  </Directory>

</VirtualHost>

Enable it and restart:

/etc/apache2/sites-enabled
ln -s ../sites-available/stats.transitionnetwork.org.conf 35-stats.transitionnetwork.org.conf
apache2ctl configtest
apache2ctl restart

MySQL

As MySQL root user:

CREATE USER 'stats'@'localhost' IDENTIFIED BY '********';
GRANT USAGE ON * . * TO 'stats'@'localhost' IDENTIFIED BY '********' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS `stats` ;
GRANT ALL PRIVILEGES ON `stats` . * TO 'stats'@'localhost';
FLUSH PRIVILEGES;

Web Install

MySQL settings:

  • database server: 127.0.0.1
  • login: stats
  • password:
  • database name: stats
  • table prefix: (blank - we don't need one, the default is piwik_)
  • adapter: PDO_MYSQL

Super User settings:

  • super user login: webproject
  • password:
  • email: webproject (at) transitionnetwork.org

Suggested JS code:

<!-- Piwik -->
<script type="text/javascript">
var pkBaseURL = (("https:" == document.location.protocol) ? "https://stats.transitionnetwork.org/" : "http://stats.transitionnetwork.org/");
document.write(unescape("%3Cscript src='" + pkBaseURL + "piwik.js' type='text/javascript'%3E%3C/script%3E"));
</script><script type="text/javascript">
try {
var piwikTracker = Piwik.getTracker(pkBaseURL + "piwik.php", 1);
piwikTracker.trackPageView();
piwikTracker.enableLinkTracking();
} catch( err ) {}
</script><noscript><p><img src="http://stats.transitionnetwork.org/piwik.php?idsite=1" style="border:0" alt="" /></p></noscript>
<!-- End Piwik Tracking Code -->

Suggested image code:

<!-- Piwik Image Tracker -->
<img src="https://stats.transitionnetwork.org/piwik.php?idsite=1&rec=1" style="border:0" alt="" />
<!-- End Piwik -->

Configure

In the General Settings page:

  • Allow Piwik archiving to trigger when reports are viewed from the browser: no
  • Reports for today (or any other Date Range including today) will be processed at most every: 1800

Set up cron:

crontab -u www-data -e

5 * * * * /web/stats.transitionnetwork.org/piwik/misc/cron/archive.sh > /dev/null

Test it:

su www-data -c "sh /web/stats.transitionnetwork.org/piwik/misc/cron/archive.sh"

This generated this error:

ALERT - script tried to increase memory_limit to 134217728 bytes which is above the allowed value (attacker 'REMOTE_ADDR not set', file '/web/stats.transitionnetwork.org/piwik/core/Piwik.php', line 947)

Which can be ignored because in /etc/php5/apache2/php.ini it's set to double that already, 256M, so I don't understand why it tried to raise it.

Things added to /web/stats.transitionnetwork.org/piwik/config/config.ini.php, taken from global.ini.php:

[General]
force_ssl_login = 1
feedback_email_address = "webproject (at) transitionnetwork.org"
assume_secure_protocol = 1

GA Import

First install Python 2.6 from source:

aptitude build-dep python2.5 
cd /usr/local/src
wget http://www.python.org/ftp/python/2.6.7/Python-2.6.7.tgz
tar -zxvf Python-2.6.7.tgz
cd Python-2.6.7
./configure --with-threads --enable-shared
make
make install 
ln -s /usr/local/lib/libpython2.6.so* /usr/lib/
cd /usr/local/src
wget 'http://pypi.python.org/packages/source/s/setuptools/setuptools-0.6c11.tar.gz#md5=7df2a529a074f613b509fb44feefe74e'
tar zxvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python setup.py install
cd /usr/local/src
wget "https://gdata-python-client.googlecode.com/files/gdata-2.0.14.tar.gz" --no-check-certificate
tar -zxvf gdata-2.0.14.tar.gz
cd gdata-2.0.14
python setup.py install
cd /usr/local/src
aptitude purge python-mysqldb
wget "http://downloads.sourceforge.net/project/mysql-python/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz"
tar -zxvf MySQL-python-1.2.3.tar.gz 
cd MySQL-python-1.2.3
vim site.cfg
 mysql_config = /usr/bin/mysql_config
aptitude install libmysqlclient-dev
python setup.py build
python setup.py install

Then the GA code and import:

cd /web/stats.transitionnetwork.org/
wget http://clearcode.cc/google2piwik-1.2.5.tgz
tar -zxvf google2piwik-1.2.5.tgz
cd google2piwik-1.2.5
cp google2piwik.conf.sample google2piwik.conf

Edit google2piwik.conf:

## Google Account Credentials
# If you don't know your site table_id type:
#                       ./google2piwik.py -p
[google]
user_login = transitiontownsnetwork [at] gmail.com
user_pass  = XXX
table_id   = ga:XXX

# MySQL Piwik Database configuration
[mysql]
db = stats
host = localhost
port = 3306
user = stats
passwd = XXX
table_prefix =

# Set export date range
[export]
start = 2008-01-01
end   = 2011-06-29

## Piwik configuration of site.
# For default Piwik installation with one website, site_id should be set to 1
[piwik]
site_id  = 1
site_url = http://transitionnetwork.org

Test and backup db:

./google2piwik.py -p
./google2piwik.py -c
mysql_backup

Then change the timezone to UTC for the site on https://stats.transitionnetwork.org/ run it in screen:

screen
./google2piwik.py --verbose=2

Dev Server

The Piwik server that is to be installed on the DevelopmentServer, the install was done on ticket:160

Config

In /web/piwik.transitionnetwork.org/piwik/config

assume_secure_protocol = 1

Install

Site: https://piwik.transitionnetwork.org/

Download

cd /web/
mkdir piwik.transitionnetwork.org
cd piwik.transitionnetwork.org/
wget http://piwik.org/latest.zip
unzip latest.zip

Apache

Then set up apache, see /etc/apache2/sites-available/piwik.transitionnetwork.org and run the web installer and:

cd /web/piwik.transitionnetwork.org/piwik/
chown -R www-data:www-data tmp/
chown -R www-data:www-data config/

MySQL

Then a MySQL user and database was added using phpMyAdmin which ran the following SQL:

CREATE USER 'piwik'@'localhost' IDENTIFIED BY '********';
GRANT USAGE ON * . * TO 'piwik'@'localhost' IDENTIFIED BY '********' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS `piwik` ;
GRANT ALL PRIVILEGES ON `piwik` . * TO 'piwik'@'localhost';

Tracking code

The installer suggested this JS tracking code, see the docs at http://piwik.org/docs/javascript-tracking/ :

<!-- Piwik -->
<script type="text/javascript">
var pkBaseURL = (("https:" == document.location.protocol) ? "https://piwik.transitionnetwork.org.webarch.net/" : "http://piwik.transitionnetwork.org.webarch.net/");
document.write(unescape("%3Cscript src='" + pkBaseURL + "piwik.js' type='text/javascript'%3E%3C/script%3E"));
</script><script type="text/javascript">
try {
var piwikTracker = Piwik.getTracker(pkBaseURL + "piwik.php", 1);
piwikTracker.trackPageView();
piwikTracker.enableLinkTracking();
} catch( err ) {}
</script><noscript><p><img src="http://piwik.transitionnetwork.org.webarch.net/piwik.php?idsite=1" style="border:0" alt="" /></p></noscript>
<!-- End Piwik Tracking Code -->

And this image tracking code:

<!-- Piwik Image Tracker -->
<img src="https://piwik.transitionnetwork.org.webarch.net/piwik.php?idsite=1&rec=1" style="border:0" alt="" />
<!-- End Piwik -->

The following parameters can also be passed to the image URL:

  • rec - (required) The parameter &rec=1 is required to force the request to be recorded
  • idsite - (required) Defines the Website ID being tracked
  • action_name - Defines the custom Page Title for this page view
  • idgoal - The request will trigger the given Goal
  • urlref - The Referrer URL: must be set to the referrer URL used before landing on the page containing the Image tracker. For example, in PHP this value is accessible via

$_SERVER['HTTP_REFERER']

  • revenue - Used with idgoal, defines the custom revenue for this conversion

Customise the logo:

cd /web/piwik.transitionnetwork.org/piwik
chown -R www-data:www-data themes/

Cron

Set up the crontab to automatically archive the reports:

crontab -u www-data -e

5 * * * * /web/piwik.transitionnetwork.org/piwik/misc/cron/archive.sh > /dev/null

Python

For the Google Analytics data import we need Python 2.6 and we are running with Python 2.5.2, so following this a source compile was done on the dev server:

aptitude build-dep python2.5 
cd /usr/local/src
wget http://www.python.org/ftp/python/2.6.7/Python-2.6.7.tgz
tar -zxvf Python-2.6.7.tgz
cd Python-2.6.7
./configure --with-threads --enable-shared
make
make install 
ln -s /usr/local/lib/libpython2.6.so* /usr/lib/
cd /usr/local/src
wget 'http://pypi.python.org/packages/source/s/setuptools/setuptools-0.6c11.tar.gz#md5=7df2a529a074f613b509fb44feefe74e'
tar zxvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python setup.py install
cd /usr/local/src
wget "https://gdata-python-client.googlecode.com/files/gdata-2.0.14.tar.gz" --no-check-certificate
tar -zxvf gdata-2.0.14.tar.gz
cd gdata-2.0.14
python setup.py install
aptitude purge python-mysqldb
wget "http://downloads.sourceforge.net/project/mysql-python/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz"
tar -zxvf MySQL-python-1.2.3.tar.gz 
cd MySQL-python-1.2.3
vim site.cfg
 mysql_config = /usr/bin/mysql_config
aptitude install libmysqlclient-dev
python setup.py build
python setup.py install

GA Import

See python notes above and Google2Piwik.

cd /web/piwik.transitionnetwork.org/
wget http://clearcode.cc/google2piwik-1.2.5.tgz
tar -zxvf google2piwik-1.2.5.tgz
cd google2piwik-1.2.5
cp google2piwik.conf.sample google2piwik.conf

Edit google2piwik.conf:

## Google Account Credentials
# If you don't know your site table_id type:
#                       ./google2piwik.py -p
[google]
user_login = XXX@gmail.com
user_pass  = XXX
table_id   = ga:XXX

# MySQL Piwik Database configuration
[mysql]
db = piwik
host = localhost
port = 3306
user = piwik
passwd = XXX
table_prefix = 

# Set export date range
[export]
start = 2011-05-01
end   = 2011-06-29

## Piwik configuration of site.
# For default Piwik installation with one website, site_id should be set to 1
[piwik]
site_id  = 1
site_url = http://www.transitionnetwork.org

Get the table_id and test and backup db:

./google2piwik.py -p
./google2piwik.py -c
mysql_backup

Piwik, for the website you are importing data to, change the timezone to UTC and run the script:

./google2piwik.py --verbose=2

Requirements

The requirements are:

  • PHP version 5.1.3 or greater, We highly recommend using at least PHP 5.3 as it is much more memory efficient than previous PHP versions, we have: PHP 5.2.6-1+lenny10 with Suhosin-Patch 0.9.6.2 (cli), when the server is migrated to squeeze we will get a new php, it can wait till then.
  • MySQL version 4.1 or greater, we have 5.0.51a-24+lenny5-log
  • (enabled by default) PHP extension pdo and pdo_mysql, weh have these already installed and they are configured via /etc/php5/apache2/conf.d/pdo.ini and /etc/php5/apache2/conf.d/pdo_mysql.ini
  • PHP GD extension, installed already
  • Increase your PHP memory_limit value in your php.ini config file to be at least memory_limit = 128M, we have 256M already in /etc/php5/apache2/php.ini