Comme je vous l’avais indiqué il y a quelques temps, j’utilise Piwik pour suivre les statistiques de fréquentation de différents sites web dont je m’occupe, que ce soit à titre personnel ou professionnel. Cette solution est une alternative libre à Google Analytics que vous pouvez installer sur n’importe quel hébergement.
Je me suis trouvé confronté il y a peu à un problème particulier. J’ai rapatrié sur un serveur dédié plusieurs sites qui étaient installés sur des hébergements mutualisés. Pour chaque site j’avais installé une instance de Piwik.
En migrant l’ensemble sur un seul serveur, j’ai cherché à fusionner les différentes instances de Piwik pour n’en garder qu’une seule.
J’ai trouvé un script PHP sur GitHub qui fait ça à merveille :
<?php
/**
* piwik_merge.php
*
* Version: 1.8.2 / 20.06.2012
* Author: Jan-Kaspar Münnich <jan@dotplex.de>
* Version: 1.9.2 / 21.12.2012
* Author: Alan Ivey <alan@echoditto.com>
* Version: 1.11.1 / 14.03.2013
* Author: Christian Hanne <hanne@laborb.de>
* Version: 2.0.0 / 18.05.2014
* Author: Christian Hanne <hanne@laborb.de>
* Version: 2.3.0 / 18.06.2014
* Author: Manuel Frei <manu@defect.ch>
*
* Description:
* Script to import sites, visits and conversions from one Piwik installation into another. Users and user permissions will not be touched.
* This version works with Piwik versions 2.3.0, probably there will be database structure changes in later versions.
* You should run this script in the shell with `php piwik_merge.php` since it could run very long.
* If the imported data doesn't show in Piwik, make sure that the created date of the site is not after the first (imported) visit.
*
* Important notice:
* I've written this script because I needed to merge two Piwik installations.
* It worked for me and this task, but maybe it won't work for you.
* The script is just quick work and may contain bugs or security issues.
* I just wanted to share this script in case it could help somebody.
*
* Changes in version 1.11.1:
* - added some further comments
* - fixed the creation date issue by automatically updating the site's creation date
* - exchanged some die-calls with echoes, to make sure we can import tables with buggy entries
* - did some syntax changes to improve readability
* - added missing database fields
*
* Changes in version 2.3.0:
* - add function to escape null values
* - add missing fields of new piwik version
*
*
* VERY IMPORTANT notice: You should really backup your database before running the script!
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License version 2
* as published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*/
/**
* Enter the database credentials in $db_old and $db_new.
*/
$db_old = array(
'server' => 'localhost',
'user' => 'root',
'pass' => 'password',
'db' => 'piwik',
'prefix' => 'piwik_',
);
$db_new = array(
'server' => 'localhost',
'user' => 'root',
'pass' => 'password',
'db' => 'piwik',
'prefix' => 'piwik_',
);
/**
* $import_sites defines the sites that should be imported from the old installation.
*
* Specify the id from the old installation as the array key. You find the ids in Piwik at "Settings" -> "Websites".
*
* If you set the value to 0, the whole site including urls, goals and reports will be copied to the new database. If you set the value to the id of a site existing in the new installation, data will be imported there. Obviously, old and new site should be identical regarding urls and goals.
*
* Example:
* $import_sites = array (
* 1 => 2, // Site with id 1 in the old installation will be imported in site with id 2 in the new installation
* 2 => 0 // Site with id 2 in the old installation will be imported a new site in the new installation
* );
*/
$import_sites = array();
/**
* Database fields, update the following arrays in case future versions of piwik add or remove fields for these tables.
*
* The name of the arrays correspond with the database tables:
* $[TABLE]_fields
*/
$log_action_fields = array(
'name',
'hash',
'type',
'url_prefix',
);
$site_fields = array(
'idsite',
'name',
'main_url',
'ts_created',
'ecommerce',
'sitesearch',
'sitesearch_keyword_parameters',
'sitesearch_category_parameters',
'timezone',
'currency',
'excluded_ips',
'excluded_parameters',
'excluded_user_agents',
'`group`',
'type',
'keep_url_fragment',
);
$site_url_fields = array(
'idsite',
'url',
);
$goal_fields = array(
'idsite',
'idgoal',
'name',
'match_attribute',
'pattern',
'pattern_type',
'case_sensitive',
'allow_multiple',
'revenue',
'deleted',
);
$report_fields = array(
'idreport',
'idsite',
'login',
'description',
'idsegment',
'period',
'hour',
'type',
'format',
'reports',
'parameters',
'ts_created',
'ts_last_sent',
'deleted',
);
$log_visit_fields = array(
'idvisitor',
'visitor_localtime',
'visitor_returning',
'visitor_count_visits',
'visitor_days_since_last',
'visitor_days_since_order',
'visitor_days_since_first',
'visit_first_action_time',
'visit_last_action_time',
'visit_exit_idaction_url',
'visit_exit_idaction_name',
'visit_entry_idaction_url',
'visit_entry_idaction_name',
'visit_total_actions',
'visit_total_searches',
'visit_total_events',
'visit_total_time',
'visit_goal_converted',
'visit_goal_buyer',
'referer_type',
'referer_name',
'referer_url',
'referer_keyword',
'config_id',
'config_os',
'config_os_version',
'config_browser_name',
'config_browser_version',
'config_device_type',
'config_device_brand',
'config_device_model',
'config_resolution',
'config_pdf',
'config_flash',
'config_java',
'config_director',
'config_quicktime',
'config_realplayer',
'config_windowsmedia',
'config_gears',
'config_silverlight',
'config_cookie',
'location_ip',
'location_browser_lang',
'location_country',
'location_region',
'location_city',
'location_latitude',
'location_longitude',
'location_provider',
'custom_var_k1',
'custom_var_v1',
'custom_var_k2',
'custom_var_v2',
'custom_var_k3',
'custom_var_v3',
'custom_var_k4',
'custom_var_v4',
'custom_var_k5',
'custom_var_v5',
);
$log_link_visit_action_fields = array(
'idsite',
'idvisitor',
'server_time',
'idvisit',
'idaction_url',
'idaction_url_ref',
'idaction_name',
'idaction_name_ref',
'idaction_event_category',
'idaction_event_action',
'time_spent_ref_action',
'custom_var_k1',
'custom_var_v1',
'custom_var_k2',
'custom_var_v2',
'custom_var_k3',
'custom_var_v3',
'custom_var_k4',
'custom_var_v4',
'custom_var_k5',
'custom_var_v5',
'custom_float',
);
$log_conversion_fields = array(
'idvisit',
'idsite',
'idvisitor',
'server_time',
'idaction_url',
'idlink_va',
'referer_visit_server_date',
'referer_type',
'referer_name',
'referer_keyword',
'visitor_returning',
'visitor_count_visits',
'visitor_days_since_first',
'visitor_days_since_order',
'location_country',
'location_region',
'location_city',
'location_latitude',
'location_longitude',
'url',
'idgoal',
'buster',
'idorder',
'items',
'revenue',
'revenue_subtotal',
'revenue_tax',
'revenue_shipping',
'revenue_discount',
'custom_var_k1',
'custom_var_v1',
'custom_var_k2',
'custom_var_v2',
'custom_var_k3',
'custom_var_v3',
'custom_var_k4',
'custom_var_v4',
'custom_var_k5',
'custom_var_v5',
);
$log_conversion_item_fields = array(
'idsite',
'idvisitor',
'server_time',
'idvisit',
'idorder',
'idaction_sku',
'idaction_name',
'idaction_category',
'idaction_category2',
'idaction_category3',
'idaction_category4',
'idaction_category5',
'price',
'quantity',
'deleted',
);
function escape_array($array) {
foreach ($array as $name => $value) {
$array[$name] = mysql_real_escape_string($value);
}
return $array;
}
function array_to_sql_values($array) {
$str = '';
foreach ($array as $value) {
// decide if empty string, NULL, or real value (=== to check type an value)
if($value === '') {
$str .= "'',";
} elseif($value === null) {
$str .= "NULL,";
} else {
$str .= "'" . mysql_real_escape_string($value) . "',";
}
}
// remove last ,
return rtrim($str, ",");
}
// connect databases
$db1 = mysql_connect($db_old['server'], $db_old['user'], $db_old['pass']);
if (!$db1) {
die("Error: " . mysql_error($db1) . "\n");
}
if (!mysql_select_db($db_old['db'], $db1)) {
die("Error: " . mysql_error($db1) . "\n");
}
$db2 = mysql_connect($db_new['server'], $db_new['user'], $db_new['pass'], true);
if (!$db2) {
die("Error: " . mysql_error($db2) . "\n");
}
if (!mysql_select_db($db_new['db'], $db2)) {
die("Error: " . mysql_error($db2) . "\n");
}
// import actions
echo "Importing actions...\n";
$action_mapping = array();
// read actions from old piwik
$query = "SELECT idaction, " . implode(', ', $log_action_fields) . " FROM " . $db_old['prefix'] . "log_action";
$res = mysql_query($query, $db1);
if ($res) {
$actions_counter = 0;
while ($action = mysql_fetch_assoc($res)) {
$action_id_old = $action['idaction'];
unset($action['idaction']);
// if action already exists in new piwik, then learn it's id, otherwise import
$query = "SELECT idaction FROM " . $db_new['prefix'] . "log_action WHERE name='" . mysql_real_escape_string($action['name']) . "' AND hash='" . $action['hash'] . "' AND type='" . $action['type'] . "'";
$res2 = mysql_query($query, $db2);
if ($res2) {
if (mysql_num_rows($res2)) {
$action_new = mysql_fetch_assoc($res2);
$action_id_new = $action_new['idaction'];
}
else {
$query = "INSERT INTO " . $db_new['prefix'] . "log_action (" . implode(', ', $log_action_fields) . ") VALUES (" . array_to_sql_values($action) . ")";
$res2 = mysql_query($query, $db2);
if ($res2) {
$action_id_new = mysql_insert_id($db2);
$actions_counter++;
}
else {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
else {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
$action_mapping[$action_id_old] = $action_id_new;
}
echo "Imported " . $actions_counter . " actions.\n";
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
// walk through sites
foreach ($import_sites as $site_id_old => $site_id_new) {
// read site
$query = "SELECT " . implode(', ', $site_fields) . " FROM " . $db_old['prefix'] . "site WHERE idsite=" . $site_id_old;
$res = mysql_query($query, $db1);
if ($res) {
$site = mysql_fetch_assoc($res);
if ($site) {
// import site if no new id specified
if (!$site_id_new) {
echo "Importing site " . $site_id_old . "...\n";
// Insert while removing the first pk field which will be auto-assigned
$query = "INSERT INTO " . $db_new['prefix'] . "site (" . implode(', ', array_slice($site_fields,1)) . ") VALUES (" . array_to_sql_values(array_slice($site,1)) . ")";
$res = mysql_query($query, $db2);
if ($res) {
$site_id_new = mysql_insert_id($db2);
}
else {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
// import additional site urls
$query = "SELECT " . implode(', ', $site_url_fields) . " FROM " . $db_old['prefix'] . "site_url WHERE idsite=" . $site_id_old;
$res = mysql_query($query, $db1);
if ($res) {
if (mysql_num_rows($res)) {
while ($site_url = mysql_fetch_assoc($res)) {
$site_url['idsite'] = $site_id_new;
$query = "INSERT INTO " . $db_new['prefix'] . "site_url (" . implode(', ', $site_url_fields) . ") VALUES (" . array_to_sql_values($site_url) . ")";
if (!mysql_query($query, $db2)) {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
// import goals
$query = "SELECT " . implode(', ', $goal_fields) . " FROM " . $db_old['prefix'] . "goal WHERE idsite=" . $site_id_old;
$res = mysql_query($query, $db1);
if ($res) {
if (mysql_num_rows($res)) {
while ($goal = mysql_fetch_assoc($res)) {
$goal['idsite'] = $site_id_new;
$query = "INSERT INTO " . $db_new['prefix'] . "goal (" . implode(', ', $goal_fields) . ") VALUES (" . array_to_sql_values($goal) . ")";
if (!mysql_query($query, $db2)) {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
// import reports for site
$query = "SELECT " . implode(', ', $report_fields) . " FROM " . $db_old['prefix'] . "report WHERE idsite=" . $site_id_old;
$res = mysql_query($query, $db1);
if ($res) {
if (mysql_num_rows($res)) {
while ($report = mysql_fetch_assoc($res)) {
$report['idsite'] = $site_id_new;
$query = "INSERT INTO " . $db_new['prefix'] . "report (" . implode(', ', $report_fields) . ") VALUES (" . array_to_sql_values($action) . ")";
if (!mysql_query($query, $db2)) {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
echo "Imported site " . $site_id_old . " with new id " . $site_id_new . "\n";
}
// make sure all visits are shown by setting the site's creation date to the first imported visits date
$query = "SELECT visit_first_action_time FROM " . $db_old['prefix'] . "log_visit WHERE idsite=" . $site_id_old . " ORDER BY visit_first_action_time ASC";
$res = mysql_query($query, $db1);
if ($res) {
if (mysql_num_rows($res)) {
$first_visit = mysql_fetch_assoc($res);
$query = "UPDATE " . $db_new['prefix'] . "site SET `ts_created` = '" . $first_visit['visit_first_action_time'] . "' WHERE idsite=" . $site_id_new;
if (!mysql_query($query, $db2)) {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
echo "Changed creation date of site " . $site_id_old . " to " . $first_visit['visit_first_action_time'] . "\n";
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
// read visits
echo "Importing visits for site " . $site_id_old . "...\n";
$query = "SELECT idvisit, " . implode(', ', $log_visit_fields) . " FROM " . $db_old['prefix'] . "log_visit WHERE idsite=" . $site_id_old . " ORDER BY idvisit";
$res = mysql_query($query, $db1);
if ($res) {
$visit_mapping = array();
$visit_action_mapping = array();
// walk through visits
$visits_counter = 0;
while ($visit = mysql_fetch_assoc($res)) {
$visit_id_old = $visit['idvisit'];
unset($visit['idvisit']);
// update ids
$visit['visit_exit_idaction_url'] = isset($action_mapping[$visit['visit_exit_idaction_url']]) ? $action_mapping[$visit['visit_exit_idaction_url']] : 0;
$visit['visit_exit_idaction_name'] = isset($action_mapping[$visit['visit_exit_idaction_name']]) ? $action_mapping[$visit['visit_exit_idaction_name']] : 0;
$visit['visit_entry_idaction_url'] = isset($action_mapping[$visit['visit_entry_idaction_url']]) ? $action_mapping[$visit['visit_entry_idaction_url']] : 0;
$visit['visit_entry_idaction_name'] = isset($action_mapping[$visit['visit_entry_idaction_name']]) ? $action_mapping[$visit['visit_entry_idaction_name']] : 0;
$query = "INSERT INTO " . $db_new['prefix'] . "log_visit (idsite, " . implode(', ', $log_visit_fields) . ") VALUES (" . $site_id_new . ", " . array_to_sql_values($visit) . ")";
$res2 = mysql_query($query, $db2);
if ($res2) {
$visit_id_new = mysql_insert_id($db2);
$visit_mapping[$visit_id_old] = $visit_id_new;
$visits_counter++;
// import visit_action
$query = "SELECT idlink_va, " . implode(', ', $log_link_visit_action_fields) . " FROM " . $db_old['prefix'] . "log_link_visit_action WHERE idvisit=" . $visit_id_old;
$res2 = mysql_query($query, $db1);
if ($res2) {
if (mysql_num_rows($res2)) {
while ($visit_action = mysql_fetch_assoc($res2)) {
$visit_action_id_old = $visit_action['idlink_va'];
unset($visit_action['idlink_va']);
$visit_action['idsite'] = $site_id_new;
$visit_action['idvisit'] = $visit_id_new;
$visit_action['idaction_url'] = isset($action_mapping[$visit_action['idaction_url']]) ? $action_mapping[$visit_action['idaction_url']] : 0;
$visit_action['idaction_url_ref'] = isset($action_mapping[$visit_action['idaction_url_ref']]) ? $action_mapping[$visit_action['idaction_url_ref']] : 0;
$visit_action['idaction_name'] = isset($action_mapping[$visit_action['idaction_name']]) ? $action_mapping[$visit_action['idaction_name']] : 0;
$visit_action['idaction_name_ref'] = isset($action_mapping[$visit_action['idaction_name_ref']]) ? $action_mapping[$visit_action['idaction_name_ref']] : 0;
$query = "INSERT INTO " . $db_new['prefix'] . "log_link_visit_action (" . implode(', ', $log_link_visit_action_fields) . ") VALUES (" . array_to_sql_values($visit_action) . ")";
$res3 = mysql_query($query, $db2);
if ($res3) {
$visit_action_mapping[$visit_action_id_old] = mysql_insert_id($db2);
}
else {
echo("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
// import conversions
$query = "SELECT " . implode(', ', $log_conversion_fields) . " FROM " . $db_old['prefix'] . "log_conversion WHERE idvisit=" . $visit_id_old;
$res2 = mysql_query($query, $db1);
if ($res2) {
if (mysql_num_rows($res2)) {
while ($conversion = mysql_fetch_assoc($res2)) {
$conversion['idvisit'] = $visit_id_new;
$conversion['idsite'] = $site_id_new;
$conversion['idaction_url'] = isset($action_mapping[$conversion['idaction_url']]) ? $action_mapping[$conversion['idaction_url']] : 0;
$conversion['idlink_va'] = isset($visit_action_mapping[$conversion['idlink_va']]) ? $visit_action_mapping[$conversion['idlink_va']] : 0;
$query = "INSERT INTO " . $db_new['prefix'] . "log_conversion (" . implode(', ', $log_conversion_fields) . ") VALUES (" . array_to_sql_values($conversion) . ")";
$query = str_replace("''", "NULL", $query);
if (!mysql_query($query, $db2)) {
echo("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
// import conversion items
$query = "SELECT " . implode(', ', $log_conversion_item_fields) . " FROM " . $db_old['prefix'] . "log_conversion_item WHERE idvisit=" . $visit_id_old;
$res2 = mysql_query($query, $db1);
if ($res2) {
if (mysql_num_rows($res2)) {
while ($conversion_item = mysql_fetch_assoc($res2)) {
$conversion_item['idvisit'] = $visit_id_new;
$conversion_item['idsite'] = $site_id_new;
$conversion_item['idaction_sku'] = isset($action_mapping[$conversion_item['idaction_sku']]) ? $action_mapping[$conversion_item['idaction_sku']] : 0;
$conversion_item['idaction_name'] = isset($action_mapping[$conversion_item['idaction_name']]) ? $action_mapping[$conversion_item['idaction_name']] : 0;
$conversion_item['idaction_category'] = isset($action_mapping[$conversion_item['idaction_category']]) ? $action_mapping[$conversion_item['idaction_category']] : 0;
$conversion_item['idaction_category2'] = isset($action_mapping[$conversion_item['idaction_category2']]) ? $action_mapping[$conversion_item['idaction_category2']] : 0;
$conversion_item['idaction_category3'] = isset($action_mapping[$conversion_item['idaction_category3']]) ? $action_mapping[$conversion_item['idaction_category3']] : 0;
$conversion_item['idaction_category4'] = isset($action_mapping[$conversion_item['idaction_category4']]) ? $action_mapping[$conversion_item['idaction_category4']] : 0;
$conversion_item['idaction_category5'] = isset($action_mapping[$conversion_item['idaction_category5']]) ? $action_mapping[$conversion_item['idaction_category5']] : 0;
$query = "INSERT INTO " . $db_new['prefix'] . "log_conversion_item (" . implode(', ', $log_conversion_item_fields) . ") VALUES (" . array_to_sql_values($conversion_item) . ")";
if (!mysql_query($query, $db2)) {
echo("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
}
else {
die("Error: " . mysql_error($db2) . "\nQuery: " . $query . "\n");
}
}
if ($visits_counter > 0) {
echo "Imported " . $visits_counter . " visits from site " . $site_id_old . "\n";
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
}
else {
echo "Site " . $site_id_old . " not found in old database. Skipping...\n";
}
}
else {
die("Error: " . mysql_error($db1) . "\nQuery: " . $query . "\n");
}
}
Ce script a fonctionné chez moi sur Piwik 2.7.
Pour l’utiliser c’est assez simple, il vous suffit de modifier les informations concernant vos deux serveur en haut du script, en renseignant l’adresse de chaque base de données mySql, les comptes permettant d’y accéder. Vous pouvez retrouver les données dans le fichier config/config.ini.php de vos installations de Piwik :
$db_old = array( 'server' => 'localhost', 'user' => 'root', 'pass' => 'password', 'db' => 'piwik', 'prefix' => 'piwik_', ); $db_new = array( 'server' => 'localhost', 'user' => 'root', 'pass' => 'password', 'db' => 'piwik', 'prefix' => 'piwik_', );
Il vous faut ensuite choisir si vous souhaitez importer les données dans un nouveau site ou si vous souhaitez les fusionner :
$import_sites = array ( 1 => 2, // Les données du site ayant pour ID 1 dans l'ancienne installation Piwik seront importées dans le site ayant l'ID 2 de la nouvelle instance de Piwik 2 => 0 // Les données du site ayant pour ID 2 dans l'ancienne installation Piwik seront importées en tant que nouveau site dans la nouvelle instance de Piwik );
Il vous suffit ensuite de lancer le script, si possible en ligne de commande, car le temps de traitement peut être un peu long si vous avez des statistiques un peu lourdes à traiter.
Dans mon cas, tout c’est très bien passé, entre deux instances de Piwik en version 2.7.