wpmu activity reports using the blog_activity plugin

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;

select distinct from_unixtime(stamp, "%H") as hour, count(*) as numberOfComments from wp_comment_activity group by hour order by hour;

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?