Delete Old Statistics

[odide]

It would be very useful to have the ability to delete some old statistics. Already some easy options would very helpful. Maybe to delete statistics older than a defined period of time or before a choosen date.

 

It's inconvenient to delete old statistics directly in database.

 

Regards Odi

[Matteo Beccati]

There's some built in pruning functionality, but I know for sure it has some limitations. We could improve on that, but sometimes deleting "old statistics" might lead to some problems, like old campaigns with a certain number of booked imps/clicks being re-activated because the system thinks they haven't reached the target.

[umarizal]

Fantastic idea and I do not understand how after so many years in the OpenX and now in Revive Adserver, never included it in the admin panel.

In OpenX I always did it through direct action on SQL via PhpMyAdmin:

DELETE FROM ox_data_intermediate_ad WHERE interval_end < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
DELETE FROM ox_data_summary_ad_hourly WHERE date_time < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
DELETE FROM ox_data_summary_zone_impression_history WHERE interval_end < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

1 MONTH can be changed by 1 WEEK or 1 DAY or other period, by the number of months, weeks or days you want.

The selected period is the remainder of the statistics, ie, choosing one month everything will be erased before the last month. If you choose 1 week everything will be erased before the last week and so on.

[Moshe L]

I have and other solution for this problem, running as MySQL scheduler task.

DELIMITER $$

CREATE DEFINER=`root`@`192.168.%` PROCEDURE `compress_ox_data_summary_ad_hourly2`()
BEGIN



set @iDay =  DAYOFMONTH(date(now()));
set  @dStart = date( now()) - interval @iDay - 1 day;


set  @dHist =  @dStart - interval 6 month;

set  @dCurrentHist =  @dStart - interval 4 month;

/* drop old tables*/

drop table if exists  openx.`ox_data_summary_ad_hourly2`;
drop table if exists  openx.`ox_data_summary_ad_hourly3`;

/* create temp table  */

CREATE TABLE openx.`ox_data_summary_ad_hourly2` (
    `data_summary_ad_hourly_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `date_time` datetime NOT NULL,
    `ad_id` int(10) unsigned NOT NULL,
    `creative_id` tinyint(10) unsigned NOT NULL,
    `zone_id` int(10) unsigned NOT NULL,
    `requests` int(10) unsigned NOT NULL DEFAULT '0',
    `impressions` int(10) unsigned NOT NULL DEFAULT '0',
    `clicks` int(10) unsigned NOT NULL DEFAULT '0',
    `conversions` int(10) unsigned NOT NULL DEFAULT '0',
    `total_basket_value` tinyint DEFAULT NULL,
    `total_num_items` tinyint DEFAULT NULL,
    `total_revenue` tinyint DEFAULT NULL,
    `total_cost` tinyint DEFAULT NULL,
    `total_techcost` tinyint DEFAULT NULL,
    `updated` datetime NOT NULL,
    PRIMARY KEY (`data_summary_ad_hourly_id`),
    KEY `ox_data_summary_ad_hourly_zone_id_date_time` (`zone_id` , `date_time`),
    KEY `ox_data_summary_ad_hourly_ad_id_date_time` (`ad_id` , `date_time` , `zone_id`),
    KEY `ox_data_summary_ad_hourly_date_time` (`date_time`)
)  ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

/*  old history saved by month, and not by day (a~97% saving, as I know) */

insert into ox_data_summary_ad_hourly2 (date_time,ad_id,zone_id,impressions, clicks)
select   date(date_time) ,ad_id,zone_id,sum(impressions),sum(clicks) from ox_data_summary_ad_hourly where date_time < @dHist group by  year(date_time),  month(date_time),  ad_id, zone_id

;

/* less-old history, saved by day (1/24)  */
insert into ox_data_summary_ad_hourly2 (date_time,ad_id,zone_id,impressions, clicks)
select   date(date_time) ,ad_id,zone_id,sum(impressions),sum(clicks) from ox_data_summary_ad_hourly where  date_time >  @dHist and date_time < @dCurrentHist group by date(date_time), ad_id, zone_id;


insert into ox_data_summary_ad_hourly2 (date_time,ad_id,zone_id,impressions, clicks)
select   date_time ,ad_id,zone_id,impressions,clicks from ox_data_summary_ad_hourly where  date_time >=  @dCurrentHist ;

/* tables saved as something can be wrong */

rename table ox_data_summary_ad_hourly to ox_data_summary_ad_hourly3;
rename table  ox_data_summary_ad_hourly2 to ox_data_summary_ad_hourly;



END

[Moshe L]

this is for  data_intermediate, as this table is used only for statistics, I can save the information, gropped for minumum rows:

ox_data_intermediate_ad

insert into openx.ox_data_intermediate_ad2
        (date_time, operation_interval, operation_interval_id, interval_Start, interval_end,                    ad_id,creative_id,zone_id,requests,impressions,clicks,updated)
select date_time,60,0,                                             date_time, date_time + interval 59 minute, ad_id, 0, zone_id,           requests,impressions,clicks, updated  from  openx.ox_data_summary_ad_hourly
 group by  ad_id, zone_id
;

this is ugly (and not ths specific code I used), but works.

[vocalist]

OpenX used to have the ability to choose how long statistics were retained - i.e., 1 week, 1 month, 3 months, 6 months, 1 year or all time - I was disapponted to find it is not available in Revive as this builds up in time to be quite heavy load !!  Would be VERY useful to re-introduce it so admins can choose how long they want to keep stats. 

[Eliza0406]

Hi all,

 

Deleting to old statistics is possible by specifying the date limit, have look over the below featured product link.

 

http://www.openxmods.com/openxmods/Delete-Statistics/prod_1426.html

 

Please drop me PM if you find it useful.