/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
protected function archiveDayAggregateGoals($archiveProcessing)
{
$query = $archiveProcessing->queryConversionsByDimension(array("location_continent", "location_country"));
if ($query === false) {
return;
}
while ($row = $query->fetch()) {
if (!isset($this->interestByCountry[$row['location_country']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCountry[$row['location_country']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
if (!isset($this->interestByContinent[$row['location_continent']][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByContinent[$row['location_continent']][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
$archiveProcessing->updateGoalStats($row, $this->interestByCountry[$row['location_country']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats($row, $this->interestByContinent[$row['location_continent']][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCountry);
$archiveProcessing->enrichConversionsByLabelArray($this->interestByContinent);
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
function archiveGeneralGoalMetrics($archiveProcessing)
{
// extra aggregate selects for the visits to conversion report
$visitToConvExtraCols = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visitor_count_visits', self::$visitCountRanges, 'log_conversion', 'vcv');
// extra aggregate selects for the days to conversion report
$daysToConvExtraCols = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visitor_days_since_first', self::$daysToConvRanges, 'log_conversion', 'vdsf');
$query = $archiveProcessing->queryConversionsByDimension(array(), '', array_merge($visitToConvExtraCols, $daysToConvExtraCols));
if ($query === false) {
return;
}
$goals = array();
$visitsToConvReport = array();
$daysToConvReport = array();
// Get a standard empty goal row
$overall = $archiveProcessing->getNewGoalRow($idGoal = 1);
while ($row = $query->fetch()) {
$idgoal = $row['idgoal'];
if (!isset($goals[$idgoal])) {
$goals[$idgoal] = $archiveProcessing->getNewGoalRow($idgoal);
$visitsToConvReport[$idgoal] = new Piwik_DataTable();
$daysToConvReport[$idgoal] = new Piwik_DataTable();
}
$archiveProcessing->updateGoalStats($row, $goals[$idgoal]);
// We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
// since it is a "negative conversion"
if ($idgoal != Piwik_Tracker_GoalManager::IDGOAL_CART) {
$archiveProcessing->updateGoalStats($row, $overall);
}
// map the goal + visit number of a visitor with the # of conversions that happened on that visit
$table = $archiveProcessing->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_CONVERSIONS, 'vcv');
$visitsToConvReport[$idgoal]->addDataTable($table);
// map the goal + day number of a visit with the # of conversion that happened on that day
$table = $archiveProcessing->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_CONVERSIONS, 'vdsf');
$daysToConvReport[$idgoal]->addDataTable($table);
}
// these data tables hold reports for every goal of a site
$visitsToConvOverview = new Piwik_DataTable();
$daysToConvOverview = new Piwik_DataTable();
// Stats by goal, for all visitors
foreach ($goals as $idgoal => $values) {
foreach ($values as $metricId => $value) {
$metricName = Piwik_Archive::$mappingFromIdToNameGoal[$metricId];
$recordName = self::getRecordName($metricName, $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $value);
}
$conversion_rate = $this->getConversionRate($values[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED], $archiveProcessing);
$recordName = self::getRecordName('conversion_rate', $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $conversion_rate);
// if the goal is not a special goal (like ecommerce) add it to the overview report
if ($idgoal !== Piwik_Tracker_GoalManager::IDGOAL_CART && $idgoal !== Piwik_Tracker_GoalManager::IDGOAL_ORDER) {
$visitsToConvOverview->addDataTable($visitsToConvReport[$idgoal]);
$daysToConvOverview->addDataTable($daysToConvReport[$idgoal]);
}
// visit count until conversion stats
$archiveProcessing->insertBlobRecord(self::getRecordName(self::VISITS_UNTIL_RECORD_NAME, $idgoal), $visitsToConvReport[$idgoal]->getSerialized());
// day count until conversion stats
$archiveProcessing->insertBlobRecord(self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME, $idgoal), $daysToConvReport[$idgoal]->getSerialized());
}
// archive overview reports
$archiveProcessing->insertBlobRecord(self::getRecordName(self::VISITS_UNTIL_RECORD_NAME), $visitsToConvOverview->getSerialized());
$archiveProcessing->insertBlobRecord(self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME), $daysToConvOverview->getSerialized());
// Stats for all goals
$totalAllGoals = array(self::getRecordName('conversion_rate') => $this->getConversionRate($archiveProcessing->getNumberOfVisitsConverted(), $archiveProcessing), self::getRecordName('nb_conversions') => $overall[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS], self::getRecordName('nb_visits_converted') => $archiveProcessing->getNumberOfVisitsConverted(), self::getRecordName('revenue') => $overall[Piwik_Archive::INDEX_GOAL_REVENUE]);
foreach ($totalAllGoals as $recordName => $value) {
$archiveProcessing->insertNumericRecord($recordName, $value);
}
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return void
*/
protected function archiveDayAggregate(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++) {
$keyField = "custom_var_k" . $i;
$valueField = "custom_var_v" . $i;
$dimensions = array($keyField, $valueField);
$where = "%s.{$keyField} != ''";
// Custom Vars names and values metrics for visits
$query = $archiveProcessing->queryVisitsByDimension($dimensions, $where);
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
// Aggregate
if (!isset($this->interestByCustomVariables[$row[$keyField]])) {
$this->interestByCustomVariables[$row[$keyField]] = $archiveProcessing->getNewInterestRow();
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariables[$row[$keyField]]);
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]]);
}
// Custom Vars names and values metrics for page views
$query = $archiveProcessing->queryActionsByDimension($dimensions, $where);
$onlyMetricsAvailableInActionsTable = true;
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
$label = $row[$valueField];
// Remove price tracked if it's zero or we if we are not currently tracking an ecommerce var
if (!in_array($row[$keyField], array('_pks', '_pkn', '_pkc'))) {
unset($row[Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED]);
}
// when custom variable value is a JSON array of categories
// possibly JSON value
$mustInsertCustomVariableValue = true;
if ($row[$keyField] == '_pkc' && $label[0] == '[' && $label[1] == '"') {
// In case categories were truncated, try closing the array
if (substr($label, -2) != '"]') {
$label .= '"]';
}
$decoded = @Piwik_Common::json_decode($label);
if (is_array($decoded)) {
$count = 0;
foreach ($decoded as $category) {
if (empty($category) || $count >= Piwik_Tracker_GoalManager::MAXIMUM_PRODUCT_CATEGORIES) {
continue;
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$category])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$category] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$category], $onlyMetricsAvailableInActionsTable);
$mustInsertCustomVariableValue = false;
$count++;
}
}
}
// end multi categories hack
if ($mustInsertCustomVariableValue) {
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]], $onlyMetricsAvailableInActionsTable);
}
// Do not report on Price viewed for the Custom Variable names
unset($row[Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED]);
// When tracking Custom Variables with scope=page we do not add up visits numbers
// as it is incorrect to sum visits this way
// for scope=visit this is allowed, since there is supposed to be one custom var value per custom variable name for a given visit
$doNotSumVisits = true;
if (!isset($this->interestByCustomVariables[$row[$keyField]])) {
$this->interestByCustomVariables[$row[$keyField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable, $doNotSumVisits);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariables[$row[$keyField]], $onlyMetricsAvailableInActionsTable, $doNotSumVisits);
}
// Custom Vars names and values metrics for Goals
$query = $archiveProcessing->queryConversionsByDimension($dimensions, $where);
if ($query !== false) {
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
if (!isset($this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
$archiveProcessing->updateGoalStats($row, $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
}
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCustomVariables);
$archiveProcessing->enrichConversionsByLabelArrayHasTwoLevels($this->interestByCustomVariablesAndValue);
// var_dump($this->interestByCustomVariables);
//var_dump($this->interestByCustomVariablesAndValue);
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return void
*/
protected function archiveDayAggregate(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
for($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++ )
{
$keyField = "custom_var_k".$i;
$valueField = "custom_var_v".$i;
$dimensions = array($keyField, $valueField);
$where = "%s.$keyField != '' AND %s.$valueField != ''";
// Custom Vars names and values metrics for visits
$query = $archiveProcessing->queryVisitsByDimension($dimensions, $where);
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]])) $this->interestByCustomVariables[$row[$keyField]]= $archiveProcessing->getNewInterestRow();
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow();
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariables[$row[$keyField]]);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]]);
}
// Custom Vars names and values metrics for page views
$query = $archiveProcessing->queryActionsByDimension($dimensions, $where);
$onlyMetricsAvailableInActionsTable = true;
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]])) $this->interestByCustomVariables[$row[$keyField]]= $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariables[$row[$keyField]], $onlyMetricsAvailableInActionsTable);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]], $onlyMetricsAvailableInActionsTable);
}
// Custom Vars names and values metrics for Goals
$query = $archiveProcessing->queryConversionsByDimension($dimensions, $where);
if($query !== false)
{
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
$archiveProcessing->updateGoalStats( $row, $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
}
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCustomVariables);
$archiveProcessing->enrichConversionsByLabelArrayHasTwoLevels($this->interestByCustomVariablesAndValue);
// var_dump($this->interestByCustomVariables);
// var_dump($this->interestByCustomVariablesAndValue);
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
protected function archiveDayAggregateGoals($archiveProcessing)
{
$dimensions = array_keys($this->interestTables);
$query = $archiveProcessing->queryConversionsByDimension($dimensions);
if ($query === false) {
return;
}
while ($row = $query->fetch()) {
// make sure regions & cities w/ the same name don't get merged
$this->setLongCityRegionId($row);
$idGoal = $row['idgoal'];
foreach ($this->interestTables as $dimension => &$table) {
$label = (string) $row[$dimension];
if (!isset($table[$label][Piwik_Archive::INDEX_GOALS][$idGoal])) {
$table[$label][Piwik_Archive::INDEX_GOALS][$idGoal] = $archiveProcessing->getNewGoalRow($idGoal);
}
$archiveProcessing->updateGoalStats($row, $table[$label][Piwik_Archive::INDEX_GOALS][$idGoal]);
}
}
foreach ($this->interestTables as &$table) {
$archiveProcessing->enrichConversionsByLabelArray($table);
}
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
function archiveGeneralGoalMetrics($archiveProcessing)
{
$query = $archiveProcessing->queryConversionsByDimension('');
if($query === false) { return; }
$goals = array();
// Get a standard empty goal row
$overall = $archiveProcessing->getNewGoalRow( $idGoal = 1);
while($row = $query->fetch() )
{
if(!isset($goals[$row['idgoal']])) $goals[$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
$archiveProcessing->updateGoalStats($row, $goals[$row['idgoal']]);
// We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
// since it is a "negative conversion"
if($row['idgoal'] != Piwik_Tracker_GoalManager::IDGOAL_CART)
{
$archiveProcessing->updateGoalStats($row, $overall);
}
}
// Stats by goal, for all visitors
foreach($goals as $idgoal => $values)
{
foreach($values as $metricId => $value)
{
$metricName = Piwik_Archive::$mappingFromIdToNameGoal[$metricId];
$recordName = self::getRecordName($metricName, $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $value);
}
$conversion_rate = $this->getConversionRate($values[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED], $archiveProcessing);
$recordName = self::getRecordName('conversion_rate', $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $conversion_rate);
}
// Stats for all goals
$totalAllGoals = array(
self::getRecordName('conversion_rate') => $this->getConversionRate($archiveProcessing->getNumberOfVisitsConverted(), $archiveProcessing),
self::getRecordName('nb_conversions') => $overall[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS],
self::getRecordName('nb_visits_converted') => $archiveProcessing->getNumberOfVisitsConverted(),
self::getRecordName('revenue') => $overall[Piwik_Archive::INDEX_GOAL_REVENUE],
);
foreach($totalAllGoals as $recordName => $value)
{
$archiveProcessing->insertNumericRecord($recordName, $value);
}
}