public function queryCustomDimensionActions(DataArray $dataArray, $valueField, $additionalWhere = '') { $metricsConfig = ActionsMetrics::getActionMetrics(); $metricIds = array_keys($metricsConfig); $metricIds[] = Metrics::INDEX_PAGE_SUM_TIME_SPENT; $metricIds[] = Metrics::INDEX_BOUNCE_COUNT; $metricIds[] = Metrics::INDEX_PAGE_EXIT_NB_VISITS; $dataArray->setActionMetricsIds($metricIds); $select = "log_link_visit_action.{$valueField},\n log_action.name as url,\n sum(log_link_visit_action.time_spent) as `" . Metrics::INDEX_PAGE_SUM_TIME_SPENT . "`,\n sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `" . Metrics::INDEX_BOUNCE_COUNT . "`,\n sum(IF(log_visit.last_idlink_va = log_link_visit_action.idlink_va, 1, 0)) as `" . Metrics::INDEX_PAGE_EXIT_NB_VISITS . "`"; $select = $this->addMetricsToSelect($select, $metricsConfig); $from = array("log_link_visit_action", array("table" => "log_visit", "joinOn" => "log_visit.idvisit = log_link_visit_action.idvisit"), array("table" => "log_action", "joinOn" => "log_link_visit_action.idaction_url = log_action.idaction")); $where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?"; if (!empty($additionalWhere)) { $where .= ' AND ' . $additionalWhere; } $groupBy = "log_link_visit_action.{$valueField}, url"; $orderBy = "`" . Metrics::INDEX_PAGE_NB_HITS . "` DESC"; // get query with segmentation $logAggregator = $this->getLogAggregator(); $query = $logAggregator->generateQuery($select, $from, $where, $groupBy, $orderBy); $db = $logAggregator->getDb(); $resultSet = $db->query($query['sql'], $query['bind']); return $resultSet; }
protected function archiveDayActions($rankingQueryLimit) { $select = "log_action.name,\n\t\t\t\tlog_action.type,\n\t\t\t\tlog_action.idaction,\n\t\t\t\tlog_action.url_prefix,\n\t\t\t\tcount(distinct log_link_visit_action.idvisit) as " . $this->db->quoteIdentifier(PiwikMetrics::INDEX_NB_VISITS) . ",\n\t\t\t\tcount(distinct log_link_visit_action.idvisitor) as " . $this->db->quoteIdentifier(PiwikMetrics::INDEX_NB_UNIQ_VISITORS) . ",\n\t\t\t\tcount(*) as " . $this->db->quoteIdentifier(PiwikMetrics::INDEX_PAGE_NB_HITS) . ""; $metricsConfig = Metrics::getActionMetrics(); $select = $this->addMetricsToSelect($select, $metricsConfig); $from = array("log_link_visit_action", array("table" => "log_action", "joinOn" => "log_link_visit_action.%s = log_action.idaction")); $where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?\n AND log_link_visit_action.%s IS NOT NULL" . $this->getWhereClauseActionIsNotEvent(); $groupBy = "log_action.idaction"; $orderBy = $this->db->quoteIdentifier(PiwikMetrics::INDEX_PAGE_NB_HITS) . " DESC, name ASC"; $rankingQuery = false; if ($rankingQueryLimit > 0) { $rankingQuery = Factory::getHelper('RankingQuery'); $rankingQuery->setLimit($rankingQueryLimit); $rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW); $rankingQuery->addLabelColumn(array('idaction', 'name')); $rankingQuery->addColumn(array('url_prefix', PiwikMetrics::INDEX_NB_UNIQ_VISITORS)); $rankingQuery->addColumn(array(PiwikMetrics::INDEX_PAGE_NB_HITS, PiwikMetrics::INDEX_NB_VISITS), 'sum'); if ($this->isSiteSearchEnabled()) { $rankingQuery->addColumn(PiwikMetrics::INDEX_SITE_SEARCH_HAS_NO_RESULT, 'min'); $rankingQuery->addColumn(PiwikMetrics::INDEX_PAGE_IS_FOLLOWING_SITE_SEARCH_NB_HITS, 'sum'); } $this->addMetricsToRankingQuery($rankingQuery, $metricsConfig); $rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($this->actionsTablesByType)); } // Special Magic to get // 1) No result Keywords // 2) For each page view, count number of times the referrer page was a Site Search if ($this->isSiteSearchEnabled()) { $this->updateQuerySelectFromForSiteSearch($select, $from); } $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_name", $rankingQuery, $metricsConfig); $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_url", $rankingQuery, $metricsConfig); }