Automatically delete old users

[Nathaniel van Diepen]

Something similar to this should be run nightly. There should be a few more checks that happen (poll votes, board/topic notifications).

delete
from smf_members
where
    is_activated = 1
    and last_login < UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH)
    and id_member not in (
        select distinct id_member
        from smf_messages
    )
    and id_member not in (
        select distinct id_member_from
        from smf_personal_messages
    )
    and id_member not in (
        select distinct id_member
        from smf_pm_recipients
    )

Another one should be run to remove users where is_activated = 0

[Nathaniel van Diepen]

@Sorunome @Geekboy1011 should we implement this?

[Sorunome]

I am against this, there is no point in deleting inactive users at all. People might make an acc but not decide to start posting until later. Implementing this would be inconvenient for the user IMO. Plus the extra data doesn't really slow down the site...

[Nathaniel van Diepen]

@Sorunome we did notice an increase in performance after the initial purge when we migrated Omnimaga to SMF 2.0 The forum also automatically sends birthday emails to users every year, so the more users the more emails we send. If a user has not posted/pm'd someone and has no poll votes, or topic notifications enabled. I'd rather not send them the emails and have them slow down any queries against the users table (Which happens a lot). We can increase the interval on the query above to lets say 1 year. If they haven't posted in a year they are probably not going to come back.

[alberthdev]

If emails are the main issue, would there be any way (either in SMF or a really easy mod) to prevent their birthday emails from being queried/sent?

And I guess if it really does come down to it, would it be possible to throw the user data into a "archived" table, and have a form of some sort to reactivate it?

User notification could be through a final email warning ("you haven't logged in for a while now, in a month you will be deactivated, if you want to reactivate use this form here", and the login page could have that notice as well.

[Nathaniel van Diepen]

@alberthdev Ideally we would send an email a week before the account removal. Another nice thing would be to do a quick email test and see which users don't have valid email addresses anymore. If they have no posts etc, and an invalid email address, I don't think we should have them left around at all. I'm not really for an archived table either. If they want their account back and to actually do things on the site, they can just recreate it. The whole point of my check is that if they haven't posted, pm'd, voted, subscribed to topics etc then they aren't actually using the account. So if after a year they aren't using the account, it's unlikely that they will even use it. As for keeping birthday emails, that is a user preference I believe.

[Nathaniel van Diepen]

New query that deletes the members who match the following:

• Members who activated over 6 months ago
• No posts
• No PMs
• Has not received a PM
• No profile comments
• No profile comments received
• No customized settings
• No polls
• Not paying us money
• No articles
• No attachments
• No article attachments
• No article comments
• No article reports
• No article ratings

delete
from smf_members
where
    is_activated = 1
    and date_registered < UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH)
    and id_member not in (
        select distinct id_member
        from smf_messages
        union
        select distinct id_member_from
        from smf_personal_messages
        union
        select distinct id_member
        from smf_pm_recipients
        union
        select distinct id_member
        from smf_profile_comments
        union
        select distinct comment_member_id
        from smf_profile_comments
        union
        select distinct id_member
        from smf_themes
        union
        select distinct id_member
        from smf_polls
        union
        select distinct id_member
        from smf_log_subscribed
        union
        select distinct id_member
        from smf_articles
        union
        select distinct id_member
        from smf_attachments
        union
        select distinct id_member
        from smf_articles_attachments
        union
        select distinct id_member
        from smf_articles_comment
        union
        select distinct id_member
        from smf_articles_creport
        union
        select distinct id_member
        from smf_articles_rating
    )

The select version of this query runs in under a second in case anybody is wondering.

[Sorunome]

How about instead of date_registered you check for last_login ? That way casually lurking people won't get deleted accidentally

[Nathaniel van Diepen]

Good point. I'll switch it to that in the example.

[Nathaniel van Diepen]

@alberthdev @Sorunome thoughts?

[Nathaniel van Diepen]

@alberthdev @Sorunome poke

[alberthdev]

@Eeems I'm fine with this method of removal - it removes users that are pretty much non-existent. Seconded with @Sorunome regarding the last_login field instead - if super old users without any activity (super long query above) haven't logged in at this point, they may very well be inactive.

That said, with the email that gets sent out, make sure that you strongly suggest posting in the introductions section. If they can't do that within 15-30 days (leaning towards longer), then their account will be purged.

[Sorunome]

maybe you should just tell them to log in? I mean, maybe people have reasons for wanting an acc and lurking?

[Nathaniel van Diepen]

@alberthdev We can update the initial agreement mentioning that accounts that are never used for anything will be removed. As well as update the initial email.

@Sorunome If they were going to lurk then they would login often right? That or they would subscribe to topics I think if they are going to register but do absolutely nothing, I don't really want to have to pay to send birthday emails to them or to maintain backups of their user.

Most recent version of the query:

delete from smf_members
where
    is_activated = 1
    and date_registered < UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH)
    and last_login < UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH)
    and id_member not in (
        select distinct id_member
        from smf_messages
        union
        select distinct id_member_from
        from smf_personal_messages
        union
        select distinct id_member
        from smf_pm_recipients
        union
        select distinct id_member
        from smf_profile_comments
        union
        select distinct comment_member_id
        from smf_profile_comments
        union
        select distinct id_member
        from smf_themes
        union
        select distinct id_member
        from smf_polls
        union
        select distinct id_member
        from smf_log_subscribed
        union
        select distinct id_member
        from smf_articles
        union
        select distinct id_member
        from smf_attachments
        union
        select distinct id_member
        from smf_articles_attachments
        union
        select distinct id_member
        from smf_articles_comment
        union
        select distinct id_member
        from smf_articles_creport
        union
        select distinct id_member
        from smf_articles_rating
    )

We currently have 167 users who would be deleted if I was to run this.