<?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");
}
}