/** * Get information about external referrers (i.e. search engines, websites & campaigns) * * @param $idaction * @param $actionType * @param Piwik_ArchiveProcessing_Day $archiveProcessing * @param $limitBeforeGrouping * @return Piwik_DataTable */ public function queryExternalReferrers($idaction, $actionType, $archiveProcessing, $limitBeforeGrouping = false) { $rankingQuery = new Piwik_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. $dimension = 'referrer_data'; $rankingQuery->addLabelColumn('referrer_data'); $select = ' CASE referer_type WHEN ' . Piwik_Common::REFERER_TYPE_DIRECT_ENTRY . ' THEN \'\' WHEN ' . Piwik_Common::REFERER_TYPE_SEARCH_ENGINE . ' THEN referer_keyword WHEN ' . Piwik_Common::REFERER_TYPE_WEBSITE . ' THEN referer_url WHEN ' . Piwik_Common::REFERER_TYPE_CAMPAIGN . ' THEN CONCAT(referer_name, \' \', referer_keyword) END AS referrer_data, referer_type'; // get one limited group per referrer type $rankingQuery->partitionResultIntoMultipleGroups('referer_type', array(Piwik_Common::REFERER_TYPE_DIRECT_ENTRY, Piwik_Common::REFERER_TYPE_SEARCH_ENGINE, Piwik_Common::REFERER_TYPE_WEBSITE, Piwik_Common::REFERER_TYPE_CAMPAIGN)); $orderBy = '`' . Piwik_Archive::INDEX_NB_VISITS . '` DESC'; $type = $this->getColumnTypeSuffix($actionType); $where = 'visit_entry_idaction_' . $type . ' = ' . intval($idaction); $metrics = array(Piwik_Archive::INDEX_NB_VISITS); $data = $archiveProcessing->queryVisitsByDimension($dimension, $where, $metrics, $orderBy, $rankingQuery, $select, $selectGeneratesLabelColumn = true); $referrerData = array(); $referrerSubData = array(); foreach ($data as $referrerType => &$subData) { $referrerData[$referrerType] = array(Piwik_Archive::INDEX_NB_VISITS => 0); if ($referrerType != Piwik_Common::REFERER_TYPE_DIRECT_ENTRY) { $referrerSubData[$referrerType] = array(); } foreach ($subData as &$row) { if ($referrerType == Piwik_Common::REFERER_TYPE_SEARCH_ENGINE && empty($row['referrer_data'])) { $row['referrer_data'] = Piwik_Referers::LABEL_KEYWORD_NOT_DEFINED; } $referrerData[$referrerType][Piwik_Archive::INDEX_NB_VISITS] += $row[Piwik_Archive::INDEX_NB_VISITS]; $label = $row['referrer_data']; if ($label) { $referrerSubData[$referrerType][$label] = array(Piwik_Archive::INDEX_NB_VISITS => $row[Piwik_Archive::INDEX_NB_VISITS]); } } } return $archiveProcessing->getDataTableWithSubtablesFromArraysIndexedByLabel($referrerSubData, $referrerData); }