New to the community? Start here

Purging Inactive Users

Hello,

I hope you are doing well and that you can help me with this issue. My community site swirlonthru.com is fast approaching 10,000 users. I know for a fact that at least 3,000 of those users were auto generated from a mailing list and have never logged in. I would like to go back a year and delete all users that show up as never having logged in. What is the best way to do this. Is there a msql query that could acheive this without tearing the site in two. As it stands the site is impossible to move. I tried exporting one of my backups and only half of the database made it.

This brings me to step 2 of my goal. We’d like to upgrade the theme, Is there a way we could reset wordpress except for the users, images, and buddypress. Wipe the slate clean and install the completed theme on a fresh install?

I dont know the best way to proceed. The other thought I had was take advatage of the new buddypress restful api and get someone to programatically get all the data. But whatever I do I feel like it will be out of our budget, or out of my skillset.

:slight_frown: Thanks in advance.

Mon, 12 Aug 2019 8:47:32
  • Adam
    • Support Gorilla

    Hello KeepitFabulous

    II hope you’re well today!

    WordPress doesn’t store anything like “last login” or similar information about users by default. In theory, some sort of indication could be the value of activation code stored in database but that’s unreliable because

    – user might have been added manually to the site or in some way (e.g. via some plugin) that this activation code wasn’t ever reset

    – user might have actually activated account and even use the site for some time but then stopped

    Unless user logins are specifically tracked, on purpose, there’s no reliable way for such “automated” clean-up.

    However, you’re running BuddyPress on site so that is a good news because BuddyPress does log “latest activity”. This would be useful.

    That said, I’m not that good with complex SQL queries (and it would be quite complex) so I’d rather go for PHP with this. There’s a nice piece of code to “check with BuddyPress if user is online” that could be a good foundation:

    https://tannermoushey.com/2015/05/determine-if-a-user-is-online-in-buddypress/

    I’d look into doing this:

    – setting the timeframe in that code to much longer period (e.g. entire year)

    – looping through all users in DB, checking with this or similar function if they were “active” within that timeframe

    – if it shows they were not active – either delete them or mark them somehow for deletion (add some _usermeta value that could later be used to cycle through users again to delete them; that’s just for safety as deletion is irreversible without db backup).

    This seems doable and shouldn’t even be that complex “code wise” except for one additional thing. From what you said, I understand that there’s a problem with creating full backup and there’s a huge amount of user accounts. In such case such code wouldn’t work well if it was just a PHP loop but it rather should be something run either using AJAX or even based on WP cron.

    Using some SQL query might be much faster but with that amount of users it still might not be fast enough and it doesn’t leave ANY “safe space” for any errors. So, in the end, I’d rather go the “code way” though I’m afraid it goes a bit too far into custom development and providing such code would be outside the scope of this support.

    However, I believe this way it would be doable.

    As for your second goal. By “reset” you mean like – “fresh install” (so no content, no plugins, default configuration etc) but with all the user accounts, including BuddyPress data, and with all the images?

    it’s not that simple, I’m afraid. I’d try one of these things though: setup a clean WordPress separately to the current site and do theme (and other plugins’:wink: install and configuration there and then try export users from original site and import them to the new one.

    There’s a set of plugins for that that could help (export and import):

    https://wordpress.org/plugins/members-import/

    though I admit I never tested them. But export is a “non-destructive” process and testing import on a fresh, separate site would be rather safe so I think it’s worth giving a try. Other than this, the API would be probably the best way to go – though it would require custom coding as well.

    As for media (images) – those can be exported/imported using standard WP import/export tools.

    Best regards,

    Adam

  • Adam
    • Support Gorilla

    Hello KeepitFabulous

    but it didnt recognize the correct users. It said they were all inactive. It only works if u have the correct plugin tracking the last login date

    Thanks for letting us know! It was worth a try anyway but I’m afraid there’s really no “easy way out” here because of the basic problem – that WordPress itself doesn’t “track” user logins out of the box. As I mentioned previously, I do believe that with some custom coding it would be doable since there’s BuddyPress in use on site but still – that’d be custom development. Unless you decide to actually go through all these account “manually” but with a huge number of members that seems like a lot of work :slight_frown:

    .in theory couldtnt i acheive similiar results using snapshot and restoring only the tables i need into a fresh site?

    I think this refers to that “data clean up” part, right? In theory, you could try it – though I’d strongly recommend testing with some temporary “dev” site as a target first. But I don’t think it will actually work fine, especially if the site is already complex.

    The thing is, data in WordPress (user data, content, configuration information) in that simple way that just excluding one table makes it “fresh”. User account data is in at least two tables (_users and _usermeta) but can also be spread across additional ones because plugins may record some information e.g. in _options table, in their own tables, in “_post”/”_postmeta” table and so on. The same applies to pretty much everything else – and there are relationships that could get broken this way. Such db export/import works perfectly fine if it’s a “one to one” scenario but it’s just a plain import without analyzing any data and it will not update/fix any relationships or, for example, warn you that “if you are copying this set of data you also need that set”…

    Best regards,

    Adam