5 {{ upvoteCount | shortNum }}
5 {{ upvoteCount | shortNum }}

Get average hits (day, week, month) with SQL/PHP

by Texo
4 replies
Hey all

Does anybody know how to work out average hits per day, week and month based on UNIX timestamps?

Basically I want to track users time on my site and average hits for analytics (I know I can do this in Google Analytics, but I've built some blogging software for personal use and want some basic analytics). I've already worked out how to calculate average time on my site with my data.

I have a table in MySQL with the following format:

Note "start" and "end" are UNIX timestamps.
Code:
+----------------+----------------+----------------+----------------+
| start          | end            | unique_id      | id             |
+----------------+----------------+----------------+----------------+
| 1358789867     | 1358789872     | (hash here)    | 65             |
+----------------+----------------+----------------+----------------+
| 1358789966     | 1358789972     | (hash here)    | 66             |
+----------------+----------------+----------------+----------------+
| 1358789998     | 1358790003     | (hash here)    | 67             |
+----------------+----------------+----------------+----------------+
Would appreciate some input.

Cheers.
#average #day #hits #month #sql or php #week
  • Profile picture of the author Valdor Kiebach
    Are you going to log the amount of visitors in a time range or will it be the individual time for every visitor.

    Your first row in your example is a 5 minute period so are you counting x visitors per 5 minute period.
    {{ DiscussionBoard.errors[8238733].message }}
    • Profile picture of the author Texo
      I'm logging the UNIX timestamp for when they land on any page on my site, and logging the UNIX timestamp for when they leave (close the browser or navigate away, updated via AJAX), so I'm not using any time frames, simply the length of time each individual is on my site.

      To calculate the hits I know I can just use the "start" time, but I don't know how to extract the data I want or which functions could help.

      Your post is appreciated!


      Thank you.
      Signature

      Join 15,000+ other Android fans on my Facebook @ Droids R Us
      Also join me at Texo digital

      {{ DiscussionBoard.errors[8239591].message }}
    • Profile picture of the author brutecky
      Originally Posted by Valdor Kiebach View Post

      Are you going to log the amount of visitors in a time range or will it be the individual time for every visitor.

      Your first row in your example is a 5 minute period so are you counting x visitors per 5 minute period.
      If Im not mistaken UNIX times tamps are the number of seconds since 1/1/1970 so the first row is a 5 second period not a 5 minute.


      To work out averages you can

      1) take the start date / end date convert time to a time stamp with strtotime()
      2) grab all the entries with a start time within that range
      3) count the entries
      Signature
      Join over 7500 marketers on my Facebook group and also get tons of free tutorials and software to help you with your business.
      {{ DiscussionBoard.errors[8239899].message }}
      • Profile picture of the author Valdor Kiebach
        Originally Posted by brutecky View Post

        If Im not mistaken UNIX times tamps are the number of seconds since 1/1/1970 so the first row is a 5 second period not a 5 minute.
        Indeed, my mistake.
        {{ DiscussionBoard.errors[8240686].message }}

Trending Topics