/** * Daily archive: processes all Referers reports, eg. Visits by Keyword, * Visits by websites, etc. * * @param Piwik_ArchiveProcessing $archiveProcessing * @throws Exception * @return void */ protected function archiveDayAggregateVisits(Piwik_ArchiveProcessing_Day $archiveProcessing) { $dimension = array("referer_type", "referer_name", "referer_keyword", "referer_url"); $query = $archiveProcessing->queryVisitsByDimension($dimension); $this->interestBySearchEngine = $this->interestByKeyword = $this->interestBySearchEngineAndKeyword = $this->interestByKeywordAndSearchEngine = $this->interestByWebsite = $this->interestByWebsiteAndUrl = $this->interestByCampaignAndKeyword = $this->interestByCampaign = $this->interestByType = $this->distinctUrls = array(); while ($row = $query->fetch()) { if (empty($row['referer_type'])) { $row['referer_type'] = Piwik_Common::REFERER_TYPE_DIRECT_ENTRY; } else { switch ($row['referer_type']) { case Piwik_Common::REFERER_TYPE_SEARCH_ENGINE: if (empty($row['referer_keyword'])) { $row['referer_keyword'] = self::LABEL_KEYWORD_NOT_DEFINED; } if (!isset($this->interestBySearchEngine[$row['referer_name']])) { $this->interestBySearchEngine[$row['referer_name']] = $archiveProcessing->getNewInterestRow(); } if (!isset($this->interestByKeyword[$row['referer_keyword']])) { $this->interestByKeyword[$row['referer_keyword']] = $archiveProcessing->getNewInterestRow(); } if (!isset($this->interestBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']])) { $this->interestBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']] = $archiveProcessing->getNewInterestRow(); } if (!isset($this->interestByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']])) { $this->interestByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $this->interestBySearchEngine[$row['referer_name']]); $archiveProcessing->updateInterestStats($row, $this->interestByKeyword[$row['referer_keyword']]); $archiveProcessing->updateInterestStats($row, $this->interestBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']]); $archiveProcessing->updateInterestStats($row, $this->interestByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']]); break; case Piwik_Common::REFERER_TYPE_WEBSITE: if (!isset($this->interestByWebsite[$row['referer_name']])) { $this->interestByWebsite[$row['referer_name']] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $this->interestByWebsite[$row['referer_name']]); if (!isset($this->interestByWebsiteAndUrl[$row['referer_name']][$row['referer_url']])) { $this->interestByWebsiteAndUrl[$row['referer_name']][$row['referer_url']] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $this->interestByWebsiteAndUrl[$row['referer_name']][$row['referer_url']]); if (!isset($this->distinctUrls[$row['referer_url']])) { $this->distinctUrls[$row['referer_url']] = true; } break; case Piwik_Common::REFERER_TYPE_CAMPAIGN: if (!empty($row['referer_keyword'])) { if (!isset($this->interestByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']])) { $this->interestByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $this->interestByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']]); } if (!isset($this->interestByCampaign[$row['referer_name']])) { $this->interestByCampaign[$row['referer_name']] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $this->interestByCampaign[$row['referer_name']]); break; case Piwik_Common::REFERER_TYPE_DIRECT_ENTRY: // direct entry are aggregated below in $this->interestByType array break; default: throw new Exception("Non expected referer_type = " . $row['referer_type']); break; } } if (!isset($this->interestByType[$row['referer_type']])) { $this->interestByType[$row['referer_type']] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $this->interestByType[$row['referer_type']]); } }
/** * @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 * @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 */ protected function archiveDayAggregateVisits($archiveProcessing) { $dimensions = array_keys($this->interestTables); $query = $archiveProcessing->queryVisitsByDimension($dimensions, $where = '', $metrics = false, $orderBy = false, $rankingQuery = null, $addSelect = 'MAX(log_visit.location_latitude) as location_latitude, MAX(log_visit.location_longitude) as location_longitude'); if ($query === false) { return; } $emptyInterestColumns = $archiveProcessing->getNewInterestRow(); while ($row = $query->fetch()) { // get latitude/longitude if there's a city $lat = $long = false; if (!empty($row['location_city'])) { if (!empty($row['location_latitude'])) { $lat = $row['location_latitude']; } if (!empty($row['location_longitude'])) { $long = $row['location_longitude']; } } // make sure regions & cities w/ the same name don't get merged $this->setLongCityRegionId($row); // store latitude/longitude, if we should if ($lat !== false && $long !== false) { $this->latLongForCities[$row['location_city']] = array($lat, $long); } // add the stats to each dimension's table foreach ($this->interestTables as $dimension => &$table) { $label = (string) $row[$dimension]; if (!isset($table[$label])) { $table[$label] = $archiveProcessing->getNewInterestRow(); } $archiveProcessing->updateInterestStats($row, $table[$label]); } } }