Removing all old sales/order and customer data

Question

We have a Magento v1.4.0.1 store and, yes, we know it is a very old version and would like to upgrade but the contractor that implemented this modified core code. It is tightly coupled with our ExpressionEngine CMS and has been fairly stable for about 3 yrs.

We are going to be implementing a new CMS this year so we may be able to either implement a new version of Magento or another cart but in the mean time, in order to speed up our site, we'd like to be able to archive and then delete old sales/order and customer data. We'd like to remove all quote/order/payment info from years before 2014 and customer data, including addresses, from customers that have not placed orders since 2014.

Our code authenticates with our ERP through a web service and creates a new Magento customer when they do not exist so we are not worried about customer history because we have their order history in our ERP.

Can this be done using SQL? What queries would I run?

See my answer bellow. It provide the code for deleting orders which where created BEFORE 2014. Just want to clarify: Do you want to delete orders BEFORE 2014, but customers who hasn't orders AFTER 2014? Is it correct, that customers without orders should be also deleted? — martin_mageworx, Feb 14, 2015 at 13:10

AreDubya · Accepted Answer · 2015-02-14 20:39:03Z

This script will remove the orders - the invoices and shipments associated with the deleted orders will be removed as well. Put this in your Magento root folder and run by navigating to the script - yourdomain.com/whatYouNamedThisScript.php

<?php
/**
 * Remove Orders
 *
 */
require 'app/Mage.php';
Mage::app('admin')->setUseSessionInUrl(false);

$salesModel = Mage::getModel("sales/order");
$salesCollection = $salesModel->getCollection();
$test_order_ids = array();
$i = 0;
/**
 * Use this version of the loop to delete ALL orders.
 */
foreach ($salesCollection as $order) {
    $orderId = $order->getIncrementId();
    array_push($test_order_ids, $orderId);
}
/**
 *  Use this version of loop to target range of orders.
 *  $i is just a counter, use it to restrict the removal to a certain total number of orders.
 *  Change the if statement in this loop to target the selected order numbers if need be.
 */
 foreach($salesCollection as $order) {
    if ($i < 100){
        $orderId = $order->getIncrementId();
        if((int)$orderId < 200){
            array_push($test_order_ids, $orderId);
        }
    }
    else{
        break;
    }
    $i++;
}
*/
foreach ($test_order_ids as $id) {
    try {
        Mage::getModel('sales/order')->loadByIncrementId($id)->delete();
        echo "order #" . $id . " is removed" . PHP_EOL;
    } catch (Exception $e) {
        echo "order #" . $id . " could not be remvoved: " . $e->getMessage() . PHP_EOL;
    }
}
echo "complete.";

If you modify the if statement in the second version of the foreach loop (currently commented out) you should be able to target any range of order IDs.

Hope that helps!

EDIT - Remove Customers

Feel free to adjust the cutoff time. This will remove all customers whose most recent order is older than the cutoff.

RemoveCustomerByOrderDate.php (place this in your magento root, navigate to it with a browser to run)

 <?php

require_once 'app/Mage.php';
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

getCustomers();

function getCustomers()
{
    //Set your cutoff date here - use / as seperator for m/d/y/ format, use - for d-m-y format.
    $cutOffDate = strtotime('1/1/2013');

    $collection = Mage::getModel('customer/customer')->getCollection()
        ->addAttributeToSelect('last_order_date');

    foreach ($collection as $customer) {
        $orderCollection = Mage::getModel('sales/order')->getCollection()
            ->addFilter('customer_id', $customer->getId())
            ->setOrder('created_at', Varien_Data_Collection_Db::SORT_ORDER_DESC)
        ;
        $newestOrder = $orderCollection->getFirstItem();

        if (strtotime($newestOrder->getCreatedAt()) < $cutOffDate) {
            $customer->delete();
            echo 'Customer '.$customer->getId().' has been deleted.<br/>';
        }
    }
    echo 'Customer Deletion Completed.';
}

Will this also delete the customer data that hasn't placed an order since 2013? Will this also remove the payment details for each order that is removed? — MB34, Feb 14, 2015 at 14:25
Just added the code to remove customers. Ah, didn't read that correctly, with no orders since 2013 - another edit coming. Yes, the first script will remove the payment info. — AreDubya, Feb 14, 2015 at 20:29
That last code chunk will remove all customers who haven't ordered since 1/1/2013. Adjust the $cutoffDate to your needs. — AreDubya, Feb 14, 2015 at 20:40

