I have a MySQL table which tracks hits like so:
- id (int autoincrement)
- unique (text, unique identifier of user - basically md5'd IP)
- date (int, unix timestamp)
I know I can find the average page views per user by simply taking count(id) / count(unique), but is there any way to find a median page views per user?
Also, I feel as though my population base consists of a group of recurring users, and a lot of users who only visit once and leave. What kind of formulas can I use to create data about this? I was thinking about creating some kind of metric based on number of hits for that user times total time between first and last visit, but I'm unsure if this will really provide useful data as I don't actually have a clear picture of my traffic / revisit patterns. If anybody has other metrics they've used that work I'd really like to hear them!
I don't need the implementation to be entirely in MySQL - I'm running on a php server. The code doesn't have to be particularly efficient, as it's only called when I load my admin dashboard.
