1. 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?

Discussion in 'XenForo Questions and Support' started by Stuart Wright, Feb 10, 2015.

  1. Stuart Wright

    Stuart Wright Well-Known Member

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

    Mouth Well-Known Member

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

    Brogan XenForo Moderator Staff Member

    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.
     
  4. Stuart Wright

    Stuart Wright Well-Known Member

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

    borbole Well-Known Member

    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.
     
    Mouth, Stuart Wright and sinucello like this.
  6. Stuart Wright

    Stuart Wright Well-Known Member

    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?
     
  7. Mike

    Mike XenForo Developer Staff Member

    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.)
     
    HWS and borbole like this.
  8. Stuart Wright

    Stuart Wright Well-Known Member

    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?
     
  9. Brogan

    Brogan XenForo Moderator Staff Member

    Can't you get it from GA?
     
    HWS likes this.
  10. CyclingTribe

    CyclingTribe Well-Known Member

  11. Mike

    Mike XenForo Developer Staff Member

    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.
     
  12. Stuart Wright

    Stuart Wright Well-Known Member

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

    Jim Boy Well-Known Member

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

    Jim Boy Well-Known Member

Share This Page