Jim Groom linked to a post by Patrick Murray-John with an interesting summary of the activity on UMWBlogs.org – and I was curious about what activity patterns are on UCalgaryBlogs.ca – so I fired up Sequel Pro and dug around in the raw data stored by the blog_activity plugin in the wp_post_activity and wp_comment_activity tables. The tables include aggregate and anonymous activity data for the last month.
There is a relatively new Reports plugin that could do much of this in an automated way, but it only supports generating activity reports for individual users or blogs, not aggregate reports.
Following is the MySQL code I ran to crunch the tables into usable data, which I then (cringingly) copied and pasted into (wincingly) MS Excel to generate tables and visuals.
Posts per Hour of Day
To get the number of posts published by hour of day, I ran this:
select distinct from_unixtime(stamp, "%H") as hour, count(*) as numberOfPosts from wp_post_activity group by hour order by hour;
Posts per Day of Week
select distinct from_unixtime(stamp, "%a") as day, count(*) as numberOfPosts from wp_post_activity group by day;
Comments per Hour of Day
select distinct from_unixtime(stamp, "%H") as hour, count(*) as numberOfComments from wp_comment_activity group by hour order by hour;
Comments per Day of Week
select distinct from_unixtime(stamp, "%a") as day, count(*) as numberOfComments from wp_comment_activity group by day;
Combining some of the data
Now that I’ve got the data out, it’s easy to combine sets to see what’s going on. Comments and Posts per Hour of Day:
and combined posts and comments per day of week:
What’s it mean?
I don’t know what it means. Mostly, I just like shiny graphs with lines that loosely correspond to something. Am I going to read anything into it? Nope. But if nothing else, it’s interesting to see that activity isn’t tightly synchronized with in-class time
Now, it’s clear that we’re nowhere NEAR the activity level of UMWBlogs, nor do we have the sustained activity (we don’t have The Reverend, after all), but I was surprised and impressed that the aggregate activity was much higher in “off” hours/days than I’d have guessed. Actual activity, outside of classroom hours. Who’d have guessed?
man,
How awesome, taking this stuff to the next level. Amazing graphs and love that you include the code for finding this stuff in the database.
What’s interesting is the scraping allows us to get all the information from sites not within the UMW Blogs database, i.e., sites that are being syndicated in through feedwordpress from other feeds. We are getting more and more of this, and I think much of our “activity” may be re-syndicated stuff to class blogs and what not, so I’m not gonna say the numbers don;t lie, because in our case they do a bit. But it is still a nice set of data and graphs to fawn over 🙂
Yep…as Jim says, I’ve got some funniness in the data due to the republishing — republished posts get counted twice. That was the trade-off to grab data from anything with an RSS feed.
I’m really interested in the fact that the posts per hour graph for you and for UMW follow the same pattern so closely. Quick rise in the morning, spike around 3pm, drop-off for dinner, then rise again before bedtime. Makes for a neat look at the patterns of behavior in the lives of our students. Not sure where else we could get a view like that. Wait, now that I’ve said that, I take it back. I’d love to compare charts of university library patronage and checkouts with these!