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

[Stuart Wright]

It's not in the monthly stats report. Is there a way to find it?

 

[Mouth]

/admin.php?stats/monthly and 'Users Active' ?

 

[Paul B]

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.

 

[Stuart Wright]

Something like
select unique user_id from xf_user where last activity in date range type thing?

 

[borbole]

Something like
select unique user_id from xf_user where last activity in date range type thing?

Try this query:

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]

Try this query:

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]

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]

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?

 

[Paul B]

Can't you get it from GA?

 

[CTXMedia]

Stuart, this won't help with past stats but may be worth giving a try for future guest / member tracking in Google Analytics: https://xenforo.com/community/resources/differentiating-members-and-guests-in-google-analytics.2648/

I haven't tried it by stumbled across earlier today and with Brogan's mention of GA wondered if it was of interest?

Cheers,
Shaun

 

[Mike]

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.

 

[Stuart Wright]

Thanks guys. I'll try GA and put in a suggestion for XF.

 

[Jim Boy]

What you could do is run a similar query against xf_ip, assuming that you aren't getting rid of data that quickly.

 

[Jim Boy]

Stuart, this won't help with past stats but may be worth giving a try for future guest / member tracking in Google Analytics: https://xenforo.com/community/resources/differentiating-members-and-guests-in-google-analytics.2648/

I haven't tried it by stumbled across earlier today and with Brogan's mention of GA wondered if it was of interest?

Cheers,
Shaun

That's out-of-date, you should be using dimensions and analytics.js, not ga.js.