OpenX报表模块分析笔记

Table of contents

1. OpenX报表模块分析笔记
   1. 报表相关数据库
      1. 即时表
      2. 中间表
      3. 汇总表
      4. 原始表(暂时没使用)
   2. 统计报表的生成
      1. 统计入口
      2. Maintenance入口
      3. Maintenance主体文件
   3. 广告项目的计费实现
   4. 统计报表的展现
      1. 统计数据的获取
      2. 高级报表Advanced Reports

OpenX报表模块分析笔记 ¶
报表相关数据库 ¶
即时表 ¶

bkt - bucket的略写

• ox_data_bkt_a
• ox_data_bkt_a_var
• ox_data_bkt_c - click 点击 (使用中)

字段名称 备注
interval_start 时间点，每5分钟为一个记录点,该时间点不可配置
creative_id banner_id - 广告编号
zone_id
count 点击次数
keyword
ahid 网站主ID，注意：若为广告交换类型的，网站主自己的广告，该ID才记录；若为CPC类别的，该ID也记录

• ox_data_bkt_country_c (使用中)
• ox_data_bkt_country_m (使用中)
• ox_data_bkt_e - expansions
• ox_data_bkt_m - impression (使用中)

字段名称 备注
interval_start 时间点，每5分钟为一个记录点，该时间点不可配置
creative_id banner_id - 广告编号
zone_id
count 显示次数
keyword
ahid 网站主ID，注意：若为广告交换类型的，网站主自己的广告，该ID才记录；若为CPC类别的，该ID也记录

• ox_data_bkt_r - request
• ox_data_bkt_t - times 展示时间，右侧富媒体用

中间表 ¶

• ox_data_intermediate_ad - the zone impression history table (使用中)

字段名称 备注
data_intermediate_ad_id
date_time 格式如 2010-04-20 19：40
operation_interval 操作时间间隔，可配置
operation_interval_id
interval_start
interval_end
ad_id
creative_id
zone_id
requests
impressions
expansions
times
clicks
conversions
total_basket_value
total_num_items
updated
keyword
ahid

• ox_data_intermediate_ad_connection - the migration of conversions
• ox_data_intermediate_ad_variable_value

汇总表 ¶

• ox_data_summary_ad_hourly (使用中)
• ox_data_summary_ad_zone_assoc
• ox_data_summary_channel_daily
• ox_data_summary_zone_impression_history (使用中)

原始表(暂时没使用) ¶

注意：此表的数据将周期性的被清空

• ox_data_raw_ad_click
• ox_data_raw_ad_impression
• ox_data_raw_ad_request
• ox_data_raw_tracker_impression
• ox_data_raw_tracker_variable_value

统计报表的生成 ¶

统计入口 ¶

• 广告记录 - 入口 www/delivery/lt.php or www/delivery/lg.php

// Run automaintenance, if needed
//注意：只有非LB配置下，而且启用了维护任务，才会触发报表统计功能；分布式方式部署后，将导致auto maintenance配置被禁用
if (!empty($GLOBALS['_MAX']['CONF']['maintenance']['autoMaintenance']) && empty($GLOBALS['_MAX']['CONF']['lb']['enabled'])) {
if (MAX_cacheCheckIfMaintenanceShouldRun()) {
include MAX_PATH . '/lib/OA/Maintenance/Auto.php';
OA_Maintenance_Auto::run();
}
}

• maintenance配置

//#参见/var目录的*.conf.php文件
[maintenance]
autoMaintenance=1 //是否启用自动维护计划: 0 - 不启用 1 - 启用
timeLimitScripts=300 //注意：单位为秒
operationInterval=20 //注意：单位为分钟
blockAdImpressions=0
blockAdClicks=0
channelForecasting=
pruneCompletedCampaignsSummaryData=

• 负载均衡配置 Load balance

[lb]
enabled=
type=mysql
host=localhost
port=3306
username=
password=
name=
persistent=

Maintenance入口 ¶

• 维护入口文件 lib/OA/Maintenance/Auto.php

Auto.php - A library class for providing automatic maintenance process methods.
Maintenance主体文件 ¶

• 维护统计引擎
  • 文件 lib/OX/Maintenance.php
  • Maintenance的执行 Link: http://www.openx.org/docs/2.4/adminguide/maintenance

Run the Maintenance Statistics Engine (MSE) process

//统计报表调用方法
/**
* A private method to run MSE.
*
* @access private
*/
function _runMSE()
{
$oMaintenanceStatistics = new OX_Maintenance_Statistics();
$oMaintenanceStatistics->run();
}

• MSE实现类 lib/OX/Maintenance/Statistics.php
  • 核心成员$oTaskRunner - OA_Task_Runner

/**
* An OA_Task_Runner instance to store the MSE tasks.
*
* @var OA_Task_Runner
*/
var $oTaskRunner;

