/** * @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); } }