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