HUGE Drop in Daily Posts - SQL Query Investigation

[ProCom]

We saw a 22% drop off in daily total posts from the 30 days prior to August 5th and the 30 days after August 5th. We did NOT see a correlating drop in visits or pageviews... just in daily posts.

Here's what we've done / are doing to try and isolate the drop in activity / posting. Hopefully this will be helpful for others trying to analyze the same!

Member Specific?
The first thing I wanted to investigate was to see if this was due to specific members, or all members. Was it a group of members that left, or maybe an update to a browser that made it harder for people to post in mobile?

I ran this following query twice... for the date range of 30 days before and after the 5th. Then I compared the stats for the top posters. It was CLEAR that a handful of members post counts had totally dropped off.
----------Show # of posts per member for date between DATES-----------
SELECT user_id, username, COUNT( user_id ) AS 'num'
FROM xf_post
WHERE post_date < UNIX_TIMESTAMP( '2017-08-20' )
AND post_date > UNIX_TIMESTAMP( '2017-07-20' )
GROUP BY user_id
ORDER BY `num` DESC

Next, I wanted to verify the data above to see when these users stopped posting as much, so I ran the following for a handful of these members. It was clear that a bunch left at the same time.
----------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

In some basic review, it seemed that a bunch of these members were young and were running super active games / chat threads, but to verify, I did the following:

This query showed the most active nodes in a date range
----------POSTS PER NODE IN DATE RANGE -------------
SELECT t4.node_id,t5.title,t4.nPost FROM
(
SELECT t3.node_id, sum(t3.n) as 'nPost' FROM
(
SELECT t1.*,t2.node_id FROM
(
SELECT thread_id,count(post_id) as 'n'
FROM xf_post
WHERE post_date < UNIX_TIMESTAMP('2017-09-08')
and post_date > UNIX_TIMESTAMP('2017-08-08')
group by thread_id
) t1, xf_thread t2
WHERE t1.thread_id=t2.thread_id
) t3
GrOup By t3.node_id
) t4, xf_node t5
WHERE t4.node_id=t5.node_id

I then took the nodes with the most activity and ran the following. This showed me that our "Games / Jokes" section had the biggest drop-off exactly where we expected it to be!
--------DAILY POSTS IN A SPECIFIC NODE IN DATE RANGE----------
SELECT t3.mydate,sum(t3.n) FROM
(
SELECT t1.*,t2.node_id FROM
(
SELECT thread_id,count(post_id) as 'n', FROM_unixtime(post_date, '%m/%d/%Y') as 'mydate'
FROM xf_post
WHERE post_date < UNIX_TIMESTAMP('2017-08-15')
and post_date > UNIX_TIMESTAMP('2017-07-15')
group by thread_id,post_date
) t1, xf_thread t2
WHERE t1.thread_id=t2.thread_id AND t2.node_id=24
) t3
GROUP BY t3.mydate;


So, we isolated who and where the dropoff happened. We're reaching out to the individuals directly to get more info on why they stopped posting and feedback on anything we can do to improve their experience.

I'd love to hear feedback on any of the above. One thing I'd like to do is to run the query "Show # of posts per month for member 1" on a daily basis in a date-range (vs. monthly). Any SQL experts know how to do that?