This is part of a series focusing on the details of each item that needs to be pruned in a Commerce database.

Stale Guest Orders

What is it?

Also known as abandoned shopping carts, these will just pile up if you don’t prune them. In addition, Guest users will not be deleted if they have any orders, including shopping carts, which Commerce uses as Orders with a status of ‘P’ (for Pending). So for guest users to be pruned (one of the more important pruning areas), you must also prune stale guest orders.

Potential for impact

Any e-commerce site should have a method for dealing with abandoned shopping carts. No one wants them to dissappear within a few minutes while a user steps away to do something else, but most users who have not signed in don’t expect items they placed in a cart to still be there three months later. This touches primarily the Orders table and cascades to orderitems and others. It’s not as high of an impact as some of the others, but it is not to be neglected either.

Where this Applies

This applies to every Commerce database that takes orders where users are allowed to add items to a cart without logging in.

Table (other tables may be involved in cascading deletes)

ORDERS (cascades to ORDERITEMS and other tables)

SQL

note: always test for yourself, don’t take my word for it

note also that you should be cautious running the SQL during peak loads, and always use “with ur”

Number of rows to be deleted:

select count(*) from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= <days_to_retain> and orders.member_id in (select distinct users_id from users where registertype = 'G') and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC' and inventorystatus is not null)) with ur

Note: with every kind of order pruning, you should understand the various statuses and if any of them are used in a non-standard way.

Pruning method

You can use dbclean to prune this one. Object=user and Type=stale_guest

If you wanted to prune it manually, you could, keeping in mind all the gotchas for big deletes – you’d probably want to break it up. You don’t want to run it as one statement, but it would be:

delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and orders.member_id in (select distinct users_id from users where registertype = 'G') and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC' and inventorystatus is not null))

Determining Retention

The retention for this area depends on how long guest users are able to return to the site and access their user – and this depends on how long before users’ cookies expire.

You should also run pruning for these before pruning for guest users, especially if you’re not running pruning every night.

So, good information? More detail needed in any areas? Let me know what you think.