AbleCommerce Gold How To Clean Up Anonymous Users

by Joe Payne 14. April 2014 15:45

Even though Able Gold has a manual cleanup option in the Maintenance page, it doesn’t always work well.  The problem arises from how Able must delete each user individually.  Not such a big deal when you have 500 users to clear out.  

It’s a very different story when you have 2,000,000 unwanted users.   A SQL query can delete all of the unwanted records in a single command.

Below is the updated query to work with Able Gold schema.  Obviously change the dates to something more recent. 

If the queries do not remove as many records as you expected, you might want to remove the “AND (AffiliateId IS NOT NULL)” criteria if you don’t care about affiliate reporting.

use <yourdbname>
DELETE FROM ac_Baskets 
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))
 
DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))
 
DELETE FROM ac_Users 
WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009')

Tags: , , ,

AbleCommerce Gold | Tech Support

Month List