Dexter · Accepted Answer · 2015-02-14 06:48:04Z

Instead of using Raw queries or script i would recommend using one of the extension listed below:

1) http://www.magentocommerce.com/magento-connect/seamless-delete-order.html

2) http://www.magentocommerce.com/magento-connect/delete-orders-6.html

3) http://www.magentocommerce.com/magento-connect/orders-eraser.html

They will delete all the unwanted and dummy orders. To remove customer account, you can use admin interface and delete them.

The main advantage of this extension will be, you will be able to delete selected orders and keep others in tact

martin_mageworx · Accepted Answer · 2015-02-20 14:37:59Z

Remove order related information for orders placed before year 2014.

This set of SQL queries is suitable for Magento >= 1.4.1.0

SELECT @max_order_id:=MAX( entity_id ) FROM  `sales_flat_order` WHERE created_at <  '2014-01-01 00:00:00';

### QUOTE ###
 DELETE FROM `sales_flat_quote` as q where q.entity_id IN (SELECT quote_id FROM `sales_flat_order` as o where o.entity_id <= @max_order_id)

DELETE a1
FROM `sales_flat_quote_address` a1 
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_item` a1 
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_address_item` a1 
LEFT JOIN sales_flat_quote_address a2 ON a1.quote_address_id = a2.address_id 
LEFT JOIN sales_flat_quote_item a3 ON a1.quote_item_id = a3.item_id 
WHERE a2.address_id IS NULL OR a3.item_id IS NULL;

DELETE a1
FROM `sales_flat_quote_item_option` a1 
LEFT JOIN sales_flat_quote_item a2 ON a1.item_id = a2.item_id 
WHERE a2.item_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_payment` a1 
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_shipping_rate` a1 
LEFT JOIN sales_flat_quote_address a2 ON a1.address_id = a2.address_id 
WHERE a2.address_id IS NULL ;

### CREDITMEMO ###
DELETE FROM `sales_flat_creditmemo` where order_id <= @max_order_id;

DELETE a1
FROM `sales_flat_creditmemo_comment` a1 
LEFT JOIN sales_flat_creditmemo a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_creditmemo_grid` a1 
LEFT JOIN sales_flat_creditmemo a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_creditmemo_item` a1 
LEFT JOIN sales_flat_creditmemo a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

### SHIPMENT ###
DELETE FROM `sales_flat_shipment` where order_id <= @max_order_id;

DELETE a1
FROM `sales_flat_shipment_track` a1 
LEFT JOIN sales_flat_shipment a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_shipment_item` a1 
LEFT JOIN sales_flat_shipment a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1 
FROM `sales_flat_shipment_grid` a1 
LEFT JOIN sales_flat_shipment a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_shipment_comment` a1 
LEFT JOIN sales_flat_shipment a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

### INVOICE ###
DELETE FROM `sales_flat_invoice` where order_id <= @max_order_id;

DELETE a1
FROM `sales_flat_invoice_comment` a1 
LEFT JOIN sales_flat_invoice a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_invoice_grid` a1 
LEFT JOIN sales_flat_invoice a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_invoice_item` a1 
LEFT JOIN sales_flat_invoice a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

### ORDER ###
DELETE FROM `sales_flat_order` where entity_id <= @max_order_id;

