[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Mark Bryant]

At 21:29 03/03/2005, you wrote:
>Quoting Mark Bryant (mark at vwe.net):
> > At 00:21 03/03/2005, you wrote:
> > >On 03/02/05 07:32, Mark Bryant wrote:
> > >>At 13:10 02/03/2005, you wrote:
> > >>>SELECT userdb.username from userdb LEFT JOIN transactions ON
> > >>>userdb.username=transactions.username WHERE transactions.code IS NULL
> > >>>
> > >>>It would be risky to delete all records found by that statement 
> since it
> > >>>does not take into account the time since the creation of the account.
> > >>>So if someone created an account 1 minute before that statement was 
> run,
> > >>>it would find that account, and delete it, if a delete statement was
> > >>>used.
> > >>>
> > >>>So probably filtering by mod_time in the userdb would be the way to 
> find
> > >>>all userdb records that have been created more than X time ago and have
> > >>>not placed an order.
> > >>All I need to figure out now is how to do it from a link on the
> > >>customer.html admin page and I'm ready to go, although with the list 
> I've
> > >>got atm (it's not terribly large at 260 usernames) I could delete them
> > >>manually and then try to stay on top of it on a monthly basis. A
> > >>clickable link in the Admin UI would be more desirable long term of
> > >>course.
> > >
> > >Actually I recommend doing it from a cron job.
> > >
> > >>If it's easy enough to exclude accounts that are less than a month old
> > >>then I'm interested in knowing how to do that as it's a better solution
> > >>than the one I currently have above.
> > >
> > >Untested, requires mysql 4.1.1 or higher:
> > >
> > >SELECT userdb.username from userdb LEFT JOIN transactions ON
> > >userdb.username=transactions.username WHERE transactions.code IS NULL AND
> > >FROM_UNIXTIME(mod_time) < SUBTIME(NOW(), '0-1-0 0:0:0')
> > >
> > >I'll leave you to figure out how to turn that into a DELETE statement.
> >
> >
> > Hi Peter,
> >
> > Thanks for your response and your suggested SELECT statement.
> >
> > Sadly I'm stuck with MySQL 3.23 and as expected the above select doesn't
> > work :(
> >
> > Failed to execute SQL : SQL SELECT userdb.username from userdb LEFT JOIN
> > transactions ON userdb.username=transactions.username WHERE
> > transactions.code IS NULL AND FROM_UNIXTIME(mod_time) < SUBTIME (NOW(),
> > '0-1-0 0:0:0'); failed :You have an error in your SQL syntax near '(NOW(),
> > '0-1-0 0:0:0')' at line 1
> >
> > Would anyone know how to get it working for 3.23 and perhaps also provide
> > the corresponding DELETE statement? MySQL knowledge is terribly limited 
> and
> > I'm already in way over my head.
>
>If you are talking about 260 usernames, from a performance standpoint it is
>not worth the trouble and the potential loss of business to remove the
>records.
>
>If you don't want to see them in the UI, why don't you just mark them
>as inactive?

Hi Mike,

Thanks for your reply.

With the kind of products we sell, if people don't checkout during their 
first visit they very rarely come back again later. If they do, it's 
usually within a few days or hours. Analysis of our logs over the last year 
or so has proven this to be true. I guess those that didn't come back found 
something more to their taste elsewhere and is a side of business that we 
all have to accept as retailers - the competition.

Anyway, I hear what you're saying about it being only 260 _complete_ user 
accounts in my DB, but I do regularly remove any that are just user & pass 
entries via the Admin UI (sorting by name puts them at the top of the 
customer list) so their absence is distorting the 260 figure significantly. 
Now that the number is starting to grow significantly (because the site has 
become more popular) I need a better way to manage them all as a whole.

If I were to follow your advice and mark these accounts as inactive, I 
would still require me to find them in the first place (the main question 
in this thread) and then mark them as inactive.

 From a performance point of view, would it not take the same time to mark 
them as inactive as it would to delete them?

Why not delete them and save longer term?

I see little point in excluding accounts from the UI view if they don't 
need to be there in the first place.

I hope this has clarified _why_ I want to delete user accounts, but my 
original question still remains, how can I find, mark off and delete user 
accounts that haven't been used to place an order and are older than XX 
days via the Admin UI?

Many thanks

Mark



Eros Shop
vwe internet ltd
PO BOX 1067
SLOUGH
SL1 7YA
UK

Shop - http://www.eros-shop.co.uk
EMail - info at eros-shop.co.uk
Tel - 0870 284 3369
Fax - 0870 284 4469