• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.4 How do we find the number of logged in users every month?

Brogan

XenForo moderator
Staff member
#3
That's a cumulative total, not unique visitors.

There is no way of checking unique data in the ACP, you would have to query the database.
 

borbole

Well-known member
#5
Something like
select unique user_id from xf_user where last activity in date range type thing?
Try this query:

Code:
SELECT COUNT(*) AS logins, FROM_UNIXTIME( last_activity, '%Y-%m') AS monthly
FROM xf_user
GROUP BY monthly
ORDER BY monthly;
That should give the count of users who logged in to your forum each month.

If you are using a different prefix for your db tables other than xf_, replace it at the query above.
 

Stuart Wright

Well-known member
#6
Try this query:

Code:
SELECT COUNT(*) AS logins, FROM_UNIXTIME( last_activity, '%Y-%m') AS monthly
FROM xf_user
GROUP BY monthly
ORDER BY monthly;
That should give the count of users who logged in to your forum each month.

If you are using a different prefix for your db tables other than xf_, replace it at the query above.
Thank you. I ran that with ORDER BY monthly DESC and got
10992 2015-02
8735 2015-01
5520 2014-12
4793 2014-11
4888 2014-10
4872 2014-09
4998 2014-08
5985 2014-07
4688 2014-06
3700 2014-05
3876 2014-04
3842 2014-03
3827 2014-02
4594 2014-01

which shows a huge increase for January, but particularly for February, which we are only 10 days in to.
This data can't be correct, surely?
 

Mike

XenForo developer
Staff member
#7
The query is reasonable, but it's in how you interpret the data -- last_activity is the time they were last active. As such, if someone shows up in a particular month, they won't show up in any others. It's telling you that you had about 5000 people who last visited (signed in) in August 2014 but it doesn't tell you whether they visited in July 2014.

If you want to know the users active in any given month, you'd need to start storing additional data that could represent this; tracking the time they were last active isn't sufficient. (You could only get the data based on last activity if you run a task at the end of every month and store that result.)
 

Stuart Wright

Well-known member
#8
I think that's a pretty important statistic. It's being asked for by a potential advertiser and it's embarrassing that I don't have it.
Once we have this, though, we're going to need daily and weekly stats. Perhaps there is a more efficient way of logging activity?
 

Mike

XenForo developer
Staff member
#11
Once we have this, though, we're going to need daily and weekly stats. Perhaps there is a more efficient way of logging activity?
Well it is already tracked at a daily level but only as a total for a day. You can't aggregate that up to larger windows of time, because a user active yesterday and today is still only active once this week.

You'd need to store the specific user IDs active over the smallest time period you want to track (eg, a day) so that you could derive the value over a larger window without double counting.
 

Jim Boy

Well-known member
#14