DELETE a1
FROM `sales_flat_order_address` a1 
LEFT JOIN sales_flat_order a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_order_grid` a1 
LEFT JOIN sales_flat_order a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_order_item` a1 
LEFT JOIN sales_flat_order a2 ON a1.order_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_order_payment` a1 
LEFT JOIN sales_flat_order a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_order_status_history` a1 
LEFT JOIN sales_flat_order a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_payment_transaction` a1 
LEFT JOIN sales_flat_order a2 ON a1.order_id = a2.entity_id 
LEFT JOIN sales_flat_order_payment a3 ON a1.payment_id = a3.entity_id 
WHERE a2.entity_id IS NULL OR a3.entity_id IS NULL;

This set of SQL queries is suitable for Magento < 1.4.1.0

SELECT @max_order_id:=MAX( entity_id ) FROM  `sales_order` WHERE created_at <  '2014-01-01 00:00:00';
SELECT @max_quote_id:=value FROM  `sales_order_int` o
LEFT JOIN eav_attribute a ON a.attribute_id = o.attribute_id
WHERE o.entity_id = @max_order_id AND a.attribute_code='quote_id';

SELECT @invoice_type_order_attribute:=attribute_id FROM  `eav_attribute` WHERE attribute_code='order_id' AND entity_type_id = 16;
SELECT @shipment_type_order_attribute:=attribute_id FROM  `eav_attribute` WHERE attribute_code='order_id' AND entity_type_id = 19;
SELECT @creditmemo_type_order_attribute:=attribute_id FROM  `eav_attribute` WHERE attribute_code='order_id' AND entity_type_id = 23;

### QUOTE ###
DELETE FROM `sales_flat_quote` WHERE entity_id <= @max_quote_id;

DELETE a1
FROM `sales_flat_quote_address` a1 
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_item` a1 
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_address_item` a1 
LEFT JOIN sales_flat_quote_address a2 ON a1.quote_address_id = a2.address_id 
LEFT JOIN sales_flat_quote_item a3 ON a1.quote_item_id = a3.item_id 
WHERE a2.address_id IS NULL OR a3.item_id IS NULL;

DELETE a1
FROM `sales_flat_quote_shipping_rate` a1 
LEFT JOIN sales_flat_quote_address a2 ON a1.address_id = a2.address_id 
WHERE a2.address_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_item_option` a1 
LEFT JOIN sales_flat_quote_item a2 ON a1.item_id = a2.item_id 
WHERE a2.item_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_payment` a1 
LEFT JOIN sales_flat_quote a2 ON a1.quote_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_quote_shipping_rate` a1 
LEFT JOIN sales_flat_quote_address a2 ON a1.address_id = a2.address_id 
WHERE a2.address_id IS NULL ;

### CREDITMEMO, INVOICE, SHIPMENT ###

DELETE `e`
FROM `sales_order_entity` AS `e`
INNER JOIN `sales_order_entity_int` AS `_table_order_id` ON (_table_order_id.entity_id = e.entity_id) AND (_table_order_id.attribute_id=@invoice_type_order_attribute)
WHERE (e.entity_type_id = '16') AND (_table_order_id.value <= @max_order_id);

DELETE `e`
FROM `sales_order_entity` AS `e`
INNER JOIN `sales_order_entity_int` AS `_table_order_id` ON (_table_order_id.entity_id = e.entity_id) AND (_table_order_id.attribute_id=@shipment_type_order_attribute)
WHERE (e.entity_type_id = '19') AND (_table_order_id.value <= @max_order_id);

DELETE `e`
FROM `sales_order_entity` AS `e`
INNER JOIN `sales_order_entity_int` AS `_table_order_id` ON (_table_order_id.entity_id = e.entity_id) AND (_table_order_id.attribute_id=@creditmemo_type_order_attribute)
WHERE (e.entity_type_id = '23') AND (_table_order_id.value <= @max_order_id);

### CREDITMEMO, INVOICE, SHIPMENT - ITEMS, COMMENTS ###
### SHIPMENT TRACK ###
DELETE `a1`
FROM `sales_order_entity` AS `a1`
LEFT JOIN `sales_order_entity` AS `a2` ON a1.parent_id = a2.entity_id
WHERE a2.entity_id IS NULL
AND a1.entity_type_id IN (17, 18, 20, 21, 22, 24, 25);


### ORDER ADDRESS, PAYMENT, STATUS HISTORY ###
DELETE FROM `sales_order_entity` WHERE entity_type_id IN (12,14,15) AND parent_id <= @max_order_id;

### ORDER ENTITY ###
DELETE a1
FROM `sales_order_entity_datetime` a1 
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_entity_decimal` a1 
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_entity_int` a1 
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_entity_text` a1 
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_entity_varchar` a1 
LEFT JOIN sales_order_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

### ORDER ###
DELETE FROM `sales_order` where entity_id <= @max_order_id;

DELETE a1
FROM `sales_order_datetime` a1 
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_decimal` a1 
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_int` a1 
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_text` a1 
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_varchar` a1 
LEFT JOIN sales_order a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_order_tax` a1 
LEFT JOIN sales_order a2 ON a1.order_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_flat_order_item` a1 
LEFT JOIN sales_order a2 ON a1.order_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `sales_payment_transaction` a1 
LEFT JOIN sales_order a2 ON a1.order_id = a2.entity_id 
WHERE a2.entity_id IS NULL;

