Lurkers who become members
I’m rather new to this, but boy - what a cool tool this is!
Here’s a (very simple!) qeuery I created which allows you to easily identify lurkers who became member in the last so many months.
-- [params] -- int :last_months = 2 -- int :posts_read = 1000 -- int :posts_written = 0 SELECT users.id , users.username, users.created_at, users.last_seen_at, user_stats.posts_read_count, user_stats.post_count from users inner join user_stats on users.id = user_stats.user_id where users.created_at < now() - interval ':last_months months' and user_stats.posts_read_count > :posts_read and user_stats.post_count <= : posts_written ORDER BY user_stats.posts_read_count DESC
Three parameters (fairly self-explanatory):
How many months do we want to go back for our new users to determine if indeed they are lurker (default: new accounts from the last 2 months)
How many posts must they have read before being considered a lurker (default: 1.000)
What is the maximum number of posts people can have written to be still considered a lurker (default: ))
I added the last parameter to be able to weed out people who only post ‘Hi! Nice here!’ or something along those lines, and then nothing else any more.
please note I’ve updated the script slightly to fix an issue that was spotted by @jerdog (see the post below) - thanks to J. for spotting this!