Delete Inactive Customers

Hi, All,

We have many inactive customers bloating the database, is there is a simple method to delete them with using SQL, I noticed there are many relationships between tables that make this harder to do?

Paul.

There is a stored procedure in the database:  [DeleteGuests]
It gets run by the scheduled task.  If it does not meet your needs as-is, it can be copied/modified to suit your needs.  (And feel free to share that, because others here also have concerns about 'spam' customers ;)

I think the cleanest method to do this is to use the existing logic of NOP to do the work by simply changing the customer role to a guest for inactive customers using SQL and then using the maintenance feature of NOP to remove all guests.

-- UPDATE ALL REGISTERED CUSTOMERS WHO ARE INACTIVE AND MAKE THEM GUESTS
UPDATE crm
  SET crm.CustomerRole_Id = 4 -- GUESTS
  FROM dbo.Customer_CustomerRole_Mapping AS crm
  INNER JOIN dbo.Customer AS c
  ON crm.Customer_Id = c.Id
  WHERE c.Active = 0
  -- Date in the past, keep recent customers, last 2 weeks as an example
  AND c.LastActivityDateUtc < -- [ADD DATE]

You can do a quick test to see if they have been set as guests below.

SELECT *
  FROM [ClickAlgo_41].[dbo].[Customer] c
   INNER JOIN dbo.Customer_CustomerRole_Mapping AS crm
  ON crm.Customer_Id = c.Id
  where c.active = 0
  and crm.CustomerRole_Id = 4;

* Always back up your database execute on your dev or test environment first

Warning

You will also need to put a start date in the "Deleting guest customers" window in Maintenance or an error will occur. I managed to remove most of the inactive records, but some still remain, if anyone else can take over and find a good solution please feel free.

phayes wrote:

Yes, I suggested that in another post and agree it's probably the best way to handle this.

However, looking for not "Active" may not work for most.  My understanding is that that feature is only if you use "Email Validation" to require that the customer's account gets  activated.  Otherwise, if not using it, then all accounts are  immediately Active when registered.   Others might be more interested in removing the 'spam' accounts (e.g. the ones with 'google' in the company name, etc. ;)  It that case, your query would need to be modified to JOIN to attributes table.