Clean up things before Magento site launch: demos orders, bestsellers statistics and most viewed statistics

boss-angry
After developed a Magento site, someone may forgot some clean-up tasks and they wanna take the site to be launch as production store as soon as possible.

In shorten, you may want to cleanup dusting files and folders, reseting files' permissions to prevent security holes, remove all testing content including orders, customers, etc.

But It is such a pain with files and folders permission changing or deleting the testing orders either.

Fortunately, there is a tool call "Magento Cleanup" will help you reset files permissions. What you need to do:

  1. Download
  2. Unzip the zip file, upload magento-cleanup.php to the root directory where Magento is installed.
  3. Browse to (Assume that you install Magento in directory /www/magento/ )

Just wait a moment it will change all files and folders to the correct permissions for you. You also should run this cleanup whenever you done a system backup (at SystemTools Backups).

Now, for the test orders and ton of statistic - I guess you also found nothing in Magento backend that can help you remove these such dusting. And you just think about browsing to phpMyAdmin page, open the database and finding some tables to empty it, do you?

It is same for me at the first time - thinking about doing something with Magento's tables is also headache. Forget it now, we did experience it and you now just need doing these steps:

Step 1: Open your phpMyAdmin page, choose the right database of your Magento site. Then click the SQL tab.

phpmyadmin-run-sql

 Step 2: Paste this code to above textarea (2) and click "Go" (3):

-- Disabled foreign key check
SET FOREIGN_KEY_CHECKS=0; 

-- Remove all orders
TRUNCATE TABLE {PREFIX}sales_flat_order;
TRUNCATE TABLE {PREFIX}sales_order_tax;

-- Remove all entities of orders, invoices, shipments
DELETE FROM {PREFIX}eav_entity_store 
WHERE entity_type_id IN (SELECT entity_type_id FROM {PREFIX}eav_entity_type WHERE entity_type_code IN ('order', 'invoice', 'creditmemo', 'shipment'));

-- Remove all orders, downloadable purchases
TRUNCATE TABLE {PREFIX}downloadable_link_purchased;

-- Remove all bestsellers statistics
TRUNCATE TABLE {PREFIX}sales_bestsellers_aggregated_daily;
TRUNCATE TABLE {PREFIX}sales_bestsellers_aggregated_monthly;
TRUNCATE TABLE {PREFIX}sales_bestsellers_aggregated_yearly;

-- Remove all most viewed statistics
DELETE FROM {PREFIX}report_event WHERE event_type_id IN (SELECT event_type_id FROM {PREFIX}report_event_types WHERE event_name IN ('catalog_product_view'));

-- Re-enabled foreign key check
SET FOREIGN_KEY_CHECKS=1;

UPDATE: Bellow queries will work on Magento CE 1.9.x

-- Disabled foreign key check
SET FOREIGN_KEY_CHECKS=0; 

-- Remove all orders
TRUNCATE TABLE {PREFIX}sales_flat_order;
TRUNCATE TABLE {PREFIX}sales_flat_order_address;
TRUNCATE TABLE {PREFIX}sales_flat_order_grid;
TRUNCATE TABLE {PREFIX}sales_flat_order_item;
TRUNCATE TABLE {PREFIX}sales_flat_order_payment;
TRUNCATE TABLE {PREFIX}sales_flat_order_status_history;

TRUNCATE TABLE {PREFIX}sales_invoiced_aggregated_order;
TRUNCATE TABLE {PREFIX}sales_order_aggregated_created;
TRUNCATE TABLE {PREFIX}sales_order_aggregated_updated;
TRUNCATE TABLE {PREFIX}sales_refunded_aggregated_order;
TRUNCATE TABLE {PREFIX}sales_shipping_aggregated_order;
TRUNCATE TABLE {PREFIX}tax_order_aggregated_created;
TRUNCATE TABLE {PREFIX}tax_order_aggregated_updated;

TRUNCATE TABLE {PREFIX}sales_order_tax;
TRUNCATE TABLE {PREFIX}sales_order_tax_item;
-- Remove all invoice data
TRUNCATE TABLE {PREFIX}sales_flat_invoice;
TRUNCATE TABLE {PREFIX}sales_flat_invoice_comment;
TRUNCATE TABLE {PREFIX}sales_flat_invoice_grid;
TRUNCATE TABLE {PREFIX}sales_flat_invoice_item;
TRUNCATE TABLE {PREFIX}sales_invoiced_aggregated;
TRUNCATE TABLE {PREFIX}sales_invoiced_aggregated_order;

-- Remove order shipping data
TRUNCATE TABLE {PREFIX}sales_flat_quote_shipping_rate;
TRUNCATE TABLE {PREFIX}sales_flat_shipment_grid;
TRUNCATE TABLE {PREFIX}sales_flat_shipment_item;
TRUNCATE TABLE {PREFIX}sales_flat_shipment_track;
TRUNCATE TABLE {PREFIX}sales_shipping_aggregated;
TRUNCATE TABLE {PREFIX}sales_shipping_aggregated_order;

-- Credit meno
TRUNCATE TABLE {PREFIX}sales_flat_creditmemo;
TRUNCATE TABLE {PREFIX}sales_flat_creditmemo_comment;
TRUNCATE TABLE {PREFIX}sales_flat_creditmemo_grid;
TRUNCATE TABLE {PREFIX}sales_flat_creditmemo_item;

-- Recurring profile
TRUNCATE TABLE {PREFIX}sales_recurring_profile;
TRUNCATE TABLE {PREFIX}sales_recurring_profile_order;

-- Remove all entities of orders, invoices, shipments
DELETE FROM {PREFIX}eav_entity_store 
WHERE entity_type_id IN (SELECT entity_type_id FROM {PREFIX}eav_entity_type WHERE entity_type_code IN ('order', 'invoice', 'creditmemo', 'shipment'));

-- Remove all orders, downloadable purchases
TRUNCATE TABLE {PREFIX}downloadable_link_purchased;

-- Remove all bestsellers statistics
TRUNCATE TABLE {PREFIX}sales_bestsellers_aggregated_daily;
TRUNCATE TABLE {PREFIX}sales_bestsellers_aggregated_monthly;
TRUNCATE TABLE {PREFIX}sales_bestsellers_aggregated_yearly;

-- Remove all most viewed statistics
DELETE FROM {PREFIX}report_event WHERE event_type_id IN (SELECT event_type_id FROM {PREFIX}report_event_types WHERE event_name IN ('catalog_product_view'));

-- Re-enabled foreign key check
SET FOREIGN_KEY_CHECKS=1;

Where:

  • Replace {PREFIX} by your table prefix. If your tables have no prefix, remove all {PREFIX} from above code.
  • We SET FOREIGN_KEY_CHECKS to 0 to disable innoDB foreign key check. This help all commands after that work perfectly. After done we SET FOREIGN_KEY_CHECKS back to 1.

When done this step, login into backend, clear Magento cache, re-index all and you are already to lunch the new site.

Cheers!