odide Posted February 3, 2014 Report Posted February 3, 2014 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 Quote
Matteo Beccati Posted February 6, 2014 Report Posted February 6, 2014 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. Quote
umarizal Posted August 7, 2014 Report Posted August 7, 2014 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. Quote
Moshe L Posted September 14, 2014 Report Posted September 14, 2014 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 Quote
Moshe L Posted September 14, 2014 Report Posted September 14, 2014 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. Quote
vocalist Posted October 6, 2014 Report Posted October 6, 2014 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. Quote
Eliza0406 Posted October 13, 2014 Report Posted October 13, 2014 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. Quote
Recommended Posts
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.