Managing Stale Carts in Umbraco Commerce

If you have an e-commerce site using Umbraco Commerce that handles a lot of traffic, you might notice an accumulation of carts that never convert into orders. This was the case for my site, which has been running for about two years and had thousands of stale carts!

Adding to the complexity, I migrated this site from Vendr to Umbraco Commerce. If you’re considering a similar migration, you can refer to the official documentation.

Post-migration, a background job was set up to export all orders to a CRM. However, the stale carts were causing issues unrelated to the migration but due to other development factors. Our definition of product changed quite a bit, causing lots of errors being thrown due to 'cart' no longer being able to identify 'product' anymore. It would have also meant customers would have 'old' products in their cart that they could no longer order, therefore, clearing them all out was the best approach.

Some people suggested deleting them manually through the CMS, selecting 30 at a time, which would have been very time-consuming.

After extensive research, I found this issue and decided to swap Vendr tables with Umbraco Commerce tables. Here’s the SQL query I used to delete the stale carts efficiently:

BEGIN TRANSACTION;

-- Select all carts before date
SELECT * INTO #tempOrders FROM umbracoCommerceOrder 
WHERE createDate < 'yyyy/mm/dd' AND (finalizedDate IS NULL)

-- Delete data referenced to orders above in related tables
DELETE FROM umbracoCommerceFrozenPrice 
WHERE umbracoCommerceFrozenPrice.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderPriceAdjustment 
WHERE umbracoCommerceOrderPriceAdjustment.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderAmountAdjustment 
WHERE umbracoCommerceOrderAmountAdjustment.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderAppliedDiscountCode 
WHERE umbracoCommerceOrderAppliedDiscountCode.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderAppliedGiftCard 
WHERE umbracoCommerceOrderAppliedGiftCard.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderLineAttribute 
WHERE umbracoCommerceOrderLineAttribute.OrderLineId IN (SELECT Id FROM umbracoCommerceOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders));

DELETE FROM umbracoCommerceOrderLineProperty 
WHERE umbracoCommerceOrderLineProperty.OrderLineId IN (SELECT Id FROM umbracoCommerceOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders));

DELETE FROM umbracoCommerceOrderProperty 
WHERE umbracoCommerceOrderProperty.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderLine 
WHERE umbracoCommerceOrderLine.OrderId IN (SELECT Id FROM #tempOrders) AND umbracoCommerceOrderLine.parentOrderLineId IS NOT NULL;

DELETE FROM umbracoCommerceOrderLine 
WHERE umbracoCommerceOrderLine.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrder 
WHERE umbracoCommerceOrder.Id IN (SELECT Id FROM #tempOrders);

-- Drop the temporary table
DROP TABLE #tempOrders;

-- Use ROLLBACK for testing
-- ROLLBACK;

-- Use COMMIT for deleting
COMMIT;

This approach allowed me to clean up the stale carts quickly and efficiently.

I hope this helps!
B