若有新任务需要扩展，可以通过一下方式扩展

$extendTask = new OX_Maintenance_Statistics_Task_ExtendTask();
$this->addTask($extenTask);

扩展的类置于:lib/OX/Maintenance/Statistics/Task目录
再如现有bucket数据（即使表）的更新，可以通过以下方式完成：

// Register this object as the controlling class for the process,
// so that tasks run by the task runner can locate this class to
// update the report, etc.
$oServiceLocator =& OA_ServiceLocator::instance();
$oServiceLocator->register('Maintenance_Statistics_Controller', $this);

// Create and register an instance of the OA_Dal_Maintenance_Statistics DAL
// class for the following tasks to use
if (!$oServiceLocator->get('OX_Dal_Maintenance_Statistics')) {
$oFactory = new OX_Dal_Maintenance_Statistics_Factory();
$oDal = $oFactory->factory();
$oServiceLocator->register('OX_Dal_Maintenance_Statistics', $oDal);
}

// Add the task to set the update requirements
$oSetUpdateRequirements = new OX_Maintenance_Statistics_Task_SetUpdateRequirements();
$this->oTaskRunner->addTask($oSetUpdateRequirements);

// Add the task to migrate the bucket data into the statistics tables //file statistics.php line 174
$oSummariseIntermediate = new OX_Maintenance_Statistics_Task_MigrateBucketData();
$this->oTaskRunner->addTask($oSummariseIntermediate);

// Add the task to handle the de-duplication and rejection of empty conversions
$oDeDuplicateConversions = new OX_Maintenance_Statistics_Task_DeDuplicateConversions();
$this->oTaskRunner->addTask($oDeDuplicateConversions);

// Add the task to handle the updating of "intermediate" statistics with
// conversion information, as a legacy issue until all code obtains
// conversion data from the standard conversion statistics tables
$oManageConversions = new OX_Maintenance_Statistics_Task_ManageConversions();
$this->oTaskRunner->addTask($oManageConversions);

// Add the task to summarise the intermediate statistics into final form
$oSummariseFinal = new OX_Maintenance_Statistics_Task_SummariseFinal();
$this->oTaskRunner->addTask($oSummariseFinal);

// Add the task to log the completion of the task
$oLogCompletion = new OX_Maintenance_Statistics_Task_LogCompletion();
$this->oTaskRunner->addTask($oLogCompletion);

// Add the task to manage (enable/disable) campaigns
$oManageCampaigns = new OX_Maintenance_Statistics_Task_ManageCampaigns();
$this->oTaskRunner->addTask($oManageCampaigns);

• Task的执行

只需调用 TaskRunner?的runTasks方法

// Run the MSE process tasks
$this->oTaskRunner->runTasks(); //file statistics.php line 209

调用该方法后，将逐个执行所有的Task，详见该方法的实现

/**
* A method to run the run() method of each task in the collection,
* in the registered order.
*
* @todo We should really make OA_Task::run return a boolean we can check.
*/
function runTasks()
{
// Remove tasks from the queue and unset them when done to prevent
// useless memory consumption
while ($oTask = array_shift($this->aTasks)) {
$oTask->run();
unset($oTask);
}
}

• 重要插件 deliveryLog

//参见 plugins/deliveryLog 目录
plugins/deliveryLog/oxLogRequest - 记录请求次数
plugins/deliveryLog/oxLogImpression - 记录显示次数
plugins/deliveryLog/oxLogClick - 记录点击
plugins/deliveryLog/oxCountry - 记录国家和区域
plugins/deliveryLog/oxLogTimes - 记录停留时间

• 重要DAL类(抽象MSE类OX_Dal_Maintenance_Statistics)

lib/OX/Dal/Maintenance/Statistics.php

注意：该类为抽象类，并不直接使用，对于mysql数据库，实际操作通过Statistics/Mysql.php来完成

• 统计报表查询页 www/admin/stats.php
  • 报表模板文件目录

1. lib/OA/Admin/Statistics/Targeting
2. lib/OA/Admin/Statistics/Delivery/

• Controller类目录

//Delivery
lib/OA/Admin/Statistics/Delivery/Controller

• DAL

lib/OA/Dal/Statistics
广告项目的计费实现 ¶

经过检查，项目计费目前是已有功能

//现有计费类型参考 - 定义于 /constants.php
// Financial constants
define('MAX_FINANCE_CPM', 1);
define('MAX_FINANCE_CPC', 2);
define('MAX_FINANCE_CPA', 3);
define('MAX_FINANCE_MT', 4); // Monthly Tennancy
define('MAX_FINANCE_RS', 5); // % Revenue split (zone-only)
define('MAX_FINANCE_BV', 6); // % Basket value (zone-only)
define('MAX_FINANCE_AI', 7); // Amount per item (zone-only)
define('MAX_FINANCE_ANYVAR', 8); // % of any variable (zone-only)
define('MAX_FINANCE_VARSUM', 9); // % of a variable sum (zone-only)
define('MAX_FINANCE_DT', 10); // Day Tennancy
统计报表的展现 ¶
统计数据的获取 ¶