Remove customers without orders. Apply customers' deletion script after deleting orders.

Please select the variant matched your Magento version (base on SQL comments bellow)

#--- Only for Magento >= 1.4.1.0 ---#
DELETE FROM `customer_entity` WHERE entity_id NOT IN (SELECT customer_id FROM sales_flat_order);

#--- Only for Magento < 1.4.1.0 --#
DELETE FROM `customer_entity` WHERE entity_id NOT IN (SELECT customer_id FROM sales_order);

DELETE a1
FROM `customer_address_entity` a1 
LEFT JOIN customer_entity a2 ON a1.parent_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `customer_address_entity_datetime` a1 
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `customer_address_entity_decimal` a1 
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `customer_address_entity_int` a1 
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ; 

DELETE a1
FROM `customer_address_entity_text` a1 
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ; 

DELETE a1
FROM `customer_address_entity_varchar` a1 
LEFT JOIN customer_address_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ; 

DELETE a1
FROM `customer_entity_datetime` a1 
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `customer_entity_decimal` a1 
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `customer_entity_int` a1 
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ; 

DELETE a1
FROM `customer_entity_text` a1 
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ; 

DELETE a1
FROM `customer_entity_varchar` a1 
LEFT JOIN customer_entity a2 ON a1.entity_id = a2.entity_id 
WHERE a2.entity_id IS NULL ; 

### WHISHLIST ###
DELETE a1
FROM `wishlist` a1 
LEFT JOIN customer_entity a2 ON a1.customer_id = a2.entity_id 
WHERE a2.entity_id IS NULL ;

DELETE a1
FROM `wishlist_item` a1 
LEFT JOIN wishlist a2 ON a1.wishlist_id = a2.wishlist_id 
WHERE a2.wishlist_id IS NULL ;

#--- Only for Magento >= 1.4.1.0 ---#
DELETE a1
FROM `wishlist_item_option` a1 
LEFT JOIN wishlist_item a2 ON a1.wishlist_item_id = a2.wishlist_item_id 
WHERE a2.wishlist_item_id IS NULL ;

The customer deletion is for anyone that placed an order before 2014 and not placed one since. Meaning, if I placed an order in 2014 I would be kept or if I placed an order in 2013 but not since, I would be deleted. There are several tables in this script the we do not use. We do not use the invoicing, shipment or creditmemo tables. We also do not have some of the flat_order tables. — MB34, Feb 14, 2015 at 14:30
The script is provided for the latest Magento version. If you are planning to upgrade your Magento, then you may upgrade it first and then apply scripts. I'll also edited my answer and added the scripts for deleting customers — martin_mageworx, Feb 14, 2015 at 20:06
We cannot upgrade as pointed out. A new version will be a fresh installation so this would be unusable on our 1.4.0.1 version. — MB34, Feb 15, 2015 at 5:10
Just added the variant for Magento 1.4.0.1. See my updated answer. Customers' deletion script has updates as well. — martin_mageworx, Feb 18, 2015 at 14:27

Giuseppe · Accepted Answer · 2015-02-13 16:00:09Z

0

Yes it could be done by SQL.

For the query to run you can do a search for "magento delete test order" or "magento delete all orders". Then you can modified the query adding the date filter you need.

For what concern customer data you should do a simple join on on the sales_flat_order on the customer_id.

answered Feb 13, 2015 at 16:00

Giuseppe

1,1318 silver badges12 bronze badges

I could not find any where they don't use TRUNCATE on the tables.
– MB34
Feb 13, 2015 at 16:20
from google you can get the table's name then you can replace the "truncate" with "delete ... where year <= 2104" You need to be able to manage a little bit of SQL and some infos about magento sales_* and customer_* tables.
– Giuseppe
Feb 13, 2015 at 16:39
I was hoping to get a more defined script as I am not as familiar with the extremely absurd Magento DB structure as I should be. I found one and am working on it. I will post the completed script so others may benefit.
– MB34
Feb 13, 2015 at 16:43

Add a comment |

Stack Exchange Network

Removing all old sales/order and customer data

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged
sales-order
or ask your own question.

Hot Network Questions

Removing all old sales/order and customer data

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sales-order or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sales-order
or ask your own question.