SQL Query Help - # Posts Per Member In Month XYZ

[ProCom]

A couple of my forums have seen a HUGE drop in engagement, primarily post counts... it's making me very

I'm trying to analyze as much data as possible to determine if it's the "head" or "longtail" users that have dropped off, and why.



One thing I thought to do was to run a report of the top 20 members who were posting during the big moths and compare that to the top 20 members posting during the smaller months. Did all my members stop posting, or did I just have a handful of main members that were posting thousands of times but then stopped?

So, can someone help me write a sql query like:

Select id, username, #posts
where month = xyz
sort desc by # posts

From there I can run it on a few months and compare the results!

Ooohh... as long as I'm asking for stuff...

How about a similar query by member, by month, e.g.: Show monthly posts for member #xyz I could plug in my top 20 posting members and see when they dropped off.

Wouldn't it be interesting to discover if a handful all dropped off at the same time and then investigate why!

Thanks for any help you can provide!

 

[ProCom]

Ugh, did my greedy dumb questioning finally catch up to me? Maybe you guys don't pitty my terrible loss in posts above?

I've been digging to try to find some queries that will do the above, but it doesn't look like there are any examples that already exist. There are a lot of queries that are forum, thread, etc. specific. Overall statistics, etc., but I can't find many that are per-user specific.

The two queries I'm looking for:

1. How many times did each member post during a specific month
2. How many times did a specific member post each month

Thanks in advance for any help you guys can toss over <cough> @Brogan @Jake Bunce </cough>

 

[Brent W]

Did this correlate with your conversion or had there been a decline already? Looks like this dates back a long way...

 

[ProCom]

Totally independent of the conversions... which are only a few months old. Those declines, especially on that site started years ago. Drops have happened on 3 of my sites. Part of the attribution is:

1. Huge drama / fall out between members (want to use the query above to isolate specifically who and when) that left and went to other sites, specifically FaceBook.
   
2. Got hit by an algo change (Hummingbird) in August.

I'm seeing some comeback post-conversion, but still a LONG ways to go to get to early 2011 levels.

 

[ProCom]

I'm seeing a slight uptick in posts, but I've still got a VERY long way to go. I'd love to really isolate which users accounted for the posts during those months (using the first query) and then run a query on their monthly post counts (second query).

Any SQL masters able to put these two queries together for me please?

1. How many times did each member post during a specific month
2. How many times did a specific member post each month

 

[ProCom]

Well, I paid a guy to write these queries! I'm posting them in my attempt to "give back" to this community that helps me out with other stuff I'm doing!

The queries are below, but I discovered some VERY interesting data! I ran the first query during the peak posting months and then ran the second query on the user_id of the top posters. The results were fascinating. Some top-posting members dropped off slowly over time while some just dropped off the map. We know what happened to some, but we're going to reach out to others to figure out what happened and what we can do to either bring them back or prevent it from happening to other members (if we can).


----------Show # of posts per member for date between 1/1/2015 and 3/31/2015-----------
SELECT user_id, username, COUNT( user_id ) AS 'num'
FROM xf_post
WHERE post_date < UNIX_TIMESTAMP( '2014-12-31' )
AND post_date > UNIX_TIMESTAMP( '2013-05-15' )
GROUP BY user_id
ORDER BY `num` DESC


----------Show # of posts per month for member 1----------
SELECT FROM_UNIXTIME( post_date, '%Y-%m' ) AS 'date', COUNT( user_id ) AS 'num'
FROM xf_post
WHERE user_id = '1'
GROUP BY date
ORDER BY `date` DESC