Bubbling Up The Best New Members With SQL Queries

[ProCom]

I have a handful of threads here on xf asking about SQL queries. Well, in my quest to make the ultimate SQL query to gather data about my members (especially the "best" new members) I built this monstrosity out of cobbled-together queries.

Basically the idea is this: We all have our "Members" tab that shows data on members from "all time"... but I've found that a lot of amazing new members (especially on really old / active forums) can get overshadowed.

So, this query helps me to identify who the "best" new members are. Lots of variables can be tweaked to your liking:

SELECT
user.user_id
,user.username
,from_unixtime(user.register_date, '%m/%d/%Y') as Registered
,from_unixtime(user.last_activity, '%m/%d/%Y') as Last_Active
,user.message_count
,user.like_count,user.trophy_points
,ifnull(pc.post_count, 0) as recent_posts
,ifnull(lc.like_count, 0) as recent_likes
,ROUND(user.like_count * 100.0 / user.message_count, 1) AS all_time_like_ratio
,ROUND(lc.like_count * 100.0 / pc.post_count, 1) AS recent_like_ratio
,CONCAT('https://www.youURL.com/members/',user.user_id) AS Link

FROM xf_user user

left join (
    select count(*) as post_count, p.*
    from xf_post as p
    where post_date > unix_timestamp(date_add(now(), interval -90 day))
    group by p.user_id
) as pc on (pc.user_id = user.user_id)

left join (
    select count(like_id) as like_count, l.*
    from xf_liked_content l
    where like_date > unix_timestamp(date_add(now(), interval -90 day))
    group by content_user_id
) as lc on (lc.content_user_id = user.user_id)

WHERE user.message_count > 100 and lc.like_count > 10 and user.register_date > unix_timestamp(date_add(now(), interval -185 day))
order by recent_like_ratio desc

(that last bit adds some limits to the query so I only pull the "best" new members over the past 185 days. Feel free to strip it out completely for non-limited data.

I'm no expert at this stuff, so if you have any suggestions / fixes, let me know!

 

[grisha2217]

I don't understand, why you use specific function as unix_timestamp, concat, round and others in SQL, i don't think the sql functions are faster than php functions.
Try use similar query:

In php:
Calculates 185 days, 90 days:

$regTimeInterval = XenForo:Application::$time - 24*3600*185;
$firstPost_firstLike_TimeInterval = XenForo:Application::$time - 24*3600*90;

And use this variables in prepares queries.
Next you can use cache system of xenforo engine: for example, you can cache your data every hour
https://xenforo.com/help/cache/

$cacheId = 'cache_bestMembers'; // cache name

        $result = array(); // our result
        if ($cacheObject = XenForo_Application::getCache()) // try get cache object
        {
            if ($cache = $cacheObject->load($cacheId, true)) // if cache is available and no expired
            {
                $result = $cache; // get cached data
            }
            else
            {
                $result= $this->getBestMembers(); //get data from DB
                $cacheObject->save($result, $cacheId, array(), 3600); // and cache it on 3600 seconds (1 hour)
            }
        }

 

[Mouth]

Thanks for sharing, thats a terrific SQL.

this query helps me to identify who the "best" new members are.

Other than identify them, do you do anything else with the results list?

 

[ProCom]

I don't understand, why you use specific function as unix_timestamp, concat, round and others in SQL, i don't think the sql functions are faster than php functions.

Well, that's a simple answer: Because I'm not the one that wrote the original queries I just pieced together stuff from other people. I know just enough SQL to be dangerous... like "What does DROP TABLE do?"

Thanks for the suggested code... I'm just not sure if I'll have the wherewithal to even know how to apply / change those settings properly. If someone's willing to rewrite it with proper coding, I'm happy to test it

Other than identify them, do you do anything else with the results list?

We use the data on members that have the best "success metrics" to keep an eye on, thank, and encourage their behavior. We might send them a nice note saying we see and appreciate their efforts... and maybe even send them a gift.

Basically, we've realized it's just too easy to miss the more recent awesome members that get shadowed by the massive stats of the old members. This helps us to quantify a lot of stuff that we used to just "rely on our guts"... which might be easy with a thousand members, but not tens of thousands.

Any other thoughts on how to optimize, add-to, use this query and information?

 

[ProCom]

I'm digging up this old thread because I'd LOVE some help in how to really tease out (with data) who the "best" members are.

Obviously the easy one is a combo of # of posts and # of likes... and then post-to-like ratios.

... but I'd like to get way more nuanced / detailed than that.
What other ways can we use the tons of backend data in our DB's to tease out best-members?

 

[S Thomas]

# of mentions and being quoted
profile views
posts / month avg