• Cheatsheet - 获的统计数据所有执行方法路径
  1. lib\OA\Admin\Statistics\Delivery\Controller\GlobalAdvertiser?.php 第134行

switch ($this->startLevel)
{
case 2:
//$this->aEntitiesData = $this->getBanners($aParams, $this->startLevel, $expand);
$this->aEntitiesData = $this->getBannersForKeyword($aParams, $this->startLevel, $expand);
break;
case 1:
//$this->aEntitiesData = $this->getCampaigns($aParams, $this->startLevel, $expand);
$this->aEntitiesData = $this->getCampaignsForkeyword($aParams, $this->startLevel, $expand);
break;
default:
$this->startLevel = 0;
//$this->aEntitiesData = $this->getAdvertisers($aParams, $this->startLevel, $expand);
$this->aEntitiesData = $this->getAdvertisersForKeyword($aParams, $this->startLevel, $expand); //以此作为入口
break;
}

1. lib\OA\Admin\Statistics\Delivery\CommonEntity?.php - line 902, function getAdvertisersForKeyword

function getAdvertisersForKeyword($aParams, $level, $expand = ''){

//$aParams['include'] = array('advertiser_id', 'placement_id', 'keyword', 'ahid');
$aParams['include'] = array('advertiser_id', 'placement_id', 'keyword');
//$aParams['include'] = array('advertiser_id', 'placement_id');
$aParams['exclude'] = array('zone_id');

$this->prepareKeywordData($aParams); // line 909,执行此方法将导致查询数据库

$period_preset = MAX_getStoredValue('period_preset', 'today');
$aAdvertisers = $this->mergeData($aParams, 'advertiser_id');

1. lib\OA\Admin\Statistics\Delivery\CommonEntity?.php - line 365, function prepareKeywordData

function prepareKeywordData($aParams)
{
if (is_null($this->data)){

$oNow = new Date();
$aParams['tz'] = $oNow->tz->getID();

// Get plugin aParams
$pluginParams = array();
foreach ($this->aPlugins as $oPlugin) {
$oPlugin->addQueryParams($pluginParams);
}

$pluginParams['add_columns']['s.keyword'] = 'keyword';
$pluginParams['add_columns']['s.ahid'] = 'ahid';

$aRows = Admin_DA::fromCache('getEntitiesStatsForAll', $aParams + $this->aDates + $pluginParams);

1. lib\max\Admin_DA.php - function fromCache

$ret = $cache->call("Admin_DA::".$method, $aParams, $allFields); //line 540 in function from Cache

1. lib\pear\Cache\Lite\Function.php 第89行，方法 call

...
89. if (strstr($target, '::')) { // classname::staticMethod
90. list($class, $method) = explode('::', $target);
91. $result = call_user_func_array(array($class, $method), $arguments); //调用AdminDA类中方法_getEntities
92. }
...

1. lib\max\Admin_DA.php 方法第200行，方法 _getEntities

function _getEntities($entity, $aParams, $allFields = false, $key = null, $isAll=false)
{

if (empty($key)) {
$key = "{$entity}_id";
}

$aColumns = SqlBuilder::_getColumns($entity, $aParams, $allFields);
$aTables = SqlBuilder::_getTables($entity, $aParams);

$aLimitations = array_merge(
SqlBuilder::_getLimitations($entity, $aParams),
SqlBuilder::_getTableLimitations($aTables, $aParams)
);
$aGroupColumns = SqlBuilder::_getGroupColumns($entity, $aParams);
$aLeftJoinedTables = SqlBuilder::_getLeftJoinedTables($entity, $aParams);

$rows = SqlBuilder::_select($aColumns, $aTables, $aLimitations, $aGroupColumns, $key, $aLeftJoinedTables, $isAll); //将调用SqlBuilder.php中的_select方法
return $rows;
}

1. lib\max\SqlBuilder?.php 第1139行，方法 _select执行查询，返回最终结果

function _select($aColumns, $aTables, $aLimitations, $aGroupColumns, $primaryKey, $aLeftJoinedTables = null, $isAll=false)
{
$conf = $GLOBALS['_MAX']['CONF'];
$oDbh = OA_DB::singleton();
$columns = '';
...

Important Tips：通过在_select方法返回值之前，添加 print('--debug begin--'.$query.'--debug end--'.);就可以看到完整的SQL语句，可以用此来检查报表数据的正确性啦

高级报表Advanced Reports ¶

• 源码目录 plugins/reports/oxReportsStandard
• 访问地址 www/admin/report-index.php