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

SQL queries to find out your forum daily stats

Discussion in 'XenForo Questions and Support' started by Andy.N, Mar 14, 2011.

  1. Andy.N

    Andy.N Well-Known Member

    In the absence of a builtin XF stats system, I'd like to monitor the activities on my site.
    Number of new registration, posts, thread per day.

    There should be some queries that can be used to get these info out. Then I can do a pre/post Xenforo to see if the new platform improves activity level.
    Thanks
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

  3. Andy.N

    Andy.N Well-Known Member

  4. Brogan

    Brogan XenForo Moderator Staff Member

    Andy.N likes this.
  5. Andy.N

    Andy.N Well-Known Member

    Pretty much but I like to have it more flexible and interactive that i can adjust date range, etc.
    And done it from the ACP. While it's not possible currently, I probably will have to query the xf_post and thread table myself
     
  6. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    I like queries.

    For registrations per day (from this post):

    Code:
    SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%m-%d') AS date
    FROM xf_user
    GROUP BY date
    ORDER BY date
    
    For posts per day:

    Code:
    SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
    FROM xf_post
    GROUP BY date
    ORDER BY date
    
    For threads per day:

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
    FROM xf_thread
    GROUP BY date
    ORDER BY date
    
    These all generate results like this:

    Screen shot 2010-12-27 at 9.17.08 AM.png
     
    dieketzer, bambua, Andy.N and 2 others like this.
  7. SchmitzIT

    SchmitzIT Well-Known Member

    I started working on a mod that basically would show similar stats as a few of Paul's vB mods do (Today's visiting members, today's registrations, etc) a few weeks ago, but have to admit I've gotten sidetracked with other projects. I have the AdminCP part ready, and mostly need to tie together the templates and queries with the settings.

    I'll see if I can somehow quickly whip something together for this. It might not be complete, or perfect, but it might serve as a start, so I can finetune it later on.
     
    Andy.N likes this.
  8. Andy.N

    Andy.N Well-Known Member

    Terrific, Jake
    Now, how we aggregate them by week/month?
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    By month:

    For registrations per month:

    Code:
    SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%m') AS date
    FROM xf_user
    GROUP BY date
    ORDER BY date
    
    For posts per month:

    Code:
    SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%m') AS date
    FROM xf_post
    GROUP BY date
    ORDER BY date
    
    For threads per month:

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m') AS date
    FROM xf_thread
    GROUP BY date
    ORDER BY date
    
     
    Mark3121, bambua and Andy.N like this.
  10. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    By week:

    For registrations per week:

    Code:
    SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%U') AS week, MIN(FROM_UNIXTIME(register_date, '%Y-%m-%d')) AS startOfWeek
    FROM xf_user
    GROUP BY week
    ORDER BY week
    
    For posts per week:

    Code:
    SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%U') AS week, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
    FROM xf_post
    GROUP BY week
    ORDER BY week
    
    For threads per week:

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%U') AS week, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
    FROM xf_thread
    GROUP BY week
    ORDER BY week
    
    These per week queries return slightly different results than the others. You will see the week number of the year, like this:

    Screen shot 2011-03-15 at 10.02.19 AM.png

    And startOfWeek isn't necessarily the first day of the week. Rather it's the earliest day of the week within the data set. So, for example, if no one posted on Sunday then startOfWeek will show the date for Monday.
     
    Andy.N and Bob like this.
  11. Bob

    Bob Well-Known Member

    Note: posted this for reference :)

    DATE_FORMAT and TIME_FORMAT Output Formats for FROM_UNIXTIME function

    format Parameter Output Format
    %r 12-hour time (hh:mm:ss (AM|PM))
    %T 24-hour time (hh:mm:ss)
    %Y Numeric year, 4 digits
    %y Numeric year, 2 digits
    %m Month with leading 0 (01, 02-12)
    %c Month without leading 0 (1, 2-12)
    %M Month name (January, February, and so on)
    %b Month name, abbreviated (Jan, Feb, and so on)
    %D Day of the month with an English suffix (1st, 2nd, and so on)
    %d Day of the month with leading 0 (00, 01, 02-31)
    %e Day of the month without leading 0 (0, 1, 2-31)
    %W Weekday name (Sunday, Monday, and so on)
    %a Weekday name, abbreviated (Sun, Mon, and so on)
    %H Hour (00, 01-23)
    %k Hour (0, 1-23)
    %h Hour (01, 02-12)
    %I Hour (01, 02-12)
    %l Hour (1, 2-12)
    %i Minutes (00, 01-59)
    %S Seconds (00, 01-59)
    %s Seconds (00, 01-59)
    %P AM or PM
    %U Week number in the year,in which Sunday is the first day of the week
    $u Week number in the year,in which Monday is the first day of the week
    %X & %V Year and week number, respectively,in which Sunday is the first day of the week
    %x & %v Year and week number, respectively,in which Monday is the first day of the week
    %j Day of year with leading 0's (001, 002-366)
    %w Weekday number (0=Sunday, 1=Monday, and so on)
    %% Literal %
     
    Andy.N likes this.
  12. Andy.N

    Andy.N Well-Known Member

    Jake,
    Your code for month and day works, but somehow the ones for week do not
     
  13. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Oh, my mistake.

    By week:

    For registrations per week:

    Code:
    SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(register_date, '%Y-%m-%d')) AS startOfWeek
    FROM xf_user
    GROUP BY yearWeek
    ORDER BY yearWeek
    
    For posts per week:

    Code:
    SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
    FROM xf_post
    GROUP BY yearWeek
    ORDER BY yearWeek
    
    For threads per week:

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
    FROM xf_thread
    GROUP BY yearWeek
    ORDER BY yearWeek
    
     
    Andy.N likes this.
  14. Andy.N

    Andy.N Well-Known Member

    The UNIXTIME is a bit off compared to the timezone my board is set at.
     
  15. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    You can add an offset where the date fields appear in the query. For example, for registrations per day:

    Code:
    SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date - 8*60*60, '%Y-%m-%d') AS date
    FROM xf_user
    GROUP BY date
    ORDER BY date
    
    That would subtract 8 hours.
     
    Andy.N likes this.
  16. Numenorean7

    Numenorean7 Member

    Did someone ever turned this into an add-on? If so, can someone point it to me, because I didn't find it yet.

    Thanks.
     
  17. Brogan

    Brogan XenForo Moderator Staff Member

    Stat's are coming in 1.1.
     
    Nix, Numenorean7 and RobParker like this.
  18. RobParker

    RobParker Well-Known Member

    We need some sneak peeks soon of 1.1 stuff soon :p
     
  19. James

    James Well-Known Member

    Kier said some more Have You Seen videos will be coming to show off some 1.1 features.
     
    RobParker likes this.
  20. Numenorean7

    Numenorean7 Member

    Thanks a bunch Brogan :)
     

Share This Page