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