Measuring forum usage trends

Discussion of non-phpBB related topics with other phpBB.com users.
Forum rules
General Discussion is a bonus forum for discussion of non-phpBB related topics with other phpBB.com users. All site rules apply.
Post Reply
v12mike
Registered User
Posts: 347
Joined: Thu Jul 09, 2015 5:03 pm

Measuring forum usage trends

Post by v12mike » Tue Aug 13, 2019 8:20 pm

If you want to know the long term tend in posts on your forum, you can run the follwing query on your mysql database and grpah the results in a spreadsheet.

Code: Select all

SELECT 
     DATE(FROM_UNIXTIME(FLOOR(post_time/2629744)*2629744)) as Date
     , COUNT(FLOOR(post_time/2629744)) as post_count 
     , COUNT(DISTINCT(poster_id)) as poster_count
  FROM my_forum_db.phpbb_posts 
  GROUP BY Date
ORDER BY Date;
Just change "my_forum_db" to your actual forum db name (and the table prefix if necessary).
This gives the number of posts and number of posters for every month since the forum database was started.
The periods are not exactly calendar months, but are exactly 1/12 of a year.

Here is a reasonably successful forum 8-)
forum1.png
Here is one where all the users migrated to facebook :roll:
forum2.png

User avatar
AmigoJack
Registered User
Posts: 5602
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Measuring forum usage trends

Post by AmigoJack » Wed Aug 14, 2019 8:46 am

A more precise way would be to group by the actual month, omitting rounding issues:

Code: Select all

SELECT date_format( from_unixtime( p.post_time ), '%Y-%m' ) AS date
     , count( p.post_id ) AS posts
     , count( DISTINCT p.poster_id ) AS users
  FROM phpbb_posts p
 GROUP BY 1
 ORDER BY 1
;
I don't trust the "reasonably successful forum" without any values - especially when multiple multiple lines don't share the same scale.

User avatar
warmweer
Registered User
Posts: 2388
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Measuring forum usage trends

Post by warmweer » Wed Aug 14, 2019 9:11 am

AmigoJack wrote:
Wed Aug 14, 2019 8:46 am
I don't trust the "reasonably successful forum" without any values - especially when multiple multiple lines don't share the same scale.
How does one define "successful".
I'm maintaining a board with ±200.000 users, 3/4 of which don't visit anymore, ±30-35 new registered members daily, 50-60 new posts daily. But when I look at the content of those posts, succesful isn't the term I would use.
The other side is my personal board with 38 members of which some 25 are and have been active the last 10 years with an average of 50 posts weekly (total). There are periods (sometimes a month) with almost no posts, but then again there peaks.
Succesful (in terms of numbers) is such a relative concept, as is "content" but I prefer to use the latter as a criterium.

Post Reply

Return to “General Discussion”