Cleaning up Customer Data in nopCommerce

Going through a nopCommerce project with a 40GB database, I found the majority of the space was allotted to a very large Customer table. To fix this, I decided to delete Customer data with the following criteria:

  • No username, email, and password data
  • Not a system account
  • Doesn’t reference a Shipping and Billing address
  • Last activity recorded a month from today’s date

I considered the customers having shopping cart items as well, but found that most of the junk data had one shopping cart item – I think this comes from a robot regularly adding specific items to a cart to check price.

Here’s the SQL used:

DECLARE @customersToDelete TABLE (id int)

INSERT INTO @customersToDelete (id)
SELECT Id FROM Customer
WHERE
	Username IS NULL
	AND Email IS NULL
	AND Password is NULL
	AND IsSystemAccount = 0
	AND BillingAddress_Id IS NULL
	AND ShippingAddress_Id IS NULL
	AND LastActivityDateUtc < DATEADD(week, -1, GETDATE())

DELETE FROM ShoppingCartItem WHERE CustomerId IN (SELECT * FROM @customersToDelete)

DELETE FROM GenericAttribute WHERE KeyGroup = 'Customer' AND EntityId IN (SELECT * FROM @customersToDelete)

DELETE FROM Customer_CustomerRole_Mapping WHERE Customer_Id IN (SELECT * FROM @customersToDelete)

DELETE FROM Customer WHERE Id IN (SELECT * FROM @customersToDelete)

Once this is done, make sure to shrink the database to reclaim the space gained by clearing out the data.

Leave a Reply

Your email address will not be published. Required fields are marked *