XF SQL Query Help: How many new members did a thread bring in?

[ProWeb]

Ok, this is a pretty tricky query... and WAY WAY over my payscale.

... if it's even possible???

We do a lot of contests, and one of the goals of these is to bring in new members.

In the past, I try to scroll through posts to see how many have a "1" next to their post count. That tells me that their very first post was this contest thread!

... but there's a problem: What if they go post 8 more times before I have a chance to note that they joined due to this thread?!?!

It would be cool to track this kind of thing via Google Analytics, but I haven't been able to figure out how to do that.

So, in the interim between nothing and finding a perfect solution, I was wondering if there was a way to do either of the following:

1) List all members where their very first post was in threadID: xyz
or
2) List all members that have under x posts AND have posted in threadID: xyz AND they joined in the past xy days

Any of you SQL and XF masters able to put one (or both) of those queries together? PLEEEEASE!

(Doug Heffernan and Karll did such a brilliant job with my last query, I'm wondering if their mad skillz could be applied here too?!?)

Thanks in advance for your help!!

 

[Mouth]

2) List all members that have under x posts AND have posted in threadID: xyz AND they joined in the past xy days

select xf_post.username from xf_post inner join xf_user on xf_user.username = xf_post.username where message_state = 'visible' and xf_user.message_count < 10 and from_unixtime(register_date) > date_sub(now(), interval 10 day) and thread_id = 12345;

 

[ProWeb]

BRILLIANT! Thanks Mouth !

I just used this to find TWO members that joined and our contest thread was their first post (and that's before we've even really promoted it outside of the community)!

I'm planning on a big promotion this weekend, so your timing is perfect.

Thanks again!!!

 

[Jeremy8]

For the first one, something like this should work. It might take a while to run if your forum has a lot of posts though.

SELECT b.user_id, b.username
FROM (
    SELECT b.user_id, MIN(b.post_id) AS min_post_id, a.thread_id
    FROM (
        SELECT user_id, thread_id
        FROM xf_post
        WHERE thread_id = 12345
        GROUP BY user_id, thread_id
        ) a
        INNER JOIN xf_post b ON a.user_id = b.user_id
    GROUP BY b.user_id, a.thread_id
    ) a
    INNER JOIN xf_user b ON a.user_id = b.user_id
    INNER JOIN xf_post c ON a.user_id = c.user_id AND a.min_post_id = c.post_id
    INNER JOIN xf_thread d ON c.thread_id = d.thread_id AND a.thread_id = d.thread_id;

 

[Mouth]

I'm planning on a big promotion this weekend

How did it go?

 

[ProWeb]

How did it go?

Well, the contest has been going really well, the promotions have been mixed (the one I did on FB was a fail), and the queries have been SUPER helpful and interesting. It's clear that people have been joining specifically because of this contest, and a few of them continue to engage, which is awesome!

Thanks for all the help guys!!!