Delete Bestsellers & Search Terms Data from Magento Dashboard

Regularly whilst a Magento store is in the last phases of improvement, you’ll develop a reasonable piece of test / dummy data (like test sales / orders and customers which will populate different tables, for example, bestsellers and search terms in your Magento dashboard.

You can delete all the bestsellers and search terms from your Magento by running the below SQL query.

This has been tested and confirmed working on Magento versions from 1.4.0.1 to 1.9.2.2.

SET FOREIGN_KEY_CHECKS=0;
 
##############################
# SEARCH TERMS RELATED TABLES
##############################

TRUNCATE catalogsearch_query;
TRUNCATE catalogsearch_fulltext;
TRUNCATE catalogsearch_result;
ALTER TABLE catalogsearch_query AUTO_INCREMENT=1;
ALTER TABLE catalogsearch_fulltext AUTO_INCREMENT=1;
ALTER TABLE catalogsearch_result AUTO_INCREMENT=1;

##############################
# BESTSELLERS RELATED TABLES
##############################
TRUNCATE TABLE sales_bestsellers_aggregated_daily;
TRUNCATE TABLE sales_bestsellers_aggregated_monthly;
TRUNCATE TABLE sales_bestsellers_aggregated_yearly;
ALTER TABLE sales_bestsellers_aggregated_daily AUTO_INCREMENT=1;
ALTER TABLE sales_bestsellers_aggregated_monthly AUTO_INCREMENT=1;
ALTER TABLE sales_bestsellers_aggregated_yearly AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;