Today in this article I will cover how to show country stats report on your UI . that’s means you will now see user by country. this is not default option in OpenX or Revive-adserver. basically it’s need Plugins & Patch than it’s work .Here i will show how to do this . so let’s start
First Download this stats_country_2.8.zip File
Now Open The Flowing File Openx/lib/max/Admin_DA.php OpenX Directory See#41 Number Line Add code here 'getCountries' => 'stats', After add this the file are looking blow
'getAdvertisersStats' => 'stats',
'getConversions' => 'stats',
'getConnectionVariables' => 'stats',
'getCountries' => 'stats',
'getPlacementsStats' => 'stats',
'getPublishersStats' => 'stats',
'getZonesStats' => 'stats',
Now we need to add more patch in this same file Now find #638 Number Line See return $aStats}; After } Copy the code and past here Admin_DA.php
/**
* Returns an array of countries.
*
* @param array $aParams
* @return array
*/
function getCountries($aParams)
{
$conf = $GLOBALS['_MAX']['CONF'];
$oDbh = &OA_DB::singleton();
$where = ”;
if (!empty($aParams[‘day’])) {
$aParams[‘day_begin’] = $aParams[‘day_end’] = $aParams[‘day’];
}
if (!empty($aParams[‘day_begin’])) {
$oStart = new Date($aParams[‘day_begin’]);
$oStart->setHour(0);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oStart->toUTC();
$where .= ‘ AND cs.date_time >= ‘. $oDbh->quote($oStart->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘day_end’])) {
$oEnd = new Date($aParams[‘day_end’]);
$oEnd->setHour(23);
$oEnd->setMinute(59);
$oEnd->setSecond(59);
$oEnd->toUTC();
$where .= ‘ AND cs.date_time <= ‘. $oDbh->quote($oEnd->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘month’])) {
$oStart = new Date(“{$aParams[‘month’]}-01”);
$oStart->setHour(0);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date(Date_Calc::beginOfNextMonth($oStart->getDay(), $oStart->getMonth, $oStart->getYear(), ‘%Y-%m-%d’));
$oEnd->setHour(0);
$oEnd->setMinute(0);
$oEnd->setSecond(0);
$oEnd->subtractSeconds(1);
$oStart->toUTC();
$oEnd->toUTC();
$where .= ‘ AND cs.date_time >= ‘. $oDbh->quote($oStart->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
$where .= ‘ AND cs.date_time <= ‘. $oDbh->quote($oEnd->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘day_hour’])) {
$oStart = new Date(“{$aParams[‘day_hour’]}:00:00″);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date($oStart);
$oStart->setMinute(59);
$oStart->setSecond(59);
$where .= ‘ AND cs.date_time >= ‘. $oDbh->quote($oStart->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
$where .= ‘ AND cs.date_time <= ‘. $oDbh->quote($oEnd->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘agency_id’])) {
$where .= ‘ AND c.agencyid=’. $oDbh->quote($aParams[‘agency_id’], ‘integer’);
}
if (!empty($aParams[‘clientid’])) {
$where .= ‘ AND c.clientid=’. $oDbh->quote($aParams[‘clientid’], ‘integer’);
}
if (isset($aParams[‘zonesIds’])) {
$where .= ‘ AND cs.zone_id IN (‘. $oDbh->escape(implode(‘,’, $aParams[‘zonesIds’])) .”)”;
}
if (!empty($aParams[‘campaignid’])) {
$where .= ‘ AND m.campaignid=’. $oDbh->quote($aParams[‘campaignid’], ‘integer’);
}
if (!empty($aParams[‘bannerid’])) {
$where .= ‘ AND d.bannerid=’. $oDbh->quote($aParams[‘bannerid’], ‘integer’);
}
$order = ‘ cs.country’;
if (!empty($aParams[‘listorder’])) {
switch ($aParams[‘listorder’]){
case ‘country’:
$order = ‘ cs.country’;
break;
case ‘name’:
$order = ‘ cs.country’;
break;
case ‘impressions’:
$order = ‘ SUM(cs.impressions)’;
break;
case ‘clicks’:
$order = ‘ SUM(cs.clicks)’;
break;
default:
$order = ‘ cs.country’;
}
}
$orderdirection = ‘ASC’;
if (!empty($aParams[‘orderdirection’])) {
if ($aParams[‘orderdirection’] == ‘down’){
$orderdirection = ‘DESC’;
}
}
$query =
“SELECT
cs.country as country,
SUM(cs.impressions) as impressions,
SUM(cs.clicks) as clicks
FROM
{$conf[‘table’][‘prefix’]}{$conf[‘table’][‘clients’]} AS c,
{$conf[‘table’][‘prefix’]}stats_country AS cs,
{$conf[‘table’][‘prefix’]}{$conf[‘table’][‘banners’]} AS d,
{$conf[‘table’][‘prefix’]}{$conf[‘table’][‘campaigns’]} AS m
WHERE
c.clientid=m.clientid
AND m.campaignid=d.campaignid
AND d.bannerid=cs.creative_id
“. $where .”
GROUP BY
cs.country
ORDER BY
“. $order . ” ” . $orderdirection
;
return $oDbh->queryAll($query, null, MDB2_FETCHMODE_DEFAULT, true);
}
Now we have need to declare Parameter country status . to go to country report page
Open lib/OA/Admin/Statistics/Fields/Delivery/Default.php here see #74 number line $this->_aFields['sum_views'] = Add the flowing Line Call 'link'
'link' => 'stats.php?entity=country&',
Now it’s look like blow code
array(
'name' => $GLOBALS['strImpressions'],
'short' => $GLOBALS['strImpressions_short'],
'pref' => 'ui_column_impressions',
'link' => 'stats.php?entity=country&',
'rank' => 1,
'active' => true,
'format' => 'default'
);
Now we need to place the file stats-country.php in this location /www/admin/. after uploding stats-country.php .Open stats.php file Flowing this location www/admin/stats.php
See #159 number line here .here past the flowing Blow code
// If displaying conversion statistics, hand over control to a different file
if ($entity == 'country') {
include_once MAX_PATH . '/www/admin/stats-country.php';
exit;
}
After the patch code the flowing #159 to #170 number line code looking like this
// If displaying conversion statistics, hand over control to a different file
if ($entity == 'conversions') {
include_once MAX_PATH . '/www/admin/stats-conversions.php';
exit;
}
// If displaying conversion statistics, hand over control to a different file
if ($entity == ‘country’) {
include_once MAX_PATH . ‘/www/admin/stats-country.php’;
exit;
}
Now time to install the OpenX plugin “openXDeliveryLogCountry” first.if you already have installed it then skip this step.
After Install “openXDeliveryLogCountry” have a look at openx /var folder, yourdomain.com.conf.php within there is a section [deliveryHooks], for impression-logging there should be a row “logImpression” containing “deliveryLog:oxLogCountry:logImpressionCountry”, like:
[deliveryHooks] logClick="deliveryDataPrepare:oxDeliveryDataPrepare:dataCommon|deliveryDataPrepare:oxDeliveryGeo:dataGeo|deliveryLog:oxLogClick:logClick|deliveryLog:oxLogCountry:logClickCountry" logConversion="deliveryLog:oxLogConversion:logConversion" logConversionVariable="deliveryLog:oxLogConversion:logConversionVariable" logImpression="deliveryDataPrepare:oxDeliveryDataPrepare:dataCommon|deliveryDataPrepare:oxDeliveryGeo:dataGeo|deliveryLog:oxLogCountry:logImpressionCountry|deliveryLog:oxLogImpression:logImpression" logRequest="deliveryDataPrepare:oxDeliveryDataPrepare:dataCommon|deliveryLog:oxLogRequest:logRequest"
If done all of Previews Steep than wait few hour after few hour you need to take a look into the database, table “ox_data_bkt_country_m” (Impressions) or “ox_data_bkt_country_c” (Clicks). Are there any rows logged after a banner has been displayed or clicked? If row are logged than country stats are show in your UI . See blow image
Done ! Now go status page and click the impression row see the country report.
Note : if you show blank page on stats.php?entity=country& in this page than please check [deliveryHooks] & Plugins
If you face any problem please drop a comment here i will try my best to assist you.
