You can use the methods in this class within {@link Piwik\Plugin\Archiver Archiver} descendants
to aggregate log data without having to write SQL queries.
### Aggregation Dimension
All aggregation methods accept a **dimension** parameter. These parameters are important as
they control how rows in a table are aggregated together.
A **_dimension_** is just a table column. Rows that have the same values for these columns are
aggregated together. The result of these aggregations is a set of metrics for every recorded value
of a **dimension**.
_Note: A dimension is essentially the same as a **GROUP BY** field._
### Examples
**Aggregating visit data**
$archiveProcessor = // ...
$logAggregator = $archiveProcessor->getLogAggregator();
get metrics for every used browser language of all visits by returning visitors
$query = $logAggregator->queryVisitsByDimension(
$dimensions = array('log_visit.location_browser_lang'),
$where = 'log_visit.visitor_returning = 1',
also count visits for each browser language that are not located in the US
$additionalSelects = array('sum(case when log_visit.location_country <> 'us' then 1 else 0 end) as nonus'),
we're only interested in visits, unique visitors & actions, so don't waste time calculating anything else
$metrics = array(Metrics::INDEX_NB_UNIQ_VISITORS, Metrics::INDEX_NB_VISITS, Metrics::INDEX_NB_ACTIONS),
);
if ($query === false) {
return;
}
while ($row = $query->fetch()) {
$uniqueVisitors = $row[Metrics::INDEX_NB_UNIQ_VISITORS];
$visits = $row[Metrics::INDEX_NB_VISITS];
$actions = $row[Metrics::INDEX_NB_ACTIONS];
... do something w/ calculated metrics ...
}
**Aggregating conversion data**
$archiveProcessor = // ...
$logAggregator = $archiveProcessor->getLogAggregator();
get metrics for ecommerce conversions for each country
$query = $logAggregator->queryConversionsByDimension(
$dimensions = array('log_conversion.location_country'),
$where = 'log_conversion.idgoal = 0', // 0 is the special ecommerceOrder idGoal value in the table
also calculate average tax and max shipping per country
$additionalSelects = array(
'AVG(log_conversion.revenue_tax) as avg_tax',
'MAX(log_conversion.revenue_shipping) as max_shipping'
)
);
if ($query === false) {
return;
}
while ($row = $query->fetch()) {
$country = $row['location_country'];
$numEcommerceSales = $row[Metrics::INDEX_GOAL_NB_CONVERSIONS];
$numVisitsWithEcommerceSales = $row[Metrics::INDEX_GOAL_NB_VISITS_CONVERTED];
$avgTaxForCountry = $row['avg_tax'];
$maxShippingForCountry = $row['max_shipping'];
... do something with aggregated data ...
}
public function test_generateQuery_WithQueryHint_ShouldAddQueryHintAsComment() { $this->logAggregator->setQueryOriginHint('MyPluginName'); $query = $this->logAggregator->generateQuery('test, test2', 'log_visit', '1=1', false, '5'); $expected = array('sql' => 'SELECT /* MyPluginName */ test, test2 FROM log_visit AS log_visit WHERE 1=1 ORDER BY 5', 'bind' => array(0 => '2012-01-01 00:00:00', 1 => '2012-01-31 23:59:59', 2 => 1)); $this->assertSame($expected, $query); }
/** * @param $idVisit * @param $idOrder * @param $actionsLimit * @return array * @throws \Exception */ public function queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit) { $sql = "SELECT\n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t"; $bind = array($idVisit, $idOrder); $itemsDetails = Db::fetchAll($sql, $bind); return $itemsDetails; }
/** * Instantiates the Archiver class in each plugin that defines it, * and triggers Aggregation processing on these plugins. */ public function callAggregateAllPlugins($visits, $visitsConverted) { Log::debug("PluginsArchiver::%s: Initializing archiving process for all plugins [visits = %s, visits converted = %s]", __FUNCTION__, $visits, $visitsConverted); $this->archiveProcessor->setNumberOfVisits($visits, $visitsConverted); $archivers = $this->getPluginArchivers(); foreach ($archivers as $pluginName => $archiverClass) { // We clean up below all tables created during this function call (and recursive calls) $latestUsedTableId = Manager::getInstance()->getMostRecentTableId(); /** @var Archiver $archiver */ $archiver = new $archiverClass($this->archiveProcessor); if (!$archiver->isEnabled()) { Log::debug("PluginsArchiver::%s: Skipping archiving for plugin '%s'.", __FUNCTION__, $pluginName); continue; } if ($this->shouldProcessReportsForPlugin($pluginName)) { $this->logAggregator->setQueryOriginHint($pluginName); $timer = new Timer(); if ($this->isSingleSiteDayArchive) { Log::debug("PluginsArchiver::%s: Archiving day reports for plugin '%s'.", __FUNCTION__, $pluginName); $archiver->aggregateDayReport(); } else { Log::debug("PluginsArchiver::%s: Archiving period reports for plugin '%s'.", __FUNCTION__, $pluginName); $archiver->aggregateMultipleReports(); } $this->logAggregator->setQueryOriginHint(''); Log::debug("PluginsArchiver::%s: %s while archiving %s reports for plugin '%s'.", __FUNCTION__, $timer->getMemoryLeak(), $this->params->getPeriod()->getLabel(), $pluginName); } else { Log::debug("PluginsArchiver::%s: Not archiving reports for plugin '%s'.", __FUNCTION__, $pluginName); } Manager::getInstance()->deleteAll($latestUsedTableId); unset($archiver); } }
protected function aggregateByPlugin() { $selects = array("sum(case log_visit.config_pdf when 1 then 1 else 0 end) as pdf", "sum(case log_visit.config_flash when 1 then 1 else 0 end) as flash", "sum(case log_visit.config_java when 1 then 1 else 0 end) as java", "sum(case log_visit.config_director when 1 then 1 else 0 end) as director", "sum(case log_visit.config_quicktime when 1 then 1 else 0 end) as quicktime", "sum(case log_visit.config_realplayer when 1 then 1 else 0 end) as realplayer", "sum(case log_visit.config_windowsmedia when 1 then 1 else 0 end) as windowsmedia", "sum(case log_visit.config_gears when 1 then 1 else 0 end) as gears", "sum(case log_visit.config_silverlight when 1 then 1 else 0 end) as silverlight", "sum(case log_visit.config_cookie when 1 then 1 else 0 end) as cookie"); $query = $this->getLogAggregator()->queryVisitsByDimension(array(), false, $selects, $metrics = array()); $data = $query->fetch(); $cleanRow = LogAggregator::makeArrayOneColumn($data, Metrics::INDEX_NB_VISITS); $table = DataTable::makeFromIndexedArray($cleanRow); $this->insertTable(self::PLUGIN_RECORD_NAME, $table); }
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); }
public function aggregateDayReport() { // these prefixes are prepended to the 'SELECT as' parts of each SELECT expression. detecting // these prefixes allows us to get all the data in one query. $prefixes = array(self::TIME_SPENT_RECORD_NAME => 'tg', self::PAGES_VIEWED_RECORD_NAME => 'pg', self::VISITS_COUNT_RECORD_NAME => 'vbvn', self::DAYS_SINCE_LAST_RECORD_NAME => 'dslv'); // collect our extra aggregate select fields $selects = array(); $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visit_total_time', self::getSecondsGap(), 'log_visit', $prefixes[self::TIME_SPENT_RECORD_NAME])); $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visit_total_actions', self::$pageGap, 'log_visit', $prefixes[self::PAGES_VIEWED_RECORD_NAME])); $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visitor_count_visits', self::$visitNumberGap, 'log_visit', $prefixes[self::VISITS_COUNT_RECORD_NAME])); $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visitor_days_since_last', self::$daysSinceLastVisitGap, 'log_visit', $prefixes[self::DAYS_SINCE_LAST_RECORD_NAME], $restrictToReturningVisitors = true)); $query = $this->getLogAggregator()->queryVisitsByDimension(array(), $where = false, $selects, array()); $row = $query->fetch(); foreach ($prefixes as $recordName => $selectAsPrefix) { $cleanRow = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_VISITS, $selectAsPrefix); $dataTable = DataTable::makeFromIndexedArray($cleanRow); $this->getProcessor()->insertBlobRecord($recordName, $dataTable->getSerialized()); } }
/** * @param $visitorDetailsArray * @param $actionsLimit * @param $timezone * @return array */ public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone) { $idVisit = $visitorDetailsArray['idVisit']; $maxCustomVariables = CustomVariables::getMaxCustomVariables(); $sqlCustomVariables = ''; for ($i = 1; $i <= $maxCustomVariables; $i++) { $sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i; } // The second join is a LEFT join to allow returning records that don't have a matching page title // eg. Downloads, Outlinks. For these, idaction_name is set to 0 $sql = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOALESCE(log_action_event_category.type, log_action.type, log_action_title.type) AS type,\n\t\t\t\t\tlog_action.name AS url,\n\t\t\t\t\tlog_action.url_prefix,\n\t\t\t\t\tlog_action_title.name AS pageTitle,\n\t\t\t\t\tlog_action.idaction AS pageIdAction,\n\t\t\t\t\tlog_link_visit_action.server_time as serverTimePretty,\n\t\t\t\t\tlog_link_visit_action.time_spent_ref_action as timeSpentRef,\n\t\t\t\t\tlog_link_visit_action.idlink_va AS pageId,\n\t\t\t\t\tlog_link_visit_action.custom_float\n\t\t\t\t\t" . $sqlCustomVariables . ",\n\t\t\t\t\tlog_action_event_category.name AS eventCategory,\n\t\t\t\t\tlog_action_event_action.name as eventAction\n\t\t\t\tFROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action\n\t\t\t\t\tON log_link_visit_action.idaction_url = log_action.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_title\n\t\t\t\t\tON log_link_visit_action.idaction_name = log_action_title.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category\n\t\t\t\t\tON log_link_visit_action.idaction_event_category = log_action_event_category.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action\n\t\t\t\t\tON log_link_visit_action.idaction_event_action = log_action_event_action.idaction\n\t\t\t\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\tORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t "; $actionDetails = Db::fetchAll($sql, array($idVisit)); foreach ($actionDetails as $actionIdx => &$actionDetail) { $actionDetail =& $actionDetails[$actionIdx]; $customVariablesPage = array(); for ($i = 1; $i <= $maxCustomVariables; $i++) { if (!empty($actionDetail['custom_var_k' . $i])) { $cvarKey = $actionDetail['custom_var_k' . $i]; $cvarKey = static::getCustomVariablePrettyKey($cvarKey); $customVariablesPage[$i] = array('customVariablePageName' . $i => $cvarKey, 'customVariablePageValue' . $i => $actionDetail['custom_var_v' . $i]); } unset($actionDetail['custom_var_k' . $i]); unset($actionDetail['custom_var_v' . $i]); } if (!empty($customVariablesPage)) { $actionDetail['customVariables'] = $customVariablesPage; } if ($actionDetail['type'] == Action::TYPE_CONTENT) { unset($actionDetails[$actionIdx]); continue; } elseif ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) { // Handle Event if (strlen($actionDetail['pageTitle']) > 0) { $actionDetail['eventName'] = $actionDetail['pageTitle']; } unset($actionDetail['pageTitle']); } else { if ($actionDetail['type'] == Action::TYPE_SITE_SEARCH) { // Handle Site Search $actionDetail['siteSearchKeyword'] = $actionDetail['pageTitle']; unset($actionDetail['pageTitle']); } } // Event value / Generation time if ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) { if (strlen($actionDetail['custom_float']) > 0) { $actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION); } } elseif ($actionDetail['custom_float'] > 0) { $actionDetail['generationTime'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000); } unset($actionDetail['custom_float']); if ($actionDetail['type'] != Action::TYPE_EVENT_CATEGORY) { unset($actionDetail['eventCategory']); unset($actionDetail['eventAction']); } // Reconstruct url from prefix $actionDetail['url'] = Tracker\PageUrl::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']); unset($actionDetail['url_prefix']); // Set the time spent for this action (which is the timeSpentRef of the next action) if (isset($actionDetails[$actionIdx + 1])) { $actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef']; $actionDetail['timeSpentPretty'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['timeSpent']); } unset($actionDetails[$actionIdx]['timeSpentRef']); // not needed after timeSpent is added } // If the visitor converted a goal, we shall select all Goals $sql = "\n\t\t\t\tSELECT\n\t\t\t\t\t\t'goal' as type,\n\t\t\t\t\t\tgoal.name as goalName,\n\t\t\t\t\t\tgoal.idgoal as goalId,\n\t\t\t\t\t\tgoal.revenue as revenue,\n\t\t\t\t\t\tlog_conversion.idlink_va as goalPageId,\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty,\n\t\t\t\t\t\tlog_conversion.url as url\n\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . Common::prefixTable('goal') . " AS goal\n\t\t\t\t\tON (goal.idsite = log_conversion.idsite\n\t\t\t\t\t\tAND\n\t\t\t\t\t\tgoal.idgoal = log_conversion.idgoal)\n\t\t\t\t\tAND goal.deleted = 0\n\t\t\t\tWHERE log_conversion.idvisit = ?\n\t\t\t\t\tAND log_conversion.idgoal > 0\n ORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t"; $goalDetails = Db::fetchAll($sql, array($idVisit)); $sql = "SELECT\n\t\t\t\t\t\tcase idgoal when " . GoalManager::IDGOAL_CART . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type,\n\t\t\t\t\t\tidorder as orderId,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue') . " as revenue,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount,\n\t\t\t\t\t\titems as items,\n\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty\n\t\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\tAND idgoal <= " . GoalManager::IDGOAL_ORDER . "\n\t\t\t\t\tORDER BY server_time ASC\n\t\t\t\t\tLIMIT 0, {$actionsLimit}"; $ecommerceDetails = Db::fetchAll($sql, array($idVisit)); foreach ($ecommerceDetails as &$ecommerceDetail) { if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) { unset($ecommerceDetail['orderId']); unset($ecommerceDetail['revenueSubTotal']); unset($ecommerceDetail['revenueTax']); unset($ecommerceDetail['revenueShipping']); unset($ecommerceDetail['revenueDiscount']); } // 25.00 => 25 foreach ($ecommerceDetail as $column => $value) { if (strpos($column, 'revenue') !== false) { if ($value == round($value)) { $ecommerceDetail[$column] = round($value); } } } } // Enrich ecommerce carts/orders with the list of products usort($ecommerceDetails, array('static', 'sortByServerTime')); foreach ($ecommerceDetails as &$ecommerceConversion) { $sql = "SELECT\n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t"; $bind = array($idVisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART); $itemsDetails = Db::fetchAll($sql, $bind); foreach ($itemsDetails as &$detail) { if ($detail['price'] == round($detail['price'])) { $detail['price'] = round($detail['price']); } } $ecommerceConversion['itemDetails'] = $itemsDetails; } $actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails); usort($actions, array('static', 'sortByServerTime')); $visitorDetailsArray['actionDetails'] = $actions; foreach ($visitorDetailsArray['actionDetails'] as &$details) { switch ($details['type']) { case 'goal': $details['icon'] = 'plugins/Morpheus/images/goal.png'; break; case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER: case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART: $details['icon'] = 'plugins/Morpheus/images/' . $details['type'] . '.gif'; break; case Action::TYPE_DOWNLOAD: $details['type'] = 'download'; $details['icon'] = 'plugins/Morpheus/images/download.png'; break; case Action::TYPE_OUTLINK: $details['type'] = 'outlink'; $details['icon'] = 'plugins/Morpheus/images/link.gif'; break; case Action::TYPE_SITE_SEARCH: $details['type'] = 'search'; $details['icon'] = 'plugins/Morpheus/images/search_ico.png'; break; case Action::TYPE_EVENT_CATEGORY: $details['type'] = 'event'; $details['icon'] = 'plugins/Morpheus/images/event.png'; break; default: $details['type'] = 'action'; $details['icon'] = null; break; } // Convert datetimes to the site timezone $dateTimeVisit = Date::factory($details['serverTimePretty'], $timezone); $details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik::translate('CoreHome_ShortDateFormat') . ' %time%'); } $visitorDetailsArray['goalConversions'] = count($goalDetails); return $visitorDetailsArray; }
/** * Get information about internal referrers (previous pages & loops, i.e. page refreshes) * * @param $idaction * @param $actionType * @param LogAggregator $logAggregator * @param $limitBeforeGrouping * @return array(previousPages:DataTable, loops:integer) */ protected function queryInternalReferrers($idaction, $actionType, $logAggregator, $limitBeforeGrouping = false) { $keyIsOther = 0; $keyIsPageUrlAction = 1; $keyIsSiteSearchAction = 2; $rankingQuery = new RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); $rankingQuery->addLabelColumn(array('name', 'url_prefix')); $rankingQuery->setColumnToMarkExcludedRows('is_self'); $rankingQuery->partitionResultIntoMultipleGroups('action_partition', array($keyIsOther, $keyIsPageUrlAction, $keyIsSiteSearchAction)); $type = $this->getColumnTypeSuffix($actionType); $mainActionType = Action::TYPE_PAGE_URL; $dimension = 'idaction_url_ref'; if ($actionType == 'title') { $mainActionType = Action::TYPE_PAGE_TITLE; $dimension = 'idaction_name_ref'; } $selects = array('log_action.name', 'log_action.url_prefix', 'CASE WHEN log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction) . ' THEN 1 ELSE 0 END AS `is_self`', 'CASE WHEN log_action.type = ' . $mainActionType . ' THEN ' . $keyIsPageUrlAction . ' WHEN log_action.type = ' . Action::TYPE_SITE_SEARCH . ' THEN ' . $keyIsSiteSearchAction . ' ELSE ' . $keyIsOther . ' END AS `action_partition`'); $where = ' log_link_visit_action.idaction_' . $type . ' = ' . intval($idaction); if ($dimension == 'idaction_url_ref') { // site search referrers are logged with url_ref=NULL // when we find one, we have to join on name_ref $dimension = 'IF( idaction_url_ref IS NULL, idaction_name_ref, idaction_url_ref )'; $joinLogActionOn = $dimension; } else { $joinLogActionOn = $dimension; } $metrics = array(Metrics::INDEX_NB_ACTIONS); $data = $logAggregator->queryActionsByDimension(array($dimension), $where, $selects, $metrics, $rankingQuery, $joinLogActionOn); $loops = 0; $nbPageviews = 0; $previousPagesDataTable = new DataTable(); if (isset($data['result'][$keyIsPageUrlAction])) { foreach ($data['result'][$keyIsPageUrlAction] as &$page) { $nbActions = intval($page[Metrics::INDEX_NB_ACTIONS]); $previousPagesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $this->getPageLabel($page, Action::TYPE_PAGE_URL), Metrics::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } $previousSearchesDataTable = new DataTable(); if (isset($data['result'][$keyIsSiteSearchAction])) { foreach ($data['result'][$keyIsSiteSearchAction] as &$search) { $nbActions = intval($search[Metrics::INDEX_NB_ACTIONS]); $previousSearchesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $search['name'], Metrics::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } if (isset($data['result'][0])) { foreach ($data['result'][0] as &$referrer) { $nbPageviews += intval($referrer[Metrics::INDEX_NB_ACTIONS]); } } if (count($data['excludedFromLimit'])) { $loops += intval($data['excludedFromLimit'][0][Metrics::INDEX_NB_ACTIONS]); $nbPageviews += $loops; } return array('pageviews' => $nbPageviews, 'previousPages' => $previousPagesDataTable, 'previousSiteSearches' => $previousSearchesDataTable, 'loops' => $loops); }
protected function getSelectAveragePrice() { $field = "custom_var_v" . \PiwikTracker::CVAR_INDEX_ECOMMERCE_ITEM_PRICE; return LogAggregator::getSqlRevenue("AVG(log_link_visit_action." . $field . ")") . " as `" . Metrics::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED . "`"; }