Lurkers who become members

Data & reporting
1

Lurkers who become members

Hi all,

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):

  • last_months
    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)

  • posts_read
    How many posts must they have read before being considered a lurker (default: 1.000)

  • posts_written
    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!

11 Likes