/** * Insert aggregated daily data serialized * * @throws \Exception */ protected function insertDayReports() { /** @var DataTable $dataTable */ $dataTable = $this->arrays->asDataTable(); $this->setVisitorIds($dataTable); $report = $dataTable->getSerialized($this->maximumRowsInDataTableLevelZero, null, Metrics::INDEX_NB_VISITS); $this->getProcessor()->insertBlobRecord(self::USERID_ARCHIVE_RECORD, $report); }
private function ensureAllHoursAreSet(DataArray &$array) { $data = $array->getDataArray(); for ($i = 0; $i <= 23; $i++) { if (empty($data[$i])) { $array->sumMetricsVisits($i, DataArray::makeEmptyRow()); } } }
protected function aggregateGeneralGoalMetrics() { $prefixes = array(self::VISITS_UNTIL_RECORD_NAME => 'vcv', self::DAYS_UNTIL_CONV_RECORD_NAME => 'vdsf'); $selects = array(); $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::VISITS_COUNT_FIELD, self::$visitCountRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::VISITS_UNTIL_RECORD_NAME])); $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::DAYS_SINCE_FIRST_VISIT_FIELD, self::$daysToConvRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME])); $query = $this->getLogAggregator()->queryConversionsByDimension(array(), false, $selects); if ($query === false) { return; } $totalConversions = $totalRevenue = 0; $goals = new DataArray(); $visitsToConversions = $daysToConversions = array(); $conversionMetrics = $this->getLogAggregator()->getConversionsMetricFields(); while ($row = $query->fetch()) { $idGoal = $row['idgoal']; unset($row['idgoal']); unset($row['label']); $values = array(); foreach ($conversionMetrics as $field => $statement) { $values[$field] = $row[$field]; } $goals->sumMetrics($idGoal, $values); if (empty($visitsToConversions[$idGoal])) { $visitsToConversions[$idGoal] = new DataTable(); } $array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::VISITS_UNTIL_RECORD_NAME]); $visitsToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array)); if (empty($daysToConversions[$idGoal])) { $daysToConversions[$idGoal] = new DataTable(); } $array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]); $daysToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array)); // We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits // since it is a "negative conversion" if ($idGoal != GoalManager::IDGOAL_CART) { $totalConversions += $row[Metrics::INDEX_GOAL_NB_CONVERSIONS]; $totalRevenue += $row[Metrics::INDEX_GOAL_REVENUE]; } } // Stats by goal, for all visitors $numericRecords = $this->getConversionsNumericMetrics($goals); $this->getProcessor()->insertNumericRecords($numericRecords); $this->insertReports(self::VISITS_UNTIL_RECORD_NAME, $visitsToConversions); $this->insertReports(self::DAYS_UNTIL_CONV_RECORD_NAME, $daysToConversions); // Stats for all goals $nbConvertedVisits = $this->getProcessor()->getNumberOfVisitsConverted(); $metrics = array(self::getRecordName('conversion_rate') => $this->getConversionRate($nbConvertedVisits), self::getRecordName('nb_conversions') => $totalConversions, self::getRecordName('nb_visits_converted') => $nbConvertedVisits, self::getRecordName('revenue') => $totalRevenue); $this->getProcessor()->insertNumericRecords($metrics); }
private function aggregateUserInformation() { $logAggregator = $this->getLogAggregator(); $query = $logAggregator->queryVisitsByDimension($dimensions = array('log_visit.user_id'), $where = '', $additionalSelects = array('log_visit.first_name as "' . ClientCertificates::DATA_FIRST_NAME . '"', 'log_visit.last_name as "' . ClientCertificates::DATA_LAST_NAME . '"', 'log_visit.agency as "' . ClientCertificates::DATA_AGENCY . '"')); $dataRows = array(); while ($row = $query->fetch()) { array_push($dataRows, $row); } $dataArray = new DataArray($dataRows); $dataTable = $dataArray->asDataTable(); foreach ($dataTable->getRows() as $row) { $label = $row->getColumn('log_visit.user_id'); $row->setColumn('label', $label); } $archiveProcessor = $this->getProcessor(); $archiveProcessor->insertBlobRecord('ClientCertificates_GetUserInformation', $dataTable->getSerialized(500)); }
protected function removeVisitsMetricsFromActionsAggregate() { $dataArray =& $this->dataArray->getDataArray(); foreach ($dataArray as $key => &$row) { if (!self::isReservedKey($key) && DataArray::isRowActions($row)) { unset($row[Metrics::INDEX_NB_UNIQ_VISITORS]); unset($row[Metrics::INDEX_NB_VISITS]); } } }
protected function aggregateByLanguage() { /** @var RegionDataProvider $regionDataProvider */ $regionDataProvider = StaticContainer::get('Piwik\\Intl\\Data\\Provider\\RegionDataProvider'); $query = $this->getLogAggregator()->queryVisitsByDimension(array("label" => self::LANGUAGE_DIMENSION)); $countryCodes = $regionDataProvider->getCountryList($includeInternalCodes = true); $metricsByLanguage = new DataArray(); while ($row = $query->fetch()) { $langCode = Common::extractLanguageCodeFromBrowserLanguage($row['label']); $countryCode = Common::extractCountryCodeFromBrowserLanguage($row['label'], $countryCodes, $enableLanguageToCountryGuess = true); if ($countryCode == 'xx' || $countryCode == $langCode) { $metricsByLanguage->sumMetricsVisits($langCode, $row); } else { $metricsByLanguage->sumMetricsVisits($langCode . '-' . $countryCode, $row); } } $report = $metricsByLanguage->asDataTable(); $this->insertTable(self::LANGUAGE_RECORD_NAME, $report); }
/** * Helper function that returns an array with common metrics for a given log_visit field distinct values. * * The statistics returned are: * - number of unique visitors * - number of visits * - number of actions * - maximum number of action for a visit * - sum of the visits' length in sec * - count of bouncing visits (visits with one page view) * * For example if $dimension = 'config_os' it will return the statistics for every distinct Operating systems * The returned array will have a row per distinct operating systems, * and a column per stat (nb of visits, max actions, etc) * * 'label' Metrics::INDEX_NB_UNIQ_VISITORS Metrics::INDEX_NB_VISITS etc. * Linux 27 66 ... * Windows XP 12 ... * Mac OS 15 36 ... * * @param string $dimension Table log_visit field name to be use to compute common stats * @return DataArray */ public function getMetricsFromVisitByDimension($dimension) { if (!is_array($dimension)) { $dimension = array($dimension); } if (count($dimension) == 1) { $dimension = array("label" => reset($dimension)); } $query = $this->queryVisitsByDimension($dimension); $metrics = new DataArray(); while ($row = $query->fetch()) { $metrics->sumMetricsVisits($row["label"], $row); } return $metrics; }
/** * Get information about external referrers (i.e. search engines, websites & campaigns) * * @param $idaction * @param $actionType * @param Logaggregator $logAggregator * @param $limitBeforeGrouping * @return DataTable */ protected function queryExternalReferrers($idaction, $actionType, $logAggregator, $limitBeforeGrouping = false) { $rankingQuery = new RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); // we generate a single column that contains the interesting data for each referrer. // the reason we cannot group by referer_* becomes clear when we look at search engine keywords. // referer_url contains the url from the search engine, referer_keyword the keyword we want to // group by. when we group by both, we don't get a single column for the keyword but instead // one column per keyword + search engine url. this way, we could not get the top keywords using // the ranking query. $dimensions = array('referrer_data', 'referer_type'); $rankingQuery->addLabelColumn('referrer_data'); $selects = array('CASE log_visit.referer_type WHEN ' . Common::REFERRER_TYPE_DIRECT_ENTRY . ' THEN \'\' WHEN ' . Common::REFERRER_TYPE_SEARCH_ENGINE . ' THEN log_visit.referer_keyword WHEN ' . Common::REFERRER_TYPE_WEBSITE . ' THEN log_visit.referer_url WHEN ' . Common::REFERRER_TYPE_CAMPAIGN . ' THEN CONCAT(log_visit.referer_name, \' \', log_visit.referer_keyword) END AS `referrer_data`'); // get one limited group per referrer type $rankingQuery->partitionResultIntoMultipleGroups('referer_type', array(Common::REFERRER_TYPE_DIRECT_ENTRY, Common::REFERRER_TYPE_SEARCH_ENGINE, Common::REFERRER_TYPE_WEBSITE, Common::REFERRER_TYPE_CAMPAIGN)); $type = $this->getColumnTypeSuffix($actionType); $where = 'visit_entry_idaction_' . $type . ' = ' . intval($idaction); $metrics = array(Metrics::INDEX_NB_VISITS); $data = $logAggregator->queryVisitsByDimension($dimensions, $where, $selects, $metrics, $rankingQuery); $referrerData = array(); $referrerSubData = array(); foreach ($data as $referrerType => &$subData) { $referrerData[$referrerType] = array(Metrics::INDEX_NB_VISITS => 0); if ($referrerType != Common::REFERRER_TYPE_DIRECT_ENTRY) { $referrerSubData[$referrerType] = array(); } foreach ($subData as &$row) { if ($referrerType == Common::REFERRER_TYPE_SEARCH_ENGINE && empty($row['referrer_data'])) { $row['referrer_data'] = \Piwik\Plugins\Referrers\API::LABEL_KEYWORD_NOT_DEFINED; } $referrerData[$referrerType][Metrics::INDEX_NB_VISITS] += $row[Metrics::INDEX_NB_VISITS]; $label = $row['referrer_data']; if ($label) { $referrerSubData[$referrerType][$label] = array(Metrics::INDEX_NB_VISITS => $row[Metrics::INDEX_NB_VISITS]); } } } $array = new DataArray($referrerData, $referrerSubData); return $array->asDataTable(); }
protected function aggregateByLanguage() { $query = $this->getLogAggregator()->queryVisitsByDimension(array("label" => self::LANGUAGE_DIMENSION)); $languageCodes = array_keys(Common::getLanguagesList()); $metricsByLanguage = new DataArray(); while ($row = $query->fetch()) { $code = Common::extractLanguageCodeFromBrowserLanguage($row['label'], $languageCodes); $metricsByLanguage->sumMetricsVisits($code, $row); } $report = $metricsByLanguage->asDataTable(); $this->insertTable(self::LANGUAGE_RECORD_NAME, $report); }
protected function aggregateByLanguage() { $query = $this->getLogAggregator()->queryVisitsByDimension(array("label" => self::LANGUAGE_DIMENSION)); $languageCodes = array_keys(Common::getLanguagesList()); $countryCodes = Common::getCountriesList($includeInternalCodes = true); $metricsByLanguage = new DataArray(); while ($row = $query->fetch()) { $langCode = Common::extractLanguageCodeFromBrowserLanguage($row['label'], $languageCodes); $countryCode = Common::extractCountryCodeFromBrowserLanguage($row['label'], $countryCodes, $enableLanguageToCountryGuess = true); if ($countryCode == 'xx' || $countryCode == $langCode) { $metricsByLanguage->sumMetricsVisits($langCode, $row); } else { $metricsByLanguage->sumMetricsVisits($langCode . '-' . $countryCode, $row); } } $report = $metricsByLanguage->asDataTable(); $this->insertTable(self::LANGUAGE_RECORD_NAME, $report); }