Jump to content
Sign in to follow this  
odide

Delete Old Statistics

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



×
×
  • Create New...