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